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):
San Francisco,CA |
Boston,MA |
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 |
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.