How to debug long stored procedures and how to create backend logging mechanism?

By | September 25, 2014

Requirement Statement:

Debug/ Investigation relevant Need

While working in Oracle backend, there are times when you are debugging your long package procedures or functions and you want to see the values being generated in different variables at runtime. What is the best way to get these values at run time?

Logging Related Need:

Sometimes you want to maintain some historical log of errors/ exceptions or some sort of audit log. You want to keep the log history even if your main transaction gets rollback. 

Solution Concept:

This paragraph contains the overview of solution in oracle. Solution to both requirements mentioned above is same. In order to enable logging when you want to maintain it if rollback happens, the only way is to use autonomous transaction. In this article I am going to create a mechanism which we can use to create autonomous transaction based procedure that we shall be using at all places where we want to enable logging either for debug purpose or for creating an audit log mechanism.

For this solution, we shall create a table, a sequence and a procedure. We shall insert a row in our log table whenever we need to make log of an event. This insert will be done through a procedure that will be having compiler hint of autonomous transaction. In this way our insert statement will become independent of main transaction and we shall be having in store in our log table even if any rollback happens after wards.

Solution Code

Create a Table: This table  will contain the Log Data

CREATE TABLE XX_TABLE_LOG(
LOG_ID NUMBER
,LOG_DATE DATE DEFAULT SYSDATE
,Module_Name Varchar2(100)
,LOG_CHAR  Varchar2(4000)
,LOG_CLOB  CLOB
)

Create a Sequence: This sequence we shall use to generate the auto number

CREATE SEQUENCE XX_TABLE_LOG_S NOCYCLE NOCACHE

 

Create Procedure:

This is the procedure that we shall be calling on places where we want to enable logging.

CREATE OR REPLACE PROCEDURE XX_TABLE_LOG_P (

P_MODULE_NAME VARCHAR2
,P_LOG_CHAR VARCHAR2
,P_LOG_CLOB  CLOB DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO  ALB_TABLE_LOG (LOG_ID,LOG_DATE,MODULE_NAME,LOG_CHAR,LOG_CLOB)
VALUES (XX_TABLE_LOG_S.NEXTVAL,SYSDATE,P_MODULE_NAME,P_LOG_CHAR,P_LOG_CLOB);
COMMIT;
END;

 

Procedure Parameters Explanation:
P_MODULE_NAME -> This will contain the name of package/ procedure/ function from where it is being called
P_LOG_CHAR ->This will hold the actual log data that we want to store to identify the information
P_LOG_CLOB  -> This will hold the CLOB data, giving value of this parameter is optional, as I have made it default NULL while defining it in procedure signature

 

Example to use it
DECLARE
–Put your Custom Logic
BEGIN
–Put your Custom Logic
                XX_TABLE_LOG_P(‘Module Name: Anonymous Block’,’In Begin Block’);
EXCEPTION WHEN OTHERS THEN
                XX_TABLE_LOG_P(‘Module Name: Anonymous Block’,’When Others Exception’);
                ROLLBACK;
END;

 

Note: Although there is roll back used in exception section. But it will have no impact on row created in log table. As this row is inserted through autonomous transaction based procedure.

Related Posts:

Apps Technical Interview Questions
Oracle HRMS Interview Questions
Question of the Day
9 Steps to become Oracle Apps Consultant
Hierarchy of OLM Tables
How to create backend logging mechanism
Step to Register Discoverer Report
Steps for creating custom form in Oracle Apps
Clear Global Cache in Oracle Apps
Profile Values in Oracle Forms Personalization
2 simple Steps to make a responsibility read only

  • Ashraf Khan

    can you give detail about alert session & type also.

  • Ashraf Khan

    used in core hr and payroll

  • fanni339

    There are two types of Alerts in Oracle Apps.

    Event Based and Periodic Alerts.

    Events Based alerts are fired on happening of some event. Whereas periodic alerts are scheduled in order to check the business requirement.

  • Ashraf Khan

    thanks if provide some document or notes for same… regards ashraf khan.

    I am in company al khodri ..alkober just now.
    i hope you already work here and given best here.

  • fanni339

    Nice to know it. KSC is nice place to Learn.

    Regarding the notes for Alerts, simply google it and you will find many documents.

  • Ashraf Khan

    THANKS…YOU KNOW MUCH Better,pl support as i am new for Techno FUNCTIONAL . but gotted clear much more cocept from urside.

    thanks alot and share some good query regarding six table given by you for HR & PAYROLL.

  • Ashraf Khan

    Asak Farhan , looking a query from table per_all_people_f for GOSI with condition Saudi’s
    & expatriates.

    For Saudi : upto 59:- 10% deduct from salary
    59 to 65: 9% deduct from salary
    65 above: 2% hazards.

    Pl support this query as per KSC.(4 case are there)

    Many Many request for same.

    Regards

    Ashraf Jahangir Khan

  • Ashraf Khan

    AKRAM BHAI IS SAYING SALAM TO YOU.

    Regards

    Ashraf Jahangir Khan