Oracle DB: query using file in where clause

Question

I have just gone through the link using input from a text file for WHERE clause to query a table with input file.

But its not working with Oracle DB 11g. Is there any way to query the OIM tables with file inputs in Where Clause.

Answer 1

You can use an external table to refer to data in a flat file as part of a query.

In the example from the question you linked to, the equivalent would be something like:

create table employeenames (first_name varchar2(20))
organization external (
  type oracle_loader
  default directory my_dir
  location ('myfile.txt')
);

Then if the file contained first names you could find all employees with those names with:

select e.employee_id, e.first_name, e.last_name, e.salary 
from employeenames en
join employees e on e.first_name = en.first_name;

If the file just contained the single entry David that would give, with the HR schema I'm looking at:

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        165 David                Lee                             6800 
        151 David                Bernstein                       9500 
        105 David                Austin                          4800 

But the file has to be on the database server, and in a filesystem directory that Oracle has access to, and which is represented by an Oracle DIRECTORY object which points to that filesystem directory, and which the owner of the external table has permission to read from. So assuming this was run from the SCOTT account, you would have had to previously do this as a DBA:

create or replace directory my_dir as 'c:\';
grant read on directory my_dir to scott;
grant write on directory my_dir to scott;

Pointing to c:\ probably isn't a good idea of course, you'd normally have a directory dedicated for this.