Oracle stored procedures do not return result sets in the traditional sense. Consequently, it is not possible to generate a query in ColdFusion using an Oracle stored procedure.
However, Oracle allows for the return of arrays of output parameters from their stored procedures in an effort to approximate a result set. We can now leverage this functionality in the Enterprise version of ColdFusion by utilizing the MAXROWS attribute of the CFPROCPARAM tag, which takes advantage of new functionality in the native Oracle drivers.
The MAXROWS attribute of the CFPROCPARAM tag is used to specify a maximum number of array elements that will be populated by the stored procedure. The result, after execution of the stored procedure, is a one-dimensional ColdFusion array for each CFPROCPARAM tag specifying the MAXROWS attribute. Its best if the user has a general idea of the potential number of rows that the procedure will return so that an appropriate MAXROWS can be specified. Specifying a MAXROWS value that is too large needlessly utilizes memory. Conversely, specifying a MAXROWS value that is too small will result in an abbreviated result set.
In order to generate arrays of output parameters, the stored procedure on the Oracle server must have a cursor that is opened and repeatedly called through a loop in the procedure. An efficient method of wrapping up the necessary functionality is through Oracle Packages. The following example uses the Oracle sample table Emp.