Return value to shell script, depending on status of pl/sql udpate

Shell Programming and Scripting
1

Hi All,

I need to return value to the main shell script, depending on whether the UPDATE command in the embedded pl/sql is successfu or not.

 
 
#!bin/ksh
 
updateStatus=`sqlplus --conn details-- << EOF
 
DECLARE
var_rows NUMBER;
 
BEGIN
update table_name 
set column_name = 'abc'
where condition;
 
var_rows := SQL%ROWCOUNT;
 
if var_rows = 1
then
        commit;
        ------Here i want to return value as 1 or true to unix variable updateStatus---------;
else
        rollback;
        ------Here i want to return value as 2 or false to unix variable updateStatus---------;

end if;

 
END;
/
exit;

EOF`
 
echo "update status : " $updateStatus

The rest of the script works fine, except for the return part
Please help :confused:

Thanks,
Ritu

2

If you have nothing else in the output of `subshell` (that goes into the variable updateStatus)
then you can simply do

echo 1

and

echo 2

If you have output that goes to the variable updateStatus, and you want to additionally return a status value, you can try

exit 1

and

exit 2

that should terminate the `subshell` at this point, and is retrievable in the next command as $?

updateStatus=`...
...
...`
retval=$?
echo $retval
1 Like
3

Note that on UNIX and Linux systems, utilities conventionally return an exit code of 0 to indicate success; not 1.

4

Hi MadeInGermany/Don,

Tried the suggested workaround, but getting this error on trying both echo 1 or exit 1 :

 
 
 ORA-06550: line 21, column 6: PLS-00103: Encountered the symbol "1" when expecting one of the following: := . ( @ % ; The symbol "; was inserted before "1" to continue.
5

the values you are setting are being fed into your sql command ...

minus better alternatives, try ...

sending the the return value you want to an external file and have your script read the value in that file ...

6

Hi Just Ice,

Even redirecting it to a file doesnt seem to wrk:

  1. exit/echo fails with the same error
  2. trying to print the output into the files using select statement doesnt work either, as it requires INTO clause for PL/SQL block

:frowning:

7 
#! /bin/ksh

PATH=/usr/bin:/bin:/usr/sbin:/sbin

file=/dir/return.file

$pl_sql_code_block_sends_return_value_into_$file 

if [ -s $file ]
then
    updateStatus=$(< $file)
    echo "Update Status: $updateStatus"
else
    echo "$file empty. No update return value."
fi

exit 0
8

You must change your SQL code.
This is a Shell Programming forum. Perhaps you better ask an SQL forum.