Monday, December 8, 2014

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.)

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

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 am using one common framework to handle errors as well as progress of execution flow. To achieve this functionality I have created three custom tables one common package which is used across the instance.

Below are the Required Database Objects:

Error Handling Tables:

xx_process_log:
This table is used to track the progress of Procedure/Function execution flow. This is first table where data is inserted having statuses like‘Start’ and after execution ‘Complete’ Or ‘Error’.
It has one unique message id which is shared by other two tables to link messages i.e. for unique reference.

xx_message_log:
This Table is used to store random user friendly messages like DBMS_OUTPUT.PUT_LINE. It has message id which is common taken from xx_process_log for each Procedure/Function flow.
xx_error_log:
This Table is used to store standard Errors i.e. exception message with sqlcode and sqlerrm. It has message id which is common taken from xx_process_log for each Procedure/Function flow.

Common Package:

xx_common_error_handle:
This package is used to Store logic for Inserting data into three error handling tables. It has three separate procedures to insert data into xx_process_log,xx_message_log and, xx_error_log.

Mandatory Common Procedure in all packages where we have to use this Framework:

xx_process_track_log:
This Procedure is common and mandatory in all Packages where we have to use Error handling framework. This is called by each Procedure or Function at start and at end to update the record into table xx_process_log.
When first time we called, it will insert details (as per your requirement you can keep in parameter) in table xx_process_log with status ‘Start’ and it has one sequence which generate unique message id and store in table. Also this message id is required OUT parameter, as we will use this message id as a reference while inserting data into table’s xx_message_log and, xx_error_log.


(Note: This is very basic example which can be enhanced as per business requirements for
e.g.
a) You can use some profile options or lookup to control these messages.
b) You can send email notification when error occurs
c) You can send data to dashboard for display using business events.

)























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

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 created Error table, sequence (to generate unique message id) and one procedure. This procedure is used to insert the log messages into error table. So whenever we have to capture some debug message (user define) we can call this procedure and this will log messages in error table.

Create Sequence
CREATE sequence xx_log_mesg_sminvalue 1 start with 1 increment BY 1 NOCACHE;

Create table

CREATE TABLE xx_error_message
  (
Text_message  VARCHAR2(100),
Mesaage_id    NUMBER,
Cretion_date DATE   
  );

Executable:

CREATE OR REPLACE PROCEDURE XX_log_message(p_str VARCHAR2)
AS
BEGIN
  INSERT
  INTO xx_error_message
  (      Text_message,
Mesaage_id,
Cretion_date    )
    VALUES
( p_str ,
xx_log_mesg_s.nextval ,
      SYSDATE    );
  COMMIT;
END XX_log_message;

Testing

DECLARE
  l_test VARCHAR2(30) :='test data';
BEGIN
XX_log_message(l_test);

END;

How to get Called and Calling Procedure name?

Sometimes we got requirement where we required few parameters like Owner name, calling procedure name, and Called procedure name.
In such scenario oracle provide some standard methods by using which we can achieve the functionality.
Below are the two ways I have used to fetch called procedure name and calling procedure name.

Method 1: Using OWA_UTIL.WHO_CALLED_ME
Method 2: Using $$PLSQL_UNIT

Example:

CREATE OR REPLACE PROCEDURE XX_CALLED
as
l_owner  varchar2(20);
l_name  varchar2(20);
l_lineno number;
l_callervarchar2(20);
BEGIN
OWA_UTIL.WHO_CALLED_ME (l_owner, l_name,l_lineno,l_caller);
dbms_output.put_line('You are called by '||'owner   '||l_owner||
' Calling Object '||l_name||' Line no '||l_lineno||' Caller Type '||l_caller  );
dbms_output.put_line('Calling Object '|| $$PLSQL_UNIT);
END XX_CALLED;

Testing
BEGIN
XX_CALLED;
END;