sqlplus and sqlldr in same ksh script

[johnpayback]

I am needing to call sqlplus and sqlldr in the same korn shell script. Below are the steps that I need to accomplish. I am new to Oracle shell scripting so an example would be great.

•CBOB_LOAD.KSH - script name
•Calls Oracle stored procedure sp_CBOB_CLEAR_LN_STAGING (SERVER: TESTSVR100)
•Calls SQL*LOADER script AIMS_LOAD_LOAN.CTL (SERVER: TESTSVR100)
•Calls Oracle stored procedure sp_CBOB_LOAD_LN (SERVER: TESTSVR100)
•Run a query to determine row counts of TBL1 & TBL2 tables – Send results via email to the me@work.com in Outlook
------------
I have this pseudo code that I just threw out there of how I would like it to go. I know the code is wrong but hopefully it is understandable of what I'm trying to accomplish.

!# /usr/bin/ksh

sqlplus -s user/passwd@TESTSVR100<<END
exec sp_CBOB_CLEAR_LN_STAGING;

sqlldr user/pass@TESTSVR100<<END
exec AIMS_LOAD_LOAN.CTL;

sqlplus -s user/passwd@TESTSVR100<<END
exec sp_CBOB_LOAD_LN;
EOF

if [ $? -eq 0 ]
then
 sqlplus -s user/passwd@TESTSVR100<<END
   exec SELECT COUNT(*) FROM TBL1
   exec SELECT COUNT(*) FROM TBL2
   ---Send results of the TBL1 and TBL2 counts via email
   ---in this format. XXX,XXX records loaded to TBL1
   ---XXX,XXX records loaded to TBL2
else
 ---Send email with "Results Failed"
fi

BS/Computer Science

 

[LKBrwnDBA]

Try something like this:

#!/usr/bin/ksh
#
export ORACLE_SID=TESTSVR100
ORAENV_ASK=NO
. /usr/local/bin/oraenv

sqlplus -s user/passwd@TESTSVR100<<ENDSQL
exec sp_CBOB_CLEAR_LN_STAGING;
exit
ENDSQL
rc=$?

[ rc -eq 0 ] && sqlldr user/pass@TESTSVR100 control=AIMS_LOAD_LOAN.CTL;
(( rc += $? ))

[ rc -eq 0 ] && TBL_CNTS=`
sqlplus -s user/passwd@TESTSVR100<<ENDSQL
exec sp_CBOB_LOAD_LN;
SELECT COUNT(*) FROM TBL1;
SELECT COUNT(*) FROM TBL2;
exit
EOFSQL`
(( rc += $? ))

if [ rc -ne 0 ]
then
  echo "--- Results Failed, table counts= $TBL_CNTS"
fi

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb