SPOOL是SQLPLUS的命令,不是SQL语法里面的东西。
方法一:采用设置分隔符的方式
set colsep '|'
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
set num 18
set feedback off
spool 路径+文件名
select * from tablename;
spool off
方法二:采用语句拼接分隔符的方法
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool 路径+文件名
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off
注意:方法一导出来的数据具有很大的不确定性,导出的数据再由sqlldr导入的时候出错的可能性在95%以上,尤其对大批量的数据表,而且导出的数据文件狂大。而方法二导出的数据文件格式很规整,数据文件的大小可能是方法一的1/4左右。经这种方法导出来的数据文件再由sqlldr导入时,出错的可能性很小,基本都可以导入成功。实践中建议使用方法二手工去控制spool文件的格式。
LINESIZE要稍微设置大些,免得数据被截断,它应和相应的TRIMSPOOL结合使用防止导出的文本有太多的尾部空格。但是如果LINESIZE设置太大,会大大降低导出的速度。
案例:
在实践中,我们常常不会在命令行执行,而是写入shell脚本
#!/usr/bin/ksh #Usage test_spool.sh echo "start spool in shell.."; cd /odsdata/ods sqlplus -s user/passwd@dbname<<EOF set colsep '|'; ##域输出分隔符,导出来的数据具有很大的不确定性,建议在select里指定连接符 set newp none; ##设置查询出来的数据分多少页显示,如果需要连续的数据,中间不要出现空行就把newp设置为none set echo off; ##显示start启动的脚本中的每个sql命令,缺省为on set feedback off; ##回显本次sql命令处理的记录条数,缺省为on即去掉最后的 "已经选择10000行" set heading off; ##输出域标题,缺省为on,设置为off就去掉了select结果的字段名,只显示数据 set pagesize 0; ##输出每页行数,缺省为24,为了避免分页,可设定为0。 set linesize 8000; ##输出一行字符个数,缺省为80 set numwidth 12; ##输出number类型域长度,缺省为10 set termout off; ##显示脚本中的命令的执行结果,缺省为on set trimout on; ##去除标准输出每行的拖尾空格,缺省为off set trimspool on; ##去除重定向(spool)输出每行的拖尾空格,缺省为off set serveroutput on; ##设置允许显示输出类似dbms_output set timing on; ##设置显示“已用时间:XXXX” set autotrace on-; ##设置允许对执行的sql进行分析 set verify off; ##/可以关闭和打开提示确认信息old 1和new 1的显示. spool /odsdata/ods/test_spool.txt; select ACTIONTYPE||'|'||ACTIONDESC||'|'||YWBH||'|'||OBJCODE||'|'||BUOCMONTH||'|'||D0101 from TABLE_NAME; spool off; EOF echo "end..";