Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: passing in list of values in stored procedure

Re: passing in list of values in stored procedure

From: BChase <bsc7080xxmqc_at_myoracleportal.com>
Date: Tue, 02 Oct 2007 01:22:25 -0400
Message-ID: <e7l3g3lhcr187k2vnd07jjjh892h7fd5bo@4ax.com>


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)



Resource Library is now Online @ http://www.MyOraclePortal.com
Received on Tue Oct 02 2007 - 00:22:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US