Re: IN comparison using PL/SQL?

On Mon, 7 Dec 1998 13:24:40 -0500, "Tony" <app1axa_at_air.ups.com> wrote:

>Within a Stored procedure:
>
>Can someone please tell me how or if you can use a string variable in
>an IN comparison
>of a Where clause? The following statement will work:
>
>Select * from Employee where First_name in ('Tony','Mark','Steve');
>
>But when the contents are passes as a variable, it will not work, as
>follows:
>
>TempStr := 'Tony','Mark','Steve'
>Select * from Employee where First_name in (TempStr);
>
>If anyone has a work around, I would appreciate the help.

There are two ways:

select *
  from Employee
 where instr( TempStr, ''''||First_name||'''' ) > 0

This is OK but you loose the use of any indexes on the column First_name. :-(

Another way is to use dynamic sql. I have posted many examples of how to use the dbms_sql package. Check out dejanews.com for old posts concerning that. If you still get stuck, email me directly.

Hope this helps.

chris.

>
>Thanks, Tony

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation