Declaration of multiple values in Oracle BIND Vari

Question

I am trying to pass multiple values about 3000 values, to a BIND variable in Oracle SQL PLUS command prompt like..

SELECT JOB
  FROM EMP 
 WHERE JOB IN :JOB -- bind variable value

I want to see my result, as all the values in EMP table on column JOB matching to that variable list has to be fetched out.

---

As its being production environment I can't create tables only I have grant on SELECT clause.

Need more information on how exactly it get executed when I run the same query from UNIX-SQL PLUS environment.

Will it prompt asking to enter the BIND variables values or can I refer to a file which has values as... :JOB1 := 'MANAGER' :JOB2 := 'CLERK' :JOB3 := 'ACCOUNTANT'

Answer 1

One way to do it in 10g and up is with subquery factoring.

Assume :JOB is a comma-separated list of values. The following would work:

with job_list as
(select trim(substr(job_list,
                    instr(job_list, ',', 1, level) + 1,
                    instr(job_list, ',', 1, level + 1)
                      - instr (job_list, ',', 1, level) - 1
                   )
            ) as job
  from (select 
               -- this is so it parses right
               ','|| :JOB ||',' job_list
         from dual)
connect by level <= length(:JOB)
                     - length (replace (:JOB, ',', '') ) + 1
)
select * from emp
 where job in (select * from job_list);

It's a bit ugly to read, yes, but it works, and Oracle's clever enough to do the parsing of the list of values once, not once per row, which is what you end up with otherwise. What it does under the covers is build a temporary table of the parsed values, which it then can join to the base table.

(I didn't come up with this on my own - original credit goes to an asktom question.)

---

:JOB is a bind variable which must be declared and populated before it can be used. The statements below demonstrate how to do that with SQL*Plus.

SQL> variable JOB varchar2(4000);

SQL> exec :JOB := '10, 20';

Answer 2

The first question I have to ask is this: where is this list of about 3000 values coming from? If it's coming from another table, then you can write something like the following:

SELECT JOB
  FROM EMP
 WHERE JOB IN (SELECT something FROM some_other_table WHERE ... )

For the rest of this answer, I'll assume it's not in the database anywhere.

In theory it's possible to do what you want. There are various ways to devise a query with a lot of bind variables in it. As an example, I'll write a script to query the all_objects data dictionary view using 3000 bind variables. I'm not going to write a SQL*Plus script with 3000 bind variables in it, so instead I wrote a Python script to generate this SQL*Plus script. Here it is:

ns = range(1, 9001, 3) # = 1, 4, 7, ..., 8998

# This gets rid of a lot of lines saying 'PL/SQL procedure successfully completed'.
print "SET FEEDBACK OFF;"
print

# Declare the bind variables and give them values.
for i, n in enumerate(ns):
    print "VARIABLE X%04d NUMBER;" % i
    print "EXEC :X%04d := %d;" % (i, n)
    print

query = "SELECT object_name FROM all_objects WHERE"

# Break up the query into lines to avoid SQL*Plus' limit of 2500 characters per line.
chunk_size = 100
for i in range(0, len(ns), chunk_size):
    query += "OR object_id IN (" + ",".join( ":X%04d" % j for j in range(i, i + chunk_size) ) + ")\n"

query = query.replace("WHEREOR", "WHERE") + ";\n"
print query

I was then able to run this script, redirect its output to a .sql file, and then run that .sql file in SQL*Plus.

You may notice above that I wrote 'In theory it's possible...'. I put the in theory clause there for a good reason. The query appears to be valid, and I don't know of a reason why it shouldn't execute. However, when I ran it on my Oracle instance (XE 11g Beta), I got the following output:

SQL> @genquery.sql
SELECT object_name FROM all_objects WHERE object_id IN (:X0000,:X0001,:X0002,:X0
003,:X0004,:X0005,:X0006,:X0007,:X0008,:X0009,:X0010,:X0011,:X0012,:X0013,:X0014
,:X0015,:X0016,:X0017,:X0018,:X0019,:X0020,:X0021,:X0022,:X0023,:X0024,:X0025,:X
0026,:X0027,:X0028,:X0029,:X0030,:X0031,:X0032,:X0033,:X0034,:X0035,:X0036,:X003
7,:X0038,:X0039,:X0040,:X0041,:X0042,:X0043,:X0044,:X0045,:X0046,:X0047,:X0048,:
X0049,:X0050,:X0051,:X0052,:X0053,:X0054,:X0055,:X0056,:X0057,:X0058,:X0059,:X00
60,:X0061,:X0062,:X0063,:X0064,:X0065,:X0066,:X0067,:X0068,:X0069,:X0070,:X0071,
:X0072,:X0073,:X0074,:X0075,:X0076,:X0077,:X0078,:X0079,:X0080,:X0081,:X0082,:X0
083,:X0084,:X0085,:X0086,:X0087,:X0088,:X0089,:X0090,:X0091,:X0092,:X0093,:X0094
,:X0095,:X0096,:X0097,:X0098,:X0099)
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 556
Session ID: 137 Serial number: 29

The ORA-03113 error indicates that the server process crashed.

I tried several variations on this:

• not using bind variables at all (i.e. putting the values in directly)
• not using IN lists, i.e. writing SELECT ... FROM all_objects WHERE object_id=:X0000 OR object_id=:X0001 OR ...,
• using OMG Ponies' approach,
• using OMG Ponies' approach without using bind variables,
• copying the data out of all_objects into a table, and querying that instead.

All of the above approaches caused an ORA-03113 error.

Of course, I don't know whether other editions of Oracle will suffer from these crashes (I don't have access to any other editions), but it doesn't bode well.

EDIT: You ask if you can achieve something like SELECT JOB FROM EMP WHERE JOB IN (:JOB). The short answer to that is no. SQL*Plus's usage message for the VARIABLE command is as follows:

Usage: VAR[IABLE] [  [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
                    VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
                    NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
                    REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE ] ]

All of the above types are single data values, with the exception of REFCURSOR, but SQL*Plus still seems to treat that as a single value. I can't find a way to query data returned in a REFCURSOR this way.

So in summary, what you're attempting to achieve is almost certainly impossible. I don't know what your ultimate aim is here, but I don't think you'll be able to do it using a single query in SQL*Plus.

Answer 3

Have a look at the Ugly-Delimited-String-Approach(tm).

That is, bind a string and convert it to a list in SQL. Ugly, that is.

Answer 4

While facing similar problem, I came up with this dirty solution:

select * from my_table where ',param_1,param_2,param_3,param_4,' LIKE '%,'||my_column||',%'

Answer 5

Oracle bind variables are a one-to-one relationship, so you'd need one defined for each value you intend to include in the IN clause:

SELECT JOB
  FROM EMP 
 WHERE JOB IN (:JOB1, :JOB2, :JOB3, ..., :JOB3000)

You need to also be aware that Oracle IN only supports a maximum of 1,000 values, or you'll get:

ORA-01795: maximum number of expressions in a list is 1000

The best alternative is to create a table (derived, temporary, actual, or view), and join to it to get the values you want. IE:

SELECT a.job
  FROM EMP a
  JOIN (SELECT :JOB1 AS col FROM DUAL
        UNION ALL
        SELECT :JOB2 FROM DUAL
        UNION ALL
        SELECT :JOB3 FROM DUAL
        UNION ALL 
        ...
        UNION ALL 
        SELECT :JOB3000 FROM DUAL) b ON b.col = a.job

Answer 6

Our team just ran into this issue and this query is very clean to pass multiple state values. Each value is separated by comma only. I can pass all 52 states if required:

SELECT county_code,state_code FROM WMS__ASSET_COUNTY_STATE 
WHERE STATE_CODE IN
(SELECT regexp_substr(:bindstateocde, '[^,]+', 1, LEVEL) token
            FROM dual
            CONNECT BY LEVEL <= length(:bindstateocde) - length(REPLACE(:bindstateocde, ',', '')) + 1) ;