Oracle导出txt⽂本⽂件
转⾃:
对于SPOOL数据的SQL，最好要⾃⼰定义格式，以⽅便程序直接导⼊,SQL语句如：
implements
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
2014年6月六级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函数执⾏时在⽂件中写⼊的其他信息。
cablet 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函数会失效。blitz
假设⽂件⽣成的路径为：/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
make friends
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 heading 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数据直接导出到⽂本⽂件的⽅法
利⽤Oracle中的Spool缓冲池技术可以实现Oracle数据导出到⽂本⽂件。
1)、在Oracle PL/SQL中输⼊缓冲开始命令，并指定输出的⽂件名：
spool
2)、在命令⾏中随便输⼊你的SQL查询：
0lect mobile from customer.
0lect mobile from client.
……
3)、在命令⾏中输⼊缓冲结果命令：head start
spool off.
则系统将缓冲池中的结果都输出到""⽂件中。
以TAB键分隔
例⼦(syoyou_xamshain.sql⽂件)
t newpage 0
t linesize 10000
t pagesize 0
t und off白洁英语
t trimspool on
t colp,
t echo off
t feedback off
spool syoyou_xamshain.csv
0lect
SHIMEI_CD , VALID_TERM_START_YMD , SHAIN_NMJ_SEI ,SHAIN_NMJ_NA , SHAIN_NMHK_SEI ,
SHAIN_NMHK_NA , SHAIN_NMHE_SEI , SHAIN_NMHE_MIDDLE , SHAIN_NMHE_NA , SEI_BETSU_KBN , NOK00J0001.NOZJ1301(birth_day),
SAIYOU_YMD , KEI_BETSU_CD , RETIRE_YMD , BANK_CD , BANK_NMJ , BANK_NMK , BANK_SHITEN_CD , BANK_SHITEN_NMJ , BANK_SHITEN_NMK , YOKIN_TYPE , KOUZA_MEIGI_KANA ,
NOK00J0001.NOZJ1301(kouza_no), SSO_TAISHO_FLG ,
paste什么意思VALID_TERM_END_YMD , NEXT_SYSTEM_TAISHO_FLG , IDOU_FLG , YUUKOU_FLG , CREATE_USER_CD , CREATE_TIME_STAMP , UPDATE_USER_CD , UPDATE_TIME_STAMP
from xamshain .
spool off
