Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- prog:plsql_spool_utf_8_file [2016/11/10 20:21] – [Export Code erstellen] gpipperr
+++ prog:plsql_spool_utf_8_file [2016/11/22 20:42] (aktuell) – [Export Code erstellen] gpipperr
@@ Zeile 1: / Zeile 1: @@
+=====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 => [[dba:sqlcl_oracle_command_line_in_12c|12c - Der neuen SQL Kommando Interpreter SQLcl]]
+Beispiel für SQLcl:
+<code sql>
+spool emp.csv
+select /*csv*/ from emp;
+spool off
+</code>
+----
+==== Lösung B - Pl/SQL ganz klassisch ====
+=== Vorbereitung ===
+Export Directory von SYS anlegen lassen:
+<code sql>
+create directory EXPORT_DIR as 'd:\csv';
+grant read on directory EXPORT_DIR to scott;
+grant write on directory EXPORT_DIR to scott;
+</code>
+=== 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:
+<code plsql 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;
+</code>
+----
+==== Quellen ====
+Oracle:
+  * DBMS_SQL => https://docs.oracle.com/database/121/ARPLS/d_sql.htm#ARPLS058
+Web:
+  *  http://stackoverflow.com/questions/33244014/pl-sql-file-writing-with-generic-input