msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Oracle REF CURSOR and ColdFusion

So you have an Oracle function (not a procedure) that returns a REF CURSOR and you want to use that function in ColdFusion. That can be easily done using <cfstoredproc>. Here is an example:

First, let’s create a test function in Oracle:

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;
/

Now, in your ColdFusion template, you can call the function like this:

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

That’s it.

Related articles:

Filed in ColdFusion, Tips on 19 Jul 05 | Tags:


Reader's Comments

  1. |

    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. |

    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. |

    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. |

    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. |

    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. |

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

  7. |

    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. |

    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?