Various techniques to store Debug log message to handle Errors - Part 1

There are various way to capture user define messages to debug the code or to display the errors encounter during the execution of code.

In this scenario I have created Error table, sequence (to generate unique message id) and one procedure. This procedure is used to insert the log messages into error table. So whenever we have to capture some debug message (user define) we can call this procedure and this will log messages in error table.

Create Sequence

CREATE sequence xx_log_mesg_sminvalue 1 start with 1 increment BY 1 NOCACHE;

Create table

CREATE TABLE xx_error_message

  (

Text_message  VARCHAR2(100),

Mesaage_id    NUMBER,

Cretion_date DATE   

  );

Executable:

CREATE OR REPLACE PROCEDURE XX_log_message(p_str VARCHAR2)

AS

BEGIN

  INSERT

  INTO xx_error_message

  (      Text_message,

Mesaage_id,

Cretion_date    )

    VALUES

( p_str ,

xx_log_mesg_s.nextval ,

      SYSDATE    );

  COMMIT;

END XX_log_message;

Testing

DECLARE

  l_test VARCHAR2(30) :='test data';

BEGIN

XX_log_message(l_test);

END;