Passing Variable No of Arguments to Oracle Stored Procedure

If you want to pass variable number of arguments in a Oracle stored procedure, you can use array Type as stored procedure's parameter to accomplish this. Like in Java you can pass varargs as an array.

1- Create an Oracle nested table type in the database

CREATE OR REPLACE TYPE nt_type IS TABLE OF VARCHAR2(30);

2- Create a procedure in the database

CREATE OR REPLACE PROCEDURE nt_proc (nt nt_type) IS

i NUMBER;

BEGIN

FOR i IN nt.FIRST .. nt.LAST

LOOP

INSERT INTO nt_test(id, description) VALUES (i, nt(i));

END LOOP;

END nt_proc;

/

3- Create a test table, This is just for test purposes.

CREATE TABLE nt_test (id NUMBER, description VARCHAR2(30));

4- Create a module which defines the array of values

DECLARE

nt nt_type := nt_type(); -- nested table variable initialized to empty

BEGIN

nt := nt_type('Chester','Swindon','Corby','London','Swansea','Cardiff'); -- create your string of variables here

nt_proc(nt);

END;

/

SELECT * FROM nt_test;