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

In article <3b1371d9.23215802_at_news>, kilidire <yc_at_hwcn.org> writes:
>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.
>

Hi! Three ideas:

1. (Sophisticated, I've never actually tried it) Use a new Oracle8 feature, varying arrays. create or replace type PARAMLIST as varray(100) of VARCHAR2(40); define a variable AS PARAMLIST, fill the array, and pass the varray in as the function's argument.
2. (Kludgey) Create a table and fill it with your desired values. Then the function can just join against your table of parameters. Not very elegant because the function has to 'know' about the table instead of being told about it through the argument list. (Hmm, there isn't any way to pass a table as a function's argument, is there?)
3. (even more kludgey) Concatenate all your desired values into one big character string, pass that in, and search it with INSTR(). Delimit the values with something so that you don't get false hits from partial matches; for instance, delimit with ':' and search for ':TIM:' in ':JENNY::TIMOTHY::CHRIS:' instead of 'TIM' in 'JENNYTIMOTHYCHRIS' (gives a false hit)

Good luck!
- Catherine

• Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net