最近研究oracle存储过程导出excel文件,但是还没有实现分sheet页,还在继续研究;
oracle的utl_file包访问文件,必须设置文件访问路径,配置方法如下:
1、alter system set utl_file_dir='e:\utl' scope=spfile;
2、在init.ora文件中,配置如下:
UTL_FILE=E:\utl或者UTL_FILE_DIR=E:\utl
SQL> alter system set utl_file_dir='/u01/app/oracle' scope=spfile;
System altered.
SQL> startup force;
SQL> show parameter utl_file
到出SQL存储过程如下:
create or replace procedure sql_to_csv(p_query in varchar2, -- plsql文
p_dir in varchar2, -- 导出的文件放置目录
p_filename in varchar2, -- csv名
p_header in varchar2 --表头
) is
l_thecursor integer default dbms_sql.open_cursor;
l_colcnt number := 0;
l_separator varchar2(2) := ',';
l_desctbl dbms_sql.desc_tab;
p_max_linesize number := 32000;
lv_sql varchar2(32000);
begin
execute immediate 'alter session set nls_date_format=''yyyymmdd hh24:mi:ss''';
lv_sql := '
declare
l_output utl_file.file_type;
l_row varchar2(32000) := null;
cursor c is ' || p_query || ';
type tp_rows is table of c%rowtype index by pls_integer;
r tp_rows;
begin
l_output := utl_file.fopen(''' || p_dir || ''', ''' ||
p_filename || '.csv'', ''w'', ' || p_max_linesize || ');
utl_file.put_line(l_output,''' || p_header ||
''');
open c;
loop
fetch c bulk collect into r;
for i in 1..r.count loop
l_row := ';
dbms_sql.parse(l_thecursor, p_query, dbms_sql.native);
dbms_sql.describe_columns(l_thecursor, l_colcnt, l_desctbl);
for i in 1 .. l_colcnt loop
if i > 1 then
lv_sql := lv_sql || ' || ''' || l_separator || ''' || ';
end if;
lv_sql := lv_sql || 'r(i).' || l_desctbl(i).col_name;
end loop;
dbms_sql.close_cursor(l_thecursor);
lv_sql := lv_sql || ';
utl_file.put_line(l_output,l_row,true);
end loop;
exit when c%notfound;
end loop;
close c;
utl_file.fclose( l_output );
exception
when others then
utl_file.fclose( l_output );
dbms_output.put_line(dbms_utility.format_error_backtrace);
raise;
end;';
dbms_output.put_line(lv_sql);
execute immediate lv_sql;
-- utl_file.fremove(p_dir,to_char(sysdate,'yyyymmdd_')|| p_filename||'.csv');
-- utl_file.frename(p_dir,p_filename||'.tmp',p_dir,to_char(sysdate,'yyyymmdd_')|| p_filename||'.csv');
end;