So it’s a known issue that Coldfusion does not support returning a ref cursor as an out parameter of a stored procedure when using the Oracle Thin Client drivers. Since I have experienced this problem in the past I thought I would post a solution that uses C# to manage the Oracle stored procedure and return the result set to Coldfusion. So this solution will use Coldfusion’s ability to create object instances of a C# class using the ‘cfobject’ tag or the ‘createObject()’ function. Also keep in mind that if Coldfusion is returned a type DataTable from the C# method it will automatically convert it into a Coldfusion Query object that can then be handled like normal. So with this in mind let’s take a quick look at a basic Oracle stored procedure and the C# class that will execute the procedure and return a DataTable object.

Oracle Stored Procedure

create or replace
PROCEDURE "SP_TEST_CF9"
(
      arg_ArgumentOne IN NVARCHAR2,
      l_cursor OUT sys_refcursor
)
AS 

BEGIN

      OPEN l_cursor FOR
      SELECT column_name, column_id
      FROM  column_table
      WHERE column_argument_one = arg_ArgumentOne;

END SP_TEST_CF9;

(more…)