Inhaltsverzeichnis

Oracle PL/SQL - Generisch aus einer Tabelle eine CSV Datei erstellen - UTL_FILE und DBMS_SQL einsetzen

ab 10g

Aufgabe: Aus mehreren Tabellen sollen separate CSV Dateien erstellt werden. Dazu soll aber nicht jedesmal einen neue Routine geschrieben werden.

Lösung A - SQLcl

In SQLcl ist das bereits integriert siehe ⇒ 12c - Der neuen SQL Kommando Interpreter SQLcl

Beispiel für SQLcl: 

spool emp.csv
SELECT /*csv*/ FROM emp;
spool off

Lösung B - Pl/SQL ganz klassisch

Vorbereitung

Export Directory von SYS anlegen lassen: 

CREATE directory EXPORT_DIR AS 'd:\csv';
GRANT READ ON directory EXPORT_DIR TO scott;
GRANT WRITE ON directory EXPORT_DIR TO scott;

Export Code erstellen

Mit Hilfe von DBMS_SQL und UTL_FILE (put_line_NCHAR für UTF8) wird ein SQL dynamisch auswertet und in eine Datei geschrieben.

Umsetzung:

createCSVFile.sql 
CREATE OR REPLACE PROCEDURE createCSVFile(p_filename           VARCHAR2
                           , p_sql               VARCHAR2
                           , p_headerline        VARCHAR2
                           , p_printTable_header BOOLEAN DEFAULT FALSE
                           , p_printHeader_line  BOOLEAN DEFAULT TRUE
                           , p_print_utf8        BOOLEAN DEFAULT TRUE)
     IS
 
        v_cur             SYS_REFCURSOR;
        v_cur_pointer     PLS_INTEGER;
        v_column_count    PLS_INTEGER;
        v_column_desc     SYS.DBMS_SQL.desc_tab;
        v_column_value    VARCHAR2(4000);
 
 
        v_file UTL_FILE.file_type;
        v_rec_count      PLS_INTEGER:=0;
        v_line           VARCHAR2(4000);     
        v_fetch          PLS_INTEGER:=0;        
        v_data_format    VARCHAR2(18):='dd.mm.yyyy hh24:mi';    
     BEGIN
 
        -- open file
        IF p_print_utf8 THEN
            v_file := UTL_FILE.FOPEN_NCHAR ('EXPORT_DIR',p_filename,'w',4000);
        ELSE
            v_file := UTL_FILE.FOPEN ('EXPORT_DIR',p_filename,'w',4000);
        END IF;
 
 
        --write header line
        IF p_printHeader_line THEN
             IF p_print_utf8 THEN
                UTL_FILE.PUT_line_NCHAR(v_file,p_headerline);
             ELSE
                UTL_FILE.PUT_line(v_file,p_headerline);
             END IF;
         END IF;
 
 
       -- set the required data format
        EXECUTE IMMEDIATE 'alter session set nls_date_format='''||v_data_format||'''';
 
        -- bild the sql
 
        DBMS_OUTPUT.put_line ('-- Info:: try to execute '||p_sql);
 
 
        -- not in 10g
        -- open the cursor
        -- open v_cur for v_sql;
        -- get pointer to the cursor
        -- v_cur_pointer   := sys.dbms_sql.to_cursor_number (v_cur);
 
        v_cur_pointer := DBMS_SQL.OPEN_CURSOR;
 
        sys.DBMS_SQL.parse(v_cur_pointer, p_sql, sys.DBMS_SQL.native);
 
 
        --get the columnns of this cursor
        sys.DBMS_SQL.describe_columns (c => v_cur_pointer, col_cnt => v_column_count, desc_t => v_column_desc);
 
 
        FOR i IN 1 .. v_column_count LOOP
            v_line           := v_line || v_column_desc (i).col_name;
            IF i < v_column_count THEN
                 v_line      := v_line || '|';
            END IF;
            DBMS_SQL.define_column (v_cur_pointer, i, v_column_value,4000);
 
        END LOOP;
 
        IF p_printTable_header THEN
                DBMS_OUTPUT.put_line ('-- Info:: Header Line '||v_line);
                IF p_print_utf8 THEN
                    UTL_FILE.PUT_line_NCHAR(v_file,v_line);
                ELSE
                    UTL_FILE.PUT_line(v_file,v_line);
                END IF;
        END IF;     
 
        v_fetch:=DBMS_SQL.EXECUTE(v_cur_pointer);
 
        WHILE (sys.DBMS_SQL.fetch_rows (v_cur_pointer) > 0) 
         LOOP
            v_rec_count   := v_rec_count + 1;
            v_line        := '';
 
            FOR i IN 1 .. v_column_count LOOP
              DBMS_SQL.column_value (v_cur_pointer, i, v_column_value);
 
              v_line           := v_line || v_column_value;
 
              IF i < v_column_count THEN
                  v_line           := v_line || '|';
              END IF;
 
            END LOOP;
 
            --dbms_output.put_line ('--info::'||v_line);
            IF p_print_utf8 THEN
                UTL_FILE.put_line_nchar(v_file,v_line);
            ELSE
                UTL_FILE.put_line(v_file,v_line);
            END IF;
 
        END LOOP;
 
        IF v_rec_count = 0 THEN
           DBMS_OUTPUT.put_line ('no data found.');
        ELSE
           DBMS_OUTPUT.put_line (v_rec_count || ' rows returned.');
        END IF;
 
         -- close the cursor
         IF sys.DBMS_SQL.is_open(v_cur_pointer) THEN
            sys.DBMS_SQL.close_cursor (v_cur_pointer);
         END IF;
 
        --close the file
         IF UTL_FILE.IS_OPEN (v_file) THEN
            UTL_FILE.fflush(v_file);
            UTL_FILE.fclose(v_file);
         END IF;
 
    EXCEPTION 
     WHEN OTHERS THEN  
 
         --close the file
         IF UTL_FILE.IS_OPEN (v_file) THEN
            UTL_FILE.fflush(v_file);
            UTL_FILE.fclose(v_file);         
         END IF;
 
         -- close the cursor
         IF sys.DBMS_SQL.is_open(v_cur_pointer) THEN
            sys.DBMS_SQL.close_cursor (v_cur_pointer);
         END IF;
 
         -- Error
         DBMS_OUTPUT.put_line ('-- Error ::'||SQLERRM);
 
     END createcsvfile;

Quellen

Oracle:

Web: