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. 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: 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(