将table内容输出为csv文件

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1、创建 directory

create or replace directory tt as'c:\';

SELECT * FROM DBA_OBJECTS DO WHERE DO.OBJECT_TYPE LIKE'%DIR%'; --- 查询目录是否创建成功

2、输出脚本：来自asktom网站

create or replace procedure dump_table_to_csv(p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2) authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2();
l_status integer;
l_query varchar2() default 'select * from ' || p_tname;
l_colCnt number := ;
l_separator varchar2();
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen(p_dir, p_filename, 'w');
execute immediate 'alter session set nls_date_format=''dd-mon-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 .. l_colCnt loop
utl_file.put(l_output,
l_separator || '"' || l_descTbl(i).col_name || '"');
dbms_sql.define_column(l_theCursor, i, l_columnValue, );
l_separator := ',';
end loop;
utl_file.new_line(l_output); l_status := dbms_sql.execute(l_theCursor); while (dbms_sql.fetch_rows(l_theCursor) > ) loop
l_separator := '';
for i in .. 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;