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.