I have a PLSQL script which has a IN list where it takes some ids as input. For example
SELECT * FROM EMPLOYEE WHERE EMPLOYEE_ID IN (comma separated list )
I want to run this quest inside a shell script but I would like to prepare the IN list dynamically where the employee ids will be there in the same directory in a different file in the below format.
File Name - employee_list
234
456
784
123
How can I insert all the employee ID into the PLSQL script in one go ? Any sample code is very helpful. Thanks in advance.
You haven't mentioned your Oracle version. To solve this problem, you could use INSTR/SUBSTR functions in any (modern) version of Oracle, or if you have 10g or higher, you could use regular expressions.
SQL>
SQL> --
SQL> -- Check the data in the EMPLOYEES table for a few EMPLOYEE_IDs
SQL> --
SQL> select employee_id, first_name, last_name
2 from employees
3 where employee_id in (100, 121, 145, 179, 202);
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
121 Adam Fripp
145 John Russell
179 Charles Johnson
202 Pat Fay
5 rows selected.
SQL>
SQL> --
SQL> var str varchar2(30)
SQL> exec :str := '100,121,145,179,202';
PL/SQL procedure successfully completed.
SQL> print str
STR
------------------------------
100,121,145,179,202
SQL>
SQL> --
SQL> -- Query 1: Should work in any (non-ancient / real-life) version of Oracle
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
2 from employees e,
3 (
4 select substr(','||:str||',',
5 instr(','||:str||',',',',1,level) + 1,
6 instr(','||:str||',',',',1,level+1) - instr(','||:str||',',',',1,level) - 1
7 ) as token
8 from dual
9 connect by level <= length(:str) - length(replace(:str,',')) + 1
10 ) x
11 where e.employee_id = x.token;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
121 Adam Fripp
145 John Russell
179 Charles Johnson
202 Pat Fay
5 rows selected.
SQL>
SQL> --
SQL> -- Query 2: Should work in Oracle 10g and higher
SQL> --
SQL> select e.employee_id, e.first_name, e.last_name
2 from employees e,
3 (
4 select regexp_substr(:str,'[^,]+',1,level) as token
5 from dual
6 connect by level <= length(:str) - length(replace(:str,',')) + 1
7 ) x
8 where e.employee_id = x.token;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
100 Steven King
121 Adam Fripp
145 John Russell
179 Charles Johnson
202 Pat Fay
5 rows selected.
SQL>
SQL>
