Hello,
First sorry for my englis I'm french.

I try to use 'whenever sqlerror exit sql.sqlcode' to pass the SQL error code to the bash but it doesn't work:
This is my script It is in a file file_name.ksh :


sqlplus -s /nolog << END | tee -a $LogFile
connect $CidUser/$CidPwd
whenever sqlerror exit sql.sqlcode
alter session enable parallel dml;
select /*+ parallel (HIST_REQUEST 4) */ count(0) "HIST_REQUEST" from HIST_REQUEST ;
select /*+ parallel (HIST_REQUEST_PARAM 4) */ count(0) "HIST_REQUEST_PARAM" from HIST_REQUEST_PARAM ;

set timing on;
DECLARE
CURSOR req_cursor(nbdays NUMBER) IS
SELECT /*+ parallel (REQUEST 4) */
DISTINCT REQUEST_ID
FROM REQUEST
WHERE PARENT_REQUEST_ID IS NULL
AND REQUEST_STATUS_CODE in ( SELECT /*+ PARALLEL (REQUEST_STATUS 2 ) */ REQUEST_STATUS_CODE FROM REQUEST_STATUS WHERE REQUEST_PROCESSED = 1)
AND TRUNC ( SYSDATE ) - TRUNC (REQUEST_STATUS_CHANGE_DATE ) >= nbdays ;
nbloop integer := 0;
nbloop2 integer := 0;
maxloop integer := 4000;
BEGIN
FOR req_record IN req_cursor(40) LOOP
/*
** Delete the child REQUEST and the associated REQUEST_PARAM
** This process need to be done recursively.
*/
PURGE_REQUEST_PRCA (req_record.REQUEST_ID);
if (nbloop = 100) then
COMMIT;
nbloop2 := nbloop2 + nbloop;
nbloop := 0;
end if;
nbloop := nbloop +1;
-- EXIT WHEN nbloop2 >= maxloop;

END LOOP;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/
select /*+ parallel (HIST_REQUEST 4) */ count(0) "HIST_REQUEST" from HIST_REQUEST ;
select /*+ parallel (HIST_REQUEST_PARAM 4) */ count(0)"HIST_REQUEST_PARAM" from HIST_REQUEST_PARAM ;
END
echo $?



The echo $? give 0 and Idon't know how??? I gonna be crazy!!
Thanks a lot for your help!!

if the SQL is run in a child process then it will be this child process that is exited and not the parent script you are running. (i think)

Thanks a lot for your help.
So i don't know if it's a child process and i don't think so.
The 'echo $?' instruction I have written after the SQL script is done to see what is the error code. But it's always 0 and i don't understand. I have written a call to a SQL stocked procedure which doesn't exist to exit with an error but it 's always 0.
That's the real problem.

'echo $?' will return the error status of the last command executed.

Have you tried just one SQL statement like (below) and tried to test $?

Reraise exception in your exception handler routine.

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;