korn shell with sqlplus and sqlldr 2

[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
ORAENV_ASK=YES

sqlplus -s user/passwd <<EOSQL
exec sp_CBOB_CLEAR_LN_STAGING;
EOSQL
rc=#?

sqlldr user/pass  control=AIMS_LOAD_LOAN.CTL
(( rc += #? ))

sqlplus -s user/passwd <<EOSQL
exec sp_CBOB_LOAD_LN;
EOSQL
(( rc += #? ))

if [ rc -eq 0 ]
then
  sqlplus -s user/passwd <<EOSQL
  SELECT COUNT(*) FROM TBL1;
  SELECT COUNT(*) FROM TBL2;
EOSQL  
  #---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

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

 

[LKBrwnDBA]

OK fixed some typo's and added the "mail" thing:

#!/usr/bin/ksh
export ORACLE_SID=TESTSVR100
ORAENV_ASK=NO
. /usr/local/bin/oraenv
ORAENV_ASK=YES
eMail=myemail@thecorp.com

sqlplus -s user/passwd <<EOSQL
exec sp_CBOB_CLEAR_LN_STAGING;
quit
EOSQL
rc=#?

sqlldr user/pass  control=AIMS_LOAD_LOAN.CTL
(( rc += #? ))

sqlplus -s user/passwd@TESTSVR100<<EOSQL
exec sp_CBOB_LOAD_LN;
quit
EOSQL
(( rc += #? ))

[ ! rc -eq 0 ] && mailx -s "$0 Results Failed" $eMail 

result=`sqlplus -s user/passwd <<EOSQL
set term off echo off ver off feed off
SELECT COUNT(*) FROM TBL1;
SELECT COUNT(*) FROM TBL2;
quit
EOSQL`  
#---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
result=`echo $result|tr -d' ' ','`
mailx -s "$0 "$result records loaded to TBL1,TBL2" $eMail

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

 

[p5wizard]

[tt](( rc += [red]#[/red]? ))[/tt] ???

You must mean this instead?

[tt](( rc += [green]$[/green]? ))[/tt]


HTH,

p5wizard

 

[johnpayback]

Thank you...this is great. One question. Will the sqlldr command finish before the last sqlplus command runs or do I need to put some kind of wait in there? Right now it seems to be trying to execute the last sqlplus command before the sqlldr command completes.

Thanks again,
JP

BS/Computer Science

 

[johnpayback]

LKBrwnDBA, I seem to be getting an error on line 33. It says this....

syntax error at line 33: ``' unmatched

This is line 33 in the code.

result=`echo $result|tr -d' ' ','`

BS/Computer Science

 

[KenCunningham]

Are you sure you've got both back ticks in there?

The internet - allowing those who don't know what they're talking about to have their say.

 

[p5wizard]

probably this line:

[tt]EOSQL`[/tt]

needs to be on two lines

[tt]EOSQL
`[/tt]

inline input (HERE document) needs its END word on a separate line

HTH,

p5wizard

 

[johnpayback]

That was it p5wizard. Much appreciated.

JP

BS/Computer Science

 

[johnpayback]

One more question.

This line has "$0 but no ending " so does it need it?:

mailx -s "$0 "$result records loaded to TBL1,TBL2" $eMail

Also, how could I add an attachment to this?

JP

BS/Computer Science

 

[LKBrwnDBA]

Ooops, so many typos...
Here, try this:

uuencode <MyAttachment.txt|\
mailx -s "$0 $result records loaded to TBL1,TBL2" $eMail

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