Oracle Procedure Log Events

Question

Bellow is my procedure.

CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL" (
   IS   
BEGIN

  INSERT INTO PROC_LOG (START_DATE,OUTPUT) VALUES (sysdate,Process Started);

  INSERT INTO NDB_AML_CUSTOMER 
   (ID, TITLE,...)
  SELECT ID, TITLE,... 
   FROM NDB_CUSTOMER_NEW
   WHERE DATE_TIME > (SELECT RUN_DATE FROM CHECK_POINT WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW');

  UPDATE CHECK_POINT SET RUN_DATE = SYSDATE WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW';

  COMMIT; 

    INSERT INTO PROC_LOG (END_DATE,OUTPUT) VALUES (sysdate,Process Ended);

END;
/

I want to output log events into a table. Such as, the start date & time of the procedure with the even called "Process started", and Process End date & time and during an exception the error message. So three columns in the Log_table, Start Date, End Date & Output. Looking for input from experts.

Answer 1

you can catch exceptions in procedures as mentioned in this article Exception handling. For your logging of the start and end date you can use a simple insert statement.

CREATE OR REPLACE PROCEDURE "CUSTOMER_INCREMENTAL" (
   IS   
BEGIN

  Process start event

INSERT INTO LOG_TABLE(ID, START_DATE, END_DATE, MESSAGE) VALUES (ID, SYSDATE, NULL, NULL);

  INSERT INTO NDB_AML_CUSTOMER 
   (ID, TITLE,...)
  SELECT ID, TITLE,... 
   FROM NDB_CUSTOMER_NEW
   WHERE DATE_TIME > (SELECT RUN_DATE FROM CHECK_POINT WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW');

  UPDATE CHECK_POINT SET RUN_DATE = SYSDATE WHERE TABLE_NAME = 'NDB_CUSTOMER_NEW';

  COMMIT;
  UPDATE LOG_TABLE SET END_DATE = SYSDATE WHERE ID = ID;
  Process End event

EXCEPTION

WHEN OTHERS THEN
UPDATE LOG_TABLE SET END_DATE = SYSDATE, MESSAGE = EXCEPTION_MESSAGE WHERE ID = ID;

END;

Some thing like this snippet