Pass Linux/Unix shell parameters to SQL scripts

In the database O & M process, shell scripts provide great convenience for O & M. The passing of shell script parameters as variables to SQL and SQL scripts is also a common situation for DBAs. This article mainly discusses how to pass parameters of shell scripts to SQL scripts and execute SQL queries.
For how to pass variables between Shell and SQL, see: Transfer variables between Linux/Unix shell SQL.

1. Execute the script and pass parameters when starting sqlplus

robin@SZDB:~/dba_scripts/custom/awr> more tmp.sh#!/bin/bash# ----------------------------------------------#  Set environment here#  Author : Robinson Cheng#  Blog   : http://blog.csdn.net/robinson_0612# ----------------------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiif [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"    read -p "please input begin ORACLE_SID:" ORACLE_SID    read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date    read -p "please input end date and time(e.g. yyyymmddhh24):" end_dateelse    ORACLE_SID=${1}    begin_date=${2}    end_date=${3}fiexport ORACLE_SID begin_date end_date#Method 1: pass the parameter to script directly after script namesqlplus -S gx_adm/gx_adm @/users/robin/dba_scripts/custom/awr/tmp.sql $begin_date $end_date exitrobin@SZDB:~/dba_scripts/custom/awr> more tmp.sqlSELECT snap_id, dbid, snap_level  FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time, 'yyyymmddhh24') = '&1'       AND TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&2';exit;

2. Pass parameters at the SQL prompt

robin@SZDB:~/dba_scripts/custom/awr> more tmp2.sh#!/bin/bash# ----------------------------------------------#  Set environment here#  Author : Robinson Cheng#  Blog   : http://blog.csdn.net/robinson_0612# ----------------------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiif [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"    read -p "please input begin ORACLE_SID:" ORACLE_SID    read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date    read -p "please input end date and time(e.g. yyyymmddhh24):" end_dateelse    ORACLE_SID=${1}    begin_date=${2}    end_date=${3}fiexport ORACLE_SID begin_date end_date#Method 2: pass the parameter in SQL prompt. Using the same method with method 1sqlplus -S " / as sysdba" <<EOF@/users/robin/dba_scripts/custom/awr/tmp.sql $begin_date $end_dateexit;EOFexit

3. Passing parameters by defining variables

robin@SZDB:~/dba_scripts/custom/awr> more tmp3.sh #!/bin/bash# ----------------------------------------------#  Set environment here#  Author : Robinson Cheng#  Blog   : http://blog.csdn.net/robinson_0612# ----------------------------------------------if [ -f ~/.bash_profile ]; then    . ~/.bash_profilefiif [ -z "${1}" ] || [ -z "${2}" ] || [ -z "${3}" ] ;then    echo "Usage: "    echo "      `basename $0` <ORACLE_SID> <begin_dat> <end_date>"    read -p "please input begin ORACLE_SID:" ORACLE_SID    read -p "please input begin date and time(e.g. yyyymmddhh24):" begin_date    read -p "please input end date and time(e.g. yyyymmddhh24):" end_dateelse    ORACLE_SID=${1}    begin_date=${2}    end_date=${3}fiexport ORACLE_SID begin_date end_date#Method 3: pass the parameter to global variable firstly.sqlplus -S " / as sysdba" <<EOFdefine begin_date=$begin_date        define end_date=$end_dateprompt "variable value for begin_date is: &begin_date"prompt "variable value for end_date id : &end_date"@/users/robin/dba_scripts/custom/awr/tmp3.sql begin_date end_dateexit;EOFexitrobin@SZDB:~/dba_scripts/custom/awr> more tmp3.sqlSELECT snap_id, dbid, snap_level  FROM dba_hist_snapshot WHERE TO_CHAR (begin_interval_time, 'yyyymmddhh24') = '&begin_date'       AND TO_CHAR (end_interval_time, 'yyyymmddhh24') = '&end_date';exit;

4. Test script

robin@SZDB:~/dba_scripts/custom/awr> ./tmp.shUsage:       tmp.sh <ORACLE_SID> <begin_dat> <end_date>please input begin ORACLE_SID:CNMMBOplease input begin date and time(e.g. yyyymmddhh24):2013030709please input end date and time(e.g. yyyymmddhh24):2013030710   SNAP_ID       DBID SNAP_LEVEL---------- ---------- ----------     13877  938506715          1robin@SZDB:~/dba_scripts/custom/awr> ./tmp2.sh MMBOTST 2013030709 2013030710   SNAP_ID       DBID SNAP_LEVEL---------- ---------- ----------     36262 3509254984          1robin@SZDB:~/dba_scripts/custom/awr> ./tmp3.sh MMBOTST 2013030710 2013030711"variable value for begin_date is: 2013030710""variable value for end_date id : 2013030711"   SNAP_ID       DBID SNAP_LEVEL---------- ---------- ----------     36263 3509254984          1

5. Summary
A. This article mainly describes how to pass shell parameters to SQL scripts.
B. method 1 is to directly follow the shell variable following the script, sqlplus USERID/pwd @ script_name $ para1 $ para2
C. method 2 is to pass parameters at the SQL prompt after sqlplus is started. SQL> @ script_name $ para1 $ para2
D. Method 3: Pass the shell variable value to the variable defined by define first, and then pass it to SQL script SQL> @ script_name var1 var2
E. Note: In method 3, the substitution variable of the SQL script is the same as the variable name defined by define.

 

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)