johnpayback
IS-IT--Management
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.
BS/Computer Science
•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.
Code:
!# /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