Shell Programming and Scripting

Environment: Sun UNIX

Language: Bourne Shell

I have the following script and it works fine. Unfortunately, from user's perspective, it looks very messy because the user is able to see the output of the process caused by the print command.

Is there a better way to overcome it?

Here's the code:

SPOOL_FILE = /tmp/spool.txt
A=1000
B=2000
DB_USER=user123
DB_USER_PASSWD=123
DB_INSTANCE=DB1

$ORACLE_HOME/bin/sqlplus /NOLOG @<<!
set echo off

Connect $DB_USER/$DB_USER_PASSWD@$DB_INSTANCE

var RETURN_CODE VARCHAR2(4);
var MAX_OUT VARCHAR2(3);
var PATH_OUT VARCHAR2(10);

exec TEST_API.GET_RECORD('$A', '$B', :RETURN_CODE, :MAX_OUT, :PATH_OUT);

spool $SPOOL_FILE
print RET_CODE;
print MAX_OUT;
print PATH_OUT;
spool off
exit
!

# Reading the spool file to get stored procedure's returned values
exec 3<&0
exec 0<$SPOOL_FILE
while read line
do
echo $line
if [ "$line" = 'RET_CODE' ]; then
read line
read line
RET_CODE=$line
fi
if [ "$line" = 'MAX_OUT' ]; then
read line
read line
CUR_MAX=$line
fi
if [ "$line" = 'PATH_OUT' ]; then
read line
read line
CUR_PATH=$line
fi
done
exec 0<&3

echo "Current Max Value is " $CUR_MAX
echo "Current Path is " $CUR_PATH

Normally, you can re-direct various output to a file. For example:

lp myfile.txt >>work.log 2>>work.err

this would print myfile.txt
and send any confirmations to file work.log
and send errors to file work.err

To suppress this completely, often output is redirected as:

lp myfile.txt >/dev/null

Add the redirections to your sqlplus command :

Code:

$ORACLE_HOME/bin/sqlplus /NOLOG >/dev/null 2>&1 @<<!

In your script, you can read the spool file without playnig with exec :

Code:

# Reading the spool file to get stored procedure's returned values
while read line
do
   echo $line
   . . .
done < $SPOOL_FILE

Jean-Pierre.

Thanks for helping. I will try out all your suggestion when I am in the office. I really appreciate your help because my deadline is almost overdue.

In my environment, without the exec, the value stored in the variable assigned inside the while loop cannot be referred. In the code as follows, you'll get "123" instead of the value obtained from the SPOOL_FILE at the last echo statement. I have no idea why. I was struggling with this problem before discovering exec method.


CUR_MAX=123
# Reading the spool file to get stored procedure's returned values
exec 3<&0
exec 0<$SPOOL_FILE
while read line
do
echo $line
if [ "$line" = 'MAX_OUT' ]; then
read line
read line
CUR_MAX=$line
fi
done
exec 0<&3

echo "Current Max Value is " $CUR_MAX


Meanwhile, the suggestion to add "</dev/null 2>&1" in the following does not prevent print section from being displayed

$ORACLE_HOME/bin/sqlplus /NOLOG </dev/null 2>&1 @<<!

I make a typo mistake, the redirection for stdout is > (and not <) :

Code:

$ORACLE_HOME/bin/sqlplus /NOLOG >/dev/null 2>&1 @<<!

Jean-Pierre.

Thanks, Jean-Pierre. It definitely improves but I still see the following output being displayed. I wonder whether it can be eliminated completely.

> ./cc.sh
SQL> print RETURN_CODE;

RETURN_CODE
--------------------------------------------------------------------------------
000000

SQL> print MAX_OUT;

MAX_OUT

SQL> print PATH_OUT;

PATH_OUT

SQL> spool off
Current Max Value is 556
Current Path is main/43