Writing a list of rows each on a different line in an exception output

[Former Member]

Hello All,

I wrote a customized SQL exception which retrieves the values of each violated constraint thrown from the exception.

In case i have 5 for example im getting the output one after the other on the same line.

What i need to know if there is a way that can enhance the output and puts every set of values on a separate line.

Here is an example:

CREATE PROCEDURE Trying_the_sqlexception ()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

v_error_message       clob;

dupp_flag             nvarchar(100);

CURSOR c_cursor FOR

  SELECT invoice_number,

         invoice_date,

         account_number,

         acc_vendor_src_internal_id

    FROM dm_l_invoice_dim dm

   GROUP BY invoice_number,

            invoice_date,

            account_number,

            acc_vendor_src_internal_id

  HAVING COUNT(1) > 1;

begin

v_error_message := 'Dupplicated Unique Key constraint on :';

FOR cur_row as c_cursor() DO 

  v_error_message := v_error_message||

                     '  Invoice number:'||cur_row.invoice_number||'  Invoice date:'cur_row.invoice_date||'  Master account FK:'||cur_row.account_number||'  Vendor FK:'cur_row.acc_vendor_src_internal_id; 

  end for;

  DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001; 

  DECLARE EXIT HANDLER FOR MYCOND RESIGNAL; 

  SIGNAL MYCOND SET MESSAGE_TEXT = v_error_message;

  end if;

  END;

In this case im getting the following result :

" user-defined error exception: Duplicated Unique Key constraint on :  Invoice number:sanjay33334444  Invoice date:2014-05-26 00:00:00.0000000  Master account FK:account number is null  Vendor FK:vendor is null  Invoice number:sanjay33334444  Invoice date:2014-05-27 00:00:00.0000000  Master account FK:account number is null  Vendor FK:vendor is null"

what i need is to have :

" user-defined error exception:

Duplicated Unique Key constraint on :  Invoice number:sanjay33334444  Invoice date:2014-05-26 00:00:00.0000000  Master account FK:account number is null  Vendor FK:vendor is null.

Invoice number:sanjay33334444  Invoice date:2014-05-27 00:00:00.0000000  Master account FK:account number is null  Vendor FK:vendor is null"

--What can i add between v_error_message and  || to get each constraint on a separate row

I hope this is clear enough

Thank

Ely

[lbreddemann]

You're trying to 'overload' the error message here a bit.

It's not meant to serve as an aggregated error message but quite obviously was designed with a single error in mind (identified by a single error number and message).

So, technically it will always just be single error string you get.

But there's no rule that forbids to insert CR/NL characters into your string. Why don't you just try this?

Other than that, I'd propose to look into using a logging table instead to keep more detailed error information.

[Former Member]

Actually i added this to my code and it got me what i needed

|| BINTOSTR( HEXTOBIN('0D0A') );