Our application consists of stored procedures. I want to the log errors to a table. I can get SQLSTATE and SQLCODE, but how do I formulate an error message that can be understood by humans, and conveys the actual value of %VSTR? For example, for ErrorCode 3807 "Object '%VSTR' does not exist.", how to determine which object does not exist?
Appendix D of the Stored Procedure/Embedded SQL doc is entitled Mapping Teradata Database Error Messages to SQLSTATE values. Can this be utilized to get %VSTR or other error information beyond SQLSTATE/SQLCODE?
Can GET DIAGNOSTICS be utilized?
I see the table DBC.ErrorMsgs.... can this help??
Can I determine the line number where a run-time error occurred?
A sample procedure capturing the error message :
REPLACE PROCEDURE TEST_P.TEST_PROCEDURE(OUT V_MESSAGE VARCHAR(100)) SQL SECURITY OWNER
DECLARE V_GETOUT INTEGER;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
GET DIAGNOSTICS EXCEPTION 1
V_MESSAGE = MESSAGE_TEXT;
INSERT INTO TABLE_DUMMY VALUES (1);