prog:plsql_spool_utf_8_file
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
prog/plsql_spool_utf_8_file.txt · Zuletzt geändert: 2016/11/22 20:42 von gpipperr