=====Overview=====
It happened that I will need to export data from oracle to CSV after each insert. Well that happens more often than you think and since oracle is the ONLY database which cannot dump to CSV, it was little problem. Therefore I used procedure from Ask Tom (little modified it myself) and created a trigger to use it.
====Trigger====
Firstly I take the new row and dump it into a dump table (which has the same structure as the source table). Also I record a unique identifier of this row, either ID either code either a combination uniquely enough:
CREATE OR REPLACE TRIGGER rougame_after_insert
AFTER INSERT
ON test.ROUGAME_TEST
referencing new as new
FOR EACH ROW
declare
p_column1Unique varchar2(16);
BEGIN
-- Insert record into audit table
INSERT INTO test.rougame_dump
( column1Unique,
column2,
column3,
column4,
column5,
column6,
column7,
column8
)
VALUES
( :new.column1Unique,
:new.column2,
:new.column3,
:new.column4,
:new.column5,
:new.column6,
:new.column7,
:new.column8
);
select :new.column1Unique into p_column1Unique
from dual;
csv_unload('schema_name.table_name','EXPDIR','table_name.csv',p_column1Unique); <- The directory EXPDIR is database directory created by default, you can create your own with: "Create directory DIRNAME as 'OS_location'
END;
/
P.S. Same can be done also for after update, just replace: after insert -> after update
====Procedure====
This procedure servers to export the data to csv as you might have guessed from the trigger :)
create or replace procedure csv_unload( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2,
p_dmcode in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname || ' where gmcode = ''' || p_dmcode ||''' order by 1 ';
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'a' ); <- "A" stands for "APPEND" meaning it will append rather than re-create (e.g. "W") the file.
execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
--utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' ); <- I excluded this because it would print the header after each insert. If you know the header and you care only about the data, then you don't care about the header :)
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/