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

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

#上面的方法是一次性将代码输入到临时文件，好处是直接按照pl/sql的书写方式来写，代码清晰，简洁明了。

robin@SZDB:~/dba_scripts/custom/bin> chmod u+x shell_call_plsql_2.sh
robin@SZDB:~/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)
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' chmod u+x shell_call_plsql_3.sh
robin@SZDB:~/dba_scripts/custom/bin> ./shell_call_plsql_3.sh CNBO1
/u02/database/CNBO1/bdump 

您可能感兴趣的文章: