Oracle Designer – Keyword Expansion upon Check Out

A little while ago I wrote the article Oracle Designer Check In – synchronize P_VERSION argument on this weblog. In it, I discuss the desirability of having Oracle Designer apply the version label of a Module that it receives during Check In to the Module Argument p_revision. This parameter is used in the About Window of the Application, to show to the end user what the version is of the Form he is working with.

Now I have realized that you want the same thing to happen during Check Out of the Module: if you start making changes to the checked out module and you generate it again, it will still display the same version label it had when it was checked in. That is clearly wrong, as the Form is now a different one. So the challenge discussed in this article is: how to have Oracle Designer apply the value of the Version-Label-after-Checkout to the module’s parameter.
Well actually, that is quite simple. Unfortunately, just like with the Check In Version Label application, we cannot do it from a Trigger on i$sdd_object_versions -which would have been preferable as it would have minimized the direct changes of the core Oracle Designer product. Performing our little action in the trigger would give rise to the Mutating Table trigger error, as we would update the Module that is currently being checked in or out (either update or insert into i$sdd_object_versions) which in turn will attempt to update the i$sdd_object_versions.obj_notm column which would fail since the record in i$sdd_object_versions is currently being updated or inserted.

So instead, we will look for the best place in the standard JR_VERSION package, to make this little change to the Check Out logic. It turns out we need to the function check_out_sub. In this function, we add our call to the procedure apply_version_label that we introduced in the previous post.

The code we need to insert into this JR_VERSION.check_out_sub function:

   ...
   --Create a new, checked out version of the object
   new_ivid := new_version(i_irid,i_ivid,i_vlabel,i_version_dependencies);
   IF new_ivid IS NULL THEN
    --Failed to create new version
    RAISE CHECK_OUT_ERROR;
   END IF;

   -- inserted my own line of code
   for ov in (select vlabel from   i$sdd_object_versions where  ivid = new_ivid) loop
    apply_version_label( p_ivid => new_ivid, p_vlabel => ov.vlabel);
   end loop;
   -- end of my own line of code

   -- Update the version info I$SDD_OBJECT_VERSIONS to correct values
   IF l_lock THEN
    l_lock_flag:='Y';
   ELSE
   ...

The procedure apply_version_label:

create or replace
procedure apply_version_label
( p_ivid  in number
, p_vlabel in varchar2
) is
begin
    -- if it is a module
-- and the module has an argument called p_revision
-- then assign the value of vlabel to that argument
for ov in (select ov.logical_type_id from i$sdd_object_versions ov where ov.ivid = p_ivid) loop
  if ov.logical_type_id = 4908 -- GEM
  then
    update ci_module_arguments arg
    set    default_value = p_vlabel
    where  arg.parent_ivid = p_ivid
    and    arg.name = 'P_REVISION'
    ;
  end if;
end loop;
end;