How can I get the actual value of %VSTR or more error info?

Database
Enthusiast

How can I get the actual value of %VSTR or more error info?

Hello,

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?

Thanks!

2 REPLIES
Enthusiast

Re: How can I get the actual value of %VSTR or more error info?

I too need help here. Please suggest.

Enthusiast

Re: How can I get the actual value of %VSTR or more error info?

Hi,

A sample procedure capturing the error message :

REPLACE PROCEDURE TEST_P.TEST_PROCEDURE(OUT V_MESSAGE VARCHAR(100)) SQL SECURITY OWNER

BEGIN

DECLARE V_GETOUT INTEGER;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS EXCEPTION 1
V_MESSAGE = MESSAGE_TEXT;
END;

INSERT INTO TABLE_DUMMY VALUES (1);

END;

Thanks,

Rohan Sawant