Benutzer-Werkzeuge

Webseiten-Werkzeuge


prog:plsql_spool_utf_8_file

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
prog:plsql_spool_utf_8_file [2016/11/10 20:21]
gpipperr [Export Code erstellen]
prog:plsql_spool_utf_8_file [2016/11/22 20:42] (aktuell)
gpipperr [Export Code erstellen]
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
"Autor: Gunther Pipperr"
prog/plsql_spool_utf_8_file.txt · Zuletzt geändert: 2016/11/22 20:42 von gpipperr