Sql command inside shell script runs without giving anything back as outout

Shell Programming and Scripting
1 
#!/bin/sh
# This script returns the number of rows updated from a function

echo "The execution is starting ....."
sqlplus -silent $UP <<EOF
set serveroutput on
set echo off
set pagesize 0
VAR no_rows_updated NUMBER;
EXEC :no_rows_updated :=0;
DECLARE
CURSOR c_update is
SELECT * FROM BELK_BOOKS FOR UPDATE OF AUTHOR_NAME;
BEGIN
FOR REC IN c_update
LOOP
   UPDATE Belk_Books 
   SET AUTHOR_NAME='ASFAKUL'
   WHERE CURRENT OF c_update;
   :no_rows_updated:=c_update%ROWCOUNT;
   --no_rows_updated:=no_rows_updated+1;
END LOOP;
Commit;
--SELECT c_update%ROWCOUNT INTO no_rows_updated from Dual;
--no_rows_updated:=c_update%ROWCOUNT;

--DBMS_OUTPUT.PUT_LINE(no_rows_updated);
EXCEPTION 
WHEN OTHERS THEN
    :no_rows_updated:=SQLCODE;
                ROLLBACK;
END;
/
exit :no_rows_updated ;
EOF

error_exit=$?
if [ $error_exit -ne 0 ]; then
    echo "There was an error or 0 rows updated"
else
   echo "The number of rows updated is : $error_exit"
fi

here is the code I am trying to run , please help

2

What is the output? What errors are you getting?

3

"There was an error or 0 rows updated" I am getting this . But 3 rows were updated in the sql , so I should have gotten the second output with counts of rows updated (i.e. 3)

4

No you shouldn't.
Your shell script is working just like you have programmed it. Because 3 rows were updated, the test $error_exit not equal to 0 is TRUE:

if [ $error_exit -ne 0 ]; then # TRUE because $error_exit is 3, which is not equal to 0
    echo "There was an error or 0 rows updated"
 ...
    ...
...

and hence you are seeing the output you have programmed.

5

Oh Yes !!! Thanks . I see my erorr :frowning: