Problem returning REF CURSOR from Oracle

[atommy06]

Hello everyone, I have a problem when trying to return a REF CURSOR from an Oracle 9i database. The version of my ColdFusion is the number 6. The error message is ".... sqlType = 2006 ....". I leave my code here. Please someone help me. Thank you. Tommy.

ColdFusion code:

<cfstoredproc procedure="OracleTest.sel_user" datasource="test_ora_dsn">
<cfprocparam type="Out" cfsqltype="CF_SQL_REFCURSOR" variable="param1">
<cfprocresult name="qry_sel_user">
</cfstoredproc>


PL/SQL code:

CREATE OR REPLACE PACKAGE OracleTest
AS
TYPE user_cursor IS REF CURSOR
RETURN Users%ROWTYPE;
PROCEDURE sel_user (
results OUT user_cursor;

END OracleTest;

CREATE OR REPLACE PACKAGE BODY OracleTest
AS
PROCEDURE sel_user (
results OUT user_cursor
)
AS
BEGIN
OPEN results FOR
SELECT *
FROM Users;
END sel_user;
END OracleTest;

[paross1]

When returning result sets from Oracle to ColdFusion, you do NOT include an OUT cfprocparam tag, as the ref cursor returned is actually captured in the cfprocresult tag. Drop the cfprocparam.

<cfstoredproc procedure="OracleTest.sel_user" datasource="test_ora_dsn">
<cfprocresult name="qry_sel_user">
</cfstoredproc>

Make sure that you are NOT using an Oracle Thin client connection, as returning result sets in this manner is not supported.

See the LiveDocs for cfprocparam, specifically this note:

To use reference cursors in packages or stored procedures, use the cfprocresult tag. This causes Datadirect JDBC to put Oracle reference cursors into a result set. (You cannot use this method with Oracle's ThinClient JDBC drivers.)

Phil

[atommy06]

What is the driver that I must use ????. How can I configure ??????

[paross1]

The ColdFusion Enterprise edition Oracle datasource, or perhaps Oracle ODBC. However, if you are using ColdFusion Standard edition and you are using Oracle's JDBC ThinClient, you won't be able to return result sets with reference cursors.

See Configuring the Oracle JDBC thin driver.

Note: Oracle Ref Cursors are only supported with the DataDirect Technologies Oracle JDBC Driver

Phil