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.

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):

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):

• SQLines Data - Data Transfer and Database Schema Migration Tool
• Free Online SQL Conversion Tool

• PostgreSQL to Oracle Migration Reference

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.