0 
0 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 ; /
Related posts:
Anydata and Collections Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases 
Aspect Oriented Programming (AOP) in PL/SQL – Is it hot? Is it cool? Or utterly useless? Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table
Lucas,
Thanks for posting this; I was able to use this information as a starting point to write my own (primitive but reasonably effective) set of queries.
Have you checked out sdd_version_associations? It has a date_changed column that *does* behave reasonably: it gets updated when an *existing* object is versioned. Between sdd_version_associations and I$SDD_OBJECT_VERSIONS I’m able to get a pretty good picture of versioning activity, without adding a trigger anywhere. Here’s a quick report of recent versioning of existing objects:
select trunc(date_changed), count(*) from sdd_version_associations
where date_changed >= trunc(sysdate – 3)
group by trunc(date_changed) order by trunc(date_changed) asc;
Also, you are probably aware of this already but I thought I’d mention the fact that Designer registers user/session information with v$session via the dbms_application_info package:
column username format a20
column module format a40
column action format a30
set linesize 99
–This works because Designer registers with v$session via the DBMS_APPLICATION_INFO package
select username, module, action from v$session where username is not null
order by username, module;
I have also found the following useful in reporting Designer activity (I use them in a LOOP):
jr_version.is_any_version_checked_out(v_irid)
jr_name.get_path_no_context(X.ivid, ‘MAIN’,’VLABELTYPE’,’/’,0);
One last thing: I’ve been using I$SDD_FOLDERS instead of CI_APPLICATION_SYSTEMS.
Referencing only the things I’ve posted here I’ve written a primitive yet effective set of “dashboard” queries:
1. “big picture” stuff: number of elements per folder, etc.
2. who’s logged into Designer and what they’re currently doing
3. Current checkouts (elements and containers)
4. Recently created objects
5. Recently versioned existing objects
I’ll post the whole thing if it’s of interest; if not I don’t want to clutter up your blog 🙂
Oh – any tips for reporting on deleted elements and containers?
*Thanks again* I feel so much more secure now, because I can tell what’s going on in my Repository without having to walk around and ask people!
My previous comments appears incorrect: new columns are not feasible as some INSERT statements in core SCM code do not list column names and will therefore fail with too few values provided!
So my alternative approach is to abuse column COMMENTS in table i$sdd_object_versions that does not seem to be used at all. Use this column to keep track of the most recent change to the object version:
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.2 10/02/2005 Lucas Replaced new columns with use of column COMMENTS;
note: new columns are not feasible as some INSERT statements in core SCM code do not
list column names and will therefore fail with too few values provided!
1.1 10/02/2005 Lucas Added support for new columns OBJ_DATE_CHANGED and OBJ_CHANGED_BY
1.0 9/28/2005 Lucas Created this trigger.
NOTES: This is an unsupported extension to Oracle 10g SCM
******************************************************************************/ BEGIN
:new.comments := to_char(SYSDATE,’DD-MM-YYYY HH24:MI:SS’)||USER;
END ;
/
This allows me to see the last time the object version state was changed (merge, check in) as well as the last time the underlying Designer object itself was modified. With the previously proposed solution, I could not have discerned between the check in date and the last modified date.
I am actually proposing to go one step further: Add columns obj_date_changed and obj_changed_by tp table i$sdd_object_versions. Then rewrite the above trigger as follows:
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.1 10/02/2005 Lucas Added support for new columns OBJ_DATE_CHANGED and OBJ_CHANGED_BY
1.0 9/28/2005 Lucas Created this trigger.
NOTES: This is an unsupported extension to Oracle 10g SCM
******************************************************************************/ BEGIN
:new.OBJ_DATE_CHANGED := SYSDATE;
:new.OBJ_CHANGED_BY := USER;
END ;
/
This allows me to see the last time the object version state was changed (merge, check in) as well as the last time the underlying Designer object itself was modified. With the previously proposed solution, I could not have discerned between the check in date and the last modified date.