Linux Unix shell 调用 PL/SQL(二)

Check the reslut
------------------------
/u02/database/CNBO1/bdump

3、变种方案(使用sql替代pl/sql)
robin@SZDB:~/dba_scripts/custom/bin> more shell_call_plsql_3.sh
# -------------------------------
# Set environment here
# ------------------------------

if [ -f ~/.bash_profile ]; then
. ~/.bash_profile
fi

export MAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56
export MAIL_LIST='Robinson.chen@2GoTrade.com'
export MAIL_FM='oracle@szdb.com'

# -----------------------------------
# Find bdump directory for database
# -----------------------------------

ORACLE_SID=$1; export ORACLE_SID
DUMP_DIR=`sqlplus -S '/ as sysdba' << EOF
set pagesize 0 feedback off verify off heading off echo off
SELECT value FROM v\\$parameter WHERE name = 'background_dump_dest';
exit
EOF`

if [ -z ${DUMP_DIR} ]; then
MAIL_SUB= "The bdump directory was not found for ${ORACLE_SID}"
$MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_SUB
exit
else
echo ${DUMP_DIR}
fi

exit

#注，上面的这个并不是调用pl/sql，而是使用了sql来完成相同的功能。如果sql能完成的功能，建议优先使用sql来完成。
#也要注意的是此处的parameter使用了两个转义符。
#同时将sql执行的返回结果直接赋予给shell变量

robin@SZDB:~/dba_scripts/custom/bin> chmod u+x shell_call_plsql_3.sh
robin@SZDB:~/dba_scripts/custom/bin> ./shell_call_plsql_3.sh CNBO1
/u02/database/CNBO1/bdump