| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: passing in list of values in stored procedure
What you might consider instead, is passing in an array of numbers and then inserting that array into a global temp table. Then
changing your code so that its a WHERE id in (SELECT id from gtt). When the session is over, the code will self purge and at any
time is available for parallel activity as well... no bind variables required... no literals... and it can be 1 or more values in
the list then.
On Mon, 01 Oct 2007 08:42:05 -0700, "tiffanythang_at_gmail.com" <tiffanythang_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.
BChase
bsc7080mqcXX_at_myoracleportal.com
(remove XX to contact)
 |
 |