SQLEXCEPTION behaves differently in different databases

Teradata Applications
mzs
Enthusiast

SQLEXCEPTION behaves differently in different databases

Hello,
I wrote a pretty simple code to test exception handling, and it works as expected, but when I tried to run the same code in a different (same-level) database, it does not work.
Here is the procedure, which takes divisor as parameter, and generates record in a table if divisor = 0 :
REPLACE PROCEDURE SP_TEST (IN divisor INT, OUT answer INT, OUT retString CHAR(1), OUT errNum INT)
BEGIN
DECLARE procName VARCHAR(100);
DECLARE processDate DATE;
DECLARE errNumber INT;
DECLARE errCode CHAR(5);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET answer = 0;
SET errCode = SQLSTATE;
SET errNumber = SQLCODE;
SET errNum = errNumber;
SET retString = 'F';

CALL SP_TRAP_ERROR(processDate, procName, errNumber, errCode);
END;

BEGIN TRANSACTION;
SET procName = 'SP_TEST';
SET processDate = CURRENT_DATE - INTERVAL '1' DAY;
-- any number of SQL statements between here
SET answer = 10 / divisor;
-- and here
SET errCode = '00000';
SET errNum = 0;
SET retString = 'I';

END TRANSACTION;

END;

error handling procedure:
REPLACE PROCEDURE SP_TRAP_ERROR (
IN process_dt VARCHAR(10)
,IN procName VARCHAR(100)
,IN errNumber INT
,IN errCode CHAR(5))

BEGIN
DECLARE processDate DATE;
DECLARE errMessage VARCHAR(255);

SET processDate = COALESCE(CAST(process_dt AS DATE), CURRENT_DATE);

SELECT ErrorText INTO errMessage
FROM DBC.ErrorMsgs
WHERE ErrorCode= errNumber;

INSERT INTO CTL_ERROR_LOG
VALUES (,processDate
,procName
,errNumber
,errCode
,errMessage
,CURRENT_TIMESTAMP(0));
END;

table definition:
CREATE SET TABLE EDWDVLP.CTL_ERROR_LOG ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ERR_ID INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
NO CYCLE),
PROCESS_DATE DATE FORMAT 'YYYY-MM-DD',
PROCEDURE_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
ERR_NUMBER INTEGER,
ERR_CODE CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
ERR_MESSAGE VARCHAR(500) CHARACTER SET LATIN NOT CASESPECIFIC,
ERR_TIMESTAMP TIMESTAMP(6))
PRIMARY INDEX ( ERR_ID );

I have another database, where I created the same set of procedures and a table, and SP_TEST just bombs out with "Invalid calculation: division by zero." error, but it does not do into SQLEXCEPTION block. Is there anything I am missing (I have all permissions in both places)?

Please help
Tags (1)
1 REPLY

Re: SQLEXCEPTION behaves differently in different databases

This is run time error and it might not get in to the SQLEXCEPTION block
________________
Read reviews of the http://www.moneytoplist.com affiliate programs and earn residual commissions