Oracle objects version control on easiest way

While I was in Germany, we had MS Source safe as database version control system. In this post I do not want to explain this software but the way that software was handling versions-with the header of the file. Let me explain.
In each file that passed VSS version control, in second line (after initial naming) there was a line like:

/* $Header: xxx yyy datetime who $ */

where:

• xxx-name of the file which contained object
• yyy-current version of the file
• datetime-timestamp of the last change (formated as you like)
• who-user that made last change

In real example entry was like:

/* $Header: whoami.fnc 1.10 02/23/2012 19:13 damirv $ */

So with these data you can easily find out all information about the last version made. This easy implementation gave me a idea, to make my own version control (manually maintained of course) without any additional software.

The solution

Here is the code of simple function which I'll use for versioning.

CREATE OR REPLACE function whoami (
/* $Header: whoami.fnc 1.10 02/23/2012 19:13 damirv $ */

/*-------------------------------------------------------------------------------------------------------------------- 

           Copyright(C) 2011-2013 Vadas savjetovanje d.o.o.


 NAME    : whoami.fnc
 PURPOSE : Function return version according second line in dba_source, which is allways here version...
           Granted to public doesn't need any DBA involvement in version checking
           SQL Plus version control

 Date    : 05.08.2002.
 Author  : Damir Vadas, damir.vadas@gmail.com

 Remarks : Example select whoami
           select whoami ('ACTIONS_DIARY_PKG','PACKAGE BODY','TOOLS') from dual;

 Changes (DD.MM.YYYY, Name, CR/TR#):
          02.10.2009, Damir Vadas
                      default 'PACKAGE', USER
          23.02.2012, Damir Vadas
                      Added size information in result            
-------------------------------------------------------------------------------------------------------------------- */
                                   p_object_name in varchar2, 
                                   p_object_type in varchar2 default 'PACKAGE', 
                                   p_owner in varchar2 default USER,
                                   p_source_line IN INTEGER DEFAULT 2
   ) return varchar2
is
   v_result varchar2(4000 char);
   v_result2 varchar2(128 char);  
begin 
  if p_object_type not in ( 'TYPE', 'PROCEDURE','PACKAGE','PACKAGE BODY','TYPE BODY','TRIGGER','FUNCTION') then
    return 'Invalid object_type value-"'||p_object_type||'".'||chr(10)||'Valid values are: TYPE, PROCEDURE, PACKAGE, PACKAGE BODY, TYPE BODY, TRIGGER, FUNCTION';
  end if;
  select text into v_result from dba_source where owner = p_owner and name = p_object_name and type = p_object_type and line =p_source_line
  ;
  if length(v_result) =1 THEN
    v_result:= 'Not found correct "$Header" tag in second line for '||p_owner||'.'||p_object_name||' ('||p_object_type||')';
  end if;
  select SUM (source_size) into v_result2 from dba_object_size where owner=p_owner and name= p_object_name;     
  return v_result ||' ('||v_result2||' bytes)';
exception  
  when no_data_found then
    return 'Object not found -'||p_owner||'.'||p_object_name||' ('||p_object_type||')';
  when others then
    return SQLERRM;
end whoami
;
/

As you can see this function is reading second line of object and then return string and length of source code in bytes, what is additional checking.
Let me say whoami didn't cover view versioning because comment in view must be placed in special place which is not second line, even thought is possible with additional codding.
The best way to explain what I did is to make few self explained example:

SQL> select tools.whoami('WHOAMI','FUNCTION','TOOLS') from dual;

TOOLS.WHOAMI('WHOAMI','FUNCTION','TOOLS')
------------------------------------------------------------------------------------------------------
/* $Header: whoami.fnc 1.10 02/23/2012 19:13 damirv $ */
 (2316 bytes)

SQL> 

Function has a ability to report malfunctions in use (in a case of unsupported object):

SQL> select whoami ('ACTIONS_DIARY_PKG','PACKAGE_BODY','TOOLS') from dual;

WHOAMI('ACTIONS_DIARY_PKG','PACKAGE_BODY','TOOLS')
------------------------------------------------------------------------------------------------------
Invalid object_type value-"PACKAGE_BODY".
Valid values are: TYPE, PROCEDURE, PACKAGE, PACKAGE BODY, TYPE BODY, TRIGGER, FUNCTION, TYPE

And in a case of missing header line, beside warning you get length in bytes, what can be again used for comparison with same object on another schema/database.

SQL> select whoami ('ACTIONS_DIARY_PKG','PACKAGE BODY','TOOLS') from dual;

WHOAMI('ACTIONS_DIARY_PKG','PACKAGE BODY','TOOLS')
------------------------------------------------------------------------------------------------------
Not found correct "$Header" tag in second line for TOOLS.ACTIONS_DIARY_PKG (PACKAGE BODY) (18671 bytes)

So when you want to check some object version, just compare results from that output (suppose I'm on DEV database and want to compare function from PROD database). Here is the way how to accomplish compare result of WHOAMI function on both mentioned database.

select tools.whoami('WHOAMI','FUNCTION','TOOLS') from dual 
minus
select tools.whoami@PROD('WHOAMI','FUNCTION','TOOLS') from dual ;

The End

Let me remind, that it was 2001 when this solution come my mind. When there was no smart software that can compare schemas (like Toad and other) and that most of this tools today are not free. And even more with this solution you can very quickly initially see the differences for many objects in few seconds. Finding real differences between them my than be a task for many open source file compare utilities. The only thing is that you have to be tidy and any change, anote in the header of file.
With Oracle 10g, I think, introducing DBMS_CRYPTO package, objects could be compared even more faster and easier in a way, with exposed hash function:

SQL> WITH q
  2       AS (SELECT DBMS_METADATA.get_ddl ('FUNCTION',
  3                                         'WHOAMI',
  4                                         'TOOLS')
  5                    stmt
  6             FROM DUAL)
  7  SELECT SYS.DBMS_CRYPTO.HASH (q.stmt, 3)
  8    FROM q;

SYS.DBMS_CRYPTO.HASH(Q.STMT,3)
-----------------------------------------------------------------------------
436E7ED7A37674C3F8D2ED4777368B8BBB835F78

SQL>

And this may be added instead of number of bytes in WHOAMI result, if you are on 10g and onward versions. In this way your compare should be even more safer than looking for object source length, as exposed in mine whoami version. However, this doesn't cover version control but pure difference.

Hope this helps someone.

Cheers!