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 MYCSV 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 Related Post: - Create CSV file using PL/SQL - Load CSV file in Oracle using PL/SQL - Create XML file using PL/SQL - Load XML File in Oracle Table - Generate XML Output from SQL - Generate XML data using SQL in Oracle Database
Create External Table from CSV File
Subscribe to:
Post Comments (Atom)
nice one.
ReplyDeletesir,
you have created directory MYCSV but within external table creation you have used "default directory MY_DIR", why?
Please provide some more clarification on this...Thanks
thankyou for pointing out the issue, i have updated the code
DeleteKeep on writing, great job!
ReplyDelete