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
Comments are now closed... Please contact us if you have any queries.