Erstellen einer CSV-Datei aus einer Oracle-Tabelle

Immer mal wieder kommt es vor, dass aus Daten einer Oracle-Datenbank CSV-Dateien erzeugt werden sollen.

Das Dateiformat CSV beschreibt den Aufbau einer Textdatei zur Speicherung oder zum Austausch einfach strukturierter Daten. Die Dateiendung CSV ist eine Abkürzung für Comma-Separated Values (seltener Character Separated Values oder Colon Separated Values). Ein allgemeiner Standard für das Dateiformat CSV existiert nicht. Jedoch wird es im RFC 4180 grundlegend beschrieben. Die zu verwendende Zeichenkodierung ist ebenso wenig festgelegt; 7-bit ASCII gilt weithin als der kleinste gemeinsame Nenner.

In CSV-Dateien können Tabellen oder eine Liste unterschiedlich langer Listen abgebildet werden. Kompliziertere, beispielsweise geschachtelte Datenstrukturen können durch zusätzliche Regeln oder in verketteten CSV-Dateien gespeichert werden. Um sie in einer Datei abzuspeichern, eignen sich jedoch andere Formate wie XML oder EDIFACT besser.

Nun zum eigentlichen Code. Mit Oracle SQL*Plus setzt man ganz einfach den Spaltenseparator auf das entsprechende Zeichen (hier ein Komma).

ORACLE SQL*Plus: CSV-Dateien erzeugen
SET UNDERLINE OFF
SET COLSEP ','
SET LINES 100 PAGES 100
SET FEEDBACK off
SET HEADING off 
 
SPOOL  C:\temp\meine_tabelle.csv
 
   SELECT * FROM meine_tabelle;
 
SPOOL OFF

Die Datei ist anschließend unter C:\temp\meine_tabelle.csv zu finden.

Noch eleganter ist die folgende Lösung: Die folgende Implementierung mit PL/SQL als Stored Procedure erlaubt die Übergabe einer Abfrage. Aus dem Resultset wird eine CSV-Datei erzeugt, die in MS Excel verwendet werden kann.

Oracle CSV-Datei mit Stored Procedure erzeugen
  1. CREATE OR REPLACE FUNCTION  dump_csv( p_query     IN varchar2,
  2.                                       p_separator IN varchar2 
  3.                                                     DEFAULT ',',
  4.                                       p_dir       IN varchar2 ,
  5.                                       p_filename  IN varchar2 )
  6. RETURN number
  7. AUTHID CURRENT_USER
  8. IS
  9.     l_output        utl_file.file_type;
  10.     l_theCursor     integer DEFAULT dbms_sql.open_cursor;
  11.     l_columnValue   varchar2(2000);
  12.     l_status        integer;
  13.     l_colCnt        number DEFAULT 0;
  14.     l_separator     varchar2(10) DEFAULT '';
  15.     l_cnt           number DEFAULT 0;
  16. begin
  17.     l_output := utl_file.fopen( p_dir, p_filename, 'w' );
  18.  
  19.     dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
  20.  
  21.     FOR i IN 1 .. 255 loop
  22.         begin
  23.             dbms_sql.define_column( l_theCursor, i, 
  24.                                     l_columnValue, 2000 );
  25.             l_colCnt := i;
  26.         exception
  27.             when others then
  28.                 IF ( sqlcode = -1007 ) then exit;
  29.                 else
  30.                     raise;
  31.                 end IF;
  32.         end;
  33.     end loop;
  34.  
  35.     dbms_sql.define_column( l_theCursor, 1, l_columnValue, 
  36.                             2000 );
  37.  
  38.     l_status := dbms_sql.execute(l_theCursor);
  39.  
  40.     loop
  41.         exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
  42.         l_separator := '';
  43.         FOR i IN 1 .. l_colCnt loop
  44.             dbms_sql.column_value( l_theCursor, i, 
  45.                                    l_columnValue );
  46.             utl_file.put( l_output, l_separator ||  
  47.                                     l_columnValue );
  48.             l_separator := p_separator;
  49.         end loop;
  50.         utl_file.new_line( l_output );
  51.         l_cnt := l_cnt+1;
  52.     end loop;
  53.     dbms_sql.close_cursor(l_theCursor);
  54.  
  55.     utl_file.fclose( l_output );
  56.     RETURN l_cnt;
  57. end dump_csv;
  58. /

Der Aufruf sieht dann beispielsweise wie folgt aus:

Aufruf zur Erstellung einer CSV-Datei
  1. declare
  2.     l_rows  number;
  3. begin
  4.     l_rows := dump_csv( 'select * 
  5.                            from all_users 
  6.                            where rownum < 25', 
  7.                         ',', '/tmp', 'test.dat' );
  8. end;
  9. /

Das Skript stammt von Tom Kyte, das Original ist unter http://asktom.oracle.com/tkyte/Misc/csv.html zu finden.