How to pass a list of numbers to a stored procedure?

Question

So, I have the following stored procedure:

CREATE OR REPLACE PROCEDURE stored_p ( ntype IN NUMBER , p_ResultSet OUT TYPES.cursorType ) AS BEGIN OPEN p_ResultSet FOR select * from table where ttype in ntype; END stored_p 

and I can call it this way:

 VARIABLE resultSet REFCURSOR EXEC stored_p(80001, :resultSet); PRINT :resultSet 

but I want to be able to call it like this:

 VARIABLE resultSet REFCURSOR EXEC stored_p([80001,80002], :resultSet); PRINT :resultSet 

How can I modify my stored procedure accordingly? I am doing this to display the results in Crystal Report ... (just in case that affects anything). Thanks!

Answer 1

The best option would be to hand over the collection

 SQL> create type empno_tbl 2 is 3 table of number; 4 / Type created. SQL> create or replace procedure stored_p 2 ( 3 empnos in empno_tbl, 4 p_rc out sys_refcursor ) 5 as 6 begin 7 open 8 p_rc for select * from emp where empno in (select * from table(empnos)); 9 end; 10 / Procedure created. SQL> var rc refcursor; SQL> ed Wrote file afiedt.buf 1 create or replace procedure stored_p 2 ( 3 empnos in empno_tbl, 4 p_rc out sys_refcursor ) 5 as 6 begin 7 open 8 p_rc for select * from emp where empno in (select * from table(empnos)); 9* end; SQL> begin 2 stored_p( new empno_tbl(7902,7934), :rc ); 3 end; 4 / PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO FAKE_COL FOO ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 1 7934 MILLER CLERK 7782 23-JAN-82 1300 10 1 

Unfortunately, Crystal Reports will not be able to pass the proper collection to the stored procedure. If so, you will need to pass a list of numbers separated by commas. Then your procedure should parse this comma separated string into a collection. You can use (or change) the Tom Kyte in_list function for this

 SQL> ed Wrote file afiedt.buf 1 create or replace function in_list( 2 p_string in varchar2 3 ) 4 return empno_tbl 5 as 6 l_string long default p_string || ','; 7 l_data empno_tbl := empno_tbl(); 8 n number; 9 begin 10 loop 11 exit when l_string is null; 12 n := instr( l_string, ',' ); 13 l_data.extend; 14 l_data(l_data.count) := 15 ltrim( rtrim( substr( l_string, 1, n-1 ) ) ); 16 l_string := substr( l_string, n+1 ); 17 end loop; 18 return l_data; 19* end; SQL> / Function created. SQL> ed Wrote file afiedt.buf 1 create or replace procedure stored_p 2 ( 3 empnos in varchar2, 4 p_rc out sys_refcursor ) 5 as 6 begin 7 open p_rc 8 for select * 9 from emp 10 where empno in (select * 11 from table(in_list(empnos))); 12* end; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 begin 2 stored_p( '7902,7934', :rc ); 3* end; SQL> / PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO FAKE_COL FOO ---------- ---------- ---------- 7902 FORD ANALYST 7566 03-DEC-81 3000 20 1 7934 MILLER CLERK 7782 23-JAN-82 1300 10 1 

Answer 2

You need to create a type.

 create or replace type NUMBER_ARRAY as table of number; CREATE OR REPLACE PROCEDURE stored_p ( ntype IN NUMBER_ARRAY , p_ResultSet OUT TYPES.cursorType ) 

You can execute a loop with.

 for i in 1 .. ntype.count loop dbms_output.put_line( ntype(i) ); end loop; 

To check it out,

 DECLARE ntypetest NUMBER_ARRAY := NUMBER_ARRAY (); BEGIN FOR i IN 1 .. 5 LOOP ntypetest.EXTEND; ntypetest (i) := i; END LOOP; stored_p(ntypetest,..) 

There may be some changes in the syntax.

Of course, you can pass values ​​separated by commas, but this will be as a string. Your line should be something like 'val1','val2','val3' . You have to be careful when you have numbers, since the whole line will look like in ('2343,3444,2222') , which will be considered as one value instead of several numbers like in (2343,3444,2222)

Answer 3

Newer versions may have different options. I work with Oracle 9 and 10, and I usually pass a string of comma-separated values ​​and dynamically create SQL. However, there are some significant dangers associated with SQL injection.