Below is the step by step example is given to export data into CSV file in Oracle using PL SQL procedure. In this example, data is being exported from some fields of Emp table of Scott schema to a CSV file using UTL_FILE Oracle package.
Follow the below steps to export data from Oracle Database table to a CSV file.
1. Create a Directory Object in which you will write CSV file.
-- for windows systems create as following change the folder highlighted with your directory
CREATE OR REPLACE DIRECTORY CSVDIR AS 'd:\temp' /
-- for Unix/Linux systems create as following
CREATE OR REPLACE DIRECTORY CSVDIR AS '/temp/' /
Note: Change the folder location to your directory location, e.g., c:\abc or /abc/.
2. Create a Database Procedure in Scott schema or if you are creating in another schema then make sure you have the Emp table in that schema, else you need to change the Cursor and the field references for any other table.
CREATE OR REPLACE PROCEDURE export_to_csv
IS
  v_file   UTL_FILE.file_type;
  v_string  VARCHAR2 (4000);
  CURSOR c_emp
  IS
   SELECT empno,
       ename,
       deptno,
       sal,
       comm
    FROM emp;
BEGIN
  v_file :=
   UTL_FILE.fopen ('CSVDIR',
           'empdata.csv',
           'w',
           1000);
  -- if you do not want heading then remove below two lines
  v_string := 'Emp Code, Emp Name, Dept, Salary, Commission';
  UTL_FILE.put_line (v_file, v_string);
  FOR cur IN c_emp
  LOOP
   v_string :=
      cur.empno
     || ','
     || cur.ename
     || ','
     || cur.deptno
     || ','
     || cur.sal
     || ','
     || cur.comm;
   UTL_FILE.put_line (v_file, v_string);
  END LOOP;
  UTL_FILE.fclose (v_file);
EXCEPTION
  WHEN OTHERS
  THEN
   IF UTL_FILE.is_open (v_file)
   THEN
     UTL_FILE.fclose (v_file);
   END IF;
END;
3. Now run the procedure as follows:
BEGIN Â Â export_to_csv; END;
You can now check your directory you specified in Create Directory command that the file empdata.csv must exist with the table data. I have created a utility which generates the procedure online try it:
