shell链接oracle导出txt文本

#!/bin/sh
search_sql=`
sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
set heading off
select SEARCH_SQL from PO_BIG_DATA_EXPORT where rownum<=1 and status=0 order by EXPORT_ID asc;
exit;
EOF`

export_id=`
sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
set heading off
select EXPORT_ID from PO_BIG_DATA_EXPORT where rownum<=1 and status=0 order by EXPORT_ID asc;
exit;
EOF`

uid=`
sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
set heading off
select USER_ID from PO_BIG_DATA_EXPORT where rownum<=1 and status=0 order by EXPORT_ID asc;
exit;
EOF`

file_id=`
sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
set heading off
SELECT SEQ_UPLOAD_FILE.nextval from dual;
exit;
EOF`

save_path=$(date +%Y%m%d%H%M%S).txt;

sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
spool $save_path
$search_sql
spool off;
EOF

sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
update PO_BIG_DATA_EXPORT set status='1',SAVE_PATH='$save_path' where EXPORT_ID=$export_id;
exit;
EOF

sqlplus -s chnlmgr/chnlmgr@vasd<<EOF # -s
insert into PO_UPLOAD_FILE (FILE_ID,SAVE_PATH,UPLOAD_FILE_NAME,USER_ID) VALUES ($file_id,'$save_path','export',$uid);
exit;
EOF

转载于:https://www.cnblogs.com/xingmeng/archive/2013/04/23/3037856.html