linux plsql 执行sql文件,Linux Unix shell 调用 PL/SQL

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 "

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

# --------------------------------

声明：本文原创发布php中文网，转载请注明出处，感谢您的尊重！如有疑问，请联系admin@php.cn处理