ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用utl_file将数据导出为csv文件

利用utl_file将数据导出为csv文件

原创 Linux操作系统 作者:NinGoo 时间:2019-06-17 07:57:05 0 删除 编辑

asktom上看到的例子。稍微修改了一下导出文件的格式:

1.输出列名
2.所有列值都以双引号引起来
3.列值中原有的双引号则改成两个双引号
4.去掉前后空格


create or replace procedure dump_to_csv(
p_query in varchar2, --sql query statement
p_dir in varchar2, --the directory of file
p_filename in varchar2, --the export filename
p_max_linesize in number default 32000 --max linesize,must less than 32787
)
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
--open file
l_output := utl_file.fopen( p_dir, p_filename, 'w', p_max_linesize);

--define date format
execute immediate 'alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''';

--open cursor
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

--dump table column name
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 );

--execute the query statement
l_status := dbms_sql.execute(l_theCursor);

--dump table column value
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 || '"' ||
trim(both ' ' from replace(l_columnValue,'"','""')) || '"');
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;

--close cursor
dbms_sql.close_cursor(l_theCursor);

--close file
utl_file.fclose( l_output );
exception
when others then
raise;
end;
/

注意:
p_dir参数要求传入一个directory,可以使用以下语句创建
create directory exp_dir as 'd:';
然后传入参数p_dir=>'EXP_DIR',必须全部采用大写字母。

另外,也可以设置初始化参数utl_file_dir,然后传utl_file_dir的值即可。但是该参数不是动态参数,修改后需要重启数据库才能生效。

限制:
每行最大不能超过32787字节

测试:
以all_objects的内容生成一个160w行数据的表,导出时间约10分钟,导出后的文件约250M,机器:PC (P4 3.0G cpu + 1G mem),同时还开着oracle9201,sql server2000,以及其他一些应用程序。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50190/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    129200