Skip to main content
Known Participant
November 13, 2012
Question

Please help with getting the result back from a procedure

  • November 13, 2012
  • 2 replies
  • 978 views

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>

This topic has been closed for replies.

2 replies

BKBK
Community Expert
Community Expert
November 14, 2012

From your above query q_bio is a parameter of type OUT. I therefore expected to see something like

<cfprocparam type="out" variable="q_bio">

<cfprocresult name="res">

aleckenAuthor
Known Participant
November 13, 2012

DO NOT Use the RETURNCODE="yes" and it is working!! what is this returncode do? it seems to annoy more so far!