12 Replies Latest reply: Apr 13, 2012 7:55 AM by BKBK RSS

    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;

        • 1. Re: error when calling Oracle procedure package, please help!
          Owain North Community Member

          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.

          • 2. Re: error when calling Oracle procedure package, please help!
            BYJ_wntrsnt Community Member

            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

            • 3. Re: error when calling Oracle procedure package, please help!
              Owain North Community Member

              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.

              • 4. Re: error when calling Oracle procedure package, please help!
                BYJ_wntrsnt Community Member

                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
                • 5. Re: error when calling Oracle procedure package, please help!
                  Owain North Community Member

                  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.

                  • 6. Re: error when calling Oracle procedure package, please help!
                    BYJ_wntrsnt Community Member

                    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>

                    • 7. Re: error when calling Oracle procedure package, please help!
                      Owain North Community Member

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

                      • 8. Re: error when calling Oracle procedure package, please help!
                        Dan Bracuk Community Member

                        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>

                        • 9. Re: error when calling Oracle procedure package, please help!
                          Adam Cameron. Community Member

                           

                          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

                          • 10. Re: error when calling Oracle procedure package, please help!
                            Owain North Community Member

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

                            Dunno, never done it. 

                            • 11. Re: error when calling Oracle procedure package, please help!
                              BYJ_wntrsnt Community Member

                              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>

                              • 12. Re: error when calling Oracle procedure package, please help!
                                BKBK CommunityMVP

                                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.