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;
At Monserrate Monastery in Bogotá, Colombia.