Oracle REF CURSOR and ColdFusion

create or replace 
package test_pkg
as
    type cursorType 
     is ref cursor;

    function test_func 
     return cursorType;

end;
/

create or replace 
package body test_pkg
as

   function test_func 
    return cursorType
   as
      cursor_v   cursorType;
   begin
        /* 
        Put whatever SQL and/or processing here.
        I use the Oracle dictionary table 
        all_objects as an example.
        */
       open cursor_v 
         for 
         select object_id, 
                object_type, 
                  object_name 
         from all_objects 
         where rownum <= 15;

       return cursor_v;

   end test_func;

end test_pkg;
/

<cfstoredproc 
 procedure="test_pkg.test_func" 
 datasource="yourDSN">
    <cfprocresult name="q">
</cfstoredproc> 
<cfdump var="#q#">

Reader's Comments

1. Jeff Dugas | 07 Sep 2007 9:10 am

   Is there a way to do the same thing via a stored procedure, returning the ref cursor as an output parameter? I have tried converting this function into a sproc with one output parameter with no luck (using Oracle 10g and CF7).

2. Eddie Awad | 07 Sep 2007 9:35 am

   Yes. In fact, we do that all the time. I’m not sure what you mean by “no luck”. Here is an example of a procedure returning a weak ref cursor:

   CREATE OR REPLACE PACKAGE test_pkg
   AS
      PROCEDURE test_proc (out_cur OUT sys_refcursor);
   END;
   /
   
   CREATE OR REPLACE PACKAGE BODY test_pkg
   AS
      PROCEDURE test_proc (out_cur OUT sys_refcursor)
      AS
      BEGIN
         OPEN out_cur FOR
            SELECT object_id, object_type, object_name
              FROM all_objects
             WHERE ROWNUM < = 15;
      END test_proc;
   END test_pkg;
   /
   

   you would then use the same ColdFusion code to call the procedure and access its returned query.

3. Jeff Dugas | 07 Sep 2007 10:30 am

   I created the package you provided as an example, and used this CF7 code, having provided a value for myDSN elsewhere.

   The exception’s Detail is: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘TEST_PROC’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored

   Adding a cfprocparam tag that defines the output parameter does not fix the problem (it causes Parameter Type Conflict: sqlType=2006).

   So, no luck yet. After doing some further reading, I suspect this could be a problem with the driver we’re using (Oracle JDBC thin driver).

4. Jeff Dugas | 07 Sep 2007 10:39 am

   Yikes, my CF code got dropped when I posted. No matter, it looked exactly like what you had originally, except I changed the procedure attribute to match the package procedure, used a variable for the DSN, and I wrapped it in a try/catch to dump the exception.

5. Jeff Aust | 07 Sep 2007 1:25 pm

   Hmmm? I get the same error as the other Jeff as well. This has been bugging the heck out of me for the past few days because I have a proc I’d like to use in this fashion.

6. Eddie Awad | 07 Sep 2007 4:27 pm

   You may have to update the macromedia_drivers.jar file. The following TechNote has more information: http://www.adobe.com/go/42dcb10a

7. Jeff Aust | 11 Sep 2007 11:37 am

   Yes, indeed, it was as you recommended. Thanks for the pointer!

   Just to add to the note my stored procedure is not contained within a package. Not that I have anything against packages, mind you.

8. Aruna | 16 Dec 2008 1:03 pm

   I want to lock the rows for update, while doing an update from cold fusion. My back end is oracle. Do you know how to use the select for update in procedures that I can call in CF?