Re: PL/SQL Using Variable IN Clause

Gert Rijs wrote:
>
> (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..).

Why not creating a temporay table (use date and time in name so that you'll get an almost unique table name) with dbms_sql in which you insert the parsed items, then the WHERE-cluase would turn into something like:

	...
      WHERE <COL_A> = 'VALUE'
      AND <COL_B> IN(select <item> from <temptable>);

And finally you drop the temporary-table.

>
> 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
> > ********************************************************************
> >
 

-- 
Regards

Matthias Gresz    :-)