We have an application that uses Oracle 10g R. 10.2.0.4.0. I need to create a procedure that exports the data for a given query to a CSV file in a specific layout, so that it can be imported into another application.
I found the following link that helped me a lot. This procedure uses utl_file and allows me to put the queries I need in the l_query field.
The file I need to generate has the following structure:
Title;9999
FIELD1;FIELD2;FIELD3;FIELD4;FIELD5
1234;30032021;0400;093000;123000
1235;30032021;0400;133000;183000
I created the titles in two levels, one to put a title for the file with two fields, and the other with the title of the columns of the data that the application will load. I achieved this using UNION ALL. I found two problems that I can't solve, however:
I can't remove the headers from the query block
As I used UNION ALL, the first blocking query forces me to have the number of fields that I need to generate the information in the following queries (five fields), and for that reason the file is generated with two headers, and in the second is generated with three ;;; that I need to remove.
Title;9999;'';'';''; --I need to remove this header Title;9999; ; ; --I need to remove the three ";;;" FIELD1;FIELD2;FIELD3;FIELD4;FIELD5 1234;08042021;123;224130;044130 1235;08042021;123;224130;044130 1236;08042021;123;224130;044130
This was the procedure that I adapted with the query that I created in the variable l_query.
create or replace procedure dump_table_to_csv(p_dir in varchar2, p_filename 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(4000)
default
'SELECT ''Title'' AS "Title", ''9999'' AS "9999" , '' '', '' '', '' '' from DUAL
UNION ALL
SELECT TO_CHAR(''FIELD1'') AS FIELD1, TO_CHAR(''FIELD2'') AS FIELD2, TO_CHAR(''FIELD3'') AS FIELD3, TO_CHAR(''FIELD4'') AS FIELD4, TO_CHAR(''FIELD5'') AS FIELD5 FROM DUAL
UNION ALL
SELECT
TO_CHAR(''9999'') AS FIELD1
,TO_CHAR(SYSDATE, ''DDMMYYYY'') AS FIELD2
,''123'' AS FIELD3
,LPAD(TO_CHAR(SYSDATE, ''HH24MISS''), 6, 0) AS FIELD4
,LPAD(TO_CHAR(SYSDATE + INTERVAL ''6:00'' HOUR TO MINUTE, ''HH24MISS''), 6, 0) AS FILED5
FROM DUAL';
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w', 4000 );
execute immediate 'alter session set nls_date_format=''DD-MM-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 || ';' );
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;
/
EXEC dump_table_to_csv('MY_DIRECTORY', 'file_to_export.csv' );
Can anyone help me fix this problem, or point me to some other way to achieve my goal, and generate this file in the correct structure?
Since the first header line doesn't appear to depend on the structure of the query, I'd just use a single utl_file.put_line to print that out first and leave the rest of Tom's procedure basically unchanged
create or replace procedure dump_table_to_csv(p_dir in varchar2,
p_filename 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(4000) :=
q'{SELECT
TO_CHAR(''9999'') AS FIELD1
,TO_CHAR(SYSDATE, ''DDMMYYYY'') AS FIELD2
,'123' AS FIELD3
,LPAD(TO_CHAR(SYSDATE, 'HH24MISS'), 6, 0) AS FIELD4
,LPAD(TO_CHAR(SYSDATE + INTERVAL '6:00' HOUR TO MINUTE, 'HH24MISS'), 6, 0) AS FILED5
FROM DUAL}';
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w', 4000 );
execute immediate 'alter session set nls_date_format=''DD-MM-YYYY HH24:MI:SS'' ';
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
utl_file.put_line( 'Title;9999' );
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || '' || l_descTbl(i).col_name || ';' );
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;
/