Create External Table from CSV File

To Create an External Table from CSV File, Follow these simple Steps
1) Create a Directory
2) Grant Read/Write Permission to that Directory
3) Place your CSV file in that directory at OS Level
4) Create EXTERNAL Table

Example:
1) create or replace directory MYCSV as '/home/oracle/mycsv'; 
 Note: /home/oracle/mycsv has to be physical location on disk.
 
2) grant read, write on directory MYCSV to scott;

3) Put your csv file on /home/oracle/mycsv at OS level

4) create table my_ext_table
   (
     ENAME VARCHAR2(10),
     DNAME VARCHAR2(14),
     JOB VARCHAR2(9)
   )
   organization external
   (
     type ORACLE_LOADER
     default directory MY_DIR
     access parameters 
     (
       records delimited by newline
       fields terminated by "~"
     )
     location ('myfile.csv')
   )
   reject limit unlimited;

   
To create this CSV File you may use following command

set pagesize 0 newpage 0 feedback off

select ENAME || '~' || DNAME || '~' || JOB from SCOTT.EMP, SCOTT.DEPT 
WHERE EMP.DEPTNO=DEPT.DEPTNO;

spool /home/oracle/test_dir/myfile.csv
/
spool off