Oracle: Select on data from a CSV file 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?
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);