Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====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