Shell Programming and Scripting

#!/bin/ksh
#set -x
SrcDir=/home/AAAA/BBBB
LogDir=$SrcDir/logs
ErrorDir=$SrcDir/error
SCRIPT_NAME=`basename $0`
Script_Pattern=.sh
PREFIX="TEST_SCRIPT_"
StartTime=`date "+%Y%m%d%H%M%S"`
LogFileName=$PREFIX`basename $SCRIPT_NAME | sed "s/$Script_Pattern$//"`$StartTime.log
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/lib:/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/usr/contrib/bin/:.
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
ORA_NLS33=$ORACLE_HOME/nls/data
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/jdbc/lib
ORACLE_HOME=/ora10g/10.2.0.4
DBCONN=user/password@DBName
# Check for if the script already running
pid=$$
pr_cnt=`ps -ef|grep "$SCRIPT_NAME$" |grep -v grep | grep -v $pid |wc -l`
if [ $pr_cnt -gt 1 ]
then
echo "`date '+%Y-%m-%d %T'`  :: ERROR : Another instance of script for $SCRIPT_NAME is already running." >>  $LogDir/$LogFile
Name
exit 1
else
echo "`date '+%Y-%m-%d %T'`  :: INFO : Upload Process Started" >>  $LogDir/$LogFileName
fi
# ORCALE PATH & VARIABLE For SQL LOADER
export ORACLE_HOME
export ORACLE_SID
export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
export NLS_DATE_FORMAT="MM.DD.YYYY HH24:MI:SS"
export LIBPATH=$ORACLE_HOME/lib
export ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH ORA_NLS33 CLASSPATH
#echo "`date '+%Y-%m-%d %T'`  :: INFO : Create Table Started" 1 >>  $LogDir/$LogFileName
TABLE=`sqlplus -s $DBCONN <<eof
set pagesize 0 feedback off verify off heading off echo off
column num_rows new_value num_rows format 9999
@/home/AAAA/BBBB/TEST_SQL_SCRIPT.sql;
exit
eof`
echo $TABLE >>  $LogDir/$LogFileName
echo "`date '+%Y-%m-%d %T'`  :: INFO : Create Table Ended" >>  $LogDir/$LogFileName
cd $LogDir/
gzip -f $LogFileName
cd -

DROP TABLE TEST_TEST1_1_3_5_9;
CREATE TABLE TEST_TEST1_1_3_5_9
NOLOGGING
AS
SELECT /*+ PARALLEL(A,8)*/ C.AAA ,
           TO_DATE(SUBSTR(A.BBB, -7, 6), 'yymmdd') ,
           A.BBB DND_BBB,
           A.CCC CCC,
           A.CO_ID,
           UPPER(SUBSTR(BBB, -1)) 
      FROM table1@DB_link A,
           table2@DB_link B,
           table3@DB_link   C
     WHERE A.CO_ID = B.CO_ID
       AND B.DN_ID = C.DN_ID
       AND A.CCC IN ('XXXX','YYYY','ZZZZ','TTTT  ','RRRR  ','RRRR  ','kdlsjfkl  ','asasrre  ','uiowefsj')
       AND B.CCC = '1' AND ( C.GGGG  NOT IN ('O') OR C.GGGG IS NULL)
       AND SUBSTR(A.BBB, -1) IN ('a', 'd')
       AND A.WWW = (SELECT MAX(WWW)
                           FROM table1@DB_link
                          WHERE RRRR = A.RRRR)
        AND TO_CHAR (TO_DATE (SUBSTR (a.BBB, -7, 6), 'yymmdd'),
                   'yyyymmdd'
                  ) =TO_CHAR(SYSDATE-1,'yyyymmdd');

2011-05-16 21:08:34  :: INFO : Upload Process Started
DROP TABLE TEST_TEST1_1_3_5_9 Archive DIR1 DIR10 DIR2 DIR3 DIR4 DIR5 DIR6 DIR7 DIR8 DIR9  
a123.txt abc_def_ytr.txt error ftp_201104141824.log.gz  lib logs mainscript.sh new_file new_file.pub newdir 
newfile nohup.out script123.sh script123_orig.sh script_for_sftp.sh script_sftp_1.sh sftpscript_imp.sh subscript.sh 
test1.sh test123 test123.sh test_shell_script.sh ERROR at line 1: 
ORA-00942: table or view does not exist ) =TO_CHAR(SYSDATE-1,'yyyymmdd') 
Archive DIR1 DIR10 DIR2 DIR3 DIR4 DIR5 DIR6 DIR7 DIR8 DIR9 
test_shell_script.sh ERROR at line 23: ORA-12801: error signaled in parallel query 
server P012 ORA-01847: day of month must be between 1 and last day of month ORA-02063: preceding 2 lines from 
table1@DB_link
2011-05-16 21:08:35  :: INFO : Create Table Ended

echo "$TABLE" >>  $LogDir/$LogFileName