Oracle spool 用法小结介绍一本好书
Spool简介
spool 是数据的导出命令，最简单的执行如下:
用sqlplus 登陆:
SQL>spool f:\
SQL> lect * from test_load;
STU_NUM STU_NAME STU_DATE
---------- -------------------------------------------------- ----------
1234 niegc 01-1月 -06
1235 ngc 02-1月 -06
SQL> spool off
这样就可以导出了
如果你想导出自己定义的格式，你可以先t 参数然后再spool
例如:
SET COLSEP ' ' --域输出分隔符，设置' '为分隔符
SET ECHO OFF --是否显示执行的命令内容，缺少为ON
SET FEEDBACK OFF --是否显示最后一行的计数反馈信息，缺省为ON
SET HEADING OFF --是否输出列标题，缺省为ON
SET pageSIZE 0 --输出每页行数，缺省为24，为了避免分页，可设定为0。
SET lineSIZE 1000 --设置屏幕显示行宽，尽量根据需要来设定，大了生成的文件也大
SET numWIDTH 12 --数值型字段的长度
SET arraySIZE 5000
SET TIMING OFF --是否关闭查询定时
SET TERMOUT OFF --是否显示脚本中的命令的执行结果，缺省为on
SET TRIMOUT ON --是否去除标准输出每行的拖尾空格，缺省为off
SET TRIMSPOOL ON --是否去除重定向（spool）输出每行的拖尾空格，缺省为off，去字段空格
在生产中常会遇到需要将数量比较大的表值导入到本地文本文件中. 方法有很多种，比较常用的就是spool命令:
要输出符合要求格式的数据文件只需在lect时用字符连接来规范格式。比如有如下表
SQL>; lect id,urname,password from myur;//测试表
1 John 1234
2 Jack 12345
3 Ro 2345
4 Joe 384657
5 Tom 384655
6 Jordan 384455
要输出符合1,John,1234，这样的数据格式就用lect id||','||urname||','||password||',' from myur这样的语句。
SQL>; lect id||','||urname||','||password||',' from myur;
1,John,1234,李世民谥号
2,Jack,12345,
写个下面这样的脚本就行可以输出符合要求格式的数据至文件中，不会含有其它不需要东西，只有数据部分。
－－脚本文件名为expmyusr.sql，存数据的文件名为e:\
t echo on --是否显示执行的命令内容
t feedback off --是否显示 * rows lected
t heading off --是否显示字段的名称
t verify off --是否显示替代变量被替代前后的语句。fil
t trimspool off --去字段空格
t pagesize 1000 --页面大小
t linesize 50 --linesize设定尽量根据需要来设定，大了生成的文件也大
define fil= 'e:\'
prompt *** Spooling to &fil
spool &fil
lect id||','||urname||','||'"'||password||'"' from myu
r;
spool off;
－－执行过程
SQL>; @e:\expmyusr.sql
*** Spooling to e:\
1,John,"1234"
2,Jack,"12345"
3,Ro,"2345"
4,Joe,"384657"
5,Tom,"384655"
6,Jordan,"384455"
检查可知结果符合要求。
=====================================
Oracle spool 用法小结
关于SPOOL(SPOOL是SQLPLUS的命令，不是SQL语法里面的东西。)
对于SPOOL数据的SQL，最好要自己定义格式，以方便程序直接导入,SQL语句如：
lect taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_ndsms_task;
spool常用的设置
t colp' '; //域输出分隔符
t echo off; //显示start启动的脚本中的每个sql命令，缺省为on
t feedback off; //回显本次sql命令处理的记录条数，缺省为on
t heading off; //输出域标题，缺省为on
t pagesize 0; //输出每页行数，缺省为24，为了避免分页，可设定为0。
t termout off; //显示脚本中的命令的执行结果，缺省为on
t trimout on; //去除标准输出每行的拖尾空格，缺省为off半坡遗址
t trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
月牙城
导出文本数据的建议格式：
SQL*PLUS环境设置SET NEWPAGE NONE
SET HEADING OFF
SET SPACE 0
SET PAGESIZE 0
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 2500
注：LINESIZE要稍微设置大些，免得数据被截断，它应和相应的TRIMSPOOL结合使用防止导出的文
本有太多的尾部空格。但是如果LINESIZE设置太大，会大大降低导出的速度，另外在WINDOWS下导出最好不要用PLSQL导出，速度比较慢，直接用COMMEND下的SQLPLUS命令最小化窗口执行。
对于字段内包含很多回车换行符的应该给与过滤，形成比较规矩的文本文件。通常情况下，我们使用SPOOL方法，将数据库中的表导出为文本文件的时候会采用两种方法，如下述：
方法一：采用以下格式脚本
t colp '|' --设置|为列分隔符
t trimspool on
t linesize 120
t pagesize 2000
t newpage 1
t heading off
t term off
t num 18
t feedback off
spool 路径+文件名
lect * from tablename;
spool off
方法二：采用以下脚本
t trimspool on
t linesize 120
t pagesize 2000
t newpage 1
南京大学宿舍
t heading off
t term off
spool 路径+文件名
lect col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off
比较以上方法，即方法一采用设定分隔符然后由sqlplus自己使用设定的分隔符对字段进行分割，方法二将分隔符拼接在SELECT
语句中，即手工控制输出格式。
在实践中，发现通过方法一导出来的数据具有很大的不确定性，这种方法导出来的数据再由sqlldr导入的时候出错的可能性在95%以上，尤其对大批量的数据表，如100万条记录的表更是如此，而且导出的数据文件狂大。
而方法二导出的数据文件格式很规整，数据文件的大小可能是方法一的1/4左右。经这种方法导出来的数据文件再由sqlldr导入时，出错的可能性很小，基本都可以导入成功。
因此，实践中我建议大家使用方法二手工去控制spool文件的格式，这样可以减小出错的可能性，避免走很多弯路。
自测例：将ssrv_ndsms_task表中的数据导出到文本(数据库Oracle 9i 操作系统 SUSE LINUX Enterpri Server 9)
spool_test.sh脚本如下：
系统故障
#!/bin/sh
DB_USER=zxdbm_ismp #DB USER
DB_PWD=zxin_smap #DB PASSWORD
DB_SERV=zx10_40_43_133 #DB SERVICE NAME
sqlplus -s $DB_USER/$DB_PWD@$DB_SERV<<EOF # -s 参数屏蔽打印到屏幕上的其他信息，只显示sql执行后从DB中查询出来的信息，过滤掉spool函数执行时在文件中写入的其他信息。
t trimspool on
t linesize 120
t pagesize 2000
t newpage 1
t heading off
t term off
lect taskindex||'|'||commonindex||'|'||tasktype||'|'||to_number(to_char(sysdate,'YYYYMMDD')) from ssrv_ndsms_task;
spool off
EOF
执行./spool_test.sh后生成，内容如下：
83|115|1|20080307
85|115|11|20080307
86|115|10|20080307
84|115|2|20080307
6|5|14|20080307
7|5|12|20080307
9|5|15|20080307
注：上面自测例中，中的目标生成文件，在HP-UNX环境下的shell脚本中调用Oracle的spool函数，如果将上述逻辑代码封装为一个function，然后来调用这个function的话，则在shell脚本中最终是不会生成文件的。只能直接执行逻辑代码，封装后则spool函数失效。
对于在相对路径下，下面2中方法在shell环境中执行时，两者只能择一，两者并存则spool函数会失效。假设文件生成的路径为：/home/zxin10/zhuo/batchoperate/spoolfile
方式[1]
echo "start spool in shell.."
sqlplus -s zxdbm_ismp/zxin_smap<<EOF
t pagesize 0
t echo off feed off term off heading off trims off
t colp '|'
t trimspool on
t linesize 10000
t trimspool on
t linesize 120
t newpage 1
spool /home/zxin10/zhuo/batchoperate/
lect batchindex||'|'||productid||'|'||contentid||'|'||optype||'|'||uploadfile from zxdbm_700.s700_batch_operation where status=1;
好员工spool off
EOF
echo "end.."
方式[2]
echo "start spool in shell.."
cd /home/zxin10/zhuo/batchoperate/spoolfile
sqlplus -s zxdbm_ismp/zxin_smap<<EOF
t pagesize 0
t echo off feed off term off head
ing off trims off
t colp '|'
t trimspool on
t linesize 10000
t trimspool on
t linesize 120
t newpage 1
lect batchindex||'|'||productid||'|'||contentid||'|'||optype||'|'||uploadfile from zxdbm_700.s700_batch_operation where status=1;
spool off
EOF
echo "end.."
========================================
在编程实现 Oracle SPOOL -----> Sqlldr进行数据迁移的时候，发现了两个奇怪的问题：
1、源表中数值型字段到了目的表中值都一样，而实际在字段在源表中是主键，查看该字段在目的表中的值似乎都导成了源表中另外一个字段的值；
2、源表的数据导到目的表中，很多字段的值都丢了
于是从源表，抽数据的视图，目的表逐个字段查了一遍，没发现问题，程序也没问题，sqlldr本身也不会将数据重复插入。那么，问题在哪呢？经过一段时间的困惑后，终于发现问题在于SPOOL过程中的两个参数设置有问题。
第一个参数就是linesize，这直接决定了导出来的每行记录的长度，原来设置为500，也就是500个字符。而源表中字段很多，这样直接导致了SPOOL过程中只导出了一部分字段的值，这就会出现上面的问题2种所描述的现象；
第二个参数就是 numwidth ，也就是数值型字段的长度，原来设置为12，而源表和目的表的最大数值长度都是20，这样导出来的数据就只能是前面的12位，所以出现了问题一中所描述的现象。
大家以后如果采用这种方法进行数据迁移，请务必特别留心这两个参数的设置，免得再走很多弯路。
(如果转载本文
=============================
t colp ',' //域输出分隔符
t echo off //显示start启动的脚本中的每个sql命令，缺省为on
t feedback off //回显本次sql命令处理的记录条数，缺省为on
t heading off //输出域标题，缺省为on
t pagesize 0 //输出每页行数，缺省为24，为了避免分页，可设定为0。
t termout off //显示脚本中的命令的执行结果，缺省为on
t trimout on //去除标准输出每行的拖尾空格，缺省为off
t trimspool on //去除重定向（spool）输出每行的拖尾空格，缺省为off
spool d:\a.txt美国科罗拉多大峡谷
lect to_char(id)||','||dd from test;
spool off
