How to create csv file using PL/SQL step by step?

In my previous article I have given information about the PLSQL XML data examples. In this article I would like to give you information and proper steps to create csv file using PL/SQL code. There are so many times we are having business requirements to create the csv file using PL/SQL or any other technologies. In this particular article I would like to give more information about to create csv file using PL/SQL code so that it will be easy for developers to tackle this kind of situation.

Steps to create csv file using PL/SQL :

Step 1 : Permission required to create Directory

The first step is to have permission for create directory as well as create file permission for creating the new csv file.

Command Used :

create or replace directory MYCSV as ‘/home/oracle/mycsv’;

Note that the location mentioned is physical location /home/oracle/mycsv

Step 2 : Grant the RW ( Read and write) access to the specified created directory so that we can place the csv in the location.
Command Used :

 grant read, write on directory MYCSV to scott;

Step 3 : Write the PL/SQL code to create the CSV file for Employee table.

PL/SQL Actual Code :

DECLARE
    F UTL_FILE.FILE_TYPE;
    CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO; —Create Cursor and place all employee details
    C1_R C1%ROWTYPE;
BEGIN
    F := UTL_FILE.FOPEN(‘MYCSV’,’My_First_CSV.CSV’,’w’,32767); — Use 32767 as highest allowed excel row
    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 (MY_FIRST_CSV.CSV) would have been created at “/home/oracle/mycsv/” location.

Step 4 : Check whether CSV is created or not

Commands used :

cd /home/oracle/mycsv
cat MY_FIRST_CSV.CSV

Output : ( The output of this command is CSV file output)

1,Amit,80000,20,IT
2,Rahul,1600,30,SALES
3,Mona,1250,30,SALES
4,Raveendra,2975,20,IT
5,Amal,1250,30,SALES
6,Pandu,2850,30,SALES
7,Rock,2450,10,ACCOUNTING
8,Sachin,3000,20,RESEARCH

With using the above steps we can create csv file using PL/SQL code. Please find data analyst jobs if you require. The above code is really useful for developers when you require to convert file in csv using programming language.