Wednesday, March 12, 2014

How to check that an Oracle database parameter is set in a unix shell script

Probably many ways to do the same thing, but I tend to spool database information to disk, then assign the values found to shell script variables, as in the following example, where I need to determined whether the parameters db_file_name_convert and log_file_name_convert is set in the database:
###############################################################
# Check that the db_convert_file_name and log_file_name_convert
# are both set. If not, abort script.
###############################################################
TRG_DB=${ORACLE_SID}
cat << EoF > check_${TRG_DB}_convert_params.sql
set termout off
set lines 200
set trimspool on
set heading off
set pages 0
set feedback off
set echo off
set verify off
col name format a30
col value format a40
spool check_${TRG_DB}_convert_params.lst
select name,value
from v\$parameter
where name in ('db_file_name_convert','log_file_name_convert');
exit
EoF
sqlplus -s / as sysdba @check_${TRG_DB}_convert_params.sql
CONV_DATA_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep db_file | awk '{print $2}'`
CONV_LOG_FILE_SET=`cat check_${TRG_DB}_convert_params.lst | grep log_file | awk '{print $2}'`

case ${CONV_DATA_FILE_SET} in
'') echo db_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo db_file_name_convert is set. Continuing...;;
esac

case ${CONV_LOG_FILE_SET} in
'') echo log_file_name_convert is not set. Correct and retry operation.;exit;;
*) echo log_file_name_convert is set. Continuing...;;
esac
exit

No comments:

Post a Comment