Declaration of multiple values in Oracle BIND Variables

sql oracle bind-variables
dilipece2001 picture dilipece2001 · Jul 9, 2011 · Viewed 19.2k times · Source

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

OMG Ponies picture OMG Ponies · Jul 9, 2011

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

Related questions

What does the colon sign ":" do in a SQL query?

What does : stand for in a query? INSERT INTO MyTable (ID) VALUES (:myId) How does it fetch the desired value? Edit: Also what is that sign called? I wanted to search on google, but what's the name for :?

Read More
How to get tracing info for binding variables passed through OracleParameter in ODP.NET?

After Googling a lot and not finding what I'm looking for I decided to ask this question. I'm using binding variables as demonstrated in this awesome article from 2005 titled The Values That Bind by Mark A. Williams, like this: OracleParameter …

Read More
PL/SQL code using :1, :2, :3 and so on

What is the meaning of this code and what do you call this method? How will I know what is the value for :1, :2, :3 and so on? (PL/SQL Procedure) UPDATE tablename SET column = :1, column = :2, column = :3, column = :4, column= :5....

Read More