Linux/unix shell arguments to SQL scripts

In the process of database operation, the Shell script provides great convenience for operation and maintenance. A shell script parameter passed as a variable to SQL and a SQL script is one of the scenarios that DBAs often encounter. This article mainly discusses how to pass the parameters of the shell script to the SQL script and execute the SQL query.

1. Execute scripts and pass parameters when starting Sqlplus

robin@szdb:~/dba_scripts/custom/awr> more tmp.sh

#!/bin/bash

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

# Set Environment here

# Author:robinson Cheng

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

If [f ~/.bash_profile]; Then

. ~/.bash_profile

Fi

If [-Z "${1}"] | | [-Z "${2}"] | | [-Z "${3}"]; then

echo "Usage:"

echo "' basename $ ' <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_date

Else

Oracle_sid=${1}

BEGIN_DATE=${2}

END_DATE=${3}

Fi

Export Oracle_sid begin_date end_date

#Method 1:pass the parameter to script directly after script name

Sqlplus-s gx_adm/gx_adm @/users/robin/dba_scripts/custom/awr/tmp.sql $begin _date $end _date

Exit

robin@szdb:~/dba_scripts/custom/awr> more Tmp.sql

SELECT 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