Below is the step by step example is given to export data into CSV file in Oracle database 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 other schema then make sure you have 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 following:

BEGIN
   export_to_csv;
END;

You can now check your directory you specified in Create Directory command that the file empdata.csv must exists with the table data.

Export data into csv in Oracle Database

Ask Your Questions By Comment Below.

Example is give to export data into csv file in Oracle using pl sql procedure. Export data to csv file from Oracle table example.

Post a Comment

  1. 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?

    ReplyDelete
    Replies
    1. 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.

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

      Delete

More Topics

V. Kapoor

{picture#https://3.bp.blogspot.com/-q3wCssWUHo8/ViDFfCpPEuI/AAAAAAAAD0M/MHPvgnl3T3U/s1600/authpic.JPG} Vinish Kapoor is a Software Consultant, currently working in a reputed IT company in Noida, India and doing blogging on Oracle Forms, SQL and PLSQL topics. He is an author of an eBook Oracle Forms Recipes, which is available on Google play. {facebook#https://www.facebook.com/foxinfotech2014} {twitter#https://twitter.com/foxinfotech} {google#https://plus.google.com/u/0/+VKapoor/posts}
Fox Infotech. Powered by Blogger.