error when calling Oracle procedure package, please help!

[BYJ_wntrsnt]

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;

[Owainnorth]

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.

[BYJ_wntrsnt]

Hi! I'm translating from an existing codes.

The existing CF codes is calling MSSQL procedure.

In this str. Proc., there are many insert statements and 1 select statement at the end.

This last select statement returns a query result back to CF8 : <cfprocresult name="MyResult"> and CF use this query result for the rest of the processes

So I imitated the same logic but now I have to make CF8 talk to Oracle 11

since Oracle does not return query result, from googling some postings I created a package using the logic described in existing MSSQL

So my CF8 need to call this package and expecting a query result back. So I'm trying to return the query result from the last select statement in the package

[Owainnorth]

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.

[BYJ_wntrsnt]

I tried your suggestion but I got another rediculous error which I'm not sure what to do. A few of the queries in this procedures are written with outer join and Oracle said Oracle can't do ourter join for more than 2 tables???? seriously??

Script like this is causing the error and I'm not sure what shoud I do

    Select some columns

    FROM table_1 d1, table_2 d2, table_3 d3, table_4 d4         
    WHERE d1.id (+)= d2.id
    AND d1.id (+)= d3.id
    AND d1.id (+)= d4.id)

Error Executing Database Query.

[Macromedia][Oracle JDBC Driver][Oracle]ORA-01417: a table may be outer joined to at most one other table

[Owainnorth]

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.

[BYJ_wntrsnt]

You are right, I modified the codes and got it to work on Oracle

But when I did your suggestion:

<cftransaction>

  <cfstoredproc ...>

  <cfquery>

    SELECT ...

  </cfquery>

</cftransaction>

I got the same error as before which is:

Error Executing Database Query.

[Macromedia][Oracle JDBC Driver]Unhandled sql type

But this time it points to the CF callling the str proc using cfprocparam

Here is how I call the str. proc:

     <CFTRANSACTION>   
          <cfstoredproc procedure="MyReport" 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)#"> <------- POINT TO THIS LINE

                      
          </cfstoredproc>
 
     </CFTRANSACTION>

[Owainnorth]

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

[Dan_Bracuk]

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>

[Owainnorth]

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

Dunno, never done it. 

[Adam Cameron.]

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

[BYJ_wntrsnt]

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>

[BKBK]

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.