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;