Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 610
Sathyajith Bhat Avatar asked May 21 '10 21:05

Sathyajith Bhat


People also ask

How do you pass an array as a parameter in PL SQL?

SQL> SQL> SQL> DECLARE 2 P_ARRAY NUM_ARRAY := NUM_ARRAY(null); 3 BEGIN 4 P_ARRAY. extend; -- add 1 element at a time 5 P_ARRAY(1) := 15; 6 P_ARRAY. extend; 7 P_ARRAY(2) := 54; 8 P_ARRAY. extend; 9 P_ARRAY(3) := 56; 10 11 END; 12 / PL/SQL procedure successfully completed.

How do I pass multiple values to one parameter in Oracle?

create procedure sp1 (p1 in varchar2) as begin select proc_id from proc_tbl where proc_id in (p1); end; The user expects to input multiple values separate by comma or space such as a1, b2, c3 in p1. All the PROC_ID stored in proc_tbl are in upper case.


1 Answers

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.

like image 167
DCookie Avatar answered Sep 28 '22 04:09

DCookie