Skip navigation
Currently Being Moderated

error when calling Oracle procedure package, please help!

Apr 3, 2012 8:53 AM

This is the 1st. time I'm working with Oracle 11 and ColdFusion 8.

It used to be very easy when I did it in MSSQL, pretty straight forward but in Oracle I need to create a package for a str. procedure that returns a query result

So I created a package procedure in oracle successfully but when calling it from CF it threw me error and I have no clue as what the error is refering to.

(I googled and found similar problem, I followed and still did not work)

Can anyone shed some light please?

 

I called it this way and it threw error at me:

 

<cfstoredproc procedure="DUP_PKG.DUP_NEW" datasource="#Trim(application.dsn)#" returncode="True"> 

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="ins_name" value="#Trim(session.instname)#">

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="aca_year" value="#Trim(ayr)#">

              <cfprocresult name="MyResult">   <------------------ ERROR POINTING TO THIS LINE          

</cfstoredproc>

 

OR

 

If I did it this way:

<cfstoredproc procedure="DUP_PKG.DUP_NEW" datasource="#Trim(application.dsn)#" returncode="True"> 

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="ins_name" value="#Trim(session.instname)#">

              <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="aca_year" value="#Trim(ayr)#">

              <cfprocparam type="Out" cfsqltype="CF_SQL_VARCHAR" variable="MyResult">  <------------------ ERROR POINTING TO THIS LINE          

</cfstoredproc>

 

The error was:

Error Executing Database Query.

[Macromedia][Oracle JDBC Driver]Unhandled sql type

 

My package looks like this:

---------------------------------------

 

create or replace
PACKAGE DUP_PKG
AS

  TYPE mypkg IS REF CURSOR;

  PROCEDURE DUP_NEW (ins_name IN VARCHAR2, aca_year IN VARCHAR2, MyResult OUT mypkg);

END;

 

 

 

create or replace
PACKAGE BODY DUP_PKG
AS

PROCEDURE DUP_NEW (ins_name IN VARCHAR2,aca_year IN VARCHAR2, MyResult OUT mypkg)

IS
  BEGIN

 

// a bunch of INSERT statements

 

    INSERT INTO....

 

 

    INSERT INTO....

  

    etc

 

//After all insert are done, get some data using select statement and return the query result to the calling CF codes for further processing

 

   OPEN MyResult FOR

 

      SELECT columns

      FROM a table

      WHERE .....

 

  END;

 

END;

 
Replies
  • Currently Being Moderated
    Apr 3, 2012 9:20 AM   in reply to BYJ_wntrsnt

    Yup, you can't return a ref cursor from a stored procedure into ColdFusion as far as I know - CF opens a connection, gets data, closes connection. It has no way to then utilise that ref cursor any more through a probably closed connection.

     

    What is it you're trying to return? Other than a ref cursor, obviously.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 4, 2012 1:43 AM   in reply to BYJ_wntrsnt

    Yeah, what you'll find is that MSSQL stored procedures can easily return query objects, whereas Oracle ones cannot. What I would do is remove the select statement from the stored procedure, assuming that's possible. Then, within one cftransaction to maintain integrity, call the stored proc then the query. So like this:

     

    <cftransaction>

      <cfstoredproc ...>

      <cfquery>

        SELECT ...

      </cfquery>

    </cftransaction>

     

    Annoying I know, but personally I've never liked using stored procedures to return query objects, feels a bit wrong to me.

     

    Hope that helps.

    O.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 4, 2012 2:12 PM   in reply to BYJ_wntrsnt

    That's an ORA error, so nothing to do with ColdFusion and I'd guess it'd error just the same in SQLPlus. You can certainly do more than one outer join though.

     

    Try using the proper ANSI syntax for joins rather than the horrible old Oracle syntax, you can join all you like.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 5:43 AM   in reply to BYJ_wntrsnt

    Same error as before, so it's probably cached the package description. Try restarting CF.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 5:57 AM   in reply to Owain North

    Regarding:  Yeah, what you'll find is that MSSQL stored procedures can easily return query objects, whereas Oracle ones cannot.

     

    Really?  I've never been in a position to know that.  What would happen if you called the sp from a cfquery tag instead of a cfstoredproc tag?  Something like

     

    <cfquery>

    execute procedure abc (param1, param2, etc)

    </cfquery>

    
     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 6:24 AM   in reply to Owain North

     

    Yeah, what you'll find is that MSSQL stored procedures can easily return query objects, whereas Oracle ones cannot.

     

    Eh?

     

    Sure they can.


    Well I ain't touched Oracle f'r'ages, Oracle 9i with the default DataDirect drivers shipped with CF8 sure could.

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 6:35 AM   in reply to Dan Bracuk

    What would happen if you called the sp from a cfquery tag instead of a cfstoredproc tag?

    

    Dunno, never done it. 

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 13, 2012 7:55 AM   in reply to BYJ_wntrsnt

    BYJ_wntrsnt wrote:

     

    Finally!!!

    I got rid of the returncode attribute and it works!

    Thank you for helping everyone! this has bene quite a learning process. Your support is very valuable! thanks again

     

    <cfstoredproc procedure="DUP_PKG.DUP_NEW" datasource="#Trim(application.dsn)#" returncode="True"> 

                  <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="ins_name" value="#Trim(session.instname)#">

                  <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" variable="aca_year" value="#Trim(ayr)#">

                  <cfprocresult name="MyResult">   <------------------ ERROR POINTING TO THIS LINE          

    </cfstoredproc>

    

    Then please kindly mark this as the answer. It will certainly help someone else.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points