Linux Unix shell calls PL/SQL

In Linux/Unix, apart from calling SQL, calling PL/SQL is also a common situation for DBA. The following mainly uses some examples to show how to call pl/SQL under shell.

Other related references:

Linux/Unix shell scripts call SQL and RMAN scripts

Passing variables between Linux/Unix shell SQL statements

1. Input pl/SQL code to a temporary file line by line.
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

# The code above is to query the dump path of the specified Oracle SID.
# Add the code to a file in line by line to form pl/SQL code.
# Note the use of escape characters. We have escaped the $ symbol of 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. One-time pl/SQL code input to a temporary file
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

# The above method is to input the code to a temporary file at a time. The advantage is that the Code is written directly in pl/SQL mode, and the code is clear and concise.

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. Variant Scheme (replace pl/SQL with 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

# Note: The above does not call pl/SQL, but uses SQL to accomplish the same function. If SQL functions can be completed, we recommend that you use SQL first.
# Note that the parameter here uses two escape characters.
# Grant the returned results of SQL Execution directly to shell Variables

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