Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

korn shell with sqlplus and sqlldr 2

Status
Not open for further replies.

johnpayback

IS-IT--Management
Oct 2, 2006
110
US
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.

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
 


Try something like this:
Code:
#!/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
[3eyes]


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


OK fixed some typo's and added the "mail" thing:
Code:
#!/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
[noevil]




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
[tt](( rc += [red]#[/red]? ))[/tt] ???

You must mean this instead?

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


HTH,

p5wizard
 
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
 
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.

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

BS/Computer Science
 
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.
 
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
 
That was it p5wizard. Much appreciated.

JP

BS/Computer Science
 
One more question.

This line has "$0 but no ending " so does it need it?:
Code:
mailx -s "$0 "$result records loaded to TBL1,TBL2" $eMail
Also, how could I add an attachment to this?

JP

BS/Computer Science
 

Ooops, so many typos...
Here, try this:
Code:
uuencode <MyAttachment.txt|\
mailx -s "$0 $result records loaded to TBL1,TBL2" $eMail
[thumbsup2]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top