TK - I think this is a problem with the jdbc thinclient driver. Look at live docs on Macromedia's site (http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b17.htm).
IAt 09:27 AM 10/20/2004 -0400, you wrote: >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). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| The annual ColdFusion User Conference is being held Sat 6/26 - Sun 6/27/04 8am-5pm in the Washington DC Area. http://www.houseoffusion.com/banners/view.cfm?bannerid=44 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:181997 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=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
