#!/bin/ksh
#*****************ORACLE CALL FUNCTION****************
function run_oracle {
sqlplus -s <<%%
${user}/${passwd}
set serveroutput off
set heading off
set feedback off
set verify off
set define off
set linesize 2000
${cmd};
exit
%%
}
#***********ORACLE CALL FUNCTION ENDS*************
 
#**********PROCESS TO RUN MULTIPLE QUERIES IN PARALLEL USING "&" IN ORACLE FUNCTION*****************
set -A result ### Declaring the Array
set -A Query ### Declaring the Array
i=0
echo "Dear all, Checks are :- ">${mailfile}
echo "" >> ${mailfile}
start_date=`date +'%H:%M:%S'`
while IFS=\| read chk_nbr chk_name chk_query ## Reading queries and check from parameter file ${chk_file} #######
do
echo ${chk_nbr}
cmd="${chk_query}"
Query[${i}]=${chk_name}
echo ${chk_query}
if [ ${chk_nbr} == "Y" ]; then
result[${i}]=`run_oracle | grep -v '^$' | tail -1` &
process[${i}]=`echo $! | head -1`
echo ${process[${i}]}
let i=i+1
fi
done < ${chk_file}
#***********************PROCESS TO RUN MULTIPLE QUERIES IN PARALLEL USING "&" IN ORACLE FUNCTION ENDS*****************
 
echo `echo ${result[0]}|sed 's/[ ]*//g'` 
 
cnt=`expr $i - 1`
n=0
while [ $n -le $cnt ]
do
p=${process[$n]} #### FETCHES ALL THE PROCESS IDS WHICH ARE TRIGGERED IN ORACLE BY ABOVE PROCESS #########
 
c=`ps -A | grep ${p} | tr ' ' '|' | awk -F'|' -v var=${p} '$1==var'|wc -l` #### CHECKS PROCESS IDS ARE LIVE WHICH ARE TRIGGERED ######
if [ $c -eq 0 ]; then 
echo " ${result[${n}]} : ${Query[$n]}"
let n=n+1
fi
done
n=`expr $n - 1`
if [ $n -eq $cnt ]; then
echo "completed successfully!!"
return 0
else
echo "failed.Please run again!"
return 1
fi

cat ${chk_file}
 

Y|Duplicate in Table1 |select count(count(*)) from TABLE1  group by COL1,COL2 having count(*)>1

Y|Duplicate in Table2 |select count(count(*)) from TABLE2 group by  COL1,COL2 having count(*)>1