Oracle: Select on data from a CSV file

Question

I have a CSV file with many integers in the form:

id
---
1
2
3

I need to write a statement that selects ids from a table that are in the list in my CSV.

I could just convert the data 1000 elements at a time into a SQL IN like:

SELECT * FROM TABLE WHERE ID IN (....)

but that would take hundreds of queries, is there a better way I can do this?

Answer 1

You can do this using an Oracle External Table.

First, create a directory pointing at the directory holding the .csv file:

CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/path/to/yourdirectory';

Grant read access to your user:

GRANT READ ON DIRECTORY MYDIRECTORY TO YOURUSER;

Create a table that'll read from your CSV file:

CREATE TABLE mytable
(
 id NUMBER
)
ORGANIZATION EXTERNAL
 (
 TYPE ORACLE_LOADER
 DEFAULT DIRECTORY MYDIRECTORY 
 ACCESS PARAMETERS
 (RECORDS DELIMITED BY NEWLINE
 FIELDS (id NUMBER)
 )
 LOCATION ('yourfile.csv')
 );

Your query would then be:

SELECT * FROM TABLE WHERE ID IN (SELECT id FROM MYTABLE);