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

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 used debug file generation technique which generates one common log file for debug messages with time stamp. Which help us to debug errors in code or we can also track code execution flow.

Example:

In this example we have created one common procedure which appends all text messages (debug Message) to common log file which is placed in our server (location of file and name are predefined in common procedure). Main motive of common procedure is to open file and append log messages. We can call this procedure from any package to append log messages, we have to pass only log message.It will automatically add times tamp and message will get append to log file.

Create directory

SQL> CREATE DIRECTORY Test_dir AS '/appl/gl/user'';

SQL> GRANT READ ON DIRECTORY Test_dir TO PUBLIC;

Executable

CREATE OR REPLACE PROCEDURE xx_test_debug_message (p_str VARCHAR2)

AS

v_file_type     UTL_FILE.FILE_TYPE;

v_file_path        VARCHAR2(100);

v_file_name      VARCHAR2(30) := ‘TEST_DEBUG_LOG’

intval    BINARY_INTEGER;

strval    VARCHAR2 (256);

paramtype        BINARY_INTEGER;

BEGIN

paramtype := DBMS_UTILITY.get_parameter_value ('utl_file_dir', intval, strval);

g_file_path := strval;

v_file_type := UTL_FILE.FOPEN('v_file_path','v_file_name','a');      

UTL_FILE.PUT_LINE(v_file_type,TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'||':'|| p_str));      

UTL_FILE.FCLOSE(v_file_type);

EXCEPTION     

WHEN OTHERS THEN          

DBMS_OUTPUT.PUT_LINE ('ERROR ' || TO_CHAR (SQLCODE) || SQLERRM);          

NULL; 

END XX_TEST_DEBUG_MESSAGE; 

Testing

Declare

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

BEGIN

xx_test_debug_message (l_test);

END;

(Note: This is very basic example which can be enhanced as per business requirements for e.g. you can set some debug level and control these messages.)