Oracle Designer Check In – synchronize P_VERSION argument

As you can probably tell from the topics and nature of my blog-postings recently, I am very deepy involved in a project using classical Oracle development technology such as Oracle 10g Designer, WebForms 10g, Headstart, CDM RuleFrame etc. We have set up a rather interesting environment and process for our version control, configuration management, incident management etc. At the heart of the infrastructure, you will find CVS, JIRA (for incident management) and Oracle SCM for version control of Designer objects, utPLSQL for unit testing of PL/SQL objects such as Business Rules and Triggers.

One of the things somewhat lacking right now is the automatic synchronization of the P_REVISION argument that we have in all of our modules: ideally, when we check in a module and assign the new version label to it, the value of this argument is updated (just prior to the check in). When the checked in module is now generated, the value of P_REVISION, that can be seen by the end user in the Help – About window, is equal to the version label assigned during the check in.

I have delved in my past – back to the time when I was working on utilities for Oracle Designer 6i as it was called back then, one of which was the Keyword Expansion utility. I have reused some of the ideas I had then and improved on them.

With just a little very simple PL/SQL code, we can achieve my goal: have a Check In of a Module synchronize the value of the P_REVISION argument.

The Designer package responsible for Check In operations is JR_VERSION. Unfortunately, we cannot achieve our objective without making a change – if only a simple one – to this package. I have to add the following line in the FUNCTION check_in_at_tip , just prior to the update of I$SDD_OBJECT_VERSIONS

      ...
DELETE FROM I$SDD_VERSION_ASSOCIATIONS WHERE irid=i_irid AND from_ivid=curr_tip_ivid AND edge_kind='T';
    END IF;

	-- inserted my own line of code
	apply_version_label( p_ivid => l_ivid, p_vlabel => new_vlabel);
	-- end of my own line of code
    --update the info in I$SDD_OBJECT_VERSIONS
    UPDATE I$SDD_OBJECT_VERSIONS
       SET VLABEL=new_vlabel

   ...

The procedure APPLY_VERSION_LABEL itself is astonishingly simple:

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;

Now when I check in a module, assigning a new version label to it, the default value property of the P_REVISION is set to that new version label.