Re: PL/SQL Using Variable IN Clause

From: Gert Rijs <nospampleasegem_at_wirehub.nl>
Date: 1997/11/20
Message-ID: <01bcf5e6$6c619930$0100007f@gertrijs>#1/1

(posted and replied to author)

This will never work:
list_string is a varchar2, so the effect of your select is:   SELECT COUNT(*) INTO RESULT
  FROM <TABLE>
  WHERE <COL_A> = 'VALUE'
  AND <COL_B> IN('TEST,MORE');
and *NOT* what you wanted (note the added quotes):   AND <COL_B> IN('TEST','MORE');

a workaround you can use if you know the maximum number of list-items is to parse LIST_STRING into several varchar2's and use those in your IN-clause. something like this:

PROCEDURE PROC(LIST_STRING IN VARCHAR2) IS   RESULT NUMBER;
/*
  default all list-items to NULL
  so any items that are not filled won't select   anything.
*/

  L1 varchar2(200) := NULL; 
  L2 varchar2(200) := NULL;
  L3 varchar2(200) := NULL;
  L4 varchar2(200) := NULL;
  L5 varchar2(200) := NULL;

  H varchar2(1000);
  tok varchar2(200);
BEGIN

• tokenize list_string into l1..l5 h := list_string ; While not h is null and Length(h)>0 Loop pos = Instr(h, ','); if pos = 0 then pos := Length(h); end if; tok := Substr(h, pos - 1); if l1 is null then l1 := tok; elsif l2 is null then l2 := tok; elsif l3 is null then l3 := tok; elsif l4 is null then l4 := tok; elsif l5 is null then l5 := tok; else raise_application_error(-20000, 'too many tokens'); end if; h := Substr(h, pos + 1); end loop; if l1 is null then
  • no tokens, why bother oracle? result := 0; else SELECT COUNT(*) INTO RESULT FROM <TABLE> WHERE <COL_A> = 'VALUE' AND <COL_B> IN(L1, L2, L3, L4, L5); end if; end;

I have not tested the above (I'm at home now), but you get the idea. A simple adjustment you could try in the above is using a pl/sql table of varchar2 instead of hardcoding L1..L5, that would cleanup the tokenize loop. However i don't know if you can use a pl/sql-table in an IN-clause (actually i'm sure you can't use COL_B IN (plsql-tablename), but perhaps you can use COL_B in (tab(0), tab(1), tab(2), tab(3), tab(4), etc..).

hope this helps,
gert

-- 
reply address is altered to keep the spam down
remove the nospamplease part to reply...

Robert Fernandes <rfernand_at_llnl.gov> wrote in article
<34746C85.86E636A6_at_llnl.gov>...


> I have spent much too much time trying to figure this one out on my own

> and it's now time to ask for some help...

> 

> I am trying to create a procedure with the following:

> 

> 

> PROCEDURE PROC(LIST_STRING IN VARCHAR2)

>   RESULT NUMBER;

> IS

> BEGIN

> 

>   <DO SOME STUFF>

> 

>   SELECT COUNT(*) INTO RESULT

>   FROM <TABLE>

>   WHERE <COL_A> = 'VALUE'

>   AND <COL_B> IN(LIST_STRING);  <-- Help Here!

> 

>   IF RESULT >= 1 THEN

>     <DO SOME STUFF>

>   ELSE

>     <DO SOME STUFF>

>   END IF;

> 

> END;

> 

> 

> I have not been able to make this work.  If I run as PROC('TEST')

> then it works as expected, but if I run as PROC('TEST,MORE') then

> it will not pick up the second value - which is expected.  So then I

> tried doing a search and replace on the LIST_STRING with , for ',' and

> it still didn't work.

> 

> Replacing the LIST_STRING with 'TEST','MORE' within the select works as

> expected, so the bottom line is whether or not a single comma delimited

> string can be passed in and some way manipulated to work within the in

> clause.

> 

> Any help would be greatly appreciated.  Thank you.

> 

> -- 

>  ___ ___ ___ ___ ___ ____   ___ ___ ___ _  _  _   _  _ ___ ___  ___

> | _ \   | _ \ __| _ \   _| | __| __| _ \ \| |/ \ | \| |   \ __|/ __/

> |  _/ | | _ < _||  _/| |   | _|| _||  _/    | _ \|    | | | _| \__ \

> |_|_\___|___/___|_|_\|_|   |_| |___|_|_\_|\_|/ \_\_|\_|___/___|/___/

> 

> ********************************************************************

>         Robert Fernandes - LLNL AIS Dept

> 

>         Phone:  (510) 423-1397     Fax:  (510) 423-5882

>         Email:  rfernand_at_llnl.gov - or - fernandes6_at_llnl.gov

> ********************************************************************

> 

Received on Thu Nov 20 1997 - 00:00:00 CST