11 Replies Latest reply on Oct 15, 2018 2:25 PM by Dejan T.

    Stored Procedure Question.

    3430959

      Hi All,

       

      Hope you are doing great.

       

      I am new to Stored Procedure's. We have a basic requirement to create a stored procedure in Oracle DB which takes 1 parameters as Input and returns 7 Parameter as OUTPUT.

       

      We have tried 2 ways. Both compiled successfully but when we try to execute the Procedure, it errors.

       

      Stored Procedure(Version-1):

      CREATE OR REPLACE PROCEDURE sysadm.HK_KFX_PO_SP

      (

        PO_NUMBER IN VARCHAR2

      , LINE_NUM OUT NUMBER

      , SCHED_NUM OUT NUMBER

      , LINE_DESCR OUT VARCHAR2

      , LINE_UOM OUT VARCHAR2

      , LINE_UNIT_PRICE OUT NUMBER

      , LINE_QUANTITY OUT NUMBER

      , LINE_MERCH_AMOUNT OUT NUMBER

      ) AS

      BEGIN

       

        SELECT PL.LINE_NBR

      , PS.SCHED_NBR

      , PL.DESCR254_MIXED

      , PL.UNIT_OF_MEASURE

      , PS.PRICE_PO

      , PS.QTY_PO

      , PS.MERCHANDISE_AMT

       

      INTO

         LINE_NUM

      , SCHED_NUM

      , LINE_DESCR

      , LINE_UOM

      , LINE_UNIT_PRICE

      , LINE_QUANTITY

      , LINE_MERCH_AMOUNT

       

        FROM PS_PO_HDR PH

        , PS_PO_LINE PL

        , PS_PO_LINE_SHIP PS

       

      WHERE PH.BUSINESS_UNIT = PL.BUSINESS_UNIT

         AND PH.PO_ID = PL.PO_ID

         AND PL.BUSINESS_UNIT = PS.BUSINESS_UNIT

         AND PL.PO_ID = PS.PO_ID

         AND PL.LINE_NBR = PS.LINE_NBR

         AND PH.PO_STATUS IN ('A', 'D')

         AND PL.CANCEL_STATUS <> 'X'

         AND PH.PO_ID = PO_NUMBER;

      END HK_KFX_PO_SP;

       

       

      Stored Procedure(Version-2):

      CREATE OR REPLACE PROCEDURE HK_KFX_PO_SP

      (

      PO_NUMBER IN varchar2,

      b_cursor OUT sys_refCURSOR

      ) AS

      BEGIN

      OPEN b_cursor FOR

      SELECT PL.LINE_NBR "LINE_NUM"

      , PS.SCHED_NBR "SCHED_NUM"

      , PL.DESCR254_MIXED "LINE_DESCR"

      , PL.UNIT_OF_MEASURE "LINE_UOM"

      , PS.PRICE_PO "LINE_UNIT_PRICE"

      , PS.QTY_PO "LINE_QUANTITY"

      , PS.MERCHANDISE_AMT "LINE_MERCH_AMOUNT"

       

        FROM PS_PO_HDR PH

        , PS_PO_LINE PL

        , PS_PO_LINE_SHIP PS

       

      WHERE PH.BUSINESS_UNIT = PL.BUSINESS_UNIT

         AND PH.PO_ID = PL.PO_ID

         AND PL.BUSINESS_UNIT = PS.BUSINESS_UNIT

         AND PL.PO_ID = PS.PO_ID

         AND PL.LINE_NBR = PS.LINE_NBR

         AND PH.PO_STATUS IN ('A', 'D')

         AND PL.CANCEL_STATUS <> 'X'

         AND PH.PO_ID = PO_NUMBER;

      END HK_KFX_PO_SP;

       

       

      when we execute the stored Procedure's, we are receiving the same error.

      ORA-06550: Line-1, Column 7

      PLS-00306:wrong number or type of arguments in call to HK_KFX_PO_SP

       

      Any help is highly appreciated.

        • 1. Re: Stored Procedure Question.
          Cookiemonster76

          So your code to call the procedure is wrong.

          Post that code.

          • 2. Re: Stored Procedure Question.
            mathguy

            In both cases (I assume), you are calling the procedure with a single argument, for the IN parameter. That's not how procedures with OUT parameters work. You need to create the variables to hold the OUT values produced by the procedure(s), and pass the names of those variables to the procedure(s) when you call it/them.

            • 3. Re: Stored Procedure Question.
              3430959

              Thanks for the quick response.

              Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

              I only has 1 input Parameter(PO_NUMBER).

              That's why i have passed 1 input variable.

              SET SERVEROUTPUT ON;
              EXEC sysadm.HK_KFX_PO_SP('GEN0000146');

              Correct me, if i am wrong.

              • 4. Re: Stored Procedure Question.
                Frank Kulash

                Hi,

                3430959 wrote:

                 

                Hi All,

                 

                Hope you are doing great.

                 

                I am new to Stored Procedure's. We have a basic requirement to create a stored procedure in Oracle DB which takes 1 parameters as Input and returns 7 Parameter as OUTPUT.

                 

                We have tried 2 ways. Both compiled successfully but when we try to execute the Procedure, it errors.

                 

                Stored Procedure(Version-1):

                CREATE OR REPLACE PROCEDURE sysadm.HK_KFX_PO_SP

                (

                PO_NUMBER IN VARCHAR2

                , LINE_NUM OUT NUMBER

                , SCHED_NUM OUT NUMBER

                , LINE_DESCR OUT VARCHAR2

                , LINE_UOM OUT VARCHAR2

                , LINE_UNIT_PRICE OUT NUMBER

                , LINE_QUANTITY OUT NUMBER

                , LINE_MERCH_AMOUNT OUT NUMBER

                ) AS

                BEGIN

                 

                SELECT PL.LINE_NBR

                , PS.SCHED_NBR

                , PL.DESCR254_MIXED

                , PL.UNIT_OF_MEASURE

                , PS.PRICE_PO

                , PS.QTY_PO

                , PS.MERCHANDISE_AMT

                 

                INTO

                LINE_NUM

                , SCHED_NUM

                , LINE_DESCR

                , LINE_UOM

                , LINE_UNIT_PRICE

                , LINE_QUANTITY

                , LINE_MERCH_AMOUNT

                 

                FROM PS_PO_HDR PH

                , PS_PO_LINE PL

                , PS_PO_LINE_SHIP PS

                 

                WHERE PH.BUSINESS_UNIT = PL.BUSINESS_UNIT

                AND PH.PO_ID = PL.PO_ID

                AND PL.BUSINESS_UNIT = PS.BUSINESS_UNIT

                AND PL.PO_ID = PS.PO_ID

                AND PL.LINE_NBR = PS.LINE_NBR

                AND PH.PO_STATUS IN ('A', 'D')

                AND PL.CANCEL_STATUS <> 'X'

                AND PH.PO_ID = PO_NUMBER;

                END HK_KFX_PO_SP;

                 

                 

                Stored Procedure(Version-2):

                CREATE OR REPLACE PROCEDURE HK_KFX_PO_SP

                (

                PO_NUMBER IN varchar2,

                b_cursor OUT sys_refCURSOR

                ) AS

                BEGIN

                OPEN b_cursor FOR

                SELECT PL.LINE_NBR "LINE_NUM"

                , PS.SCHED_NBR "SCHED_NUM"

                , PL.DESCR254_MIXED "LINE_DESCR"

                , PL.UNIT_OF_MEASURE "LINE_UOM"

                , PS.PRICE_PO "LINE_UNIT_PRICE"

                , PS.QTY_PO "LINE_QUANTITY"

                , PS.MERCHANDISE_AMT "LINE_MERCH_AMOUNT"

                 

                FROM PS_PO_HDR PH

                , PS_PO_LINE PL

                , PS_PO_LINE_SHIP PS

                 

                WHERE PH.BUSINESS_UNIT = PL.BUSINESS_UNIT

                AND PH.PO_ID = PL.PO_ID

                AND PL.BUSINESS_UNIT = PS.BUSINESS_UNIT

                AND PL.PO_ID = PS.PO_ID

                AND PL.LINE_NBR = PS.LINE_NBR

                AND PH.PO_STATUS IN ('A', 'D')

                AND PL.CANCEL_STATUS <> 'X'

                AND PH.PO_ID = PO_NUMBER;

                END HK_KFX_PO_SP;

                 

                 

                when we execute the stored Procedure's, we are receiving the same error.

                ORA-06550: Line-1, Column 7

                PLS-00306:wrong number or type of arguments in call to HK_KFX_PO_SP

                 

                Any help is highly appreciated.

                That error means the way you're calling the procedure doesn't match the way it's defined.  Make sure you calling the procedure with exactly 8 arguments, that each one has the correct data type, and that the OUT arguments are all variables (not literals, or any other kind of expression).

                 

                Post the code that actually calls the procedure (and raises the error).  Include the declarations of all the variables you're passing as arguments.  Post your code directly on this site, not in an attachment.

                • 5. Re: Stored Procedure Question.
                  Frank Kulash

                  Hi,

                  3430959 wrote:

                   

                  Thanks for the quick response.

                  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

                  I only has 1 input Parameter(PO_NUMBER).

                  That's why i have passed 1 input variable.

                  SET SERVEROUTPUT ON;
                  EXEC sysadm.HK_KFX_PO_SP('GEN0000146');

                  Correct me, if i am wrong.

                  The procedure you posted had 8 arguments:

                  Stored Procedure(Version-1):

                  CREATE OR REPLACE PROCEDURE sysadm.HK_KFX_PO_SP

                  (

                    PO_NUMBER IN VARCHAR2                   -- 1

                  , LINE_NUM OUT NUMBER                        -- 2

                  , SCHED_NUM OUT NUMBER                    -- 3

                  , LINE_DESCR OUT VARCHAR2                -- 4

                  , LINE_UOM OUT VARCHAR2                     -- 5

                  , LINE_UNIT_PRICE OUT NUMBER            -- 6

                  , LINE_QUANTITY OUT NUMBER               -- 7

                  , LINE_MERCH_AMOUNT OUT NUMBER   -- 8

                  )

                  You need to pass all 8.

                   

                  OUT means the procedure will assign a value whatever variable to pass.

                  OUT does not mean you don't have to pass an argument; in fact, it's the other way around.  IN arguments can be made optional, so you don't need to pass them, but OUT arguments always need to be passed.

                  • 6. Re: Stored Procedure Question.
                    mathguy

                    Here is a brief demo.

                     

                    Creating the procedure and compiling it successfully:

                     

                    create procedure sqr(x in number, y out number)

                    as

                    begin

                      y := x * x;

                    end;

                    /

                     

                    Procedure SQR compiled

                     

                     

                    Calling it incorrectly, the same way you did. You will see the same error message.

                     

                    begin

                      sqr(3);

                    end;

                    /

                     

                    Error starting at line : 8 in command -

                    begin

                      sqr(3);

                    end;

                    Error report -

                    ORA-06550: line 2, column 3:

                    PLS-00306: wrong number or types of arguments in call to 'SQR'

                    ORA-06550: line 2, column 3:

                    PL/SQL: Statement ignored

                    06550. 00000 -  "line %s, column %s:\n%s"

                    *Cause:    Usually a PL/SQL compilation error.

                    *Action:

                     

                     

                    Calling it the right way. Here I create a variable, then I call the procedure. To show what happened with that, I use DBMS_OUTPUT to show the value of the variable after procedure execution. SET SERVEROUTPUT ON is so I can see the output from DBMS_OUTPUT.

                     

                     

                    set serveroutput on

                     

                    declare

                      my_square number;

                    begin

                      sqr(3, my_square);

                      dbms_output.put_line(my_square);

                    end;

                    /

                     

                    9

                     

                    PL/SQL procedure successfully completed.

                    • 7. Re: Stored Procedure Question.
                      3430959

                      Hi Frank,

                       

                      Thanks for the Response. I really appreciate it.

                       

                      My Requirement is to pass 1 input variable and retrieve the 7 output parameters.

                       

                      Thats why i passed 1 input variable.

                       

                      SET SERVEROUTPUT ON;

                      EXEC sysadm.HK_KFX_PO_SP('GEN0000146');

                       

                      Should we also pass OUT Variables?

                      • 8. Re: Stored Procedure Question.
                        Mike Kutz

                        For those that didn't know:

                         

                        You can  cheat by using SQL*Developer.

                         

                        If you and Drag+Drop the Procedure to the Worksheet, SQL*Developer will write the sample code for you. 

                         

                        MK

                         

                         

                        • 9. Re: Stored Procedure Question.
                          Frank Kulash

                          Hi,

                          3430959 wrote:

                           

                          Hi Frank,

                           

                          Thanks for the Response. I really appreciate it.

                           

                          My Requirement is to pass 1 input variable and retrieve the 7 output parameters.

                           

                          Thats why i passed 1 input variable.

                           

                          SET SERVEROUTPUT ON;

                          EXEC sysadm.HK_KFX_PO_SP('GEN0000146');

                           

                          Should we also pass OUT Variables?

                          Yes, you MUST pass variables for the OUT arguments.

                           

                          In my neighborhood, there's a vending machine for ice on a street corner.  You have to bring your own container that's big enough to hold the amount of ice that the machine will dispense.

                          OUT arguments are like that.  You have to supply your own container (a variable) that has the right data type and size to hold the value that the procedure will dispense.

                          • 10. Re: Stored Procedure Question.

                            Should we also pass OUT Variables?

                            You should READ THE DOCUMENTATION to learn how to use Oracle functionality.

                            https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#g1170259

                             

                            Try the example in the doc and then modify it for your use case.

                             

                            More likely you should NOT be using OUT parameters like that anyway.

                             

                            If you need to return a SET of attribute values then you should create an object type that has those attributes and return an instance of the object type.

                            • 11. Re: Stored Procedure Question.
                              Dejan T.

                              Hi,

                              what other guys want to tell you, is that you need to create also variables to handle the returned values from the procedure. For example:

                               

                              create or replace procedure sp_inout (
                                p_in IN number,
                                p_out1 OUT number,
                                p_out2 OUT number,
                                p_out3 OUT number,
                                p_out4 OUT number,
                                p_out5 OUT number,
                                p_out6 OUT number,
                                p_out7 OUT number)
                              as
                              begin
                                select 1, 2, 3, 4, 5, 6, 7
                                  into p_out1, p_out2, p_out3, p_out4, p_out5, p_out6, p_out7
                                  from dual
                                  where 1=1;
                              end;
                              
                              
                              SQL> var p_out1 number;
                              SQL> var p_out2 number;
                              SQL> var p_out3 number;
                              SQL> var p_out4 number;
                              SQL> var p_out5 number;
                              SQL> var p_out6 number;
                              SQL> var p_out7 number;
                              
                              SQL> exec sp_inout(1234, :p_out1, :p_out2, :p_out3, :p_out4, :p_out5, :p_out6, :p_out7);
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> print :p_out1 :p_out2 :p_out3 :p_out4 :p_out5 :p_out6 :p_out7
                              
                                  P_OUT1
                              ----------
                                       1
                              
                              
                                  P_OUT2
                              ----------
                                       2
                              
                              
                                  P_OUT3
                              ----------
                                       3
                              
                              
                                  P_OUT4
                              ----------
                                       4
                              
                              
                                  P_OUT5
                              ----------
                                       5
                              
                              
                                  P_OUT6
                              ----------
                                       6
                              
                              
                                  P_OUT7
                              ----------
                                       7
                              
                              
                              
                              
                              declare
                                v_out1 number;
                                v_out2 number;
                                v_out3 number;
                                v_out4 number;
                                v_out5 number;
                                v_out6 number;
                                v_out7 number;
                              begin
                                sp_inout(1234, v_out1, v_out2, v_out3, v_out4, v_out5, v_out6, v_out7);
                              end;
                              /