Re: passing in list of values in stored procedure

"tiffanythang_at_gmail.com" <tiffanythang_at_gmail.com> wrote in news:1191253325.251981.89310_at_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);

You can't do that. You can't create SQL-statements on the fly by using variables.

There is a package called DBMS_SQL which with you can create dynamic SQL, and with it you can do what you are trying.

Beware: dynamic SQL in dangerous. If you let a user enter that list, he may format it so that the resulting SQL may be unhealthy for your database.

Instead, you might be able to use function INSTR(), take a look at it too. Received on Mon Oct 01 2007 - 10:55:36 CDT