srbank_qrq 2015-03-30 03:19
浏览 1018

oracle spool定时导出数据脚本 周末无法导出 求解啊

定时任务定时每天凌晨3点导出数据,刚开始几天都正常导出,一到周天就导出个空文件了,sql测试过当天是有数据的,环境变量打印出来也是正常的,求解啊:
定时任务:
#!/bin/bash
. /home/unpay/etc/.profile

echo "test"$DBI_CONNSTR
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
/s01/oracle/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus $DBI_CONNSTR << EOF
@/home/unpay/sbin/lssql.txt;
EOF
cd /home/unpay/tmp/TransFile/czzh
if [! -f result.txt ];
then
echo "no file exits"
exit 1
fi
cp -rf result.txt test.txt
sed /^$/d result.txt > dxe.txt
ftp -n<<!
open **.*.***.**
user elink *******
binary
prompt off
cd czzhgl/$(date +%Y%m%d)
mput dxe.txt
close
bye
!
mv /home/unpay/tmp/TransFile/czzh/dxe.txt /home/unpay/tmp/TransFile/czzh/$(date +%Y%m%d).txt
rm -rf /home/unpay/tmp/TransFile/czzh/result.txt

lssql.txt:
set trimspool on;
set linesize 1000;
set pagesize 2000;
set newpage 1;
set heading off;
set term off;
set echo off;
set feed off;
spool /home/unpay/tmp/TransFile/czzh/result.txt;
select decode(txn_dir,1,rcv_brno,2,snd_brno,null)||'|'||decode(txn_dir,1,rcv_st_brno,2,snd_st_brno,null)||'|'||decode(txn_dir,1,rcv_
brname,2,snd_brname,null) from srpay_cnaps_txn where case when txn_dir='1' and act_status='01' and biz_type_code!='A105' and msg_no
!='007' then 1 when txn_dir='2' and (( msg_no not in ('141','253') and act_status='02' ) or ( msg_no in ('141','253') and act_
status='01')) then 1 else 0 end=1 and pboc_status = 'PR04' and work_date = to_char(sysdate-1,'YYYYMMDD');
spool off;

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 js调用html页面需要隐藏某个按钮
    • ¥15 ads仿真结果在圆图上是怎么读数的
    • ¥20 Cotex M3的调试和程序执行方式是什么样的?
    • ¥20 java项目连接sqlserver时报ssl相关错误
    • ¥15 一道python难题3
    • ¥15 牛顿斯科特系数表表示
    • ¥15 arduino 步进电机
    • ¥20 程序进入HardFault_Handler
    • ¥15 oracle集群安装出bug
    • ¥15 关于#python#的问题:自动化测试