PDA

View Full Version : Give error code from sqlplus to shell script

Fajr
12-05-02, 20:46
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!!
WingMan
12-06-02, 08:22
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)


After calling the child process try something like
if [ $? = "0" ]; then
echo "child process Ran ok"
else
echo "child process failed"
exit 3
fi
Fajr
12-06-02, 13:04
Originally posted by WingMan
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)


After calling the child process try something like
if [ $? = "0" ]; then
echo "child process Ran ok"
else
echo "child process failed"
exit 3
fi




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.
WingMan
12-10-02, 08:38
'echo $?' will return the error status of the last command executed.

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

select /*+ parallel (HIST_REQUEST_PARAM 4) */
count(0)"HIST_REQUEST_PARAM"
from HIST_REQUEST_PARAM ;
ndb
03-26-08, 14:09
Reraise exception in your exception handler routine.

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;