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.


Possibly related:


Tagged | Post a Comment