Passing an array of data as an input parameter to an Oracle procedure

This is one way to do it:

SQL> set serveroutput onSQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);  2  /Type createdSQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS  2  BEGIN  3    FOR i IN 1..t_in.count LOOP  4      dbms_output.put_line(t_in(i));  5    END LOOP;  6  END;  7  /Procedure createdSQL> DECLARE  2    v_t MyType;  3  BEGIN  4    v_t := MyType();  5    v_t.EXTEND(10);  6    v_t(1) := 'this is a test';  7    v_t(2) := 'A second test line';  8    testing(v_t);  9  END; 10  /this is a testA second test line

To expand on my comment to @dcp's answer, here's how you could implement the solution proposed there if you wanted to use an associative array:

SQL> CREATE OR REPLACE PACKAGE p IS  2    TYPE p_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;  3    4    PROCEDURE pp (inp p_type);  5  END p;  6  /Package createdSQL> CREATE OR REPLACE PACKAGE BODY p IS  2    PROCEDURE pp (inp p_type) IS  3    BEGIN  4      FOR i IN 1..inp.count LOOP  5        dbms_output.put_line(inp(i));  6      END LOOP;  7    END pp;  8  END p;  9  /Package body createdSQL> DECLARE  2    v_t p.p_type;  3  BEGIN  4    v_t(1) := 'this is a test of p';  5    v_t(2) := 'A second test line for p';  6    p.pp(v_t);  7  END;  8  /this is a test of pA second test line for pPL/SQL procedure successfully completedSQL> 

This trades creating a standalone Oracle TYPE (which cannot be an associative array) with requiring the definition of a package that can be seen by all in order that the TYPE it defines there can be used by all.

If the types of the parameters are all the same (varchar2 for example), you can have a package like this which will do the following:

CREATE OR REPLACE PACKAGE testuser.test_pkg IS   TYPE assoc_array_varchar2_t IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;   PROCEDURE your_proc(p_parm IN assoc_array_varchar2_t);END test_pkg;CREATE OR REPLACE PACKAGE BODY testuser.test_pkg IS   PROCEDURE your_proc(p_parm IN assoc_array_varchar2_t) AS   BEGIN      FOR i IN p_parm.first .. p_parm.last      LOOP         dbms_output.put_line(p_parm(i));      END LOOP;   END;END test_pkg;

Then, to call it you'd need to set up the array and pass it:

DECLARE  l_array testuser.test_pkg.assoc_array_varchar2_t;BEGIN  l_array(0) := 'hello';  l_array(1) := 'there';    testuser.test_pkg.your_proc(l_array);END;/