Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> One variable interpreted as an IN list
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 Received on Tue May 09 2000 - 00:00:00 CDT
![]() |
![]() |