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;