Site navigation (main menu).

Blog

Monitoring changes to customised objects in E-Business Suite

As a Systems Implementer I often have to help my clients with a difficult decision: should we customise some Oracle-supplied E-Business Suite code? “No!” I hear you cry, “You can’t do that! Oracle won’t support you!” Well, sometimes to achieve a certain piece of functionality customers are prepared to accept the risks that come with customisation.

One of the biggest problems to overcome is dealing with the fact it is almost guaranteed that your customised code will be overwritten by an Oracle patch at some point in the future. It is often down to the Developer or Functional Consultant to look at the Release Notes for a patch to try and determine whether a certain customisation might be affected. For small patches this isn’t really a problem, but with the larger rollup patches the impact analysis becomes quite a major task. But even then, that approach can be problematic – sometime Oracle, in their infinite wisdom, release new versions of packages that are not even mentioned in the Release Notes. The upshot of this is that the only sure way to find out what is impacted is to search through every single file included in the patch – a lengthy and tedious exercise.

I have recently been working with a client who has quite a large number of customisations that have been in place since “go-live” eighteen months ago. The system is now stable after the initial implementation issues and the organisation have started to try and catch-up with the patches that they had neglected to apply whilst trying to get to “business as usual”. Performing the impact analyses of the patches was taking up a considerable amount of time and so I proposed an automated solution, which I have been kindly given permission to share with you.

The main problem is that we need to capture the “state” of a customised object just after it has been installed, so that we can compare that state with the state after a patch has been applied. If the states are different then we know that Oracle has overwritten our customisation. My first version of this utility used the LAST_DDL_TIME from ALL_OBJECTS. However, this was not entirely reliable as this timestamp is changed when an object is compiled, even if it hasn’t changed.

I was recently browsing through the PL/SQL Packages and Types manual for the 10g Database, when I came across the new DBMS_CRYPTO package, which effectively supersedes the quaintly named DBMS_OBFUSCATION_TOOLKIT.

The DBMS_CRYPTO.HASH function is overloaded so that we can perform the hash on a RAW, BLOB or a CLOB and it returns the hash value as a RAW. The function supports three different hashing algorithms: MD4, MD5 and SHA1. The two Message Digest (MD) algorithms return a 128-bit hash value; the Secure Hash Algorithm returns a 160-bit hash value.

When investigating whether to use hashes, I was concerned that two different bits of code could potentially produce the same hash value. To see how many different hash values can be generated from a function we need to look at the size of the returned hash values.

For MD4/5:  2^128 = 3.4 * 10^38
For SHA:  2^160 = 1.5 * 10^48

Both of these are very large numbers indeed! You stand more chance of winning the lottery jackpot two weeks in a row than ever hitting the same hash value. I decided to go with SHA because 160-bits is only 20-bytes, not a lot of storage required for each object that we want to monitor.

Now to write some code! The first function that I wrote just encapsulated the call to the HASH function:

FUNCTION get_hash
  ( p_clob                  IN            CLOB )
RETURN RAW
IS
BEGIN
  RETURN dbms_crypto.hash(p_clob, dbms_crypto.hash_sh1);
END get_hash;

I decided to use the CLOB variant as I thought that it would be easiest to create CLOB representations of the objects that I want to monitor. The next problem was how to how to actually get these representations out of the database. A lot of the things that I wanted to monitor are found in the Data Dictionary, but some things like Workflows and Alerts, are stored in E-Business Suite tables where there are quite complex relationships between the tables.

Having been working with XML Publisher quite extensively over the last two years I thought that XML would be the right way to go. As it happens, Oracle Server provides quite a number of different ways of creating XML from the database – the package that I was most interested in was DBMS_XMLGEN. This package, to quote the manual, “converts the results of a SQL query into a canonical XML format. The package takes an arbitrary SQL query as input, converts it to XML format, and returns the result as a CLOB” – exactly what I needed:

FUNCTION get_xml
  ( p_sql                   IN            VARCHAR2
  , p_rowset_tag            IN            VARCHAR2  DEFAULT NULL
  , p_row_tag               IN            VARCHAR2  DEFAULT NULL )
RETURN CLOB
IS
  l_ctx     dbms_xmlgen.ctxhandle;
  l_result  CLOB

BEGIN
  l_ctx := dbms_xmlgen.newContext(p_sql);

  dbms_xmlgen.setConvertSpecialChars(l_ctx, TRUE);
  dbms_xmlgen.setRowSetTag(l_ctx, NVL(p_rowset_tag,'ROWSET'));
  dbms_xmlgen.setRowTag(l_ctx, NVL(p_row_tag,'ROW'));

  l_result := dbms_xmlgen.getXML(l_ctx);

  dbms_xmlgen.closeContext(l_ctx);

  RETURN l_result;
END get_xml;

I then needed a function to build a suitable SQL statement given a table name and one or more primary key column names and values. This could then call GET_XML and GET_HASH and return the hash value:

PROCEDURE add_condition
  ( p_sql                   IN OUT NOCOPY VARCHAR2
  , p_pk_column             IN            VARCHAR2
  , p_pk_value              IN            VARCHAR2 )
IS
BEGIN
  IF (p_pk_column IS NOT NULL)
  THEN
    p_sql := p_sql || ' AND '||p_pk_column||' = '''||p_pk_value||'''';
  END IF;
END add_condition;

FUNCTION calculate_data_hash
  ( p_table_name            IN            VARCHAR2
  , p_pk_column1            IN            VARCHAR2  DEFAULT NULL
  , p_pk_value1             IN            VARCHAR2  DEFAULT NULL
  , p_pk_column2            IN            VARCHAR2  DEFAULT NULL
  , p_pk_value2             IN            VARCHAR2  DEFAULT NULL
  , p_pk_column3            IN            VARCHAR2  DEFAULT NULL
  , p_pk_value3             IN            VARCHAR2  DEFAULT NULL )
RETURN RAW
IS
  l_sql     VARCHAR2(2000);

BEGIN
  l_sql := 'SELECT * FROM '||p_table_name||' t WHERE 1=1';

  add_condition(l_sql, p_pk_column1, p_pk_value1);
  add_condition(l_sql, p_pk_column2, p_pk_value2);
  add_condition(l_sql, p_pk_column3, p_pk_value3);

  RETURN get_hash(get_xml(l_sql));

END calculate_data_hash;

Using the above CALCULATE_DATA_HASH function we can calculate the hash value for almost any type of database object:

FUNCTION calculate_hash
  ( p_object_type           IN        VARCHAR2
  , p_object_name           IN        VARCHAR2
  , p_table_name            IN        VARCHAR2  DEFAULT NULL
  , p_pk_column1            IN        VARCHAR2  DEFAULT NULL
  , p_pk_value1             IN        VARCHAR2  DEFAULT NULL
  , p_pk_column2            IN        VARCHAR2  DEFAULT NULL
  , p_pk_value2             IN        VARCHAR2  DEFAULT NULL
  , p_pk_column3            IN        VARCHAR2  DEFAULT NULL
  , p_pk_value3             IN        VARCHAR2  DEFAULT NULL )
RETURN RAW
IS
  l_hash_value  RAW(40) := NULL;

BEGIN
  CASE
    WHEN (p_object_type = 'DATA')
    THEN
      l_hash_value := calculate_data_hash
        ( p_table_name  => p_table_name
        , p_pk_column1  => p_pk_column1
        , p_pk_value1   => p_pk_value1
        , p_pk_column2  => p_pk_column2
        , p_pk_value2   => p_pk_value2
        , p_pk_column3  => p_pk_column3
        , p_pk_value3   => p_pk_value3 );

    WHEN (p_object_type IN
      ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TYPE','TYPE BODY'))
    THEN
      l_hash_value := calculate_data_hash
        ( p_table_name  => 'ALL_SOURCE'
        , p_pk_column1  => 'TYPE'
        , p_pk_value1   => p_object_type
        , p_pk_column2  => 'NAME'
        , p_pk_value2   => p_object_name );

    WHEN (p_object_type = 'VIEW')
    THEN
      l_hash_value := calculate_data_hash
        ( p_table_name  => 'ALL_VIEWS'
        , p_pk_column1  => 'VIEW_NAME'
        , p_pk_value1   => p_object_name );

    WHEN (p_object_type = 'MATERIALIZED VIEW')
    THEN
      l_hash_value := calculate_data_hash
        ( p_table_name  => 'ALL_MVIEWS'
        , p_pk_column1  => 'MVIEW_NAME'
        , p_pk_value1   => p_object_name );

    WHEN (p_object_type = 'TRIGGER')
    THEN
      l_hash_value := calculate_data_hash
        ( p_table_name  => 'ALL_TRIGGERS'
        , p_pk_column1  => 'TRIGGER_NAME'
        , p_pk_value1   => p_object_name );

    WHEN (p_object_type = 'MESSAGE')
    THEN
      l_hash_value := calculate_data_hash
        ( p_table_name  => 'FND_NEW_MESSAGES'
        , p_pk_column1  => 'MESSAGE_NAME'
        , p_pk_value1   => p_object_name );
    ELSE
      l_hash_value := NULL;

  END CASE;

  RETURN l_hash_value;

END calculate_hash;

Now that we can create the hash value for a wide variety of objects, we need somewhere to store those values:

CREATE SEQUENCE xxc_custom_objects_seq;

CREATE TABLE xxc.xxc_customised_objects
  ( custom_object_id        NUMBER          NOT NULL
  , module_name             VARCHAR2(20)    NOT NULL
  , object_name             VARCHAR2(80)    NOT NULL
  , object_type             VARCHAR2(20)    NOT NULL
  , table_name              VARCHAR2(80)
  , pk_column1              VARCHAR2(80)
  , pk_value1               VARCHAR2(255)
  , pk_column2              VARCHAR2(80)
  , pk_value2               VARCHAR2(255)
  , pk_column3              VARCHAR2(80)
  , pk_value3               VARCHAR2(255)
  , hash_value              RAW(40)         NOT NULL
  , CONSTRAINT cob_pk
      PRIMARY KEY (custom_object_id) );

CREATE SYNONYM xxc_customised_objects FOR xxc.xxc_customised_objects;

And, to make life easier for the developers, a simple API to either create or update a hash record in this table. This API is to be called immediately after any customised object has been created:

PROCEDURE register_object
  ( p_module_name           IN        VARCHAR2
  , p_object_type           IN        VARCHAR2
  , p_object_name           IN        VARCHAR2
  , p_table_name            IN        VARCHAR2  DEFAULT NULL
  , p_pk_column1            IN        VARCHAR2  DEFAULT NULL
  , p_pk_value1             IN        VARCHAR2  DEFAULT NULL
  , p_pk_column2            IN        VARCHAR2  DEFAULT NULL
  , p_pk_value2             IN        VARCHAR2  DEFAULT NULL
  , p_pk_column3            IN        VARCHAR2  DEFAULT NULL
  , p_pk_value3             IN        VARCHAR2  DEFAULT NULL )
IS
  l_object_id     xxc_customised_objects.custom_object_id%TYPE;
  l_hash_value    RAW(40) := NULL;

BEGIN
  BEGIN
    SELECT  cob.custom_object_id
    INTO    l_object_id
    FROM    xxc_customised_objects cob
    WHERE   cob.object_type        = p_object_type
    AND     cob.object_name        = p_object_name;
  EXCEPTION
    WHEN no_data_found THEN
      l_object_id := NULL;
  END;

  l_hash_value := calculate_hash
    ( p_object_name => p_object_name
    , p_object_type => p_object_type
    , p_table_name  => p_table_name
    , p_pk_column1  => p_pk_column1
    , p_pk_value1   => p_pk_value1
    , p_pk_column2  => p_pk_column2
    , p_pk_value2   => p_pk_value2
    , p_pk_column3  => p_pk_column3
    , p_pk_value3   => p_pk_value3 );

  IF (l_object_id IS NULL)
  THEN
    INSERT INTO xxc_customised_objects
      ( custom_object_id
      , module_name
      , object_name
      , object_type
      , table_name
      , pk_column1
      , pk_value1
      , pk_column2
      , pk_value2
      , pk_column3
      , pk_value3
      , hash_value )
    VALUES
      ( xxc_custom_objects_seq.NEXTVAL
      , p_module_name
      , p_object_name
      , p_object_type
      , p_table_name
      , p_pk_column1
      , p_pk_value1
      , p_pk_column2
      , p_pk_value2
      , p_pk_column3
      , p_pk_value3
      , l_hash_value );

  ELSE
    UPDATE  xxc_customised_objects cob
    SET     cob.hash_value         = l_hash_value
    WHERE   cob.custom_object_id   = l_object_id;
  END IF;

END register_object;

We now have a table holding all the information about the objects that we want to monitor and their current hash values. After a patch has been applied to our Patch Test environment, we can re-calculate the hash values to see whether anything has changed. To make life simpler, I added a function that returns the current (as opposed to the stored) hash value for a given CUSTOM_OBJECT_ID.

FUNCTION calculate_hash
  ( p_custom_object_id      IN        NUMBER )
RETURN RAW
IS
  r_object  xxc_customised_objects%ROWTYPE;

BEGIN
  SELECT  cob.*
  INTO    r_object
  FROM    xxc_customised_objects cob
  WHERE   cob.custom_object_id   = p_custom_object_id;

  RETURN calculate_hash
    ( p_object_type => r_object.object_type
    , p_object_name => r_object.object_name
    , p_table_name  => r_object.table_name
    , p_pk_column1  => r_object.pk_column1
    , p_pk_value1   => r_object.pk_value1
    , p_pk_column2  => r_object.pk_column2
    , p_pk_value2   => r_object.pk_value2
    , p_pk_column3  => r_object.pk_column3
    , p_pk_value3   => r_object.pk_value3 );

END calculate_hash;

To make it easier to determine whether any objects have changed, I wrote the following view which calls the above function:

CREATE OR REPLACE VIEW xxc_customised_objects_v
AS
SELECT  OBJ.*
,       DECODE(OBJ.stored_hash_value
          , OBJ.current_hash_value, 'N'
          , 'Y') AS object_changed
FROM (
  SELECT  cob.module_name
  ,       cob.object_type
  ,       cob.object_name
  ,       cob.table_name
  ,       cob.pk_column1
  ,       cob.pk_value1
  ,       cob.pk_column2
  ,       cob.pk_value2
  ,       cob.pk_column3
  ,       cob.pk_value3
  ,       cob.hash_value                AS stored_hash_value
  ,       xxc_custom.calculate_hash
            (cob.custom_object_id)      AS current_hash_value
  FROM    xxc_customised_objects cob
) OBJ
ORDER BY OBJ.module_name, OBJ.object_type, OBJ.object_name

In summary, this is a relatively simple, non-invasive system for monitoring changes to database objects. It is also simple to extend to monitor other object types by adding further sections to the first overload of the CALCULATE_HASH function.

In fact, the version that you can download from our website has support for Workflows and Alerts. This takes advantage of the fact that the DBMS_XMLGEN package fully supports nested cursor statements in the SQL, which can give you an XML representation of a complete relational data structure. The following example does not show the complete SQL, just enough to whet your appetite:

SELECT  ity.*
,       CURSOR (
          SELECT  iat.*
          FROM    wf_item_attributes_vl iat
          WHERE   iat.item_type = ity.name ) AS item_attributes
,       CURSOR (
          SELECT  lty.*
          ,       CURSOR (
                    SELECT  lkp.*
                    FROM    wf_lookups lkp
                    WHERE   lkp.lookup_type = lty.lookup_type ) AS lookup_codes
          FROM    wf_lookup_types lty
          WHERE   lty.item_type = ity.name ) AS lookup_types
,       CURSOR (
          SELECT  msg.*
          ,       CURSOR (
                    SELECT  mat.*
                    FROM    wf_message_attributes_vl mat
                    WHERE   mat.message_type  = msg.type
                    AND     mat.message_name  = msg.name ) AS message_attributes
          FROM    wf_messages msg
          WHERE   msg.type = ity.name ) AS messages         
,       CURSOR (
          SELECT  act.*
          FROM    wf_activities_vl act
          WHERE   act.item_type = ity.name
          AND     act.version   = (
                    SELECT  MAX(act2.version)
                    FROM    wf_activities_vl act2
                    WHERE   act2.item_type    = act.item_type
                    AND     act2.name         = act.name ) ) AS activities
FROM    wf_item_types ity
WHERE   ity.name = :p_item_type

27 Apr, 2009 by

E-Business Suite

Comments are now closed... Please contact us if you have any queries.

gilchrest@mailxu.com