Create CSV file using PL/SQL


To create a file, we need to create a directory and have the read write permission as

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;

Following is the pl/sql sample code to create CSV file

DECLARE
    F UTL_FILE.FILE_TYPE;
    CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
    C1_R C1%ROWTYPE;
BEGIN
    F := UTL_FILE.FOPEN('MYCSV','EMP_DEPT.CSV','w',32767);
    FOR C1_R IN C1
    LOOP
        UTL_FILE.PUT(F,C1_R.EMPNO);
        UTL_FILE.PUT(F,','||C1_R.ENAME);
        UTL_FILE.PUT(F,','||C1_R.SAL);
        UTL_FILE.PUT(F,','||C1_R.DEPTNO);
        UTL_FILE.PUT(F,','||C1_R.DNAME);
        UTL_FILE.NEW_LINE(F);
    END LOOP;
    UTL_FILE.FCLOSE(F);
END;
/



After the execution of above procedure, a file (EMP_DEPT.CSV) would have been created at "/home/oracle/mycsv/" location.

You may check it on linux by

cd /home/oracle/mycsv
cat EMP_DEPT.CSV

7369,SMITH,800,20,RESEARCH
7499,ALLEN,1600,30,SALES
7521,WARD,1250,30,SALES
7566,JONES,2975,20,RESEARCH
7654,MARTIN,1250,30,SALES
7698,BLAKE,2850,30,SALES
7782,CLARK,2450,10,ACCOUNTING
7788,SCOTT,3000,20,RESEARCH
7839,KING,5000,10,ACCOUNTING
7844,TURNER,1500,30,SALES
7876,ADAMS,1100,20,RESEARCH
7900,JAMES,950,30,SALES
7902,FORD,3000,20,RESEARCH
7934,MILLER,1300,10,ACCOUNTING



Related Post:
- Load CSV file in Oracle using PL/SQL
- Create External Table from CSV File
- Create XML file using PL/SQL
- Load XML file in Oracle Table


11 comments:

  1. it is a neat & clean example. thanks !!!

    ReplyDelete
  2. awesome. Thanks.

    ReplyDelete
  3. Thanx for appreciation, keep visiting for new & useful articles

    ReplyDelete
  4. Thanks for this. You might want to insert few comments on the code e.g C1_R C1%ROWTYPE; // what's this doing

    ReplyDelete
    Replies
    1. C1_R C1%ROWTYPE is declaration for a record having fields of cursor C1

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. there's no need to declare that rowtype

    ReplyDelete
  7. C1_R C1%ROWTYPE; not required

    ReplyDelete