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

Home -> Community -> Usenet -> c.d.o.tools -> Re: how does a pl/sql function handle an 'IN' list operator as a parameter

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

From: <catherine_devlin_at_purpleturtle.com>
Date: 29 May 2001 15:54:34 GMT
Message-ID: <9f0gnq$6id$1@news.netmar.com>

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

Received on Tue May 29 2001 - 10:54:34 CDT

Original text of this message

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