-- Efficient error logging helps analysis of various types of errors
-- and their resolution.  Oracle package DBMS_UTILTY has error logging
-- features that can be used in logging.

-- DBMS_UTILITY.format_error_stack
--   function returns 2k bytes of error stack

-- DBMS_UTILITY.format_error_backtrace
--   procedure returns VARCHAR2 without size limitation. If error 
--   content needs to be stored in an error log table, CLOB would be 
--   a good data type choice to get complete error log details

-- DBMS_UTILITY.format_call_stack 
--   function returns 2k bytes of call stack details

-- The first code shows the basic error message from SQLERRM

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
 v_val  SIMPLE_INTEGER := 0;
BEGIN
  SELECT 1 INTO v_val  FROM DUAL  WHERE  2 < 1;

EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/



-- To just know the source of error and line number exclude 
-- format_call_stack from the code as shown below.

DECLARE
 v_val  SIMPLE_INTEGER := 0;
BEGIN
  SELECT 1 INTO v_val  FROM DUAL  WHERE  2 < 1;

EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack);
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace); 
END;
/



-- Same code with error messages output by DBMS_UTILITY functions 
-- and procedure, showing complete error details.

DECLARE
 v_val  SIMPLE_INTEGER := 0;
BEGIN
  SELECT 1 INTO v_val  FROM DUAL  WHERE  2 < 1;

EXCEPTION
 WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_stack);
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_call_stack);
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.format_error_backtrace); 
END;
/