Shell Programming and Scripting

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

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

/home/dev/uat_report>./UAT_usr_login_report.sh
Connecting to ALA01r
Connection problem
/home/dev/uat_report>

oifs="$IFS"
while IFS="|" read county_code county_name usr_nm passwd domain xstr
do
        IFS="$oifs"
        echo "Connecting to ${domain}"

        $ORACLE_HOME/bin/sqlplus ${usr_nm}/${passwd}@${domain} @sqlquery.sql >> ${conn_dir}/${county_name}_conn.txt 2>error.txt
        stat=$?
        [ "$stat" = 0 ] && echo "OK" && break  # or continue ?

        echo "LOOP over for ${county_name}"
        cat error.txt
done < country_list

while IFS="|" read county_code county_name usr_nm passwd domain
do
 echo "Connecting to county $county_code"
$ORACLE_HOME/bin/sqlplus ${usr_nm}/${passwd}@${domain} @sqlquery.sql >> ${conn_dir}/${county_name}_conn.txt 2>error.txt

stat=$?
echo "PRINTING STATUS $stat"
[ "$stat" != 0 ] && continue

echo "SEARCHING FOR ERROR IN LOG FILE"
cnt=`grep "ERROR" ${conn_dir}/${county_name}_conn.txt|wc -l`
if [ $cnt -ne 0 ]
then
     echo "pattern match found - next continue command will be executed"
    continue
else
     echo "no match with the pattern"
fi

echo "OUT OF IF LOOP"
echo ""
count=`sed -n "4 p" /tmp/uat_temp.txt | sed 's/ //g'`

echo "${county_code},${county_name},${count}" >> $rep_dir/UAT_Report_${CUR_DATE}.CSV

rm -f /tmp/uat_temp.txt

done < county_list

echo "OUT OF WHILE LOOP"

/home/devdbg2/uat_report>./UAT_usr_login_report.sh
Connecting to county 1
PRINTING STATUS 0
SEARCHING FOR ERROR IN LOG FILE
no match with the pattern
OUT OF IF LOOP

Connecting to county 7
PRINTING STATUS 0
SEARCHING FOR ERROR IN LOG FILE
pattern match found - next continue command will be executed
OUT OF WHILE LOOP

/home/devdbg2/uat_report/DB_conn>cat ContraCosta_conn.txt

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 11 02:22:41 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name: Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

/home/devdbg2/uat_report>cat county_list
1|Alameda|abc|def|ALA01r
7|ContraCosta|abc|xyz|CC07r
10|Fresno|abc|def|FR10r

/home/devdbg2/uat_report>./UAT_usr_login_report.sh
Connecting to county 1
PRINTING STATUS 0
SEARCHING FOR ERROR IN LOG FILE
no match with the pattern
OUT OF IF LOOP

Connecting to county 7
PRINTING STATUS 1
OUT OF WHILE LOOP