Bourne Shell: Clean Display of stored procedure's output

Shell Programming and Scripting
1

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&lt;&0
       exec 0&lt;$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&lt;&3

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

2

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

3

Add the redirections to your sqlplus command :

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

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

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

Jean-Pierre.

4

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.

5

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 :frowning:

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

6

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

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

Jean-Pierre.

7

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

8

I am actually working on 2 scripts. The process is similar and facing the same problem.

It's funny that one of the script's update section calling the same stored procedure returning the same output parameters did not display any output on the screen. However, the other section including another script always display the "print" output on the screen. Failing to identify the difference, I copied and pasted the working section to the other section and another script. Unfortunately the problem remain. This is really weird. I had to ask a few colleagues to spot the difference but none of them were successful.

I wonder whether there is any special setting/configuration that I may have set without realising that may cause the difference. Any idea?

9

Try to modify your SQL script :

$ORACLE_HOME/bin/sqlplus /NOLOG >/dev/null 2>&1c @<<!
SET ECHO     OFF
SET FEEDBACK OFF
SET HEADING  OFF
SET PAGESIZE 0
SET VERIFY   OFF
Connect $DB_USER/$DB_USER_PASSWD@$DB_INSTANCE

Jean-Pierre.

10

Thanks, again, Jean-Pierre.

I tried those setting, they're not effective either :frowning:

11

I have just found the main cause of the problem. It's due to a simple mistake. The "echo $line" is the one that displays the data unnecessarily. It took me days trying to figure out. Should have spotted this echo earlier.

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