Oracle – Read identifiers from a CSV and lookup records in a table in oracle database

It is a regular occurrence when DBA’s or developers are required to lookup data in a database for which identifiers are provided in a spreadsheet or a CSV file.

Follow steps below to read data using an external oracle table:

Create a folder or a directory to store the spreadsheet / csv file 

CREATE OR REPLACE DIRECTORY MYDIRECTORY AS '/LOCAL/NEW/FOLDER' ;

Grant read access to the oracle user e.g. 123123

GRANT READ ON DIRECTORY MYDIRECTORY TO 123123 ;

Create a table as 'DATA_READER' to read the contents from the spreadsheet / csv file and store the identifier data

CREATE TABLE DATA_READER
(
IDENTI NUMBER
)
ORGANIZATION EXTERNAL 
(
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY  MYDIRECTORY
      ACCESS PARAMETERS
            (RECORDS DELIMITED BY NEWLINE
             FIELDS (IDENTI NUMBER)
            )
      LOCATION ('IDENTIFIER_EXTRACT.CSV')
);

Execute the following query to perform the lookup:

SELECT * FROM USER_IDENTIFIER_TABLE
WHERE 1=1
AND IDENTIFIER IN (SELECT IDENTI FROM DATA_READER);