passing in list of values in stored procedure

From: <tiffanythang_at_gmail.com>
Date: Mon, 01 Oct 2007 08:42:05 -0700
Message-ID: <1191253325.251981.89310@50g2000hsm.googlegroups.com>

Hi,
Can someone tell me what I'm doing wrong in the following code? I would like to pass in a list of numbers such as (1,2) to a WHERE clause in my stored procedure but it kept complaining "ORA-01722: invalid number" when the procedure was executed.

create or replace procedure myproc (v_idlist in varchar2) AS

    v_id              number;
    v_name         varchar2(20);
    cursor g_name IS
       select id, name from mytab where id in (v_idlist);

begin

    open g_name;
    loop

	    dbms_output.put_line('idlist = '||v_idlist);
	    fetch g_name into v_id,v_name;
            exit when g_name%NOTFOUND;
            dbms_output.put_line('id   = '||v_id);
            dbms_output.put_line('name = '||v_name);

    end loop;
    close g_name;
end;
/

SQL> exec myproc('1')
idlist = 1
id = 1
name = JANE
idlist = 1

PL/SQL procedure successfully completed.

SQL> exec myproc('''1'',''2''');
idlist = '1','2'
BEGIN myproc('''1'',''2'''); END;

*
ERROR at line 1:

ORA-01722: invalid number
ORA-06512: at "TTHANG.MYPROC", line 11
ORA-06512: at line 1


SQL> select id, name from mytab where id in ('1','2');
       ID NAME
---------- --------------------
         1 JANE
         2 GEORGE

2 rows selected.

Thanks. Received on Mon Oct 01 2007 - 10:42:05 CDT