How to create an ERROR_LOG table and used to log, find and fix where the error occured.  
Few steps as simple as this:

 

CREATE TABLE HR.ERROR_LOG

(

ERROR_ID NUMBER NOT NULL,

ERROR_STAGE VARCHAR2(1000 BYTE),

ERROR_SQL_MSG VARCHAR2(2000 BYTE),

INSERT_DATE DATE,

INSERT_USER VARCHAR2(50 BYTE),

UPDATE_DATE DATE,

UPDATE_USER VARCHAR2(50 BYTE)

);

 

Now create a sequence (error_id_seq) that will create the increment for the error_id  primary key similar to family table related sequence.

 

Now create a synonym for error_log as we created a synonym for family table.  

 

Now create a trigger that will populate error_id, insert_date, insert_user and when updated will populate update_date and update user

similar to the one we created for family table.

 

Error log table is completed.

 

When you write a procedure or a function, always create a variable local_stage VARCHAR2(300).

Populate this variable before every important steps of the program.

Create another variable called local_sqlerrm VARCHAR2(2000);

 

In your exception you can do the following:

 

EXCEPTION

   WHEN OTHETS THEN

      local_sqlerrm := SQLERRM;

      INSERT INTO error_log (

          error_stage,

          error_sql_msg)

      VALUES

          (lchr_stage|| whatever value you want to check such as log family_id, so we know for which family_id the error happened,

           lchr_sqlerrm);

 

      COMMIT;

END;

 

Believe me, lchr_stage with variable values and error_log table will ease your debugging efficiently.