1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Example of stored procedure with parameters?

Discussion in 'SQL PL/SQL' started by pditty8811, Feb 20, 2014.

  1. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Could someone please post an example of store procedure with parameters? I am confused if I need to declare the variables in the parentheses with the procedure name.

    This is my code:

    Code (Text):

    SET SERVEROUTPUT ON;
         
    DECLARE

        vOrid NUMBER(4,0);
        vPid NUMBER(4,0);
        vQty Number(4,0);
    BEGIN  
        vOrid := 6099;
        vPid := 1001;
        vQty := 1;

     
      EXECUTE insert_orderitems (6099, 1001, 1);
     
        --Insert data into ORDERITEMS table
      CREATE OR REPLACE PROCEDURE insert_orderitems()
      IS
        vOrderid NUMBER(4,0);
        vPartid NUMBER(4,0);
        vQuantity Number(4,0);
        vDetail NUMBER(2,0);
      BEGIN  
        vOrderid := &1;
        vPartid := &2;
        vQuantity := &3;
     
              DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||vOrderid||'-'||vQuantity||vPartid);
        BEGIN
          SELECT MAX(ORDERITEMS.DETAIL) + 1
          INTO vDetail
          FROM ORDERITEMS
          WHERE ORDERITEMS.ORDERID = vOrderid;
         
          IF vDetail IS NULL THEN
              vDetail := 1;
          END IF;
        END;
       
          INSERT INTO ORDERITEMS
          (ORDERID, DETAIL, PARTID, QTY)
          VALUES
          (vOrderid, vDetail, vPartid, vQuantity);  
     
      EXCEPTION
        WHEN others THEN
          DBMS_OUTPUT.put_line('An error was encountered - general error - '||SQLERRM||'-'||SQLCODE);
          ROLLBACK;
      END;
    END;
     

    This error:

    Error report -
    ORA-06550: line 12, column 11:
    PLS-00103: Encountered the symbol "INSERT_ORDERITEMS" when expecting one of the following:

    := . ( @ % ; immediate
    The symbol ":=" was substituted for "INSERT_ORDERITEMS" to continue.
    ORA-06550: line 15, column 3:
    PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

    begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimi
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,


    First compile the following procedure.

    Code (SQL):


      CREATE OR REPLACE PROCEDURE insert_orderitems(p_order_id NUMBER,p_partid NUMBER,p_quantity NUMBER)
      IS
        vOrderid NUMBER(4,0);
        vPartid NUMBER(4,0);
        vQuantity NUMBER(4,0);
        vDetail NUMBER(2,0);
      BEGIN  
        vOrderid := p_order_id ;
        vPartid := p_partid;
        vQuantity := p_quantity;
     
              DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||vOrderid||'-'||vQuantity||vPartid);
        BEGIN
          SELECT MAX(ORDERITEMS.DETAIL) + 1
          INTO vDetail
          FROM ORDERITEMS
          WHERE ORDERITEMS.ORDERID = vOrderid;
     
          IF vDetail IS NULL THEN
              vDetail := 1;
          END IF;
        END;
     
          INSERT INTO ORDERITEMS
          (ORDERID, DETAIL, PARTID, QTY)
          VALUES
          (vOrderid, vDetail, vPartid, vQuantity);  
     
      EXCEPTION
        WHEN others THEN
          DBMS_OUTPUT.put_line('An error was encountered - general error - '||SQLERRM||'-'||SQLCODE);
          ROLLBACK;
      END;
    /
     

    Then execute that procedure by using Pl/sql block.

    Code (SQL):

    DECLARE
     
        vOrid NUMBER(4,0);
        vPid NUMBER(4,0);
        vQty NUMBER(4,0);
    BEGIN  
        vOrid := &vOrder_Id;
        vPid := &v_Partid;
        vQty := &v_qty;
       insert_orderitems (vOrid, vPid, vQty);
     
    END;
     

    Regards
    Sambasiva Reddy.K
     
  3. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm still getting this error:

    Error report -
    ORA-06550: line 15, column 2:
    PLS-00103: Encountered the symbol "CREATE"
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  4. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    How you are compiling the procedure ? are you using SQL * Plus.

    If yes please find the attached .txt file and save it as .sql file and store it some path (Ex: D:\INSERT_ORDERITEMS.sql ). Then in the sql * plus compile like below.


    SQL> @ D:\INSERT_ORDERITEMS.sql




    Regards
    Sambasiva Reddy.K
     

    Attached Files:

  5. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm still getting this error:

    Error report -
    ORA-06550: line 15, column 2:
    PLS-00103: Encountered the symbol "CREATE"
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  6. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I'm using oracle with sql developer.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Take new tab in sql developer and paste the code ans select all then press F5.
     
  8. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    Same error.

    This what I have:

    Code (Text):

    SET SERVEROUTPUT ON;
         
    DECLARE
     
        vOrid NUMBER(4,0);
        vPid NUMBER(4,0);
        vQty NUMBER(4,0);
    BEGIN  
        vOrid := &vOrder_Id;
        vPid := &v_Partid;
        vQty := &v_qty;
       insert_orderitems (vOrid, vPid, vQty);
     
    END;
     
        --Insert data into ORDERITEMS table
     CREATE OR REPLACE PROCEDURE insert_orderitems(p_order_id NUMBER,p_partid NUMBER,p_quantity NUMBER)
      IS
        vOrderid NUMBER(4,0);
        vPartid NUMBER(4,0);
        vQuantity NUMBER(4,0);
        vDetail NUMBER(2,0);
      BEGIN  
        vOrderid := p_order_id ;
        vPartid := p_partid;
        vQuantity := p_quantity;
     
        DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||vOrderid||'-'||vQuantity|| '- ' ||vPartid);
       
        BEGIN
          SELECT MAX(ORDERITEMS.DETAIL) + 1
          INTO vDetail
          FROM ORDERITEMS
          WHERE ORDERITEMS.ORDERID = vOrderid;
     
          IF vDetail IS NULL THEN
              vDetail := 1;
          END IF;
        END;
     
          INSERT INTO ORDERITEMS
          (ORDERID, DETAIL, PARTID, QTY)
          VALUES
          (vOrderid, vDetail, vPartid, vQuantity);  
     
      EXCEPTION
        WHEN others THEN
          DBMS_OUTPUT.put_line('An error was encountered - general error - '||SQLERRM||'-'||SQLCODE);
          ROLLBACK;
      END;
     
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    You just execute this first and check whether it is compiled or not.

    CREATE OR REPLACE PROCEDURE insert_orderitems(p_order_id NUMBER,p_partid NUMBER,p_quantity NUMBER)
    IS
    vOrderid NUMBER(4,0);
    vPartid NUMBER(4,0);
    vQuantity NUMBER(4,0);
    vDetail NUMBER(2,0);
    BEGIN
    vOrderid := p_order_id ;
    vPartid := p_partid;
    vQuantity := p_quantity;

    DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||vOrderid||'-'||vQuantity|| '- ' ||vPartid);

    BEGIN
    SELECT MAX(ORDERITEMS.DETAIL) + 1
    INTO vDetail
    FROM ORDERITEMS
    WHERE ORDERITEMS.ORDERID = vOrderid;

    IF vDetail IS NULL THEN
    vDetail := 1;
    END IF;
    END;

    INSERT INTO ORDERITEMS
    (ORDERID, DETAIL, PARTID, QTY)
    VALUES
    (vOrderid, vDetail, vPartid, vQuantity);

    EXCEPTION
    WHEN others THEN
    DBMS_OUTPUT.put_line('An error was encountered - general error - '||SQLERRM||'-'||SQLCODE);
    ROLLBACK;
    END;

    if it is compiled then execute this.

    DECLARE

    vOrid NUMBER(4,0);
    vPid NUMBER(4,0);
    vQty NUMBER(4,0);
    BEGIN
    vOrid := &vOrder_Id;
    vPid := &v_Partid;
    vQty := &v_qty;
    insert_orderitems (vOrid, vPid, vQty);

    END;
     
  10. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    pditty8811,

    When using SQL Developer, you can't just paste in a bunch of stuff and run it. What you have posted above is an Anonymous PL/SQL block followed by a specific SQL command.

    Go to SQL Developer, open your DB connection nav tree (this will open a tab window to the right as well...ignore it for now), right click PROCEDURE, select NEW, give it a name, select the DDL tab, paste in the CREATE statement from above (note paste in ONLY the create or replace statement), click OK.

    That will open a new SQL Dev tab w/ your Create Procedure statement...look it over and make sure it's correct. Near the top of the tab will be an icon that looks like some gears. Click that to compile the procedure.

    Provided the code compiles successfully, paste the SQL statement ONLY into the tab where your DB connection is (the one you ignored from earlier). Click VIEW from the top level menu, select DBMS Output. This will open yet another window. Click the Green plus sign in DBMS Output window, select the DB connection that you are using and click OK. Now go back to the SQL window where you pasted the Declare/Begin/End stuff and click the green arrow to run it.

    If you haven't already...try reading the online help "Getting Started..." or go to Oracle's site and find the OBE tutorials.

    HTH

    CJ
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    By default, it appears, procedure parameters are considered IN parameters, however it is good programming practice to actually declare them as such:


    Code (SQL):

    CREATE OR REPLACE PROCEDURE insert_orderitems(p_order_id  IN NUMBER,p_partid IN NUMBER,p_quantity IN NUMBER)
      IS
        vOrderid NUMBER(4,0);
        vPartid NUMBER(4,0);
        vQuantity NUMBER(4,0);
        vDetail NUMBER(2,0);
      BEGIN  
        vOrderid := p_order_id ;
        vPartid := p_partid;
        vQuantity := p_quantity;

        DBMS_OUTPUT.put_line('An error was encountered matching CUSTID with ORDERID - '||vOrderid||'-'||vQuantity|| '- ' ||vPartid);

        BEGIN
          SELECT MAX(ORDERITEMS.DETAIL) + 1
          INTO vDetail
          FROM ORDERITEMS
          WHERE ORDERITEMS.ORDERID = vOrderid;

          IF vDetail IS NULL THEN
              vDetail := 1;
          END IF;
        END;

          INSERT INTO ORDERITEMS
          (ORDERID, DETAIL, PARTID, QTY)
          VALUES
          (vOrderid, vDetail, vPartid, vQuantity);  

      EXCEPTION
        WHEN others THEN
          DBMS_OUTPUT.put_line('An error was encountered - general error - '||SQLERRM||'-'||SQLCODE);
          ROLLBACK;
      END;
    /
     

    I also noticed you failed to include the ending '/', a character necessary for PL/SQL units to actually compile. Please verify that code that IS posted works AS posted.
     
  12. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    I got it to work. I didn't realize the Stored procedure has to appear before the execute command. Thank you!
     
  13. pditty8811

    pditty8811 Active Member

    Messages:
    53
    Likes Received:
    0
    Trophy Points:
    130
    How do I pass params to a stored procedure that is in a file called "Lab8.sql"?
    And the Stored procedure is called "ValidateCustID". And it takes one parameter that is NUMBER.

    How do I call it from another file called Lab8test.sql? Do I have to compile the stored procedure first? How do I do this?

    My instructor says nothing can be in the Lab8.sql file except the stored procedure. So no other blocks outside the stored procedure within Lab8.sql.
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    All of this has been explained to you before in THIS thread. I will do this ONE last time so please Pay attention:


    To create the stored procedure defined in a file named Lab8.sql you do THIS from the SQL> prompt:


    SQL> @Lab8


    You SHOULD see output like this:


    Procedure created.


    SQL>


    Let's presume that Lab8test.sql contains the following text:


    declare
    v_inpar number:=9;


    begin
    ValidateCustID(v_inpar);
    end;
    /


    You would simply do THIS:


    SQL> @Lab8test


    You would then see the output from the procedure. Or let's say Lab8test.sql looks like this:


    declare
    v_inpar number:=&custnum;


    begin
    ValidateCustID(v_inpar);
    end;
    /


    Executing THAT script would produce the following output:


    SQL> @Lab8test
    Enter value for custnum:


    You would then type in a number and hit 'Enter', after which you would see the output from the procedure.


    I REALLY hope you understand this time.