Pass a list of values ​​as an input parameter to a PL / SQL procedure

Question

Hi I have a requirement when I get a list of values ​​for an input parameter in a PL / SQL procedure. The size of the input list depends on the dynamic. How to cope with this requirement any help?

Answer 1

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

A type can be declared as "TABLE" 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( PKG_TEST.X('1','2','3','4'));
END;
/

Answer 2

You can use the varchar parameter in sql, each value should be separated by a comma, something like this: 'Value1, value2, value3, value4, ...,

So you can read the values ​​using split sql function

I hope I understand your question