Re: how does a pl/sql function handle an 'IN' list operator as a parameter

"kilidire" <yc_at_hwcn.org> wrote in message news:3b1371d9.23215802_at_news...
> I want to use the folowing query inside a pl/sql function or procedure
> and return a resultset or recordset. The challenging part I'm finding
> out is how to supply the IN List as a parameter . Any ideas ? Thanks.
>
>
> select * from emp where empname IN ('smith','john','white','jake')
>
>
> of course, the in list can contain anything from 1 value to 100
> values. The values are only known at runtime.
>
>
> eg
> create or replace function doThis(param1 in varcahr2) return
> ref_cursor_type
> as
> c1 is ref_cursor_type;
>
> begin
> open c1 as select * from emp where empname IN (param1);
>
> return c1;
> end;
>
>
> Thanks for any feedback.
>

You can but you need to use NDS (Native Dynamic Sql) or dbms_sql In native dynamic sql you need to setup a string with your statement and append the string param1 to it and then
open c1 for :sqlstr;
In dbms_sql set up the string
allocate a cursor (dbms_sql.open)
parse the cursor (dbms_sql.parse(cur_handle, sqlstr, compatibility flag) etc.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue May 29 2001 - 06:09:34 CDT