Checking Command Completion Status

[Milleniumlegend]

Hi

I am running a SQL Script from Korn Shell Script. I am reading the parameters from a file and feeding each line as an input to this Korn shell script.

Parameter file
xxxx 111 111 111
yyyy 222 222 222.. etc

The Korn Shell script gets each line and feeds them to another korn shell command which updates the oracle database. I would like to know if there is a way to find out if the command is completed successfully before running the next command.

Say I have command updatesql.ksh xxxx 111 111 111

Is there a way I can find out this command executed successfully before running the next command for example
updatesql.ksh yyyy 222 222 222.

Thanks in advance.

 

[PHV]

You may test the $? value after each call.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Milleniumlegend]

The command takes some time to be processed and would $? help if this is in a while loop.

say if I have
while cat $file | read $line
updatesql.ksh $line

until $? -eq 0 then continue

What I am trying to do is reading each line and then passing the parameters to the updatesql.ksh and this scripts calls the oracle sql*plus. If we send another command through updatesql.ksh script The sql*plus will process the other command even if the first command is not completed.
So I wanted to check the if each command is executed successfully and then call the second command.

Do you think $? will do the job?

 

[Mike042]

The success or failure status $? (0 = success, Non 0 = failure) changes as the result of virtually every command, so if you want to construct a while read line do loop then insert the test if [ $? -ne 0 ]; then break; fi immediately after the call to updatesql.ksh to force an exit of the do loop in the event of a updatesql failure.
Please also note that the Korn shell script updatesql.ksh must not end with exit 0 otherwise it will always appear to be successful ! The exit code should be the status of the actual SQL command itside it.

I hope that helps you.

Mike

 

[Milleniumlegend]

Thanks for your help mike and PHV.
The $? helps to get the last command status.
It does not persistently check for a certain command completion periodically.
Say all I need to check if the updatesql.ksh script has completed either by looking at the process and verifying the process is completed or command completion status.

As I mentioned by mike if I use the break command it will break out of the loop and process the next line and what it will do is execute the rest of the script.

I am looking something like

updatesql.ksh xxxx 111 111 111 --> command completed Successfully for xxxx 111 111 111.
updatesql.ksh yyyy 222 222 222 --> command competed successfully yyyy 222 222 222.

Till the all the lines are processed from the parameter file.

Thanks for your help in advance.

 

[PHV]

Please post the updatesql.ksh script too, to see why the sql*plus will process the other command even if the first command is not completed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Milleniumlegend]

#!/usr/bin/ksh
# The sql script with configurations parameters.
sqlfile=ca-replication.sql

echo "Processing $arg1 $arg2 $arg3 $arg4"

cat $sqlfile | sed s/"define CA_ID=.*$"/"define CA_ID=$arg1"/ | sed s/"define ISS_ID=.*$"/"define ISS_ID=$arg2"/ | sed s/"define RUN_NBR_FROM.*$"/"define RUN_NBR_FROM=$arg3"/ | sed s/"define RUN_NBR_TO.*$"/"define RUN_NBR_TO=$arg4"/ > $sqlfile.t
mp
mv $sqlfile $sqlfile.old
mv $sqlfile.tmp $sqlfile

nohup run-ca-replication &

============================================================
The sql script looks like this
-- Must be logged onto B2
set echo on
set arraysize 5000
define CA_ID=xxxx
define ISS_ID=111
define RUN_NBR_FROM=111
define RUN_NBR_TO=111
--spool ???
insert into T012_CORP_ACTION_CALCULATION
select * from T012_CORP_ACTION_CALCULATION@b1 B1
where T012_020_CORP_ACTION_ID = '&CA_ID'
and NOT EXISTS (Select 1
from T012_CORP_ACTION_CALCULATION B2
Where B2.T012_020_CORP_ACTION_ID = B1.T012_020_CORP_ACTION_ID
And B2.T012_103_SECURITY_ID = B1.T012_103_SECURITY_ID
And B2.T012_TIMESTAMP = B1.T012_TIMESTAMP);
============================================================

The script that calls the sqlplus which is on oracle is

sqlplus B2/username @ ca-replication.sql

============================================================

this sqlplus will execute the script ca-replication.sql with the new changes again if we try to pass the parameters even if the update command is not completed.

 

[PHV]

nohup run-ca-replication &
this command is run in background.
If you want sequential processing of your sql scripts, you may try to replace it with this:
run-ca-replication

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244