oracle存储过程导出scv文件

最近研究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,'yyyymmdd_')|| p_filename||'.csv');

end;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!