I am running into problems when trying to use Oracle 10g stored procedure that returns
a cursor. I have done a bit of research on the topic and so far non of the methods
that where shown work. I am sure many of you use Oracle and CF 6.1 without any
problems, thus I am sure someone will catch the mistake I have in my code.
Oracle Stored procedure: (I just changed table names and the query a bit � it works
fine from SQL Plus so there is no problem with the data the stored procedure returns,
it compiles and runs fine):
CREATE OR REPLACE PACKAGE pkg_userservice
IS TYPE RC_GENERIC IS REF CURSOR;
PROCEDURE p_gettabs ( ag_id_in INT,
getTabRefCur IN OUT RC_GENERIC );
END pkg_userservice;
/
CREATE OR REPLACE PACKAGE BODY pkg_userservice
IS
PROCEDURE p_gettabs ( ag_id_in INT,
getTabRefCur IN OUT RC_GENERIC )
IS
BEGIN
OPEN getTabRefCur FOR
' SELECT d.agf_id, '
||' g.agf_id p_id, '
||' FROM bbb.acc_grp c '
||' INNER JOIN bbb.acc_grp_func d ON (c.ag_id = :ag_id_in) '
||' ORDER BY d.P_AGF_ID desc
USING ag_id_in;
EXCEPTION
WHEN others THEN
NULL;
END p_gettabs;
END pkg_userservice;
Here is my CF code that calls above stored procedure:
cfset ag_id_in = 100>
<CFSTOREDPROC PROCEDURE="jmelnick.pkg_userservice.p_gettabs" DATASOURCE="umap">
<cfprocparam type="In" cfsqltype="CF_SQL_DECIMAL" dbvarname="ag_id_in"
value="#ag_id_in#">
<!--- <cfprocparam type="Out" cfsqltype="cf_sql_refcursor"
variable="searchResults"> --->
<CFPROCRESULT NAME="getTabRefCur">
</CFSTOREDPROC>
<CFDUMP VAR="#getTabRefCur#">
If I run above as it is now I get:
ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to
'P_GETTABS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
If I include �out� parameter but no result or �out� and result I get this error:
Parameter Type Conflict: sqlType=2006
I am using type definitions from http://www.blinex.com/~sam/CF_SQL_TYPES.cfm
Through I played around and changed them a bit just in case.
I am using :
jdbc:oracle:thin:@(description=(address=(protocol=tcp)(host=dbdev)(port=1521))(connect_data=(sid=dbdev)))
To connect to the db server
Stored procedure execution is enabled on the server and user executing the stored
procedure has full right to it (grant all).
Any ideas what I am doing wrong? Anyone has a working example for ColdFusion MX 6.1?
Thank you for your help,
TK
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the mailserver that powers this list at
http://www.houseoffusion.com/banners/view.cfm?bannerid=17
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181990
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
