Linux/Unix 下除了调用SQL之外，调用PL/SQL也是DBA经常碰到的情形，下面主要通过一些示例给出如何在shell下面来调用pl/sql。
Linux/Unix 下除了调用SQL之外，调用PL/SQL也是DBA经常碰到的情形，下面主要通过一些示例给出如何在shell下面来调用pl/sql。
其它相关的参考：
Linux/Unix shell 脚本中调用SQL,RMAN脚本
Linux/Unix shell sql 之间传递变量
1、将pl/sql代码逐行输入到临时文件
robin@SZDB:~/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 的$符号，我们进行了转义。
robin@SZDB:~/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代码到临时文件
robin@SZDB:~/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 "
将服务器输出设置为大小 1000000
设置 feed 关闭
声明
光标 c1 (param1 varchar2) 是
选择解码(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');
获取 c1 到 v_value;
dbms_output.put_line(v_value);
end;
/
退出 ">/tmp /plsql_scr.sql
# --------------------------------
# 执行 plsql 脚本
# --- ------------------------------
