Re: passing in list of values in stored procedure

On Oct 1, 5:42 pm, "tiffanyth..._at_gmail.com" <tiffanyth..._at_gmail.com> wrote:
> 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.

static sql. You can't have an IN clause with multiple elements in static sql.
For a solution, see http://asktom.oracle.com, search for 'Dynamic IN list'

Hth

--
Sybrand Bakker
Senior Oracle DBA