Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Lightbulb Unanswered: Dynamic List of Params in Stored Procedure

    Hi ,

    I need some guidance in order to code a Storepd procedure in such way that it accepts a list of params dynamically.
    For example :
    CREATE OR REPLACE PROCEDURE emp_name (id1 IN NUMBER,id2 IN NUMBER,id3 IN NUMBER,id4 IN NUMBER,id5 IN NUMBER........ )
    IS
    BEGIN
    SELECT first_name INTO emp_name FROM emp_tbl WHERE empID = id;
    END;
    /

    In this for each id i have defined a fixed number of variables as list of params.I do not want any restriction on the list of params provided by the end user.

    please advice.


    With Regards,
    Subhadeep Sarkar

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INSERT the values into a table
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2010
    Posts
    64

    Question response

    Thanks for your response !

    I can insert all the parameters into a table. what i should write while defining the list of parameters. I am not sure on that.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >what i should write while defining the list of parameters. I am not sure on that.
    I only know what you post; which is not very much.
    I don't understand statement above.
    I don't know what problem you are really trying to solve.
    I don't know what the parameters stand for or how they will be used.
    You asked how to accommodate a varying number of "parameters" & I suggested using rows in a table.
    Whether or not this is appropriate for your situation, only you can decide.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2012
    Posts
    84
    Hello,

    PL/SQL doesn't support variable number of arguments in a call to a procedure or a function, like for example java.
    However if all parameters would be of the same type, then collections could be used, for example in this way:

    Code:
    create or replace
    package params
    is
      TYPE par_typ IS TABLE OF NUMBER;
      PROCEDURE test( params IN par_typ );
    END ;
    /
    
    create or replace
    package body params
    is
      PROCEDURE test( params IN par_typ )
      IS
      BEGIN
        FOR i IN 1 .. params.count LOOP
           DBMS_OUTPUT.PUT_LINE( params( i ) );
        END LOOP;
      END;
    END;
    /
    Code:
    execute params.test( params.par_typ( 1, 4, 6, 8 ) );
    
    1
    4
    6
    8
    
    execute params.test( params.par_typ( 69, 100, 98, 59, 92, 27, 18, 0, 11, 26, 42, 94, 17,53,79,19,91,82,50,68,8,38,86,78,33,5,46,84,48,96,25,33,96,22,93,87,50,10,87,61,37,29,55,54,82,33,74,73,14,24,40,23,62,25,0,96,31,47,79,79,42,3,11,37,26,4,24,76,14,11,36,51,40,91,5,21,23,80,95,38,3,34,62,65,59 ));
    
    69
    100
    98
    59
    92
    27
    18
    0
    11
    26
    42
    94
    17
    53
    79
    19
    91
    82
    50
    68
    8
    38
    86
    78
    33
    5
    46
    84
    48
    96
    25
    33
    96
    22
    93
    87
    50
    10
    87
    61
    37
    29
    55
    54
    82
    33
    74
    73
    14
    24
    40
    23
    62
    25
    0
    96
    31
    47
    79
    79
    42
    3
    11
    37
    26
    4
    24
    76
    14
    11
    36
    51
    40
    91
    5
    21
    23
    80
    95
    38
    3
    34
    62
    65
    59

  6. #6
    Join Date
    Jan 2010
    Posts
    64

    Thumbs up Thanks a lot !

    Thanks for your guidance. This is what i was looking for. Cheers!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •