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_profile
Fi

If [-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_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;

1 2 3 4 Next Page