1

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?

2

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.