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
- Generate XML Output from SQL
- Generate XML data using SQL in Oracle Database
- Convert XML to Rows and Columns in Oracle


29 comments:

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

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

    ReplyDelete
  3. 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
    2. HELLO
      I WANT TO CREATE A CSV FILE FROM A FUNCTION , IS IT POSSIBLE

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

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

    ReplyDelete
  6. C1_R C1%ROWTYPE; not required

    ReplyDelete
  7. 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; ---->>>>

    how do u do this ?

    ReplyDelete
    Replies
    1. execute these commands using DBA privileged user (SYS or SYSTEM).

      Delete
    2. Kool.
      What if i need the column names too for each of the columns.

      Delete
  8. hi
    I have an issue..
    Database server is configured in other country, i have all DML privilages, some times i need to take the backup of some tables few row. so can i take the backup on my personal computer as CSV before any update ...?

    ReplyDelete
  9. Thanx very usefull !!

    ReplyDelete
  10. Hi,

    Thanks for the post very informative.

    What if I want to export all the columns from my table to CSV file? Is there a work around for this or I have to specify each column name in the loop?

    ReplyDelete
  11. how can we apply column type?

    ReplyDelete
  12. Nice & Simple Example ...
    Thank You,
    Shri

    ReplyDelete
  13. INVALID DIRECTORY PATH ERROR WHAT CAN I DO ?

    ReplyDelete
  14. I wanted to create export csv file using plsql function, how can it be done

    ReplyDelete
  15. I have created csv file with above procedure.Now I want to create four csv file from this csv file with 5 records in each csv file.
    Anybody know how to create?

    ReplyDelete
  16. Hi Sir, I have created in SQL developer in windows its is successfully executed but unable to find the CSV file , directory and plsql has no issue

    ReplyDelete
  17. Thanks you so much.
    Regards,
    Jagan

    ReplyDelete
  18. Thank you so much.
    Regards,
    Jagan

    ReplyDelete
  19. Thanks for sharing such worthy content, this information is useful for knowledge seekers. Waiting for a more upcoming post like this.
    Graphic Designer Roles
    What Can Graphic Designers Do

    ReplyDelete