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

I like the idea of dynamic sql, but I realize I don't know enough about it. I was trying to use execute immediate, but it won't allow me to use
execute immediate open c1 as select * from emp where empname in :sqlstring;
or
execute immediate open c1 as select * from emp where :sqlstring ;

whatever. What is the right way to handle those dynamic sql ? Is there a sight where I can find more ? Thanks.

On Tue, 29 May 2001 13:09:34 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>
>"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 Wed May 30 2001 - 00:13:52 CDT