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_IDNUMBER NOT NULL,
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:
WHEN OTHETS THEN
local_sqlerrm := SQLERRM;
INSERT INTO error_log (
(lchr_stage|| whatever value you want to check such as log family_id, so we know for which family_id the error happened,
Believe me, lchr_stage with variable values and error_log table will ease your debugging efficiently.