ORCLE导出大量数据到txt

ORCLE导出大量数据到txt

方法一使用SQLPlus的spool命令

操作步骤

新建一个脚本文件存放起来如D:\spoolsql具体代码如下

?

Sql代码

set echo off 在用start命令执行一个sql脚本时是否显示脚本中正在执行的SQL语句

set feedback off 是否显示当前sql语句查询或修改的行数

set newpage none 会在页和页之间没有任何间隔

set verify off

set pagesize 分多少页

?

set term off 在用spool命令将一个大表中的内容输出到一个文件中时将内容输出在屏幕上会耗费大量的时间设置set termspool off后则输出的内容只会保存在输出文件中不会显示在屏幕上极大的提高了spool的速度

set trims on 将SPOOL输出中每行后面多余的空格去掉

set linesize 设置屏幕显示行宽默认

set heading off 禁止输出列标题

set timing off 默认为OFF设置查询耗时可用来估计SQL语句的执行时间测试性能

set numwidth

SPOOL D:\aaatxt

select UserTelNo || || to_char(ReceiveTimeyyyyMMdd HH:MI:SS) || || UserContent || || ReplyContent FROM LogSMSHall_Mutual WHERE rownum<=;

SPOOL OFF

注意如果有clob字段的话最好用to_char函数进行转换

用sqlplus登录到oracle数据库

如果是在数据库本机直接在命令行(WIN+R>CMD)窗口输入SQLPlus [用户名]/[密码]即可

如果远程登录在命令行中输入SQLPlus [用户名]/[密码]@[数据库IP地址:监听端口号]/[数据库实例名称]回车即可如sqlplus Ajita/@:/orcl

执行脚本文件

在SQLPlus命令窗口中执行步骤中的脚本文件命令方式为@[脚本文件位置]如@D:\spoolsql

优点简单易用

缺点不容易封装成存储过程

方法二使用UTL_FILE包的文件操作命令

操作步骤(只需要一步也可以封装成存储过程)

编写PL/SQL过程导出到txt即可

?

Sql代码

DECLARE

row_result varchar();

selectsql varchar();

qrycursor SYS_REFCURSOR;

txt_handle UTL_FILEfile_type;

BEGIN

selectsql := select bizname || || bizstatus from bizbusinessbaseinfo ;

txt_handle := UTL_FILEFOPEN(D:/UTLatxtw);

open qrycursor for selectsql;

loop

fetch qrycursor into row_result;

exit when qrycursor%notfound;

UTL_FILEPUT_LINE(txt_handlerow_result);

end loop;

关闭游标

close qrycursor;

UTL_FILEFCLOSE(txt_handle);

end;

注意事项

如果没有设置参数utl_file_dir参数本方法会报ora 的错误解决方案有两种

设置utl_file_dir参数两种方式

a) 在initora中设置直接修改文件即可

b) 在sqlplus命令行设置

?

alter system set utl_file_dir=* scope=spfile;

注意设置utl_file_dir的要点

utl_file_dir=* 这表示你能操作任何目录尽量不要用

utl_file_dir=d:\ 这表示你能操作d:\目录下的文件但你不能操作d:\目录下的子目录

注意在设置 utl_file_dir=路径时如果路径是长路径名例如c:\my temp目录则你必须加上例如 utl_file_dir=c:\my temp

utl_file_dir可以是多个路径 utl_file_dir=c:\d:\d:\tempc:\my temp

设置完必须重新启动数据库

创建自己的目录(不需要重启数据库)

在命令行中执行命令create or replace directory MY_DIR as c:/abc;即可然后调用的时候把frw:=utl_filefopen(c:\abcemptxtw);改成frw:=utl_filefopen(MY_DIRemptxtw);注意MY_DIR要大写

优点容易封装成存储过程

缺点要使用UTL_FILE配置稍微复杂点