How to increase the length of message_text generated from the RESIGNAL/SIGNAL command in the Stored Procedures

Database
Enthusiast

How to increase the length of message_text generated from the RESIGNAL/SIGNAL command in the Stored Procedures

Hi,

I have a stored procedure which has kind of a lengthy message that needs to be thrown out  in case of failure . I am currently using the below code to signal the error message ,I found that the message_text could be only varchar(128) ,so is there a workaround to handle this limit such that I can throw a lengthy error message back to users calling the sp.

Please find the code below I am using .

/**************************** Initialize variables *******************************/

     DECLARE vRunStepMsgTxt VARCHAR(2560);

/************Initialize SQLEXCEPTION***************/

      DECLARE EXIT HANDLER FOR SQLEXCEPTION

        BEGIN

          SET sqlErrCde = sqlcode;

         SET vRunStepMsgTxt = 'This is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom message';

          SET OUTMSG = vRunStepMsgTxt;

          RESIGNAL SQLSTATE 'U0016' SET MESSAGE_TEXT = vRunStepMsgTxt;  

        END;

Error message throws is as below .

*** Failure 7693 TEST_SPROC:SQLSTATE U0016: This is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageThis is my custom messageT

 *** Total elapsed time was 1 second.

Thanks,

RN

3 REPLIES
Enthusiast

Re: How to increase the length of message_text generated from the RESIGNAL/SIGNAL command in the Stored Procedures

I am using Teradata 15.00 version. 

Teradata Employee

Re: How to increase the length of message_text generated from the RESIGNAL/SIGNAL command in the Stored Procedures

You can't increase the MESSAGE_TEXT length.

Perhaps you could pass back a larger string in a dynamic result set or OUT parameter, or insert it into a log table.

Enthusiast

Re: How to increase the length of message_text generated from the RESIGNAL/SIGNAL command in the Stored Procedures

Thank you for the response !!. Will add it in the OUT parameter.