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 @/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
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
# --------------------------------