Declaration of multiple values in Oracle BIND Variables

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

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