Benutzer-Werkzeuge

Webseiten-Werkzeuge

Action disabled: index

prog:plsql_spool_utf_8_file

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

Cookies helfen bei der Bereitstellung von Inhalten. Diese Website verwendet Cookies. Mit der Nutzung der Website erklären Sie sich damit einverstanden, dass Cookies auf Ihrem Computer gespeichert werden. Außerdem bestätigen Sie, dass Sie unsere Datenschutzerklärung gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website. Weitere Information
"Autor: Gunther Pipperr"
prog/plsql_spool_utf_8_file.txt · Zuletzt geändert: 2016/11/22 20:42 von gpipperr