All Forums Database
gpolanch 46 posts Joined 12/11
27 Mar 2012
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!

 

drmkd17 54 posts Joined 10/12
25 Nov 2014

I too need help here. Please suggest.

Rohan_Sawant 55 posts Joined 07/14
13 Jan 2015

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

You must sign in to leave a comment.