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 :
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
F := UTL_FILE.FOPEN(‘MYCSV’,’My_First_CSV.CSV’,’w’,32767); — Use 32767 as highest allowed excel row
FOR C1_R IN C1
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 :
Output : ( The output of this command is CSV file output)
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.