Assign Multiple Values to a Bind Variable

[Bonediggler1]

Hi-

I am wondering how to assign multiple values to a bind variable. For example, my code utilizes something like

SELECT X, Y, Z FROM TABLE X WHERE PRODUCT = PRODUCT)

At run time, the user would enter something like "01" when prompted to enter a value for PRODUCT. I would like them to be able to enter multiple values like 01,02,03 etc.


Thank you!

 

[SantaMufasa]

Diggler,

There are many ways to resolve your need in Oracle. One thing to be aware of up front is that if you are looking for "01" (literally), then it is not the same as "1" unless you are comparing values that Oracle interprets exclusively as number expressions. Specifically, if you want product codes such as "02", then you must define the PRODUCT column as VARCHAR2(5), for example.

Additionally, if you have a PRODUCT whose code is "01001", you don't want it to appear in the result set if the contents of your RODUCT bind variable happens to be "01".

Here are some sample data:

SQL> select * from x;

         X          Y          Z PRODUCT
---------- ---------- ---------- -------
         1          1          1 01
         2          2          2 02
         3          3          3 03
         4          4          4 04

Then, to allow your users to input values from a prompt, placing the entered values into your bind variable, RODUCT, I elected to use a script that I titled "tt_543.sql":

var product varchar2(1000)
ACCEPT prods prompt "Enter one or more product codes in the format 01,02,03: "
exec :product := '~'||replace('&prods',',','~')||'~'
SELECT X, Y, Z, PRODUCT
  FROM X
 WHERE INSTR(:PRODUCT,'~'||product||'~') > 0
/

Here are a couple of executions of "tt_543" from a SQL*Plus prompt:

SQL> @tt_543
Enter one or more product codes in the format 01,02,03: 02,01,03

         X          Y          Z PRODUCT
---------- ---------- ---------- -------
         1          1          1 01
         2          2          2 02
         3          3          3 03

SQL> SQL> @tt_543
Enter one or more product codes in the format 01,02,03: 03,1,6

         X          Y          Z PRODUCT
---------- ---------- ---------- -------
         3          3          3 03

Let us know if this meets you needs and/or if you have follow-up questions.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”