Oracle Designer and Oracle SCM - Last Date Modified and querying in the ROB 20188367001

Oracle Designer and Oracle SCM – Last Date Modified and querying in the ROB

Today I decided to write a small package, an extension, to the Repository Object Browser for Oracle 10g (or 9i) Designer and Oracle 10g SCM. This extension will provide something like a Dashboard, that will show a quick overview of the most recent events in our Repository or Workarea, depending on the scope you select. Events are currently defined as: Check In, Check Out, Change of Object. You can regard this extension as a tailor made application of the functionality already offered by the Repository Search facility.

The Repository Search facility relies on the column DATE_CHANGED in the table I$SDD_OBJECT_VERSIONS to find all objects changed on or after a certain date – or before a certain date of course. However, I just noticed the following:

  • When the Primary Element is changed – for example an Entity or Module or Table Definition – this column is correctly updated, as is the column OBJ_NOTM (number of times modified)
  • When a Secondary Element is changed – for example an Attribute, Module Component or Constraint – this column is NOT updated. However, the column OBJ_NOTM (number of times modified) IS updated!

This means that for my ROB Dashboard I cannot rely on the Date Changed column. What’s more: the Repository Search in the ROB as it is shipped by Oracle has a flaw: if you ask for all objects changed before or after a certain date, you are pretty likely to receive an incorrect answer, as the DATE_CHANGED column used in this type of query is not maintained as you would expect it to be.

It would be very expensive by the way to make use of the DATE_CHANGED column in every individual Element Table to yield the correct answer. However, there is a very simple way to ensure that DATE_CHANGED in the table I$SDD_OBJECT_VERSIONS has the correct value: whenever column OBJ_NOTM is updated in this table, we should simply also update DATE_CHANGED. Et voila!

What we need therefore is a trigger:

CREATE OR REPLACE TRIGGER I$SDD_OBJECT_VERSIONS_NOTM
BEFORE UPDATE
OF OBJ_NOTM
ON I$SDD_OBJECT_VERSIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/******************************************************************************
   NAME:       I$SDD_OBJECT_VERSIONS_NOTM
   PURPOSE:    Ensure that DATE_CHANGED is updated whenever OBJ_NOTM is updated
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        9/28/2005 Lucas         1. Created this trigger.
   NOTES: This is an unsupported extension to Oracle 10g SCM
******************************************************************************/
BEGIN
  :new.DATE_CHANGED := SYSDATE;
END ;
/

3 Comments

  1. Peter Wenker March 23, 2006
  2. Lucas October 2, 2005
  3. Lucas October 2, 2005