Export von Tabellen als csv-Files mit UTL_FILE

06.
Juni
2012
Veröffentlicht von: Elke Fritsch

Der Export von Tabellen als csv-Files ist immer wieder ein Thema in unseren PL/SQL- und Packages-Kursen, weil die meisten gerne mit Excel arbeiten. Deshalb gebe ich unseren Teilnehmern immer eine einfache Prozedur mit, über die man Inhalte beliebiger Tabellen (solange sie keine LOB-Spalten oder ähnliches enthalten) mittels UTL_FILE als semikolon-separierte Ascii-File exportieren kann. Der Einsatz von UTL_FILE ist nicht der schnellste Weg, aber man kann diese Prozeduren sehr gut in Datenbank-Jobs einbinden oder für andere PL/SQL-Programme  verwenden.

Der Export von Tabellen als csv-Files ist immer wieder ein Thema in unseren PL/SQL- und Packages-Kursen, weil die meisten gerne mit Excel arbeiten. Deshalb gebe ich unseren Teilnehmern immer eine einfache Prozedur mit, über die man Inhalte beliebiger Tabellen (solange sie keine LOB-Spalten oder ähnliches enthalten) mittels UTL_FILE als semikolon-separierte Ascii-File exportieren kann. Der Einsatz von UTL_FILE ist nicht der schnellste Weg, aber man kann diese Prozeduren sehr gut in Datenbank-Jobs einbinden oder für andere PL/SQL-Programme  verwenden.

Bei Tom Kyte findet man übrigens eine schöne Übersicht seiner diversen Export-Utilities unter Opens external link in new windowhttp://tkyte.blogspot.de/2009/10/httpasktomoraclecomtkyteflat.html . Für seine PL/SQL-Funktion dump_csv verwendet er DBMS_SQL

Das Problem ist allerdings: Mit den üblichen Beispieltabellen wie scott.emp oder all_objects klappt das alles natürlich prima, aber wie sieht's denn aus, wenn man "historisch gewachsene" Tabellen mit fast 100 oder mehr Spalten vor sich hat, in denen wegen der Verwendung von CHAR-Datentypen u. U. viel heiße Luft gespeichert ist? Deshalb wollte ich mal ausprobieren, wie man einserseits die kleine Prozedur ausbauen müßte, um solche Problemfälle in den Griff zu kriegen und welche Möglichkeiten es gibt, das Ganze schneller zu machen.

Von Adrian Billington stammt ein sehr lesenswerter Artikel von 2008: Opens external link in new windowTuning PL/SQL-File-IO über verschiedene Ansätze, die einfache Ausgabe über UTL_FILE zu beschleunigen und Performance-Vergleiche zwischen dem Einsatz von UTL_FILE auf der einen und der Kombination von DBMS_LOB und DBMX_XSLPROCESSOR auf der anderen Seite. Seine Codebeispiele für UTL_FILE habe ich leicht abgewandelt, in meine alte Exportprozedur eingebaut und mit verschiedenen Tabellen getestet.


Funktionen und Prozeduren

Die Erstellung der Spaltenlisten wird in eine Funktion ausgelagert. Die TRIM-Funktion macht beim späteren Export die CHAR- und VARCHAR2-Spalten-Inhalte "schlanker", indem sie Leerzeichen von beiden Seiten löscht.

CREATE OR REPLACE FUNCTION col_list (
      p_tabname   VARCHAR2,
      p_schema    VARCHAR2 DEFAULT user,
      p_trim      NUMBER DEFAULT 0,
      p_delim     VARCHAR2 DEFAULT ',')  RETURN VARCHAR2
AS
    l_col_list VARCHAR2(4000) := ' ';
BEGIN
    FOR rec IN (SELECT column_name,data_type FROM all_tab_columns
                WHERE table_name = UPPER(p_tabname)
                AND owner        = UPPER(p_schema)
                ORDER BY column_id) LOOP
      IF p_trim = 1 AND rec.data_type IN ('CHAR', 'VARCHAR2') THEN
        l_col_list  := l_col_list ||p_delim||'TRIM('||rec.column_name||')';
      ELSE
        l_col_list := l_col_list ||p_delim||rec.column_name;
      END IF;
    END LOOP;
    RETURN NVL(LTRIM(l_col_list, p_delim||' '), 'ungültiger Tabellenname oder fehlende Berechtigung');
END;
/


Diese Prozedur exportiert den Inhalt einer Tabelle als csv-File (mit der Angabe von Datum und Uhrzeit). Das Directory muss natürlich existieren und der Ersteller der Prozedur muss Lese- und Schreibrechte darauf haben.

CREATE OR REPLACE PROCEDURE tab2csv (
   p_directory VARCHAR2,
   p_tabname   VARCHAR2,
   p_schema    VARCHAR2 DEFAULT USER)
IS
    tab_refcur        SYS_REFCURSOR;
    l_header          VARCHAR2(4000);
    l_zeile           VARCHAR2(4000);
    l_col_list        VARCHAR2(4000);
    l_stmt            VARCHAR2(4000);
    l_file            UTL_FILE.FILE_TYPE;
 BEGIN
    l_file := UTL_FILE.FOPEN(
        location  => UPPER(p_directory),
        filename  => p_tabname||'_'||TO_CHAR(sysdate,'yyyy-mm-dd-hh24-mi')||'.csv',
        open_mode => 'w',
     max_linesize => 32767);
  -- Zusammenstellung der Spaltenliste für die Überschriften-Zeile
    l_header := col_list(
            p_tabname => p_tabname,
             p_schema => p_schema,
              p_delim => ';');
  -- Die Überschriften werden in die Datei geschrieben
     UTL_FILE.PUT_LINE(l_file, l_header);
  -- Zusammenstellung der Spaltenliste für den Select
     l_col_list := col_list(
             p_tabname => p_tabname,
              p_schema => p_schema ,
               p_delim => q'[||';'||]', -- alternativ '||'';''||'
                p_trim => 1);
     l_stmt   := 'SELECT '||l_col_list||' FROM '||p_schema||'.'||p_tabname;
  -- Über den Ref Cursor werden die Spalteninhalte jeder Zeile
  -- als Strings aneinandergehängt
     OPEN tab_refcur FOR l_stmt;
     LOOP
  -- und in die Variable eingelesen
       FETCH tab_refcur INTO l_zeile;
       EXIT WHEN tab_refcur%NOTFOUND;
       -- mit der Prozedur PUT_LINE wird Zeile für Zeile geschrieben
     UTL_FILE.PUT_LINE(l_file, l_zeile);
    END LOOP;
    CLOSE tab_refcur;
   UTL_FILE.FCLOSE(l_file);
EXCEPTION
 WHEN OTHERS THEN
   -- hier sollte natürlich eine vernünftige Fehleraufzeichnung passieren
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    UTL_FILE.FCLOSE(l_file);
   RAISE;
END tab2csv;
/


In der folgenden Prozedur habe ich Adrian Billingtons Idee zur Zwischenspeicherung der Zeilen in einer 32KB-VARCHAR2-Variable verwendet. Erst wenn der Puffer voll ist, wird die Zeile geschrieben. Vor allem für größere Tabellen macht das einen Unterschied.

CREATE OR REPLACE PROCEDURE tab2csv_buffered (
   p_directory VARCHAR2,
   p_tabname   VARCHAR2,
   p_schema    VARCHAR2 DEFAULT USER)
IS
    tab_refcur        SYS_REFCURSOR;
    l_header          VARCHAR2(4000);
    l_zeile           VARCHAR2(4000);
    l_col_list        VARCHAR2(4000);
    l_stmt            VARCHAR2(4000);
    l_file            UTL_FILE.FILE_TYPE;
    l_buffer          VARCHAR2(32767);
 BEGIN
  l_file := UTL_FILE.FOPEN(
        location => UPPER(p_directory),
        filename => p_tabname||'_'||TO_CHAR(sysdate,'yyyy-mm-dd-hh24-mi')||'.csv',
       open_mode => 'w',
    max_linesize => 32767);
    l_header := col_list(
            p_tabname => p_tabname,
              p_schema => p_schema,
              p_delim => ';');
   UTL_FILE.PUT_LINE(l_file, l_header);
    l_col_list := col_list(
             p_tabname => p_tabname,
               p_schema => p_schema ,
               p_delim => q'[||';'||]',
                p_trim => 1);
    l_stmt := 'SELECT '||l_col_list||' FROM '||p_schema||'.'||p_tabname;
    OPEN tab_refcur FOR l_stmt;
    LOOP
       FETCH tab_refcur INTO l_zeile;
       EXIT WHEN tab_refcur%NOTFOUND;
  -- solange der Buffer nicht voll ist, werden weitere Zeilen
  -- getrennt durch Linefeed (chr(10)) eingeladen
       IF LENGTH(l_buffer) + 1 + LENGTH(l_zeile) <= 32767 THEN
         l_buffer := l_buffer || CHR(10) ||l_zeile;
       ELSE
         IF l_buffer IS NOT NULL THEN
  -- der volle Buffer wird in die Datei geschrieben
            UTL_FILE.PUT_LINE(l_file, l_buffer);
         END IF;
  -- der Buffer wird zurückgesetzt
         l_buffer := l_zeile;
       END IF;
    END LOOP;
  -- was nach dem Ende der Schleife noch im Buffer ist
  -- wird hier rausgeschrieben
    UTL_FILE.PUT_LINE(l_file, l_buffer);
    CLOSE tab_refcur;
   UTL_FILE.FCLOSE(l_file);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    UTL_FILE.FCLOSE(l_file);
    RAISE;
END tab2csv_fast;
/


Und jetzt Billingtons Turboversion mit Parallel-Antrieb (etwas vereinfacht). Die parallele Ausgabe funktioniert nur mit einer Table Function und ist deshalb etwas komplexer. Zudem ist die Parallelisierung nur bei der Enterprise-Edition möglich.

Statt mit einem Cursor durch die Ergebnismenge zu laufen, wird hier eine nested Table verwendet, die in Portionen von je 100 Zeilen befüllt wird (Dies allein bringt noch keinen Geschwindigkeitsgewinn, da auch bei der Ref Cursor-Version im Hintergrund ein Bulk Collect von je 100 Zeilen durchgeführt wird). Diese 100 Zeilen werden dann über die Puffermethode ins File geschrieben, dann holt sich die PL/SQL Engine die nächsten 100.
Nach dem Schließen des Files wird als Dummy ein Sequenzwert über PIPE ROW ausgegeben, damit die Funktion einen Rückgabewert hat.

-- Nested Table-Objekttyp zum Auffangen der Ergebnismenge
CREATE TYPE num_nt_type AS TABLE OF NUMBER;
/

CREATE SEQUENCE file_seq;

CREATE OR REPLACE FUNCTION parallel_output (
      p_refcur    SYS_REFCURSOR,
      p_directory VARCHAR2,
      p_tabname   VARCHAR2,
      p_schema    VARCHAR2 DEFAULT USER) RETURN num_nt_type PIPELINED
 -- Parallelisierte Abarbeitung aktivieren
   PARALLEL_ENABLE
 -- Oracle soll sich die Anzahl der Slave-Prozesse selber aussuchen
   (PARTITION p_refcur BY ANY)
AS
 -- Nested Table für den zu exportierenden Text
   TYPE string_nt_type IS TABLE OF VARCHAR2(32767);
   string_nt    string_nt_type;
    l_name       VARCHAR2(200);
    l_header     VARCHAR2(4000);
    l_zeile      VARCHAR2(4000);
    l_col_list   VARCHAR2(4000);
    l_stmt       VARCHAR2(4000);
    l_file       UTL_FILE.FILE_TYPE;
    l_buffer     VARCHAR2(32676);
BEGIN
   l_name := p_tabname||'_'||TO_CHAR(sysdate,'yyyy-mm-dd-hh24-mi');
   l_file := UTL_FILE.FOPEN(
        location => UPPER(p_directory),
        filename => l_name||'.csv',
       open_mode => 'w',
    max_linesize => 32767);
    l_header := col_list(
            p_tabname => p_tabname,
              p_schema => p_schema,
              p_delim => ';');
   UTL_FILE.PUT_LINE(l_file, l_header);
   l_name := l_name|| '_' ||file_seq.NEXTVAL|| '.csv';
   l_file := UTL_FILE.FOPEN(
        location => UPPER(p_directory),
        filename => l_name,
       open_mode => 'w',
    max_linesize => 32767);
    LOOP
     FETCH p_refcur BULK COLLECT INTO string_nt LIMIT 100;
     EXIT WHEN string_nt.COUNT = 0;
      FOR i IN 1 .. string_nt.COUNT LOOP
         IF LENGTH(l_buffer) + 1 + LENGTH(string_nt(i)) <= 32676 THEN
            l_buffer := l_buffer ||CHR(10)|| string_nt(i);
         ELSE
            IF l_buffer IS NOT NULL THEN
               UTL_FILE.PUT_LINE(l_file, l_buffer);
            END IF;
            l_buffer := string_nt(i);
         END IF;
      END LOOP;
   END LOOP;
   CLOSE p_refcur;
   UTL_FILE.PUT_LINE(l_file, l_buffer);
   UTL_FILE.FCLOSE(l_file);
   PIPE ROW (file_seq.NEXTVAL);
   RETURN;
 EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
    UTL_FILE.FCLOSE(l_file);
    RAISE;
END parallel_output;
/


Der Aufruf dieser Funktion ist nicht ganz ohne. Die Verwendung der Funktion col_list  innerhalb der CURSOR-Klausel ist zwar möglich, aber das Ganze sieht eher sperrig aus.

SELECT * FROM TABLE(parallel_output(
      p_refcur => CURSOR(SELECT /*+ PARALLEL(c,4) */ col_list('csv_test1', 'scott', 1, '||'';''||') FROM csv_test1 c),
      p_directory => 'extern',
      p_tabname => 'csv_test1'));


Die folgende Prozedur vereinfacht den parallelen Export. Es werden immer 1 File für den Header und 4 für den Inhalt erzeugt. Diese müssen dann nachträglich noch zusammengeführt werden.

CREATE OR REPLACE PROCEDURE tab2csv_parallel(
    p_directory VARCHAR2,
    p_tabname   VARCHAR2,
    p_schema    VARCHAR2 DEFAULT USER)
AS
  TYPE num_nt_type IS TABLE OF NUMBER;
  num_nt num_nt_type;
  l_stmt     VARCHAR2(5000);
  l_col_list VARCHAR2(5000);
BEGIN
  l_col_list := col_list(p_tabname, p_schema, 1, '||'';''||');
  l_stmt  := 'SELECT * FROM TABLE(parallel_output(
               p_refcur => CURSOR(SELECT /*+ PARALLEL(t,4) */ '||l_col_list||' FROM '||p_tabname||' t),'||
               q'[p_directory => ']'||upper(p_directory)|| q'[', p_tabname => ']'||p_tabname||q'['))]';
  -- optional zur Kontrolle des Statements
  -- DBMS_OUTPUT.PUT_LINE(l_stmt);
  EXECUTE IMMEDIATE l_stmt BULK COLLECT INTO num_nt;
END;
/


Testergebnisse

Die Ergebnisse sind Mittelwerte aus jeweils 2 Durchgängen auf 3 verschiedenen Rechnern. Die Datenbankversion war 11.2.03.
Den Code für die Erstellung der Tabellen finden Sie Initiates file downloadhier.

Testergebnisse

Tabellenname

Zeilen /Spalten

Blöcke /MB

Zeit in Sek.
tab2csv

Zeit in Sek.
tab2csv_buffered

Zeit in Sek.
tab2csv_parallel

big_tab (Copyright Tom Kyte)

 2 Mio / 14 

29696 / 232


133,5515

80,979

20,932

csv_test1  

100.000 / 49

50328 / 393

23,147

19,511

14,893

csv_test2

30.000 / 81

15168 / 119

10,224

8,756

6,619


Fazit

Wie zu erwarten war, sind die Performancegewinne über die Prozeduren tab2csv_buffered und tab2csv_parallel umso größer, je höher die Zeilenanzahl der Tabelle ist. Beim Export der big_tab erreicht man fast die doppelte Geschwindigkeit mit der Puffer-Variante bzw. die 10-fache über die Parallele Ausführung. Bei kleineren Tabellen mit wesentlich mehr Spalten ist der Gewinn dagegen relativ klein. Das heißt, man kommt mit der "primitiven" Prozedur tab2csv schon relativ weit.

Wenn allerdings die konkatenierten Spalteninhalte länger sind als 4.000 Zeichen - und das kann bei vielen real existierenden Tabellen durchaus der Fall sein - erhalten Sie die Fehlermeldung:

ORA-01489: Das Ergebnis der Zeichenfolgenverkettung ist zu lang

Wie Sie dann auch noch diese Hürde überwinden können, erfahren Sie in unserem Opens internal link in current windowPL/SQL II oder Packages-Kurs zusätzlich zu allem Wissenswerten über den Umgang mit UTL_FILE, Pipelined Table Functions etc.

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.