java shell spool_利用shell脚本spool数据到一指定文

!/usr/bin/sh

###############################

# 1.define #

###############################

#----------1.1_set param-------#

#数据库用户名

ORA_USER=xxx

#数据库用户密码

ORA_PASS=xxx

#登陆本地命名(也即@后的连接串)

ORA_LOCAL_NAME=db80

#存放导出数据的本地路径

DataPath='/home/oracle/'

echo "======================================================================="

echo "database username: ${ORA_USER}"

echo "the path of the file: ${DataPath}"

###############################

# 2.main program #

###############################

#----------2.1 export data------#

cd ${DataPath}

Cur_Date=`date +%Y%m%d%H%M%S`

TMPFILE="service_${Cur_Date}.txt"

echo "export max service data to file： ${TMPFILE}"

sqlplus -s $ORA_USER/$ORA_PASS@$ORA_LOCAL_NAME

set echo off;

set feedback off;

set head off

set pagesize 0;

set linesize 1000;

set termout off;

set trimout on;

set trimspool on;

select distinct a.icpcode||chr(31)||a.icpservid||chr(31)||a.servname||chr(31)||decode(a.servtype,2,'10',3,'10',6,'10',9,'10',1,'19',4,'19',5,'19',8,'19',21,'19',22,'19',23,'19')||chr(31)||decode(c.type,'nt:gcontent:redSegment','18','nt:gcontent:audio','12','nt:gcontent:cartoon','14','nt:gcontent:image','13','nt:gcontent:game','16','nt:gcontent:video','15','nt:gcontent:news','17','nt:gcontent:coloring','11') from v_service a,t_r_gcontent b,T_R_base c where a.icpcode=b.icpcode and a.icpservid=b.icpservid and b.id=c.id;

spool off;

exit;

EOF