oracle pkg自动导出发邮件

export ORACLE_SID=PROD
source /home/oracle/.bash_profile

a="$1"

str=`echo $a | tr '[:lower:]' '[:upper:]'`

mkdir -p /tmp/dumppkg/
rm -rf /tmp/dumppkg/*

 

for pkg in ${str[@]};do
echo $pkg
################for#########################################
sql="select case when t.line=1 then  'CREATE OR REPLACE '||t.TEXT when t.line=(SELECT max(v.line)  FROM dba_source v  WHERE v.owner =t.owner AND v.type =t.type AND v.name = t.name) then   t.TEXT||chr(13)||'/ ' else t.TEXT end as txt from dba_source t where t.owner = '' and t.type in ('PACKAGE','PACKAGE BODY') AND T.name='"${pkg}"'  order by t.type,t.line;"

echo 'set heading off'        >  /tmp/${pkg}.sql
echo 'set headsep off'        >>  /tmp/${pkg}.sql
echo 'set newp none'          >>  /tmp/${pkg}.sql
echo 'set sqlblanklines OFF'  >>  /tmp/${pkg}.sql
echo 'set trimspool ON'       >>  /tmp/${pkg}.sql
echo 'set echo off'           >>  /tmp/${pkg}.sql
echo 'set feedback off'       >>  /tmp/${pkg}.sql
echo 'set termout off'        >>  /tmp/${pkg}.sql
echo 'set linesize 4001'      >>  /tmp/${pkg}.sql
echo "spool /tmp/dumppkg/${pkg}.pck"  >>  /tmp/${pkg}.sql

echo $sql >> /tmp/${pkg}.sql

echo 'spool off'  >>  /tmp/${pkg}.sql

execsql=`/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus / as sysdba <<EOF
@/tmp/${pkg}.sql
EOF`  
 

#########for done###########################################
done

#cd /tmp/dumppkg && zip ${pkg}.pck.zip ${pkg}.pck

/usr/local/bin/sendEmail -f 发件人  -t 收件人 -cc 抄送人  -s smtp.qq.com -u dbprod_pkg_dump -xu 发件人  -xp 密码 -m ${str}   -a /tmp/dumppkg/*