Please help with getting the result back from a procedure
I'm using CF8 and Oracle 11g
I've been calling Oracle procedure many manyy times from CF <cfstoreproc with no problem.
I even get the result back from calling an Oracle Package. But since yesterday I can't get the result back from calling this str. procedure. I even get an error that says:
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver]Unhandled sql type
The error occurred in /space/users/www/mywebdir/test/cbiorpt.cfm: line 22
20 : <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#Trim(Form.Year)#"> 21 :
22 : <cfprocresult name="q_bio">
23 :
24 : </cfstoredproc>
This is how I call the procedure and try to capture the result back:
--------------------------------------------------------------------------
<cfstoredproc procedure="SP_STRpt" datasource="#Application.DSN#" returncode="yes">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#Trim(FORM.Inst)#">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="#Trim(Form.Year)#">
<cfprocresult name="q_bio">
</cfstoredproc>
<cfdump var="#q_bio#">
This is the top part of my procedure:
--------------------------------------------
create or replace PROCEDURE "SP_STRpt" (
Inst IN VARCHAR2,
year IN VARCHAR2,
q_bio OUT SYS_REFCURSOR)
AS
BEGIN
sql script here ...
RETURN;
END;
When I called this procedure right from SQLDeveloper using this call (see below), it works & I got all the results I wanted but when I use cfstoreproc tag I got that error even when I hardcoded the IN values:
This script works on SQL Developer:
------------------------------------------
variable x refcursor;
execute SP_STRpt (year => '1291', Inst => 'PP', q_bio => :x)
print x;
This doesn't and produce the unhandled error:
--------------------------------------------------
<cfstoredproc procedure="SP_STRpt" datasource="#Application.DSN#" returncode="yes">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="PP">
<cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" value="1291">
<cfprocresult name="q_bio">
</cfstoredproc>
