Building Check In protection into Oracle Designer/Oracle SCM – Check In only by the user that did the Check Out

One of the peculiarities – that word once took me three minutes to pronounce more or less correctly during a workshop in Melbourne, Australia and I vowed never to use it again – of Oracle Designer’s versioning functionality is the Shared Workarea. In a Shared Workarea, all users are equal. That even goes so far to mean that after any user in the workarea has checked out an object, that object is available to the whole workarea commune: every user – whether or not he is the one that did the check out of it – can manipulate the object. At the same time, every user in the workarea can checkin that object.

This is different from most version control or source code control systems, where the Checked Out object is available only to the user who did the check out and where the Check In must be performed by the same user that did the check out. It turns out to be relatively simple to add such ‘protection’ to Oracle Designer – although of course highly unsupported. I will show you the code for implementing the check that Check In must be done by the same user that did the check out.

I am relying on the fact that a Check In is technically speaking just an update of the I$SDD_OBJECT_VERSIONS table in the Repository. I can intercept that update event using a very normal After Row Update Trigger. In this trigger, I can compare the current user doing the check in with the user that did the checkout for the version being checked in. Finally I am making use of one of Oracle SCM’s standard messages, to present a somewhat nice message to the end user:

The code required to implement this check is shown below. It can of course be embellished – to only take effect for specific workareas or users, to be overridable through specials tags in the check in notes or for super users, to show nicer messages. You can also implement similar checks to enforce a minimum amount of check in notes, a certain format for the version label etc. You can also implement a before row trigger that derives the version label.

Note: adding triggers like the one shown below is not a supported operation on your Oracle Designer environment. If you are experiencing any problems, please disable or remove the trigger and try again. Only if the problem persists in the absence of the trigger should you contact Oracle Support.

CREATE OR REPLACE
TRIGGER CHECKIN_PROTECTOR
AFTER UPDATE
ON I$SDD_OBJECT_VERSIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
    -- cursor for check in protection
    -- to select the user who checked out
    -- and the check out date
    cursor c_co
         ( b_ivid in number
         ) is
     SELECT created_by
     ,      va.date_created
     FROM   i$SDD_VERSION_ASSOCIATIONS va
     WHERE  va.to_ivid = b_ivid
     ;
    r_co c_co%rowtype;
  begin
    if :new.state ='I' -- a check in is attempted
    then
      open  c_co( b_ivid => :new.ivid);
      fetch c_co
      into  r_co;
      close c_co;
      -- now r_co contains both the check out user and date of the version that
      -- must be checked in
      if r_co.created_by <> USER -- current user is not the user who performed the check out
      then
        Rmmes.post
        ( 'CDR'
        , 1012
        , ' This object was not checked out by you. You can only check in an object version if you are the one who checked it out.'
        ||' This version was checked out by '||r_co.created_by
        ||' on '||to_char(r_co.date_created, 'DD-MM-YYYY')
        ||'. It can only be checked in by that user. It not '
        );
        RAISE jr_version.CHECK_IN_ERROR;
     end if;
   end if;
END ;
/

A second example: here I create a trigger that enforces the rule that objects can only be changed by the user that checked it out. Every change that is saved in Oracle Designer causes an update of the column OBJ_NOTM (Number of Times Modified) in table I$SDD_OBJECT_VERSIONS. That update can be intercepted with an UPDATE trigger. In this trigger, we compare the current USER with the user that Checked Out the object being modified. If the two are different, a fatal error is raised. Note: you can try to find the best error message number for this issue. I have used number 7 here, that results in: Message CDA-00007: API message instantiation error: no message text found for Sorry but you cannot update this object. It has been checked out by SCM and updates can only be made by that user.. Clearly not ideal, but clear enough I would say.

CREATE OR REPLACE TRIGGER COMMIT_CHANGE_CHECKER
BEFORE UPDATE OF OBJ_NOTM
ON I$SDD_OBJECT_VERSIONS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
    -- cursor to select the user who checked out
    -- and the check out date
    cursor c_co
         ( b_ivid in number
         ) is
     SELECT created_by
     ,      va.date_created
     FROM   i$SDD_VERSION_ASSOCIATIONS va
     WHERE  va.to_ivid = b_ivid
	 ;
    r_co c_co%rowtype;
begin
  open  c_co( b_ivid => :new.ivid);
  fetch c_co
  into  r_co;
  close c_co;
  -- now r_co contains both the check out user and date of the version that is being updated
  if r_co.created_by <> USER -- current user updating the object is not the user who performed the check out
  then
    ciierr.fatal(7,'Sorry but you cannot update this object. It has been checked out by '||r_co.created_by||' and updates can only be made by that user.');
  end if;
END ;
/