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:
- Producing XML from SQL using cursor expressions
- XML configuration files used in ColdFusion MX
- ColdFusion + AJAX = CFAjax
- Read XML from an HTTP POST
- See How To Hack Oracle Using Dangling Cursor Snarfing
Tagged cursor | Post a Comment
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).
September 7th, 2007, at 9:10 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.
September 7th, 2007, at 9:35 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).
September 7th, 2007, at 10:30 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.
September 7th, 2007, at 10:39 am #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.
September 7th, 2007, at 1:25 pm #You may have to update the macromedia_drivers.jar file. The following TechNote has more information: http://www.adobe.com/go/42dcb10a
September 7th, 2007, at 4:27 pm #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.
September 11th, 2007, at 11:37 am #