Linux/Unix 下除了調用SQL之外，調用PL/SQL也是DBA經常碰到的情形，下面主要通過一些示例給出如何在shell下面來調用pl/sql。
轉自：http://blog.csdn.net/robinson_0612/article/details/8556639
1、将pl/sql代碼逐行輸入到臨時檔案
[email protected]:~/dba_scripts/custom/bin> more shell_call_plsql.sh
#/bin/bash
# +--------------------------------------------+
# + An example of calling plsql in Shell +
# + Usage: +
# + ./shell_call_plsql.sh $ORACLE_SID +
# + Author: Robinson +
# +--------------------------------------------+
#
# ---------------------------------
# Define variable and check SID
# ---------------------------------
if [ -f ~/.bash_profile ]; then
. ~/.bash_profile
fi
if test $# -lt 1
then
echo You must pass a SID
exit
fi
ORACLE_SID=$1; export ORACLE_SID
# ---------------------------------
# Prepare plsql script
# ---------------------------------
echo "set serveroutput on size 1000000" > /tmp/plsql_scr.sql
echo "set feed off" >> /tmp/plsql_scr.sql
echo "declare" >> /tmp/plsql_scr.sql
echo "cursor c1 (param1 varchar2) is" >> /tmp/plsql_scr.sql
echo "select decode(substr(value, 1, 1), '?', param1 || substr(value, 2), value) dd" >> /tmp/plsql_scr.sql
echo "from v\$parameter where name = 'background_dump_dest';" >> /tmp/plsql_scr.sql
echo "v_value v\$parameter.value%type;" >> /tmp/plsql_scr.sql
echo "begin open c1 ('$ORACLE_HOME'); fetch c1 into v_value; close c1;" >> /tmp/plsql_scr.sql
echo "dbms_output.put_line(v_value);" >> /tmp/plsql_scr.sql
echo "end;" >> /tmp/plsql_scr.sql
echo "/" >> /tmp/plsql_scr.sql
# --------------------------------
# Execute plsql script
# --------------------------------
if [ -s /tmp/plsql_scr.sql ]; then
echo -e "Running SQL script to find out bdump directory... \n"
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" > /tmp/plsql_scr_result.log << EOF
@/tmp/plsql_scr.sql
EOF
fi
echo " Check the reslut "
echo "------------------------"
cat /tmp/plsql_scr_result.log
exit
#上面的代碼是查詢指定Oracle SID 的dump路徑。
#通過逐行逐行的方式将代碼添加到檔案以形成pl/sql代碼。
#需要注意轉義字元的使用,對于parameter 的$符号，我們進行了轉義。
[email protected]:~/dba_scripts/custom/bin> ./shell_call_plsql.sh CNBO1
Running SQL script to find out bdump directory...
Check the reslut
------------------------
/u02/database/CNBO1/bdump
2、一次性輸入pl/sql代碼到臨時檔案
[email protected]:~/dba_scripts/custom/bin> more shell_call_plsql_2.sh
#/bin/bash
# +--------------------------------------------+
# + An example of calling plsql in Shell +
# + Usage: +
# + ./shell_call_plsql_2.sh $ORACLE_SID +
# + Author: Robinson +
# +--------------------------------------------+
#
# ---------------------------------
# Define variable and check SID
# ---------------------------------
if [ -f ~/.bash_profile ]; then
. ~/.bash_profile
fi
if test $# -lt 1
then
echo You must pass a SID
exit
fi
ORACLE_SID=$1; export ORACLE_SID
# ---------------------------------
# Prepare plsql script
# ---------------------------------
echo "
set serveroutput on size 1000000
set feed off
declare
cursor c1 (param1 varchar2) is
select decode(substr(value, 1, 1),'?' , param1 || substr(value, 2), value) dd
from v\$parameter where name = 'background_dump_dest';
v_value v\$parameter.value%type;
begin
open c1 ('/users/oracle/OraHome10g');
fetch c1 into v_value; close c1;
dbms_output.put_line(v_value);
end;
/
exit ">/tmp/plsql_scr.sql
# --------------------------------
# Execute plsql script
# --------------------------------
if [ -s /tmp/plsql_scr.sql ]; then
echo -e "Running SQL script to find out bdump directory... \n"
$ORACLE_HOME/bin/sqlplus -s "/ as sysdba" @/tmp/plsql_scr.sql >/tmp/plsql_scr_result.log
fi
echo " Check the reslut "
echo "------------------------"
cat /tmp/plsql_scr_result.log
exit
# Author : Robinson Cheng
# Blog : http://blog.csdn.net/robinson_0612
#上面的方法是一次性将代碼輸入到臨時檔案，好處是直接按照pl/sql的書寫方式來寫，代碼清晰，簡潔明了。
[email protected]:~/dba_scripts/custom/bin> chmod u+x shell_call_plsql_2.sh
[email protected]:~/dba_scripts/custom/bin> ./shell_call_plsql_2.sh CNBO1
Running SQL script to find out bdump directory...
Check the reslut
------------------------
/u02/database/CNBO1/bdump
3、變種方案(使用sql替代pl/sql)
[email protected]:~/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='[email protected]'
export MAIL_FM='[email protected]'
# -----------------------------------
# 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變量
[email protected]:~/dba_scripts/custom/bin> chmod u+x shell_call_plsql_3.sh
[email protected]:~/dba_scripts/custom/bin> ./shell_call_plsql_3.sh CNBO1
/u02/database/CNBO1/bdump