分类: Mysql/postgreSQL
2013-02-02 17:53:03
Linux/Unix 下除了调用SQL之外,调用PL/SQL也是DBA经常碰到的情形,下面主要通过一些示例给出如何在shell下面来调用pl/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 # -------------------------------- 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的书写方式来写,代码清晰,简洁明了。 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' << 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变量 robin@SZDB:~/dba_scripts/custom/bin> chmod u+x shell_call_plsql_3.sh robin@SZDB:~/dba_scripts/custom/bin> ./shell_call_plsql_3.sh CNBO1 /u02/database/CNBO1/bdump