Oracle: dump data to csv-file

When oracle was new to me I had the suspicion that the oracle corp. pursues a sinister plan to preserve hegemony in the databaseworld  by letting all data in but no data out of the database. 😉
It seemed as if there was no easy way to simply dump some data as a plain csv-file – as if you have to code your own exporter with the UTL_FILE-package.

But no. One simple solution to get data out of an oracle-database and into a csv-file is via good old SQL*Plus. You just have to trim the interface a little and then spool the query:

SET HEADING OFF
SET TIMING OFF
SET FEEDBACK OFF
SET PAGES 0
SET COLSEP ";"

SPOOL \path\to\outputfile.csv
select * from mytable;
SPOOL OFF

Now you just have to cut off the “SELECT”-line from the beginning of the file and the “SPOOL OFF”-line from the end and you’re done.

Assuming your Prompt looks like the default “SQL>” the cut-off could
easily be done with sed:

sed -i '/SQL> select * from mytable;/d' outputfile.csv

sed -i '/SQL> SPOOL OFF/d' outputfile.csv

To have the names of the columns as a headline in the csv-file you replace the above “SET HEADING OFF” with “SET UNDERLINE OFF” and “SET PAGES 0” with “SET PAGES 100” (or some other value). But then you will have a blank line every 100 rows. To prevent that you must use “SET PAGES 0” but then no columnnames will show.

But you could also easily delete the blank lines from the output-file with “sed” on a shell:

sed -i '/^$/d' outputfile.csv

Eventually the procedure above doesn’t fit to your needs. Mostly when you have very large fields the output may look quite ugly. If your largest record would fit into 32767 bytes, you could concatenate the fields using an anonymous PL/SQL-block like this:

set serveroutput on;

set linesize 32000;

spool d:\mtempel\temp\lr_final.csv

BEGIN
  FOR a IN
  (SELECT PORTLETPREFERENCESID,
    OWNERID,
    OWNERTYPE,
    PLID,
    PORTLETID,
    REPLACE(REPLACE(PREFERENCES,CHR(10),''),CHR(13),'') PREFS
  FROM portletpreferences
  )
  LOOP
    dbms_output.put_line(a.PORTLETPREFERENCESID || ';' || a.OWNERID || ';' || 
    a.OWNERTYPE || ';' || a.PLID || ';' || a.PORTLETID || ';' || a.PREFs);
  END LOOP;
END;
/

spool off

The challenge in this example was the column “PREFERENCES” – a CLOB-field. To get every record’s data into one line, I had to strip off the linebreaks in the CLOB-data – thus the REPLACE.

Both export-approaches are eventually not very nice, but the best you could do when all you have is SQL-Plus on a client-machine…

Advertisements