Press "Enter" to skip to content

How to Export Data into CSV File in Oracle Using PL SQL Procedure

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:
Export data into csv file using pl sql procedure
Export data into csv in plsql

3 Comments

  1. arulnarayanan arulnarayanan December 15, 2015

    Hi. While creating the directory object,will it be mapped to the directory in the user system or mapped to directory where the database is installed(remote system)? Does the UTL_FILE available by default?

  2. V. Kapoor V. Kapoor December 15, 2015

    Hi, Oracle database code runs at server-side, hence the directory object would be created at server-side and will refer to directory exists on Oracle database server. Yes Utl_File package is available by default in Oracle database.

  3. Unknown Unknown July 24, 2016

    if i want to write in the local directory,not server side what i have to do?

Comments are closed.