Re: Using SQL IN predicate in PL/SQL

You can use instr as suggested by Terje, but to make sure the string can be
matched correctly in more general cases, then follow one of these examples:

1) If the v_Test  and pool values are exactly one character:

    v_Pool := 'ABCDEF123456';
    if instr(v_Pool, v_Test) > 0 then
    ....;
    end if;
 

2) If the v_Test and pool values are all the same fixed length and do not
    contain the delimiter char used in v_Pool:

    v_Pool := 'ABC,DEF,GHI';
    if instr(v_Pool, vTest) > 0 then
    ...;
    end if;
 

3) If the v_Test and pool values are arbitrary length but do not contain
    the delimiter char used in v_Pool:

    v_Pool := ',ABC,AB,CDEF,GH,EFG,';
    if instr(v_Pool, ','||v_Test||',') > 0 then
    ...;
    end if;

    In this last case, you need delimiters at beginning and end of v_Pool and
    also concatenated to beginning and end of v_Test.  This prevents false
    matches of test strings eg:
       BC
       EF
       A
       B
       CD
     etc.

If this clause were used in SQL, then consider that
   v_Test in ('ABC','DEF','GHI')
is actually transformed in the optimizer to
   v_Test = 'ABC'  or  v_Test = 'DEF'  or  v_Test = 'GHI'
In a complex SQL query this may be transformed again to
   SELECT fields FROM tables WHERE v_test = 'ABC'
   UNION
   SELECT fields FROM tables WHERE v_test = 'DEF'
   UNION
   SELECT fields FROM tables WHERE v_test = 'GHI'
which could severely impact performance.

In a simple PL/SQL expression as you have shown, this shouldn't
be a problem.

Hope this helps.
 
 

R Chin wrote:

In PL/SQL I have constructed a variable (called v_Pool) with the value
('ABC','DEF','GHI')   to be used in the where clause of dynamic sql

The following code does not work...please help

if v_test  IN   v_Pool then
........;
end if;

Thanks

Robert

--
Barry P. Grove                          BarrySoft Consulting Inc.
Consulting Analyst                      (604)220-5568
PeopleSoft, Oracle DBA, UNIX SysAdmin   bpgrove_at_barrysoft.com
  Received on Fri Mar 01 2002 - 03:27:21 CET