8 Replies Latest reply: Mar 18, 2011 7:31 AM by Adam Cameron. RSS

    Calling an Oracle Stored Procedure.

    ilssac Community Member
    ilssac Mar 17, 2011 4:19 PM

      Stored procedures have always given me trouble, I'm not sure why.

       

      I want to call an Oracle Stored Procedure from a piece of CFML code.

       

      My CFML code:

      <cfstoredproc datasource="purload" procedure="PUR_XML_LOAD.load_raw" returncode="yes">
    <cfprocparam cfsqltype="CF_SQL_FLOAT" value="999" type="out">
</cfstoredproc>

       

      The error I am getting:

      [Macromedia][Oracle JDBC Driver][Oracle]ORA-06550: line 1, column 18:
      PLS-00306: wrong number or types of arguments in call to 'LOAD_RAW'
      ORA-06550: line 1, column 7:
      PL/SQL: Statement ignored

       

       

      Test PL/SQL code that works in Oracle SQL Developer:

      DECLARE 
 foo NUMBER :=999;
begin
  PUR_XML_LOAD.load_raw (foo);
  dbms_output.put_line('>' || foo || '<');
end;

       

      The top of the stored procedure:

      create or replace
PACKAGE BODY PUR_XML_load AS


   -- *** PROCEDURE: PUR_XML_load ***
   -- Loads values from previously-validated XML file
   -- into the raw_pur table
   PROCEDURE Load_raw (res_val OUT NUMBER)
   AS

       

      So how can I make the CFML code function the same way as the PL/SQL code?

       

      TIA

      Ian

      • 914 Views
      • Tags:
        • 1. Re: Calling an Oracle Stored Procedure.
          Dan Bracuk Community Member
          Dan Bracuk Mar 17, 2011 6:28 PM (in response to ilssac)

          Make sure you have the correct value for the type attribute in your cfprocparam tag.

          • 2. Re: Calling an Oracle Stored Procedure.
            ilssac Community Member
            ilssac Mar 18, 2011 6:29 AM (in response to Dan Bracuk)

            I've tried all three types, 'In, Out, InOut".  I get the same error for each one.

             

            This does not seem to be that hard.  There is only one paramter and it is a 'Number' type, which the chart tells me should be equivalent to "CF_SQL_FLOAT".

            • 3. Re: Calling an Oracle Stored Procedure.
              Adam Cameron. Community Member
              Adam Cameron. Mar 18, 2011 6:37 AM (in response to ilssac)

              I hate to say it, Ian, but RTFM!

               

              You've specified it as an OUT param, but you're not saying what variable to put the result into.  And you're passing a value IN to it.

               

              Don't you want something like:

               

              <cfprocparam type="out" variable="myVar" cfsqltype="as apporpriate">

               

              I'm surprised CF ain't just syntax-erroring on that (this in itself is a bug, in my view), even before passing it to JDBC.

               

              --

              Adam

              • 4. Re: Calling an Oracle Stored Procedure.
                ilssac Community Member
                ilssac Mar 18, 2011 6:41 AM (in response to ilssac)

                All right:

                 

                This WORKS:

                <cfquery datasource="purload" name="foobar">
   DECLARE 
     foo NUMBER :=999;
   begin
     PUR_XML_LOAD.load_raw (foo);
   end;        
</cfquery>

                 

                 

                This DOES NOT WORK:

                <cfstoredproc datasource="purload" procedure="PUR_XML_LOAD.load_raw" returncode="yes">
    <cfprocparam cfsqltype="CF_SQL_FLOAT" value="999" type="in|out|inout">
</cfstoredproc>

                 

                Yes, I mean each of the three values for type does not work, not that I tried to put all three values into the type parameter at the same time.

                 

                So what is the difference?

                • 5. Re: Calling an Oracle Stored Procedure.
                  ilssac Community Member
                  ilssac Mar 18, 2011 6:50 AM (in response to Adam Cameron.)

                  Adam Cameron. wrote:

                   

                  I hate to say it, Ian, but RTFM!

                   

                  Adam

                   

                  To be fair, I had the Manual open in the tab preceding this tab.

                   

                  Adding the variable property did not change the behavior.  I am still getting the same error.  I tried as many permutations of which I could think.

                  • 6. Re: Calling an Oracle Stored Procedure.
                    Adam Cameron. Community Member
                    Adam Cameron. Mar 18, 2011 7:13 AM (in response to ilssac)

                    OK, maybe you're RTFMing, but maybe RWAS (Read What Adam Said) ;-)

                     

                     

                    Does THIS work:

                     

                    <cfstoredproc datasource="purload" procedure="PUR_XML_LOAD.load_raw">
                        <cfprocparam cfsqltype="CF_SQL_FLOAT" variable="myresult" type="out">
                    </cfstoredproc>

                     

                    ?

                     

                    --

                    Adam

                    • 7. Re: Calling an Oracle Stored Procedure.
                      ilssac Community Member
                      ilssac Mar 18, 2011 7:24 AM (in response to Adam Cameron.)

                      Ok Adam, now WHY does that work?

                       

                      How is that equivalent to the PL/SQL code I was trying to replicate?

                       

                      DECLARE 
                       foo NUMBER :=999;
                      begin
                        PUR_XML_LOAD.load_raw (foo);
                        dbms_output.put_line('>' || foo || '<');
                      end;

                       

                      Looking at that code, it seems to be passing IN a value, is it not?

                      • 8. Re: Calling an Oracle Stored Procedure.
                        Adam Cameron. Community Member
                        Adam Cameron. Mar 18, 2011 7:31 AM (in response to ilssac)

                        You're not really passing anything in, you're providing a variable name to put a result in.

                         

                        Think of it like the NAME attribute of a CFQUERY tag, eg:

                         

                        <cfquery name="q">

                         

                        You're not passing q in, you're telling it to put the result into q.

                         

                        --

                        Adam