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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With