Re: Select * from Table 1 where Col1 in (VARIABLE1) inside a stored proc.

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/02
Message-ID: <8enb4c$p0o$1@nnrp1.deja.com>#1/1

In article <Fty1wy.6EK_at_news.boeing.com>,   "Muon Ngu" <muon.ngu_at_boeing.com> wrote:
> I have a simple stored procedure, that I would like to have a passed
 in
> string(varchar2) for used in select from where col1 in (var1) in a
 stored
> procedure. I've tried everything but doesn't work. Followed is my
 proc.
>
> Thanks
>
> CREATE OR REPLACE PROCEDURE WSREVSECT_5
>
> pSectNos varchar2,
> pRetCode OUT varchar2
> )
> AS
> nCount number;
>
> BEGIN
>
> SELECT count(fksrev) into nCount
> FROM SREVSECT
> WHERE sectno IN (pSectNos ) /* as in 'abc', 'xyz', '012' */
> ;
> pRetCode:=to_char(ncount);
>
> End;
>
>

it works -- the above is the same as

where sectno = pSectNos

though, not what you want. You want it to be:

where sectno in ( 'abc', 'xyz', '012' )

NOT: where sectno in ( '''abc'', ''xyz'', ''012''' )

which is effectively is (else you could never search on a string with commas and quotes and so on -- it is doing the only logical thing right now).

You can do this:

ops$tkyte_at_dev8i> create or replace type myTableType as table of varchar2 (255);
  2 /

Type created.

ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> create or replace function in_list( p_string in

varchar2 ) return myTableType
  2 as

  3      l_string        long default p_string || ',';
  4      l_data          myTableType := myTableType();
  5      n                               number;
  6  begin
  7     loop
  8         exit when l_string is null;
  9         n := instr( l_string, ',' );
 10         l_data.extend;
 11         l_data(l_data.count) := ltrim( rtrim( substr( l_string, 1,
n-1 ) ) );
 12         l_string := substr( l_string, n+1 );
 13     end loop;
 14
 15     return l_data;

 16 end;
 17 /

Function created.

ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> select *

  2 from THE ( select cast( in_list('abc, xyz, 012') as mytableType ) from dual ) a
  3 /

COLUMN_VALUE

---

---

abc
xyz
012

ops$tkyte_at_dev8i>
ops$tkyte_at_dev8i> select * from all_users where username in   2 ( select *
  3 from THE ( select cast( in_list('OPS$TKYTE, SYS, SYSTEM') as mytableType ) from dual ) )
  4 /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
OPS$TKYTE                           23761 02-MAY-00
SYS                                     0 20-APR-99
SYSTEM                                  5 20-APR-99



--
Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html

--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 02 2000 - 00:00:00 CDT