PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving

28

Source code control for PL/SQL is often discussed in the organizations that I encounter. The database itself will always hold the latest version of the PL/SQL objects we are using or developing. The sources can of course also be held in files and those files can be managed in Source Code Control systems like CVS, Subversion, ClearCase, Visual Source Safe etc. that do version control.

Many tools for PL/SQL development like TOAD and PL/SQL Developer or even JDeveloper, do not readily integrate with such tools for PL/SQL code. Besides, most of these tools are used by developers directly on the PL/SQL source as it is held in the database. Developers do not write the code to file all the time and they certainly do not check in their code after each change they make. They compile any changes, sending the latest version of their code to be held in the database – overwriting the previous state of their PL/SQL object – and that is that. Perhaps after a number of compilations they may save to a file and at the end of the day or some large task they may check in to the Source Code Control system.

The potential issues with this way of working – and believe me, I have run into them on many occasions – is that developers often lose stages of their code that later on they want to retrieve.

For example: you start working in the morning on a source that you retrieve from the filesystem or source code control environment. You make changes, compiling them as you go along. At some point, you have a pretty good intermediate result. You
then continue with some intricate changes and you make some additional modifications and all of a sudden, you have messed up your code. And you would really like to return to that intermediate result. But: where is it? No longer in the database – overwritten by the next compilation. Not on the file system, as you forgot to save. And certainly not in the Source Code Control repository, as you did not check in.

Then there is the everpresent risk of two developers working on the same package at about the same time. Compilation by the second developer will immediately override and therefore discard any changes made by the first.

In this article I would like to outline a very simple mechanism for using the database itself as archive for all these intermediate versions. The essential pieces for this ‘working archive’ or operational repository: a table, a package and a trigger. The machinery is put into motion by a Oracle 9i and higher DDL Event trigger, fired on the CREATE (or compile) event.
The table PLSQL_ARCHIVE will hold earlier versions of the PL/SQL objects in the schema you are working on. For each previous version, it will
The table is set up to hold a number of values for each Version of PL/SQL Objects: the PL/SQL source, the timestamp for the compilation/creation, the status, size and errors and when provided by the developer through annotations in the PL/SQL source also the Author, the Version, a Branch, the Priority and possibly additional Comments. We define the table like this:

create table plsql_archive<br />( object_name   varchar2(128)<br />, object_type   varchar2(19)<br />, object_owner  varchar2(32)<br />, creation_time date<br />, status        varchar2(7)<br />, source        clob<br />, errors        clob<br />, author        varchar2(128)<br />, version_label varchar2(32)<br />, seq           number(10)<br />, branch        varchar2(32)<br />, source_size   number(10)<br />, priority      varchar2(32)<br />, comments      varchar2(4000)<br />, label         varchar2(4000) <br />)<br />/<br />

Whenever we compile – create or replace – a PL/SQL object, the CREATE DDL event is fired and we can intercept that event with our own trigger. At that point we have various pieces of information that we can use to insert a new record into the PLSQL_ARCHIVE table.

CREATE OR REPLACE TRIGGER plsql_archiver_trg<br />AFTER CREATE<br />ON SCHEMA<br />BEGIN<br />  if ora_dict_obj_type in ( 'PACKAGE', 'PACKAGE BODY', 'TRIGGER','FUNCTION', 'PROCEDURE')<br />  then<br />    plsql_archiver.archive_version<br />    ( p_object_name   =&gt; ora_dict_obj_name <br />    , p_object_type   =&gt; ora_dict_obj_type<br />    , p_object_owner  =&gt; ora_dict_obj_owner<br />    , p_creation_time =&gt; systimestamp<br />    );<br />  end if;<br />END plsql_archiver_trg;<br />/  <br />

We need a Nested Table Type defined in the server:

create type string_table as table of varchar2(4000)<br />/<br />

The package plsql_archiver:

CREATE OR REPLACE package plsql_archiver<br />as<br />procedure archive_version<br />( p_object_name   in  varchar2<br />, p_object_type   in  varchar2<br />, p_object_owner  in varchar2<br />, p_creation_time in date<br />);<br />--<br />procedure revert_to_previous_version -- undo last change; can be called repeatedly<br />( p_object_name   in varchar2<br />, p_object_type   in varchar2<br />, p_object_owner  in varchar2<br />, p_purge_latest  in varchar2 default 'Y' <br />);<br />--<br />procedure revert_to_version<br />( p_object_name   in varchar2<br />, p_object_type   in varchar2<br />, p_object_owner  in varchar2<br />, p_version_label in varchar2<br />, p_branch        in varchar2<br />, p_seq           in number<br />, p_purge_later   in varchar2 default 'N' <br />);<br />-- wildcards allowed<br />procedure purge<br />( p_object_name   in varchar2<br />, p_object_type   in varchar2<br />, p_object_owner  in varchar2 <br />, p_status        in varchar2 default null<br />, p_priority      in varchar2 default null<br />, p_from_datetime in date     default null<br />, p_to_datetime   in date     default null<br />, p_branch        in varchar2 default null<br />, p_seq_from      in number   default null<br />, p_seq_to        in number   default null<br />);<br />--<br />/* Not yet implemented:<br />-- wildcards allowed<br />-- this procedure will link all specified object versions to the label specified through p_label<br />procedure create_stripe<br />( p_label         in varchar2<br />, p_object_name   in varchar2 <br />, p_object_type   in varchar2<br />, p_object_owner  in varchar2 <br />, p_from_datetime in date     default null<br />, p_to_datetime   in date     default null<br />, p_branch        in varchar2 default null<br />, p_seq           in number   default null<br />);<br />*/<br />function version_graph<br />( p_object_name   in varchar2<br />, p_object_type   in varchar2<br />, p_object_owner  in varchar2 <br />, p_show_version_label in  varchar2 default 'Y' -- show the version label <br />, p_show_seq           in  varchar2 default 'N' -- show the seq value for each version (appended to the version label if that is requested too)<br />, p_show_datetime      in  varchar2 default 'N' -- display the timestamp of the creation of each version<br />, p_show_author        in  varchar2 default 'N' -- display the author of each version<br />, p_show_labels        in  varchar2 default 'N' -- display the labels or stripes a version is associated with<br />, p_show_status        in  varchar2 default 'N' -- display the status (VALID or INVALID) of the version<br />, p_show_comments      in  varchar2 default 'N' -- display the Comments for each version<br />) return string_table<br />;<br />end plsql_archiver;<br />/<br />

We will bother with the actual implementation of this package later on. Let’s first see how we can make use of what we have created thusfar:

  • Get an overview of all compilations in our schema, including timestamp and resulting status
  • Get the errors for a particular "version" of a PL/SQL object
  • Get the source at a particular moment in time, for example to compare with the current version
  • Revert to a specifc stage in the development of a PL/SQL object
  • Create a Stripe or Label, associating specific versions of all or at least many PL/SQL program units with what could be a release, a patch or whatever the stripe is to signify
  • Gather some analytical data on the PL/SQL development process: frequency of compilations, evolution of number of errors and size of the source
  • When the developer uses annotations to provide comments, priority, version label and branch we can even retrieve version graphs

Some of the information stored in the PLSQL_ARCHIVE table is inherently available at AFTER CREATE trigger time, such as the source, the errors, the object name, size and type and the compilation time. Others will have to be provided as annotations or meta-data in the source code, specified by the developer him or herself.

In the implementation of the PLSQL_ARCHIVER presented below, we make use of the following annotations, largely following the style used in JavaDoc and also adopted in PLDoc – an open source project for generation of technical documentation for PL/SQL code:

  • @author – for the author or developer of the (current version of the) PL/SQL unit
  • @version – the version label for the current instance of the PL/SQL object
  • @label – the label or stripe that this current instance of the PL/SQL object is associated with; examples of Labels are Releases, Patches, Versions of (reusable) Components etc.
  • @branch – an indication of the particular branch or subproject the PL/SQL unit is currently being reworked for; when not explicitly set, the MAIN branch is assumed. Examples of other branches are ADD_SECURITY (a subproject), 1.0PTCH2 (Patch 2 on Release 1.0) and 10gR2_PORT (the collection instances that make use of new Oracle 10gR2 PL/SQL features.
  • @priority – an indication of the extent of changes in this version compared to the previous version; typicaly values are Pico, Minor, Normal, Major
  • @comments – any additional description of this version of the object, for example the changes with respect to the previous version

The developer would include these keywords or annotations somewhere in a comment section in the PL/SQL object. For example:

create or replace procedure test_archiver<br />is<br />  l_t number;<br />  /****<br />  *  Annotations for the PLSQL_ARCHIVER framework<br />  *<br />  *  @author Lucas Jellema<br />  *  @version 1.2<br />  *  @comments Just a simple trial procedure to build some version history for<br />  *  @priority Normal<br />  *****/  <br />begin<br />  for i in 1..100 loop<br />    null;<br />    i=i+1;<br />  end loop;<br />end;<br />/<br />

Some simple examples:
To list the recent history of PL/SQL compilation:

select object_name<br />,      object_type<br />,      object_owner<br />,      source_size<br />,      creation_time <br />,      errors<br />from   plsql_archive<br />order<br />by     creation_time desc<br />/<br />

To find the most recent compilation errors:

select *<br />from   ( select errors<br />         from   plsql_archive<br />         order<br />         by     creation_time desc<br />       )<br />where  rownum =1 <br />/<br />

To undo the last "check in" or last compilation of an object:

begin<br />   plsql_archiver.revert_to_previous_version -- undo last change; can be called repeatedly<br />  ( p_object_name   =&gt; 'TEST_ARCHIVER'<br />  , p_object_type   =&gt; 'PROCEDURE'<br />  , p_object_owner  =&gt; USER<br />  , p_purge =&gt; 'Y' -- delete the most recently compiled code<br />  ) ;<br />end;<br />/<br />

To view the entire version tree for our TEST_ARCHIVER object:

set long 4000<br />set linesize 300<br />set pagesize 1000<br />select *<br />from   table ( plsql_archiver.version_graph<br />               ( 'TEST_ARCHIVER'<br />               , 'PROCEDURE'<br />               , USER <br />               , 'Y' -- version_label<br />               , 'Y' -- seq<br />               , 'Y' -- creation datetime<br />               , 'Y' -- author<br />               , 'Y' -- labels<br />               , 'Y' -- status<br />               , 'Y' -- comments<br />               )<br />             ) version_history<br />/<br />

It is probably wise to start with somewhat less information. Let’s say just the Version Label, the Sequence, Status and the Creation Date Time. The result for our TEST_ARCHIVER object looks something like:

select *<br />from   table ( plsql_archiver.version_graph<br />               ( 'TEST_ARCHIVER'<br />               , 'PROCEDURE'<br />               , USER<br />               , 'Y' -- version_label<br />               , 'Y' -- seq<br />               , 'Y' -- creation datetime<br />               , 'N' -- author<br />               , 'N' -- labels<br />               , 'Y' -- status<br />               , 'N' -- comments<br />               )<br />             ) version_history<br />/<br />COLUMN_VALUE<br />----------------------------------------------------------------------------------------------------<br />Version Graph for PROCEDURE TEST_ARCHIVER (schema AGS)<br />MAIN                                    Patch 3 Release 2.0                     ADD_SECUTIRY                            New_BRANCH<br />1.1 ()<br />09-OCT 21:28:33<br />    |<br />1.1 ()<br />09-OCT 21:28:43<br />    |<br />1.2 (1)<br />09-OCT 21:39:03<br />    |<br />1.2 (2)<br />09-OCT 21:40:21<br />    |<br />1.2 (3)<br />09-OCT 21:40:22<br />    |<br />1.2 (4)<br />09-OCT 21:40:23<br />    |<br />1.2 (5)<br />09-OCT 22:11:57                         1.2PTCH203_1.1 (1)<br />    |                                   09-OCT 22:11:59<br />                                            |<br />                                        1.2PTCH203_1.1 (2)<br />                                        09-OCT 22:12:11<br />                                            |<br />                                        1.2PTCH203_1.2 (1)<br />                                        09-OCT 22:12:20<br />                                            |<br />                                        1.2PTCH203_1.3 (1)<br />                                        09-OCT 22:12:21<br />                                            |<br />                                        1.2PTCH203_1.3 (2)<br />                                        09-OCT 22:14:14                         1.0SECU_1.0 (1)<br />                                            |                                   09-OCT 22:14:24<br />                                                                                    |<br />                                                                                1.0SECU_1.1 (1)<br />                                                                                09-OCT 22:14:34<br />                                                                                    |<br />                                                                                1.0SECU_1.2 (1)<br />                                                                                09-OCT 22:14:36<br />                                                                                    |<br />                                                                                1.0SECU_1.2 (2)<br />1.3 (1)                                                                         10-OCT 10:34:12<br />11-OCT 06:14:12                                                                     |                                   1.3NEW_BRANCH1.0 (1)<br />    |                                                                                                                   11-OCT 06:14:16<br />                                                                                                                            |<br />                                                                                                                        1.3NEW_BRANCH1.0 (2)<br />1.4 (1)                                                                                                                 11-OCT 06:14:56<br />11-OCT 06:33:27                                                                                                             |<br />    |<br />1.5 (1)<br />11-OCT 06:33:33<br />    |<br />1.6 (1) *<br />12-OCT 16:45:27<br />    |<br />

Note: the asterisk (*) indicates that the object version is INVALID or its PL/SQL code cannot be compiled!

Next Steps

Things I can envision for next steps with this revolutionary technology framework:

  • Include a Compare facility that can report the differences between two versions. If I feel really incredibly brave, I will do Merge as well. Actually, I think there must be Java based Compare and perhaps Merge components for Text; we could probably upload them as Java Stored Procedures and wrap them within PL/SQL wrappers
  • Provide a GUI for this database backend or repository
  • Provide a migration from CVS to my source code control system (or perhaps the other way round)
  • Ensure that a call to REVERT_TO_VERSION does not itself create a new version upon compilation (as it currently probably does).
  • Provide automatic version labeling: if I check in the next version after 1.4 the tool will call it 1.5 for me. It could even look at the priority level – major, minor, pico, normal – and decide to go from 1.4 to 1.4.1, 1.5, 2.0 or 1.4.0.1.
  • Add support for other database objects such as Tables and Views

Resources

Download the sources for this article: plsql_Archiver.zip 

Implementation of the PLSQL_ARCHIVER

The Package Body for PLSQL_ARCHIVER is on the second page of this article. You can also download it, with all SQL DDL scripts.

1 2
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

28 Comments

  1. Hi,
    Thanks for this great tool. When making a change to a PL/SQL package, the source column in the plsql_archive table combines the body and header of the package. So it is almost non-usable.
    Does anyone have a solution for this?

    Thanks.

  2. Martin Irigaray on

    Hi. I have a question. You are using the METADATA API, wich means that you can’t query a DDL from a object from another schema. In the documentation says we can use the SELECT_CATALOG_ROLE, but inside a package the roles are not loaded.
    Did you have any solution for this?. Thanks in advance.

  3. Thanks for the quick response Site Admin.
    It seems that the complete source code is not in the plsql_Archiver.zip file. But when I go to
    the second page to see the SQL DDL scripts I don’t have the download option. By just copy and paste the text doex not come out right (one huge line into my unix VI session)
    How to get the complete plsql_archive.f the PLSQL_ARCHIVER
    The Package Body for PLSQL_ARCHIVER is on the second page of this article. You can also download it, with all SQL DDL scripts.

  4. Hi Patrick;
    Could you please send me your code as well. I am not a developer and not very efficient in PL/SQL. our developers always ask DBA to restore code and I think this version control will save us lots of time.
    THANKS in advance.

  5. in your procedure there is a calling to get_errors and get_code, are these oracle built-in procedures or they are your own code?

    This is code is very useful if I can get it to work with the get_errors:)

    Thanks

  6. Dear Patrick, I am also interested on you Version Control Tool. would you please email me the code (minimi at yahoo)

  7. Dear Patrick Song, I am also interesting to to see your code for “Version Control tool covered collision control and source archive”. Can you please send me your code (brane.crcek@gmail.com)

  8. Dear Patrick Song, I am interestin to to see your codebut i can’t get your email
    can you please send me your code (nmokhayesh@gmail.com)

  9. Patrick Song on

    thanks for the great idea and the useful code(even though it has some problems), base on this, we developed a little Version Control tool covered collision control and source archive. if someone interests in this, please send a mail to me, I can mail the code to you!

  10. Thanks for the useful code,if anynews abouts this?and interesting to multiple developers,sofar ,if i can get conflict alerts if someone else has changed an object that I have also changed?i am looking forward to your replying.

  11. Interesting, but I don’t think this meets the real needs of real organizations working with multiple development DBs, etc.

    What we really need is a tool like “Tortoise SVN” that integrates with a database IDE like PL/SQL Developer or SQL Developer.

    I need to be able to do updates from Source Control, commit changed DB objects, and get conflict alerts if someone else has changed an object that I have also changed.

  12. Thank you very much, Lucas for your wonderful idea. I guess you are too busy to reply to other guys questions. For the dbms.metadata.set_transform_param , since I am using a 9iR2 DB I just comment out the fourth parameter, and it compiles OK.
    As for the get_errors function , I am thinking write my own implementation , just select the Text of the ERROR$ table providing the object_name and object_type .

    If anyone has better idea to make Lucas’s code working, pls let me know.

    Also I found a version control plug-in for PLSql Developer which we are using, http://www.allroundautomations.nl/

  13. Hi Lucas,

    You’ve had a couple of questions already regarding the dbms.metadata.set_transform_param. I seem to have the same problem with wrong number of arguments. How can this be fixed?

  14. Lucas,

    I like the concept. I am also interested in tracking changes to views. Any ideas?

  15. >> Another question is, in what versions does this work,

    Lucas’s code the calls to DBMS_METADATA.SET_TRANSFORM_PARAM
    have four parameters, which is the syntax specified in the 10g R2 docs. Whereas
    in 9.2 DBMS_METADATA.SET_TRANSFORM_PARAM can only take three
    parameters.

    Incidentally the package also uses a method GET_ERRORS. Is this also a 10g built-in
    or is it a piece of custom code, available for download?

    Cheers, APC

  16. Jeffrey Castro on

    Please provide the URL to download the DDL scripts. Because the posted source code has errors it even includes smiley faces. Another question is, in what versions does this work, I also get that DBMS_METADATA.SET_TRANSFORM_PARAM has not the right amount of parameters.

    Thanks

  17. Thanks, this is good stuff. I’m having trouble recording package compilation when the compile is done from OEM. It works fine from SQL Plus – even if I click “Show SQL” in OEM and copy/paste the generated code into SQL Plus, it works just great. I log in as the same user either way.

    Does anyone have any insight as to what my problem might be? I am excited to use this versioning system, but all our developers use OEM. :(

  18. For example, Quest software development tools like: SQL*Navigator (v5.0) and TOAD (8.x) does support integration with CVS.
    .
    We use the following logic for development: (1) we use a tool which creates a base version of database source code (synonyms, tables, indexes, packages, … in different files) that will be checked into CVS. (2) to create a development database, this source code will be installed into empty database (3) SQL*Navigator and TOAD integration with CVS does automatic check-out/check-in into CVS and all your development history will be seen in CVS.

  19. Instead of only using the @author keyword, we can also take advantage of some the values available from the USERENV context with SYS_CONTEXT(‘USERENV’, parameter name). For example: OS_USER, TERMINAL, CLIENT_IDENTIFIER and CLIENT_INFO could render useful information that can provude a default value for the Author.

  20. Great insight! I’m just setting up our own version control system and will be relying on Subversion and developers having their own Oracle Personal Edition running on their own workstation. This gives them the necessary isolation from the rest of the team. You’re article gave me the idea to trap the same trigger and write the new PL/SQL code automatically to their local working directory on their C: drive. It’s then up to the developer which version(s) to check back into Subversion. At least it saves them the time from extracting the PL/SQL code to a SQL file. The SQL file will be kept up-to-date automatically by the triggers, and the developer just has to check in his/her changes every once in a while.

  21. Stephan H. Wissel on

    Great stuff. Now (as you envisioned) the link to the “regular” versioning is needed. Typically SVN holds less atomic changes, since a developer submits only when (s)he deems the code fit (or to be memorized). A nightjob (??) that gets triggered on schedule (or demand) that dumbs the PL/SQL object source into a directory (using packages as subdirs) and the invoking SVN submit shouldn’t be too difficult (?) Love to read about it in a future blog of yours.
    But why bother to move it to SVN at all? Simply: there is more that PL/SQL in a normal project. So comitting back to SVN allows a project to be complete (PL/SQL, Server Code, Client Code, Doku etc) as well as the application of unit testing (e.g. create a new DB from scratch).
    :-) stw

  22. Thanks for the useful code.

    Some time ago, I created a code “lock manager” to prevent multiple developers modifying the same stored code
    at the same time. It used simple table (maintained by a trigger like yours) to tracked the last compile time
    of a piece of code, the IP address and the developer’s network login name. etc. As soon a developer modifed some
    stored code (“create or replace…”), they would get a “lock” on it. If another developer tried to modify the
    same object within say 24hrs, the trigger raised an exception like:

    XYZ is locked by USER1(123.11.22.33), you are (222.44.55.66).
    Unlock at http://svr1:7777/pls/dev1/lock_pkg.admin?p_objid=123
    This error is generated by code_admin.LOCK_TRG trigger

    Where the URL pointed to a simple mod_plsql web app. An “unlock” button “released” the lock and recorded
    who unlocked it. In the absence of an admin webpage, the trigger can still show the pl/sql to release the lock like:
    exec lock_pkg.admin(p_obj_id=>123, p_action=>’unlock’);

    It worked very well and had no manual checkin/checkout overhead or learning curve for developers.