Create 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

For Example: 

1) create or replace directory MYCSV as 'E:\test'; 
 Note: E:\test has to be physical location on disk.
 
2) grant read, write on directory MYCSV to scott;

3) Put your csv file on E:\test at OS level

4) create table 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 ('testfile.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 E:\test\testfile.csv
/
spool off