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

Database

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