Shell Script - SQL

Shell Programming and Scripting
1

Guys,Please look at a simple shell which validates presence of a customer table in OASIS schema. SQL Query for validation is wrong(Table name in quotes should not have schema name OASIS.). And shell is working correctly (with exit 1) as it will never get count=1 with this query.

But it is failing today in our application.and it was not failing in last 6 months with the same code.(This shell is running as host - concurrent program in oracle applications ERP) . Any suggetions/thoughts.

Thanks and Regards,
Bhushan

#!/bin/sh
# Parameter Name Description
# ------------ --------------------
# $1 = Ora apps/user ID
# $2 = User id
# $3 = User Name
# $4 = Request Id

ORA_USERPASS=${1}
#----------------------------------------------#
Validate Database table exists or not
#----------------------------------------------#

TP_PASS=`sqlplus -s /nolog <<end_stmt1
set heading off
set feedback off
set verify off
set termout off
set pages 0
connect $ORA_USERPASS
select count(*)
from all_objects
where object_name = 'OASIS.CUSTOMER_STAGE_TABLE'
and owner='OASISAR'
and status='VALID';
end_stmt1`

TP_PASS=`echo $TP_PASS|cut -d" " -f2`

if ! [ $TP_PASS = 1 ]
then
echo " Staging table OASIS.CUSTOMER_STAGE_TABLE is not present in the schema OASIS" >> $OUTPUT
ERROR_FLAG="Y"
echo " " >> $OUTPUT
exit 1
fi

2

Are you sure that the query returns 1 ?
Have you tried the execute the query at the shel prompt (with option -x) ?
'But it is failing today ' meens that the query produces Oracle errors or that the exit status is not set to the right value ?

#!/bin/sh
# Parameter Name                       Description
# ------------                         --------------------
#	$1			= Ora apps/user ID
#       $2				= User id 
#       $3 			= User Name   
#	$4 			= Request Id

ORA_USERPASS=${1}
#----------------------------------------------#
Validate Database table exists or not
#----------------------------------------------#

TP_PASS=`sqlplus -s /nolog <<end_stmt1
                set heading off
                set feedback off
                set verify off
                set termout off
                set pages 0
                whenever sqlerror exit failure
                whenever oserror  exit failure
		connect $ORA_USERPASS
                select count(*) 
		from all_objects 
                where object_name = 'OASIS.CUSTOMER_STAGE_TABLE'
		and   owner='OASISAR'
		and status='VALID';
end_stmt1`

if [ $? -ne 0 ]
   then
   echo "Error accessing database!" >> $OUTPUT
   exit 2
fi

TP_PASS=`echo $TP_PASS|cut -d" " -f2`

if ! [ $TP_PASS = 1 ]
   then
   echo " Staging table OASIS.CUSTOMER_STAGE_TABLE is not present in the schema OASIS" >> $OUTPUT
   ERROR_FLAG="Y" 
   echo " " >> $OUTPUT
   exit 1	
fi
3

Sorry for the confusion:

What i mean to say, the SQL Query is definitely wrong. It should not have "OASIA." in table name.But with this wrong query shell was working from last 6 months and suddenly it failed in validation today.I.e. it is now exiting with status 1 and giving message that "Staging table OASIS.CUSTOMER_STAGE_TABLE is not present in the schema OASIS".

I am trying to find out why it not errored with exit 1 status before. we were running this shell in many instances 100 and more times.....

Thanks