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

Question

I'm trying to pass an array of (varchar) data into an Oracle procedure. The Oracle procedure would be either called from SQL*Plus or from another PL/SQL procedure like so:

BEGIN
 pr_perform_task('1','2','3','4');
END;

pr_perform_task will read each of the input parameters and perform the tasks.

I'm not sure as to how I can achieve this. My first thought was to use an input parameter of type varray but I'm getting Error: PLS-00201: identifier 'VARRAY' must be declared error, when the procedure definiton looks like this:

CREATE OR REPLACE PROCEDURE PR_DELETE_RECORD_VARRAY(P_ID VARRAY) IS

To summarize, how can I pass the data as an array, let the SP loop through each of the parameters and perform the task ?

I'm using Oracle 10gR2 as my database.

Aristophanes

Answer 1

This is one way to do it:

SQL> set serveroutput on
SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50);
  2  /

Type created

SQL> 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 created

SQL> 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 test
A 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 created
SQL> 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 created
SQL> 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 p
A second test line for p

PL/SQL procedure successfully completed

SQL> 

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.

Gyrfalcon

Nice answer. I wondered if it's possible to do what your first method does, but for the situation where the number of elements in t_in is dynamic (that is, it's different for each call to testing)? It seems like one needs to at least hard code an upper bounds for MyType? Can that restriction be lifted somehow? – Recumbent

@ggkmath, it does not matter how large the array is, but you must declare the VARRAY upper limit when you create or alter the TYPE. – Gyrfalcon

Is there alternate solution that can accommodate such dynamic size, perhaps without VARRAY? If off topic, I can start a new thread. – Recumbent

Use the second half of the answer - make the type a PL/SQL associative array and define it in the package spec. – Gyrfalcon

For me I had to initialize the table type using the following v_t p.p_type = p.p_type(); later call v_t.extend(10) to create space for 10 elements. Not sure why it is different from what you are doing and what I have. Btween I have created a table_type of a type that we created – Deuteronomy

Regarding the inflexible limit for varrays, you can use a nested table collection type instead of a varray: create or replace type mytype as table of varchar2(50); I generally wouldn't recommend varrays for use in PL/SQL programing as they have some major limitations. – Cornhusk

Answer 2

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;
/

Villainy

You can't create an Oracle Type of associative array. The only way this can work is by defining the type in a package and referencing it that way. – Gyrfalcon

@Gyrfalcon - My fault, thanks for pointing out the mistake. I have corrected my answer :). – Villainy

Is It necesary create a package? – Marrowbone

I think you're asking the wrong question. Except for a few rare cases, you should always use packages, refer here: asktom.oracle.com/pls/asktom/… – Villainy

Answer 3

You may user like:

1. Create table types. user below command

   CREATE OR REPLACE TYPE StringListType AS TABLE OF VARCHAR2(1024);
   

2. Create Procedure:

   CREATE OR REPLACE PROCEDURE PR_DELETE_RECORD_VARRAY(
       pList  IN StringListType
   ) AS
   BEGIN
   
       FOR i IN 1..pList.COUNT LOOP
           DBMS_OUTPUT.PUT_LINE('item: ' || pList(i));
         /*
          write logic here
        */
       END LOOP;
   END;
   

3. Call the procedure:

   DECLARE
       vList StringListType := StringListType('1', '2', '3');
   
   BEGIN
       PR_DELETE_RECORD_VARRAY(pList => vList);
   END;
   /
   

Roma