Testing the status of an ORACLE call in a UNIX script

[weberm]

I have some inherited UNIX shell scripts that invoke sqlplus via a script called "qs" and then attempt to test their success or failure and behave accordingly but I'm having trouble finding researching "$?" online. Here is such an example:

echo '--------------------------------------------------------------' 
echo ' STEP01 -- PRODUCE THE UNRECONCILED ECOMMERCE REPORT          '
echo '--------------------------------------------------------------'
echo 
$HOME/bin/qs "exec RCPI.ecommerce_reconciliation_rpt"

#Check if last command is successful.
if [ $? -ne 0 ]
then
echo "An error occurred while running the Unreconciled report. Exiting..."
exit 11
fi

Is the script testing a return code of some sort? If so, won't the only time it returns a bad value be if the invoked stored proc returns a non-zero value?

 

[razalas]

$? is the shell variable that stores the last return code

A few others are:

echo "Command name:          " $0
echo "Process id:            " $$
echo "Background Process id: " $!
echo "Number of args:        " $#
echo "Return code:           " $?
echo "Args:                  " $*

man sh

Code what you mean,
and mean what you code!
But by all means post your code!

Razalas

 

[feherke]

Hi

Razalas, based on the OP's code, he already knows how to use the [tt]$?[/tt] variable.

weberm, note that [tt]qs[/tt] is a client application, it not executes the received code. So it is not so sure it has to report the errors found by the server. ( I never used [tt]qs[/tt], this is based on other similar tools' behaviors. )

Personally I would try to detect the errors based on some specific words in the output :

[navy]$HOME[/navy]/bin/qs [green][i]"exec RCPI.ecommerce_reconciliation_rpt"[/i][/green] [highlight][purple]2[/purple][teal]>&[/teal][purple]1[/purple] [teal]|[/teal] grep -qi [green][i]'error'[/i][/green][/highlight]

[gray]#Check if last command is successful.[/gray]
[b]if[/b] [teal][[/teal] [navy]$?[/navy] -ne [purple]0[/purple] [teal]][/teal]
[b]then[/b]
  echo [green][i]"An error occurred while running the Unreconciled report. Exiting..."[/i][/green]
  [COLOR=chocolate]exit[/color] [purple]11[/purple]
fi

Tested with GNU [tt]grep[/tt]. Other [tt]grep[/tt] implementations may not support -q ( --quiet ) and -i ( --ignore-case ) switches.

Feherke.

http://free.rootshell.be/~feherke/

 

[weberm]

$? is the shell variable that stores the last return code

Thanks! I suspected question mark was a return code shell var but searching for "?" wasn't getting me anywhere.

weberm, note that qs is a client application, it not executes the received code. So it is not so sure it has to report the errors found by the server. ( I never used qs, this is based on other similar tools' behaviors. )
Personally I would try to detect the errors based on some specific words in the output

Feherke, qs is another script which invokes plsql:

echo "$*" |sqlplus -s scott/tiger

There are statements in the shell script which grep for "ORA-" and other key words, so I'm wondering why the original coder used $? in the first place since it appears to doing nothing.

 

[SamBones]

Well, your "qs" script may not be returning the return code from [tt]sqlplus[/tt]. You might try changing it to this...

echo "$*" |sqlplus -s scott/tiger
return $?

That way the script's return code is sqlplus' return code.