Pass parameter to procedure

  1. Sparamanga

    Sparamanga Active Member

    Messages:
    29
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Hi I have a basic procedure which bulk collects the results of a select statement into a table array. I then print out one line to show that it has worked..

    Code (SQL):
    CREATE OR REPLACE PROCEDURE use_var
    IS
    TYPE r_tab IS TABLE OF msf010%rowtype;
    rr_tab r_tab;
    BEGIN

    SELECT msf010.* bulk collect INTO
    rr_tab
    FROM msf010 WHERE table_type='E6' ;

    DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE);
    END;
    The procedure works fine but I wanted to be able to pass a parameter to the procedure that would form part of the where clause as below..

    Code (SQL):
    CREATE OR REPLACE PROCEDURE use_var2 (rob_var2 IN varchar2)
    IS
    TYPE r_tab IS TABLE OF msf010%rowtype;
    rr_tab r_tab;
    BEGIN

    SELECT msf010.* bulk collect INTO
    rr_tab
    FROM msf010 WHERE table_type=rob_var2 ;

    DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE);
    END;

    EXEC use_var2('E6')
    Which produces the following error ouput..

    Error starting at line 14 in command:
    exec use_var2('E6')
    Error report:
    ORA-06533: Subscript beyond count
    ORA-06512: at "RREES4.USE_VAR2", line 11
    ORA-06512: at line 1
    06533. 00000 - "Subscript beyond count"
    *Cause: An in-limit subscript was greater than the count of a varray
    or too large for a nested table.
    *Action: Check the program logic and explicitly extend if necessary.


    Do I need to use dynamic sql to pass the parameter? Or is there something else that needs to be changed?

    Thanks,

    Rob.
     
  2. Sparamanga

    Sparamanga Active Member

    Messages:
    29
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    UK
    Ah incorrect datatype for parameter .. char in underlying table. Works now.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    778
    Likes Received:
    149
    Trophy Points:
    830
    Location:
    Russian Federation
    In your code is present the row
    Code (SQL):

    DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE)
     
    If collection (rr_tab) is empty, then oracle is generating of the exception when access to element.

    Therefore, in your of code will better to add a some check for debugging
    Code (SQL):

    IF RR_TAB.COUNT >0 THEN
    DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE);
    END IF;
     
    Also, parameters by your of the procedures need to linked with column type on table:
    Code (SQL):

    CREATE OR REPLACE PROCEDURE use_var2 (rob_var2 IN  msf010.table_type%TYPE)
    IS
    TYPE r_tab IS TABLE OF msf010%rowtype;
    rr_tab r_tab;
    BEGIN

    SELECT msf010.* bulk collect INTO
    rr_tab
    FROM msf010 WHERE table_type=rob_var2 ;
    IF RR_TAB.COUNT >0 THEN
    DBMS_OUTPUT.PUT_LINE(RR_TAB(1).TABLE_CODE);
    END IF;
    END;