Export to CSV from Stored Procedure - PostgreSQL to Oracle Migration

In PostgreSQL, you can use a COPY command to export data to a .csv file from a stored procedure (function in terms of PostgreSQL).

In Oracle, you can use UTL_FILE package and a cursor (or DBMS_SQL package) to write the data to a .csv file.

COPY in PostgreSQL

Assume there is the following table in PostgreSQL:

PostgreSQL:

  -- Test table for COPY command
  CREATE TABLE cities
  (
     name VARCHAR(70),
     state CHAR(2)
  );
 
  -- Sample data
  INSERT INTO cities VALUES ('San Francisco', 'CA');
  INSERT INTO cities VALUES ('Boston', 'MA');

Now we can use the following procedure to export data to .csv file:

PostgreSQL (Tested on PostgreSQL 9.0.4 for Windows):

  CREATE OR REPLACE FUNCTION export_cities(file_name VARCHAR(255)) RETURNS void 
  AS $$
  DECLARE
    select_stmt VARCHAR(100) := 'SELECT name, state FROM cities';
  BEGIN
     EXECUTE('COPY (' || select_stmt || ') TO ' || QUOTE_LITERAL(file_name) || ' CSV'); 
  END;
  $$
  LANGUAGE plpgsql;

Now let's execute the function:

  -- Execute function to export data to CSV file 
  SELECT export_cities(E'd:\\cities.csv');

When this function is executed if creates d:\cities.csv file with the following content (2 rows):

San Francisco,CA
Boston,MA

Export to CSV from a Stored Procedure in Oracle

Let's create the same sample table in Oracle:

Oracle:

  -- Test table for export to CSV
  CREATE TABLE cities
  (
     name VARCHAR2(70),
     state CHAR(2)
  );
 
  -- Sample data
  INSERT INTO cities VALUES ('San Francisco', 'CA');
  INSERT INTO cities VALUES ('Boston', 'MA');

You can use UT_FILE package and a cursor to export data into a CSV file:

  CREATE OR REPLACE PROCEDURE export_cities(dir VARCHAR2, file_name VARCHAR2) 
  IS
    CURSOR cur IS SELECT name || ',' || state AS row_value FROM cities;
    file UTL_FILE.FILE_TYPE;
  BEGIN  
      -- Open the file for writing
    file := UTL_FILE.FOPEN(UPPER(dir), file_name, 'w', 32767);
 
    -- Export rows one by one 
    FOR rec IN cur LOOP 
       -- All columns were concatenated into single value in SELECT
       UTL_FILE.PUT_LINE(file, rec.row_value);
    END LOOP;
 
     UTL_FILE.FCLOSE(file);
  END;
  /

You 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
  CREATE OR REPLACE PROCEDURE export_cities(dir VARCHAR2, file_name VARCHAR2) 
  IS
    select_stmt VARCHAR2(100) := 'SELECT name || '','' || state FROM cities';
    cur INTEGER;
    file UTL_FILE.FILE_TYPE;
    row_value VARCHAR2(4000);
    ret INTEGER;
  BEGIN
    -- Open a cursor for the specified SELECT statement
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, select_stmt, DBMS_SQL.NATIVE);
    ret := DBMS_SQL.EXECUTE(cur);
 
    -- All columns were concatenated into single value in SELECT
    DBMS_SQL.DEFINE_COLUMN(cur, 1, row_value, 4000);
 
    -- Open the file for writing
    file := UTL_FILE.FOPEN(UPPER(dir), file_name, 'w', 32767);
 
    -- Export rows one by one 
    LOOP
       ret := DBMS_SQL.FETCH_ROWS(cur);       
       EXIT WHEN ret = 0;
 
       -- Get the value 
       row_value := NULL;
       DBMS_SQL.COLUMN_VALUE(cur, 1, row_value);
 
       -- Write the row to the file
       UTL_FILE.PUT_LINE(file, row_value);  
    END LOOP;
 
     UTL_FILE.FCLOSE(file);
     DBMS_SQL.CLOSE_CURSOR(cur);     
  END;
  /

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

  CONNECT sys/password 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 'd:'; 
  GRANT READ ON DIRECTORY exportdir TO PUBLIC;
  GRANT WRITE ON DIRECTORY exportdir TO PUBLIC;

Now let's execute the stored procedure:

  -- Execute procedure to export data to CSV file 
  CALL export_cities('exportdir', 'cities2.csv');

When this procedure is executed if creates d:\cities2.csv file with the following content (2 rows):

San Francisco,CA
Boston,MA

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.

Discussion

, April 01, 2014 3:29 pm

Hi, I'm new to pl/sql. I have a requirement where i need to fetch 1 million records from oracle DB and write it to a csv file. As we know with csv there is a constraint on a size limit (max of 10,000 records per batch). Can some one help me do it using stored procedure or sql batch.

Thanks in Advance, Phaneendra.

, April 01, 2014 3:31 pm

Hi, In continuation to above requirement I've been going through several blogs and got to know that Oracle comes with UTIL_FILE package which helps in acheiving the same but i'm not sure about the performance i.e. amount of time this utility takes to write 1 million records to a csv file. Please help.

Thanks, Phaneendra.

You could leave a comment if you were logged in.