::CSV files in Oracle ::

How to make a csv/flat file in oracle through UTL_FILE
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

CSV(comma separated values) are mostly useful in data transformation. I believe these files are mostly useful in DWH.

We can do the same through sqlplus features.

SQL> set heading off
SQL> set feedback off
SQL> spool /home/oracle/test/csv.txt
SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp;

7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20

7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

SQL> spool off;

SQL> host more /home/oracle/test/csv.txt
SQL> select empno||','||ename||','||job||','||mgr||','||hiredate||','||sal||','||comm||','||deptno from emp;

7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20

7934,MILLER,CLERK,7782,23-JAN-82,1300,,10


Here you can find the other way with UTL_FILE. This concept is similar to FILES in C language.

Step 1:
--------
Need to create a directory at os level

Ex:-
-----
/home/oracle/test

Step 2:
---------
Need to connect to the database as a sysdba grant below privileges to the required user.

CONNECT / AS SYSDBA

GRANT EXECUTE ON UTL_FILE TO scott;

CREATE OR REPLACE DIRECTORY test AS '/home/oracle/test'; in windows('c:\test\');

GRANT read, write ON DIRECTORY test TO scott;


Step 3:
---------
CONNECT scott/tiger

CREATE OR REPLACE Procedure make_csv_emp(csv_symbol char)
IS

fp UTL_FILE.FILE_TYPE;
BEGIN
fp:=UTL_FILE.FOPEN('TEST','emp_csv.txt','w');
FOR REC IN (SELECT * FROM EMP)
LOOP
UTL_FILE.PUTF(fp,REC.EMPNO||csv_symbol||REC.ENAME||csv_symbol||REC.JOB||csv_symbol||REC.MGR||csv_symbol||REC.HIREDATE||csv_symbol||REC.SAL||csv_symbol||REC.COMM||csv_symbol||REC.DEPTNO||'\n');
END LOOP;
UTL_FILE.FCLOSE(fp);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000,'ORA-ERROR::'||SQLCODE||'::'||SQLERRM);
END;

Step 4:
--------

Just execute the function and open the file.

SQL> exec make_csv_emp(',');

PL/SQL procedure successfully completed.

[oracle@localhost test]$ more emp_csv.txt
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10