=====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