EXPORT TABLE Data to Flat Files

How to Export Data into file from ORACLE Table?

There are different ways you can achieve this. One is by Using Unix Scripts if you have access to do so in your Project and other is by UTL_FILE utility. In this post, we will see how it can be done using ORACLE Stored Procedure- I will write a separate post for Unix Scripting.

Follow the below steps to export data from Oracle Database table to a CSV file.

•          Create a Directory Object in which you will write CSV file.
•          Create Stored Procedure with UTL_FILE Package
•          Now run the Procedure and check if Data has been Exported

 1. Create a Directory Object in which you will write CSV file.

Note that before using UTL_FILE package in Oracle you may need to enable it and create a directory object:

CONNECT SYS AS SYSDBA;

***Enable UTL_FILE****

  GRANT EXECUTE ON UTL_FILE TO PUBLIC;

****Create a directory object for UTL_FILE and grant permissions****

  CREATE DIRECTORY EXPORTDIR AS 'F:\EXPORT';

  GRANT READ ON DIRECTORY EXPORTDIR TO PUBLIC;

  GRANT WRITE ON DIRECTORY EXPORTDIR TO PUBLIC;

NOTE: You will need to create Directory manually and can set the target location according to your choice.

2. Create Stored Procedure with UTL_FILE Package

Now, Create a Stored Procedure:

SQL> CREATE OR REPLACE PROCEDURE EXPORT_DATA
  2  IS
  3  v_file     UTL_FILE.File_type;
  4  v_string   VARCHAR2 (4000);
  5
  6  CURSOR C IS
  7  SELECT EMP_NO, ENAME, DEPTNO, HIREDATE, DNAME, DLOC, SALARY, JOB_ID
  8  FROM EMPLOYEES;
  9  BEGIN
 10  v_file := UTL_FILE.FOPEN('EXPORTDIR','EXTRACT.csv','w',1000);
 11  v_string := 'EMP_NO, ENAME, DEPTNO, HIREDATE, DNAME, DLOC, SALARY, JOB_ID';
 12  UTL_FILE.put_line (v_file, v_string);
 13
 14  FOR cur IN C
 15  LOOP
 16  v_string := cur.EMP_NO|| ','|| cur.ENAME|| ','|| cur.DEPTNO|| ','|| cur.HIREDATE|| ','||cur.DNAME|| ','||cur.DLOC|| ','||cur.SALARY|| ','||cur.JOB_ID;
 17  UTL_FILE.put_line (v_file, v_string);
 18  END LOOP;
 19  UTL_FILE.FCLOSE(v_file);
 20  EXCEPTION
 21  WHEN OTHERS THEN
 22  IF UTL_FILE.IS_OPEN(v_file)
 23  THEN
 24  UTL_FILE.FCLOSE(v_file);
 25  END IF;
 26  END;
 27  /

Procedure created.

3. Now run the procedure as following:

BEGIN

EXPORT_DATA

END;

You can now check your directory you specified in Create Directory command for Exported File.

Another Example:

We will use the same Directory in This Example and will Export data for the below Table. We will create a Stored Procedure which will accept Directory and File name as input.

SQL> CREATE TABLE STATE_CAPITAL

           (

           STATE VARCHAR2(20),

           CAPITAL VARCHAR2(20)

           );

Table created.

SQL> INSERT INTO STATE_CAPITAL Values('Karnataka','Bangalore');

1 row created.

SQL> INSERT INTO STATE_CAPITAL Values('Tamilnadu','Chennai');

1 row created.

SQL> INSERT INTO STATE_CAPITAL Values('Uttarakhand','Dehradun');

1 row created.

SQL> COMMIT;

Commit complete.

Let’s create a Stored Procedure with UT_FILE package and a cursor to export data into a CSV file:

SQL> CREATE OR REPLACE PROCEDURE EXPORT_RECORDS(Dir VARCHAR2, File_Name VARCHAR2)
  2  IS
  3  CURSOR C IS SELECT STATE || ',' || CAPITAL AS RECORD FROM STATE_CAPITAL;
  4  File UTL_FILE.FILE_TYPE;
  5  BEGIN
  6
  7  /*Open the file for writing*/
  8  File := UTL_FILE.FOPEN(UPPER(Dir), File_Name, 'w', 32767);
  9
 10  /*Export rows one by one*/
 11  FOR rec IN C LOOP
 12
 13  /*All columns were concatenated into single value in SELECT*/
 14  UTL_FILE.PUT_LINE(File, rec.RECORD);
 15  END LOOP;
 16  UTL_FILE.FCLOSE(File);
 17  END;
 18  /

Procedure created.

Now let's execute the stored procedure and check the file at the desired location:

/*Execute procedure to export data to CSV file */

EXEC EXPORT_RECORDS ('EXPORTDIR', 'STATE_DATA.txt');

We can also DBMS_SQL built-in package to export data to a CSV file. The DBMS_SQL package can be useful when you need to get column metadata (number of columns, their data types i.e) and data dynamically:

Using DBMS_SQL package:

SQL> CREATE OR REPLACE PROCEDURE EXPORT_FEED(Dir VARCHAR2, File_Name VARCHAR2)
  2  IS
  3  Select_Stmt VARCHAR2(100) := 'SELECT STATE || '','' || CAPITAL FROM STATE_CAPITAL';
  4  cur INTEGER;
  5  File UTL_FILE.FILE_TYPE;
  6  RECORD VARCHAR2(4000);
  7  ret INTEGER;
  8  BEGIN
  9  /*Open a cursor for the specified SELECT statement*/
 10  cur := DBMS_SQL.OPEN_CURSOR;
 11  DBMS_SQL.PARSE(cur, Select_Stmt, DBMS_SQL.NATIVE);
 12  ret := DBMS_SQL.EXECUTE(cur);
 13
 14  /*All columns were concatenated into single value in SELECT*/
 15  DBMS_SQL.DEFINE_COLUMN(cur, 1, RECORD, 4000);
 16
 17  /*Open the file for writing*/
 18  File := UTL_FILE.FOPEN(UPPER(Dir), File_Name, 'w', 32767);
 19
 20  /*Export rows one by one */
 21  LOOP
 22  ret := DBMS_SQL.FETCH_ROWS(cur);
 23  EXIT WHEN ret = 0;
 24
 25  /*Get the value*/
 26  RECORD := NULL;
 27  DBMS_SQL.COLUMN_VALUE(cur, 1, RECORD);
 28
 29  /*Write the row to the file*/
 30  UTL_FILE.PUT_LINE(File, RECORD);
 31  END LOOP;
 32  UTL_FILE.FCLOSE(File);
 33  DBMS_SQL.CLOSE_CURSOR(cur);
 34  END;
 35  /

Procedure created.

Now let's execute the stored procedure and check the file at the desired location:

/*Execute procedure to export data to CSV file */

 EXEC EXPORT_FEED ('EXPORTDIR', 'DATA_FEED.xls');

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.