pass list of values as input parameter to PL/SQL procedure

Question

Hi I have a requirement where I get list of values to a input parameter in PL/SQL procedure. The size of the input list varies which is dynamic. How to handle this requirement any help?

user1118468 · Accepted Answer · 2019-03-23 13:41:39Z

10

CREATE OR REPLACE PACKAGE PKG_TEST AS
TYPE X IS TABLE OF VARCHAR2(30);
PROCEDURE XYZ(Y IN X);
END PKG_TEST;
/

The type can be declared as "TABLE OF" OR "VARRAY(10)";

CREATE OR REPLACE PACKAGE  BODY PKG_TEST AS
PROCEDURE XYZ(Y IN X) AS
BEGIN
  FOR I IN Y.FIRST..Y.LAST
    LOOP
      DBMS_OUTPUT.PUT_LINE('THE VALUE OF I IS'||Y(I));
    END LOOP;
  END;
END PKG_TEST;
/

DECLARE   
BEGIN
  PKG_TEST.XYZ('1','2','3','4');
END;
/

edited Mar 23, 2019 at 13:41

answered Feb 6, 2014 at 16:15

user1118468

7234 gold badges11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

razielx4crazy · Accepted Answer · 2014-02-06 14:19:24Z

2

You could use a varchar parameter in sql, each value must be separated by a comma, something like this: 'value1,value2,value3,value4,...,'

So, you can read the values using the function split of sql

I hope that I understood your question

answered Feb 6, 2014 at 14:19

razielx4crazy

1599 bronze badges

2 Comments

user1118468 Over a year ago

I didn't understand your reply, I have parameters in procedure as input, but one of teh input needed to be a list.

razielx4crazy Over a year ago

why dont you write some of your code? The statement of your procedure and your list, maybe I'll understand

Collectives™ on Stack Overflow

pass list of values as input parameter to PL/SQL procedure

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related