All Forums Database
WAQ 158 posts Joined 02/10
03 Dec 2015
Using variable in Teradata SIGNAL

Hi,
I am using Teradata SIGNAL to raise an exception with my own SQL code and message. My SIGNAL code for raising the exception is given below:

SIGNAL SQLSTATE VALUE 'U0927' SET MESSAGE_TEXT = vError_Msg;

I want to know is there any way I can use variables in place of hardcoded SQLSTATE, something like the below one?

SIGNAL SQLSTATE VALUE vError_Code SET MESSAGE_TEXT = vError_Msg;

If not, is there any other alternative to acheive the same?
Thanks and Regards

MaximeV 19 posts Joined 11/13
03 Dec 2015

hey,
you should always declare something like this on a sp to handle generic sql errors and warning:
 

	DECLARE EXIT  /* OR CONTINUE?*/ HANDLER FOR SQLEXCEPTION
	BEGIN
	SET v_SQL_ERR_CD = SQLCODE;
	SET v_SQL_STATE = SQLSTATE;


        /*  CASE v_SQL_STATE  WHEN ... THEN.. set MESSAGE_TEXT =... */

/* set whatever you want here for custom error message */


/* after you can even insert this message on a log table */
	END;

	DECLARE EXIT /* OR CONTINUE */ HANDLER FOR SQLWARNING
	BEGIN
	SET v_SQL_ERR_CD = SQLCODE;
	SET v_SQL_STATE = SQLSTATE;

        /*  CASE v_SQL_STATE  WHEN ... THEN.. set MESSAGE_TEXT =... */

/* set whatever you want here for custom error message */


/* after you can even insert this message on a log table */

	END;

 

WAQ 158 posts Joined 02/10
03 Dec 2015

Hi MaximeV,
Yes we are using handler but my question is that is there any way we can use the variable in SIGNAL statement?

MaximeV 19 posts Joined 11/13
03 Dec 2015

Actually i don't know (although I don't think it's possible) . I was just giving an option for generating custom and reusable error messages.

WAQ 158 posts Joined 02/10
07 Dec 2015

Okay then that means that we can not SIGNAL/RESIGNAL the SQLSTATE dynamically from the caller procedure and the only way is to hardcode the SQL code in SIGNAL/RESIGNAL?

Fred 1096 posts Joined 08/04
07 Dec 2015

SIGNAL requires a literal value or a (static) declared condition name. But you certainly have the option to RESIGNAL the original condition outward (by not specifying a condition on the RESIGNAL).

WAQ 158 posts Joined 02/10
08 Dec 2015

Hi Fred, thanks for your response.
Actually I have a scenario where I have a set of custom SQLSTATEs (around 50) with messages which I want to raise based on certain conditions in Teradata SPs. Now there are different levels of SPs e.g L1 SP calling L2 SP which is then calling the main SP. I want to raise that custom exception in main SP and want that custom SQLSTATE and messages to pass through till L1 SP which is the first caller and eventually the invoker which is BTEQ.
Since SIGNAL does not allow to use SP variables for SQLSTATE, I cannot hardcode all custom SQLSTATES. I am using RESIGNAL from he handlers of main, L2 and L1 SP but L2 SP which is calling main SP does not get the SQLSTATE of the custom exception raise from the main SP.
What do you suggest, could be the best way to achieve this, if it’s not possible using variables?

WAQ 158 posts Joined 02/10
10 Dec 2015

Can someone please help me in resolving the issue.

Fred 1096 posts Joined 08/04
10 Dec 2015

Can you help me understand the issue?
L2 calls MAIN.
Logic within MAIN issues SIGNAL SQLSTATE 'U0100' SET MESSAGE_TEXT='Some custom message';
Does MAIN have a condition handler that intercepts this SQLSTATE? If so, the handler can RESIGNAL; with no options, to simply pass it outward to L2. If not handled within MAIN, then it will be automatically passed.

WAQ 158 posts Joined 02/10
10 Dec 2015

Hi Fred,
L1 is calling L2 and L2 is calling MAIIN. All these SPs have handlers.
Yes you are correct, I am calling SIGNAL from MAIN SP with my custom SQLSTATE and message. Now the handler in main performs some action and then issue RESIGNAL (just literally RESIGNAL without any other parameter) to let L2 know which exception is raised.
Now the problem is that the RESIGNAL in main SP hanlder is not passing the custom SQL state to L2. So L2 is not aware of the custom SQL state raised my main SP.
Same goes with L1, as the control passes on to L1 from L2.
Thanks and Regards.

Fred 1096 posts Joined 08/04
14 Dec 2015

It's difficult to solve the problem without seeing the actual code. Two things you probably already know:

  1. The handler is a single statement, so if you are performing an action and doing RESIGNAL, you must have BEGIN/END around those to make a compound statement.
  2. If you exit MAIN via RESIGNAL, control will be passed to L2's handler for that exception, (if any).

You say that the custom SQLSTATE is not passed to L2. What is being passed back instead?

WAQ 158 posts Joined 02/10
15 Dec 2015

Hi Fred, Following is the code:

REPLACE PROCEDURE MAIN(P_DEPT_NAME VARCHAR(30))
BEGIN
	DECLARE vError_Text		VARCHAR(256);
	DECLARE vError_Msg		VARCHAR(256);
	DECLARE vSQL_State	  	VARCHAR(6) DEFAULT ''XC;
	
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET vSQL_State = SQLSTATE;
		GET DIAGNOSTICS EXCEPTION 1 vError_Text = MESSAGE_TEXT;
		INSERT INTO TEST_TPT_1 VALUES (vSQL_State, vError_Text);
		RESIGNAL;
	END;
	
	IF P_DEPT_NAME = 'ABC' THEN
		SET vError_Msg = '"This is an error message 1"';
		SIGNAL SQLSTATE 'U0927' SET MESSAGE_TEXT = vError_Msg;
	ELSE
		SET vError_Msg = '"This is an error message 2"';
		SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = vError_Msg;
	END IF;
END;
REPLACE PROCEDURE TEST_L2(P_DEPT_NAME VARCHAR(30))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		DECLARE vSQL_State	  	CHAR(5);
		DECLARE vError_Msg		VARCHAR(256);
		RESIGNAL;
	END;
	CALL MAIN(P_DEPT_NAME);
END;

So if I make a call to L2 using TEST_L2 ('ABC'); I get correct SQL state in TEST_TPT_1 table, which is U0927 with message "This is an error message 1"error message 1"
However L2 is not getting the SQL State U0927 from Main and I only get the following message in SQL Assitant:
CALL Failed 7693:  TEST L2 "This is an error message 1"error message 1"
 

Fred 1096 posts Joined 08/04
15 Dec 2015

If you move the logging into the handler block in TEST_L2, you'll see it actually does receive the SQLSTATE. And if you don't handle the exception in TEST_L2, it gets passed back to the caller correctly. But there appears to be an issue when you intercept the exception and then try to propagate it outward again via RESIGNAL. (The message text also seems to have an issue.) I would suggest you open an incident with Teradata support to report this as a bug.

WAQ 158 posts Joined 02/10
16 Dec 2015

Hi Fred,
Sorry I didn't get what you mean by "move the logging into the handler block in TEST_L2". Can you explain a bit so that I can try that.
Moreover, L2 will always have its handler which will RESIGNAL to let invoker know about the error.
Yes you are right regarding the issue with message text. Can you please tell me how can I report this to Teradata support.
Thanks and Regards.

Fred 1096 posts Joined 08/04
16 Dec 2015

I meant you can copy (or move) code like your GET DIAGNOSTICS / INSERT into the SQLEXCEPTION handler in TEST_L2.
 
Someone at your site should have access to Teradata at Your Service http://tays.teradata.com to open an incident. Or contact your Teradata representative for assistance.

WAQ 158 posts Joined 02/10
16 Dec 2015

Hi Fred, I can not move all the code from main SP handler to TEST_L2 hanlder because certain logging needs to be done in main SP.
But still for testing it, I changed the code and made the following changes:

REPLACE PROCEDURE WAQ.MAIN(P_DEPT_NAME VARCHAR(30))
BEGIN
	DECLARE vError_Text		VARCHAR(256);
	DECLARE vError_Msg		VARCHAR(256);
	DECLARE vSQL_State	  	VARCHAR(6) DEFAULT ''XC;
	
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		RESIGNAL;
	END;
	
	IF P_DEPT_NAME = 'ABC' THEN
		SET vError_Msg = '"This is an error message 1"';
		SIGNAL SQLSTATE 'U0927' SET MESSAGE_TEXT = vError_Msg;
	ELSE
		SET vError_Msg = '"This is an error message 2"';
		SIGNAL SQLSTATE 'U0123' SET MESSAGE_TEXT = vError_Msg;
	END IF;
END;
REPLACE PROCEDURE WAQ.TEST_L2(P_DEPT_NAME VARCHAR(30))
BEGIN
	DECLARE vError_Text		VARCHAR(256);
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		DECLARE vSQL_State	  	CHAR(5);
		DECLARE vError_Msg		VARCHAR(256);
		GET DIAGNOSTICS EXCEPTION 1 vError_Text = MESSAGE_TEXT;
		INSERT INTO WAQ.TEST_TPT_1 VALUES (SQLSTATE, vError_Text);
		RESIGNAL;
	END;
	CALL WAQ.MAIN(P_DEPT_NAME);
END;

But still the result is same and I am unable to get SQLSTATE in L2.

Fred 1096 posts Joined 08/04
17 Dec 2015

The handler in L2 does receive the custom SQLSTATE (though it does not RESIGNAL it correctly, so it doesn't solve your problem).

WAQ 158 posts Joined 02/10
17 Dec 2015

Hi Fred,
You are right because I have an INSERT statement in the handler of L2 and I changed the code in L2 as given below:

REPLACE PROCEDURE WAQ.TEST_L2(P_DEPT_NAME VARCHAR(30))
BEGIN
	DECLARE vError_Text		VARCHAR(256);
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		DECLARE vSQL_State	  	CHAR(5);
		DECLARE vError_Msg		VARCHAR(256);
		GET DIAGNOSTICS EXCEPTION 1 vSQL_State = RETURNED_SQLSTATE;
		GET DIAGNOSTICS EXCEPTION 1 vError_Text = MESSAGE_TEXT;
		INSERT INTO WAQ.TEST_TPT_1 VALUES (vSQL_State, vError_Text);
		RESIGNAL;
	END;
	CALL WAQ.MAIN(P_DEPT_NAME);
END;

Now After execution, I can see that the custom SQLSTATE is inserted in the table.
Why L2 is not RESIGNALing the custom SQLSTATE correctly? Am I doing something wrong in the code OR is it a bug?

Fred 1096 posts Joined 08/04
18 Dec 2015

It's a bug.

WAQ 158 posts Joined 02/10
21 Dec 2015

Thanks a lot Fred for your help. I'll report these two issues to Teradata team.

You must sign in to leave a comment.