oracle存储过程导出scv文件

来源：互联网发布：出租房网络方案系统图编辑：程序博客网时间：2024/06/06 07:43

最近研究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;

0 0