Re: One variable interpreted as an IN list

In article <D6JR4.71952$O7.170765_at_typhoon.southeast.rr.com>,   "Ryan Kapil" <frankrizzo2k_at_hotmail.com> wrote:
> I'm dealing with the problem of being passed a list of strings from a
 web
> front end, and using the list in a SQL statement in a stored
 procedure.
> Example:
>
> Passed: "A,B,C" , pull it into variable v_list.
>
> Need to use it in SQL:
>
> First thought is to use:
> SELECT vendor_name, merchant_code, merchant_id
> FROM gsa_merchant
> WHERE merchant_code IN (v_list)
>
> But if you pass the variable to IN, Oracle interprets it as a single
 string,
> because it isn't broken up into an arbitrary number of separate
 values The
> database searches for all rows where merchant code is an exact match
 to the
> string ''A,B,C''.
>
> I found examples at JL Computer Consultancy's web page
> http://www.jlcomp.demon.co.uk/ind_plsql.html that handle this
 problem, but
> the best example requires you have the Objects option enabled in
 Oracle 8
> (we don't, and the bizness people are dragging their feet on the 8i
 update).
> I can use the example for 7.3 that uses a Package to pull the
 variables into
> a PL/SQL table, uses a dummy sequential numbers table and other
 strange
> stuff, but I was wondering if someone had a better way? The article
 called,
> PL/SQL arrays v7, shows what I'm doing now.
>
> Sorry if this is a "newbie" question, as I have been called recently
 on this
> newsgroup, but I am in fact new to PL/SQL, and would appreciate any
 help.
> Thanks
>
>

that is the best way. There are other ways -- but using that plsql is the most efficient, performant, scalable methods.

I hesitate to show you other ways -- you are doing the right thing as it is. If you were doing it other ways, we would tell you how to do it as you are above.

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