自动生成CSV文件，使用Oracle 10g将标题分为两个标题级别，列数不同

Question

我们有一个使用Oracle 10g R.10.2.0.4.0的应用程序。我需要创建一个过程，将给定查询的数据导出到特定布局中的CSV文件，以便将其导入另一个应用程序。

我发现以下链接对我帮助很大。此过程使用utl_file，并允许我将需要的查询放在l_query字段中。

导出到CSV-通过询问TOM

我需要生成的文件具有以下结构：

Title;9999
FIELD1;FIELD2;FIELD3;FIELD4;FIELD5
1234;30032021;0400;093000;123000
1235;30032021;0400;133000;183000

我创建了两个级别的标题，一个是为具有两个字段的文件放置标题，另一个是应用程序将加载的数据列的标题。我使用UNION ALL实现了这一点。然而，我发现了两个我无法解决的问题：

1. 我无法从查询块中删除标头

2. 当我使用UNION ALL时，第一个阻塞查询迫使我拥有生成以下查询中的信息所需的字段数(五个字段(，因此，文件生成时有两个标头，第二个生成时有三个需要删除的;;;。

   职务；9999；''；''；'''--我需要删除此标头标题9999--我需要删除三个"FIELD1；FIELD2；FIELD3；FIELD4；字段51234；08042021；123；224130；0441301235；08042021；123；224130；0441301236；08042021；123；224130；044130

这是我根据在变量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' );

有人能帮我解决这个问题吗，或者为我指明其他实现目标的方法，并以正确的结构生成这个文件吗？

Answer 1

由于第一个标题行似乎不依赖于查询的结构，所以我只需要使用一个utl_file.put_line先打印出来，并保持Tom过程的其余部分基本不变

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;
/