Export Oracle dump query result to CSV format


Here the simple procedure to do it,

CREATE OR REPLACE FUNCTION DUMP_CSV (
P_QUERY IN VARCHAR2,
P_SEPARATOR IN VARCHAR2 DEFAULT ‘,’,
P_DIR IN VARCHAR2,
P_FILENAME IN VARCHAR2
) RETURN NUMBER
IS

L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2 (2000);
L_STATUS INTEGER;
L_COLCNT NUMBER DEFAULT 0;
L_SEPARATOR VARCHAR2 (10) DEFAULT ”;
L_CNT NUMBER DEFAULT 0;

BEGIN
L_OUTPUT := UTL_FILE.FOPEN (P_DIR, P_FILENAME, ‘w’);
DBMS_SQL.PARSE (L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
FOR I IN 1 .. 255 LOOP
BEGIN
DBMS_SQL.DEFINE_COLUMN (L_THECURSOR, I, L_COLUMNVALUE, 2000);
L_COLCNT := I;
EXCEPTION WHEN OTHERS THEN
IF (SQLCODE = -1007) THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;

DBMS_SQL.DEFINE_COLUMN (L_THECURSOR, 1, L_COLUMNVALUE, 2000);
L_STATUS := DBMS_SQL.EXECUTE (L_THECURSOR);

LOOP EXIT WHEN (DBMS_SQL.FETCH_ROWS (L_THECURSOR) <= 0);
L_SEPARATOR := ”;
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE (L_THECURSOR, I, L_COLUMNVALUE);
UTL_FILE.PUT (L_OUTPUT, L_SEPARATOR || L_COLUMNVALUE);
L_SEPARATOR := P_SEPARATOR;
END LOOP;

UTL_FILE.NEW_LINE (L_OUTPUT);
L_CNT := L_CNT + 1;
END LOOP;

DBMS_SQL.CLOSE_CURSOR (L_THECURSOR);
UTL_FILE.FCLOSE (L_OUTPUT);

RETURN L_CNT;

END DUMP_CSV;
/

To test it, you can simply run

SELECT dump_csv (‘SELECT * FROM EMPLOYEES’, ‘,’, ‘WORK_DIR’, ‘test.csv’) FROM DUAL;
– P_QUERY            = ‘SELECT * FROM EMPLOYEES’
– P_SEPARATOR  = ‘,’
– P_DIR                   = ‘WORK_DIR’
– P_FILENAME     = ‘test.csv’

WORK_DIR is oracle directory, that create through this script,

CREATE OR REPLACE DIRECTORY WORK_DIR AS ‘C:\csv’;

Advertisements

12 Responses

  1. Excellent post !
    Thanks chanda.

  2. Looks cool, I have xxxxx.sql file, which I want to execute it using Java and the output should be csv.
    Can I use the post above….

  3. if you can using java to access oracle and and file access to create file, of course it can be used to export it.

  4. i’m new to oracle untill recently, I run into leading zero disappear in zip code after exporting to csv format. can you explain a little more? Thank you in advance..

  5. yo.. it really helps..
    may i noe how to import back the csv file to sql server?

    • sorry, i’m not familiar with sql server
      for now, i’m only deal with oracle

  6. Greate code scripting !
    Tq..
    Thanks n regards,
    Darma
    Tangerang, Indonesia.

  7. Rate OK Excl.

  8. […] the data to a CSV file. We need more PL/SQL for this. In this page there is an excellent method to dump query results in a csv […]

  9. Hi, if the select to export compare char or varchar equal ‘XX’, the function not work but understand the ‘ is the end of query.
    How to compare character in the select?
    Regards.

  10. How would you include the column name in the .csv file?

  11. […] the data to a CSV file. We need more PL/SQL for this. In this page there is an excellent method to dump query results in a csv […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: