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