How do I pass a list of numbers into a stored procedure?

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 like this:

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 should I modify my stored procedure accordingly? I am doing this so that I can display the results in a Crystal Report... (just in case that affects anything).. Thanks!!

The best option would be to pass a 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 may not be able to pass a proper collection to a stored procedure. If that is the case, you'd have to pass in a comma-separated list of numbers. Your procedure would then have to parse that comma-separated string into a collection. You can use (or modify) Tom Kyte's 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

Newer versions might have different options. I work some with Oracle 9 and 10, and I will typically pass in a string of comma-separated values and dynamically build the SQL. There are some significant dangers with SQL injection to be aware of, though.

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 loop it using..

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

To test it,

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 variation in syntax.

Of course you can pass in comma separated values too but that will come in as a string. Your string should be something like 'val1','val2','val3'. You need to be careful when you have numbers as the whole string will look like in ('2343,3444,2222') which will be treated as one value instead of multiple numbers as in (2343,3444,2222)