Im trying to run a query in multiple db thro sqlplus in a loop written n shell script. whenevr there is a problem in connecting to db, the shell script abruptly exits. My requirement is , when db connection fails, the script should print the msg and contnue to connect to the next db mentioned in the file county_list Below is my code. Pls assist in getting it fixed.
cat county_list | while read details
do
county_code=`echo $details | cut -d '|' -f1`
county_name=`echo $details | cut -d '|' -f2`
usr_nm=`echo $details | cut -d '|' -f3`
passwd=`echo $details | cut -d '|' -f4`
domain=`echo $details | cut -d '|' -f5`
echo "Connecting to ${domain}"
$ORACLE_HOME/bin/sqlplus ${usr_nm}/${passwd}@${domain} @sqlquery.sql >> ${conn_dir}/${county_name}_conn.txt
if [ "$?" = "1" ]
then
echo "Connection problem"
continue
fi
echo "LOOP over for ${county_name}"
done
sqlquery.sql :
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET MARKUP HTML OFF SPOOL OFF
SET PAGESIZE 50000
SET LINESIZE 5000
SET HEADING ON
SET TRIMSPOOL ON
SET TRIMOUT ON
COLUMN clogname NEW_VALUE logname NOPRINT
SET COLSEP ,
SET SERVEROUTPUT ON
SPOOL /tmp/uat_temp.txt
DECLARE
v1_sql_err_code CHAR (10) := NULL;
v1_success BOOLEAN := TRUE;
v1_count NUMBER(7,2);
BEGIN
DBMS_OUTPUT.enable (NULL);
v1_success := TRUE;
SELECT COUNT(*) INTO v1_count
FROM se_usr a
DBMS_OUTPUT.put_line (v1_count);
EXCEPTION
WHEN OTHERS
THEN
v1_success := FALSE;
v1_sql_err_code := SUBSTR (SQLERRM, 1, 10);
DBMS_OUTPUT.put_line (
'EXCEPTION CAUGHT. SQL Error Code: ' || v1_sql_err_code);
END;
/
SPOOL OFF
OUTPUT ( for incorrect login detail in the first db connection. )
/home/dev/uat_report>./UAT_usr_login_report.sh
Connecting to ALA01r
Connection problem
/home/dev/uat_report>