用spool+unix shell生成文本文件

比较熟悉使用ORACLE的人一般都会用spool命令来生成OS下的文本文件.
 例如我们把scott.dept表生成文本文件的语句写成dept.sql,内容如下:

set echo on            --是否显示执行的命令内容  

set feedback off       --是否显示 *   rows   selected  

set heading off        --是否显示字段的名称

set verify off         --是否显示替代变量被替代前后的语句。fil

set trimspool off      --去字段空格
set pagesize 1000      --页面大小

set linesize 50//linesize设定尽量根据需要来设定，大了生成的文件也大
define fil= 'e:/exp.txt'
prompt *** Spooling to &fil
spool &fil
select deptno||','||dname||','||loc from scott.dept;
spool off;

但是生成的文本文件头和尾里含有不需要的空行和提示信息(虚线里面是生成文本文件的详细内容):
----------------------------------------
－－执行过程
SQL>; @e:/expmyusr.sql
*** Spooling to e:/exp.txt

10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
50,MARKETING,FOTTH WORTH
60,HEAD,Amercia

6 rows selected.

-----------------------------------------

我请教了kane,他写出来的del_head_tail_line.sh可以把文件头尾的行去掉,并生成新文件:

#!/bin/sh
#第一个参数是要处理的文件名
#第二个参数是文件头要去掉的行数
#第三个参数是文件尾要去掉的行数
#第四个参数是要输出的文件名
#created by kane@geeyo.com
lines=`wc -l $1 | awk '{print $1}'`
taillines=`expr $lines - $2`
tail -$taillines $1 > $1.tmp
headlines=`expr $taillines - $3`
head -$headlines $1.tmp > $4
rm $1.tmp

我们执行下面命令,就可以把不符合要求的行去掉,生成新文件1.txt

$./del_head_tail_line.sh dept.lst 1 3 1.txt

这样就解决了文本文件格式的问题.

具体应用举例:

因为工作需要,我经常要把一个小时或者一个星期变动过的数据生成文本文件上传到各个地区.

借用上面的方法, 我只需要写几个sql语句+ unix shell程序,配合OS定时就可以自动来完成这些工作:

第一步: 写好用来生成文本文件的SQL语句(假设是/oracle_backup/bin/test.sql)

   提示1: 如果有几个不同的查询条件,可以把两个查询结果用union all合并起来生成一个结果集

   提示2: Tab键的ascii码是char(9)                                             


第二步: 写好UNIX OS调用test.sql的脚本gen_test.sh

su - oracle -c "sqlplus username/password"<<EOF
@/oracle_backup/bin/test.sql;
exit

第三步: 删除文本文件头和尾的空行, 生成符合要求的文本文件gen_test_file.sh

   提示1: 我生成的结果文本文件的头和尾增加了有效记录的行数 

  提示2: 生成的文本文件名含有年月日小时的信息                 
     
cd /oracle_backup/log/test
rm 1.txt 2.txt
/oracle_backup/bin/del_head_tail_line.sh dept.lst 1 3 1.txt
CDAY=`date +%Y%m%d%H`;export CDAY
cat 1.txt|wc|awk '{print $1}'>2.txt
cat 2.txt>english_companyname_$CDAY.txt
cat 1.txt>>english_companyname_$CDAY.txt
cat 2.txt>>english_companyname_$CDAY.txt

第四步: 生成FTP传文件的脚本ftp_test.sh

   提示1: /oracle_backup/log/test/log/目录下记录了上传文件前后的时间差,是上传日志    
     
   提示2: 下面例子里FTP服务器目的地址为默认的/目录,如果要传到子目录,可以在/后添加子目录名
     
CDAY=`date +%Y%m%d%H`;export CDAY
cd /oracle_backup/log/test/
chmod 777 *.txt
date>/oracle_backup/log/test/log/ftp_test_$CDAY.log
ftp -i -n 192.168.0.253 <<FTPIT
user username password
bin
put /oracle_backup/log/test/english_companyname_$CDAY.txt /english_companyname_$CDAY.txt
quit
FTPIT
date>>/oracle_backup/log/test/log/ftp_test_$CDAY.log

第五步: 设定unix root用户的定时操作

Sun Solaris 文件 /var/spool/cron/crontabs/root
Linux 文件 /var/spool/cron/root

例如每小时执行一次:

3 0-23 * * * /oracle_backup/bin/gen_test.sh
5 0-23 * * * /oracle_backup/bin/gen_test_file.sh
8 0-23 * * * /oracle_backup/bin/ftp_test.sh

重新启动unix OS的定时服务，使新添加的任务生效
Sun Solaris
#/etc/rc2.d/S75cron stop
#/etc/rc2.d/S75cron start

Linux
#/etc/rc.d/init.d/crond restart

注意事项:

    unix shell脚本都应该有755的可执行权限.
   
    超过50000行的文件,会在每50000行的分隔处多出一个空行;
        如果这样的格式不合要求,可以再写一个unix shell重写文件,遇到空行时去掉空行.