Checking plsql flag status in UNIX

Shell Programming and Scripting
1

Hi,I have a stored procedure to verify no of months since current fiscal.I want a shell script to exit with returtn code when the verification fails,I have shell script below but this doesnt work

sqlplus / << EOF
WHENEVER SQLERROR exit 1
WHENEVER OSERROR  exit 2

DECLARE
v_time_period number;
v_months number;
v_error_flag number;
BEGIN

SELECT COUNT (DISTINCT (TIME_PERD)) into  v_time_period 
FROM table
WHERE TIME_PERD > (select to_date('30-06' ||to_char(sysdate, 'YYYY') ,'dd-mm-yyyy') from dual);

        
SELECT TRUNC (
MONTHS_BETWEEN (SYSDATE, TO_DATE ('0106'||(to_char(sysdate, 'YYYY')-1) ,'dd-mm-yyyy')))
Months into v_months  FROM DUAL;


IF v_time_period  != v_months
  THEN
  v_error_flag := 1;
Sp_Dr_Send_Email_test ('No of months since current fiscal','Verification failed',
'myname@email.com');
 END IF;
END;
/

exit;
EOF`

if [ "$error_flag" = "1" ] ; then

echo $?
exit 1

fi
2

You can't set an environment variable in that fashion; that, technically is not a shell script. It is sqlplus script. You can assign the results of the sqlplus operation to an environment variable and then test. What ever you echo to standard output in the sqlplus script will be assigned to the variable

foo=`sqlplus......`

See unix - How to store result from SQLPlus to a shell variable - Stack Overflow

3

All you have to do is the the $? variable on exit of sqlplus. zero == okay, anything else is an error - in your case 1 and 2.

if [ $? -ne 0 ] ; then
  # do something here to notify error status exists.
fi