Thursday, March 8, 2012

FAQ #37 - How to create audit trail records using ADFBC, Pt. 2


In this second part of creating historical audit trail records using ADFBC we will see how to integrate, in a generic way, the stored procedures introduced in part 1 of these series. For more information on the database schema and stored procedures that are required to support this technique refer to FAQ #36 - How to create audit trail records using ADFBC, Pt. 1.

Main Theme

As explained in part 1 (FAQ #36 - How to create audit trail records using ADFBC, Pt. 1), historical audit trails are generated by calling the stored procedures AUDIT_TABLE and AUDIT_DETAILS. Both of these procedures are part of the AUDIT_PKG database package.

The AUDIT_TABLE procedure is called to create a historical audit trail of the corresponding table. As explained in part 1, corresponding history tables are created for each table being audited. These history tables conform to the following naming convention: table_name_HISTORY where table_name is the table being audited.  For example the corresponding history REGIONS table is called REGIONS_HISTORY.

To create a generic implementation, we will need to create framework extension classes for both the oracle.jbo.server.EntityImpl and oracle.jbo.server.ApplicationModuleImpl classes. Once these framework extension classes are created ensure that the ADFBC project settings are changed to reflect this fact. You do this by bringing up the Project Properties dialog and selecting ADF Business Components | Base Classes as it is shown in the picture below:

The next step is to override the EntityImpl doDML() method for the EntityImpl extension framework class and add the following code to it:

Note a few things about this overridden doDML() method:

  1. We call getEntityDef().getProperty(DISABLE_AUDIT_PROPERTY) to determine whether auditing for the specific entity object has been disabled. This is controlled by the presence of an entity object property identified by the constant DISABLE_AUDIT_PROPERTY. As long as this property has not been defined in the entity object, auditing is enabled by default.
  2. We call getEntityDef().getSourceType() to determine the source type of the entity object. We will audit entity objects based on database tables only. This is done by comparing the return value of getEntityDef().getSourceType() to DBOBJ_TYPE_TABLE. DBOBJ_TYPE_TABLE is a constant defined in oracle.jbo.server.EntityDefImpl indicating that the entity object is based on a database table.
  3. The first parameter required by the AUDIT_TABLE stored procedure is the user's current session identifier. This can be retrieved by calling SessionImpl.findOrCreateSessionContextManager().getCurrentSession().getId(). Take a look at this post for information on this: Bit #39 - Retrieving the current session id at the ADFBC layer.
  4. The second parameter passed to AUDIT_TABLE stored procedure is the commit identifier. We would like to keep track changes for each commit done in the same user session. We retrieve the commit identifier from a database sequence by calling the helper getCommitId() (see source code below).
  5. We pass the DML operation as a string (INSERT, UPDATE or DELETE) based on the value of the operation parameter passed to doDML() by the ADFBC framework.
  6. We call getEntityDef().getSource() to get the name of the table associated with the entity object.
  7. Finally we call the getEntityId() helper (see code below) to get the entity object's primary key. Note that we convert the primary key to a string before passing as a parameter to the AUDIT_TABLE stored procedure.
Here is the code for the getCommitId() and getEntityId() helpers:

Notice a couple of things about these helper methods:
  1. In getEntityId() we iterate through the entity object attributes in order to determine the primary key attribute. We do this by first calling getStructureDef().getAttributeDefs() to get the attribute definitions and then by calling isPrimaryKey() for each attribute to determine if it is a primary key attribute. If it is, we call getAttribute() to retrieve and return its value. Notice that you will have to expand this implementation if you have an entity object with a composite primary key.
  2. In getCommitId() we add the commit identifier returned from the database sequence to the session user data by calling getDBTransaction().getSession().getUserData(). We do this because we will need the same commit identifier at a later stage when calling the AUDIT_DETAILS stored procedure.
As explained in part 1 of this post, to create the specific details of the audit trail we need to call AUDIT_PKG.AUDIT_DETAILS stored procedure. To do this, override the afterCommit() in the ApplicationModuleImpl framework extension class and add the following code to it:

Notice in this case how we call getSession().getUserData().get(ExtEntityImpl.COMMIT_ID) to get the same commit identifier and pass it as a parameter to the AUDIT_DETAILS stored procedure. Also, notice that we remove the commit identifier from the session user data once we are done with calling the stored procedure.


In this two-part series we've seen a custom implementation of creating a historical audit trail based on a stored procedure implementation in the database. Notice that this technique is fairly generic as it does not require any additional implementation in the specific entity objects used throughout the application. As mentioned above, auditing is enabled by default and can be disabled by the presence of a property defined in the entity object. This can be easily reversed so that auditing is not done by default. If you found this useful, let me know so I can email you or post the source code as well.

Until the next time, have fun JDeveloping!


  1. Hi.
    Nice post with a lot of useful stuff.
    But I'm having problem with accessing session from entity object when I set Transaction from to something else (either "Always Begin New Transaction" or "Always Use Existing Transaction" or "Use Existing Transaction if Possible").
    In this case getDBTransaction().getSession().getUserData() is null. Why is this a bug?
    I'm using JDev

  2. Hi,

    Thanks for your comment.

    If the userData is null, try creating it (see below):

    Hashtable userdata = getDBTransaction().getSession().getUserData();
    if (userdata == null) {
    userdata = new Hashtable();

  3. Perfect post.....thanks. Please share the sample code -

  4. Very Useful post. Kindly send me the source code

  5. Hi Nick,
    Very well thought and useful codes. Please email me the source code at




  7. Send my your email so I can email you the sources.

  8. Very interesting post! Can you email the source code?

  9. Very informative and simple for this cross-cutting concerns.
    Can you please send me the source code at Thanks,Angelo

  10. Could you please send me the source code at

  11. Hi Nick, really interesting, i'm willing to try this in my application. Can you send me the source code at spamvenami[at]gmail[dot]com

  12. Hi Nick, I think this is more or less what I'm trying to achieve, can you send me the source code to sergiomahs[at]gmail[dot]com ? It will be very appreciated.


  13. Plz, Post Example source code

  14. plz, send full example source code at

  15. thanks for the post, could you please send source code to me at

  16. Nice post. Can you please send the source code to me @

  17. Hello Nick,
    Nice post, I was looking for similar requirement to achieve, could you please send me code including plsql to Thanks.

  18. hi Nick, very clever approach. Can i have the source code as well?

  19. Hello Nick - Can you please share the source code to


  20. Hello Nick - can you please share the source code to


  21. Hi Nick, Can I have the source code ? please send me the code to this mail id :

  22. Thanks for the post, nice approach, I am going to work on same kind of requirement. can you please send your code to this email id


Related Posts Plugin for WordPress, Blogger...