External Table To Insert Data From csv File to Staging Table

 

EXTERNAL TABLE

Step 1 : Grant READ and WRITE access on the directory object to users

 

        grant read,write on directory lang_external to ot;

 

Step 1 : Create a table as shown

 

Create table employees

( employee_id number,

Employee_name varchar2(20)

) ORGANIZATION EXTERNAL 

(

TYPE oracle_loader

DEFAULT DIRECTORY <Directory_name>

ACCESS PARAMETERS

( RECORDS DELIMITED BY NEWLINE

SKIP=1

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

)

  LOCATION('CSV_FILE_NAME.csv')

); 

 

Step 2 :

Upload the .csv file on the directory you had mentioned.

Step 3 : query the table

 

select * from employees;