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

28
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+1Email this to someoneShare on Tumblr0Buffer this page

The body of this package:

CREATE OR REPLACE package body plsql_archiver
as
-- forward declarations (implementation is at the end of the package body)
function get_code
( p_name in varchar2
) return clob
;
function get_errors
( p_name in varchar2
, p_type in varchar2
) return clob
;
--
procedure archive_version
( p_object_name in varchar2
, p_object_type in varchar2
, p_object_owner in varchar2
, p_creation_time in date
) is
l_code clob;
l_object_type varchar2(32):= p_object_type;
l_archive_rec plsql_archive%rowtype;
l_rowid rowid;
l_errors clob;
procedure debug
( p_txt in varchar2
) is
begin
dbms_output.put_line(substr(p_txt,1,255));
end;
-- this function tries to locate the specified keyword in the current code block; if the keyword is found,
-- the string following the keyword until the first newline character (chr(10)) is returned
function get_annotation
( p_keyword in varchar2 -- values include: version, author, branch, comments, priority
) return varchar2
is
l_pos number(5); -- position of the keyword, including the @ character
l_pos2 number(5); -- position of the first chr(10) following the keyword
l_return varchar2(4000);
begin
debug(p_keyword);
l_pos:= instr( l_code, '@'||p_keyword);
debug('pos of keyword in code '||l_pos);
if l_pos > 0
then
l_pos2:= instr( l_code, chr(10), l_pos, 1); -- find the first instance of chr(10), starting from position l_pos in the l_code block
debug( 'Position of chr(10) after keyword '||l_pos2);
if l_pos2 > 0
then
l_return:= ltrim(substr( l_code, l_pos + length(p_keyword)+2, l_pos2 - (l_pos + length(p_keyword)+2)));
debug('return value '||l_return);
end if;
end if;
return l_return;
end get_annotation;
begin
l_archive_rec.object_name:= p_object_name;
l_archive_rec.object_type:= p_object_type;
l_archive_rec.object_owner:= p_object_owner;
l_archive_rec.creation_time:= sysdate;
if l_object_type = 'PACKAGE BODY'
then
l_object_type:= 'PACKAGE';
-- make sure that dbms_metadata does return the package body
DBMS_METADATA.SET_TRANSFORM_PARAM
( transform_handle => dbms_metadata.SESSION_TRANSFORM
, name => 'BODY'
, value => true
, object_type => 'PACKAGE'
);
-- make sure that dbms_metadata does not return the package specification as well
DBMS_METADATA.SET_TRANSFORM_PARAM
( transform_handle => dbms_metadata.SESSION_TRANSFORM
, name => 'SPECIFICATION'
, value => false
, object_type => 'PACKAGE'
);
elsif l_object_type = 'PACKAGE'
then
-- make sure that dbms_metadata does return the package body
DBMS_METADATA.SET_TRANSFORM_PARAM
( transform_handle => dbms_metadata.SESSION_TRANSFORM
, name => 'BODY'
, value => false
, object_type => 'PACKAGE'
);
-- make sure that dbms_metadata does not return the package specification as well
DBMS_METADATA.SET_TRANSFORM_PARAM
( transform_handle => dbms_metadata.SESSION_TRANSFORM
, name => 'SPECIFICATION'
, value => true
, object_type => 'PACKAGE'
);
end if;
begin
l_code:= dbms_metadata.get_ddl(l_object_type, p_object_name, p_object_owner);
exception
when others
then
l_archive_rec.comments:= sqlerrm;
l_code:= get_code(p_object_name);
end;
l_archive_rec.source_size:= length(l_code);
l_errors:= get_errors( p_name => p_object_name, p_type => p_object_type);
l_archive_rec.source:= get_code( p_name => p_object_name);
l_archive_rec.errors:= l_errors;
begin
select status
into l_archive_rec.status
from all_objects
where object_name = p_object_name
and object_type = p_object_type
;
exception
when others
then
if l_errors is null or length(l_errors) <2
then
l_archive_rec.status:= 'VALID';
else
l_archive_rec.status:= 'INVALID';
end if;
end;
l_archive_rec.version_label:= get_annotation('version');
l_archive_rec.comments:= get_annotation('comments');
l_archive_rec.branch:= get_annotation('branch');
l_archive_rec.priority:= get_annotation('priority');
l_archive_rec.author:= get_annotation('author');
-- find the max seq for objects on the same branch with the same version label
-- this assumes that either no version labels are used at all or that every object has a version label
-- and it is only changed once in a while when a new meaningful stage has been reached for a particular object
select nvl(max(seq),0)+1
into l_archive_rec.seq
from plsql_archive
where object_name = l_archive_rec.object_name
and object_type = l_archive_rec.object_type
and object_owner = l_archive_rec.object_owner
and nvl(branch,'MAIN') = nvl(l_archive_rec.branch,'MAIN')
and nvl(version_label, 'x.y') = nvl(l_archive_rec.version_label, 'x.y')
;
insert into plsql_archive
values l_archive_rec
returning rowid into l_rowid;
end archive_version;

procedure revert_to_previous_version -- undo last change; can be called repeatedly
( p_object_name in varchar2
, p_object_type in varchar2
, p_object_owner in varchar2
, p_purge_latest in varchar2 default 'Y'
) is
l_source clob;
procedure debug
( p_txt in varchar2
) is
begin
dbms_output.put_line(substr(p_txt,1,255));
end;
begin
select source
into l_source
from ( select pae.source
, row_number() over (partition by object_name, object_type, object_owner
order by creation_time desc) rn
from plsql_archive pae
where object_name = p_object_name
and object_type = p_object_type
and object_owner = p_object_owner
) all_versions
where rn = 2
;
if p_purge_latest = 'Y'
then
-- delete last version from plsql_archive
delete
from plsql_archive
where rowid = ( select latest_version.rowid
from ( select pae.rowid
, row_number() over (partition by object_name, object_type, object_owner
order by creation_time desc) rn
from plsql_archive pae
where object_name = p_object_name
and object_type = p_object_type
and object_owner = p_object_owner
) latest_version
where rn = 1
)
;
end if;
debug(l_source);
-- create plsql object based on current source
execute immediate 'create or replace '||cast( l_source as varchar2);

end revert_to_previous_version;



procedure revert_to_version
( p_object_name in varchar2
, p_object_type in varchar2
, p_object_owner in varchar2
, p_version_label in varchar2
, p_branch in varchar2
, p_seq in number
, p_purge_later in varchar2 default 'N'
) is
l_source clob;
l_creation_time date;
procedure debug
( p_txt in varchar2
) is
begin
dbms_output.put_line(substr(p_txt,1,255));
end;
begin
select source
, creation_time
into l_source
, l_creation_time
from plsql_archive
where object_name = p_object_name
and object_type = p_object_type
and object_owner = p_object_owner
and nvl(version_label, 'X') = nvl(p_version_label,nvl(version_label, 'X'))
and nvl(branch, nvl(p_branch,'X')) = nvl(p_branch,nvl(branch,'X'))
and nvl(seq, 0) = nvl(p_seq,nvl(seq, 0))
;
if p_purge_later = 'Y'
then
-- delete last version from plsql_archive
delete
from plsql_archive
where object_name = p_object_name
and object_type = p_object_type
and object_owner = p_object_owner
and nvl(branch, nvl(p_branch,'X')) = nvl(p_branch,nvl(branch,'X'))
and creation_time > l_creation_time
;
end if;
debug(l_source);
-- create plsql object based on current source
execute immediate 'create or replace '||cast( l_source as varchar2);
end revert_to_version;

procedure purge
( p_object_name in varchar2
, p_object_type in varchar2
, p_object_owner in varchar2
, p_status in varchar2 default null
, p_priority in varchar2 default null
, p_from_datetime in date default null
, p_to_datetime in date default null
, p_branch in varchar2 default null
, p_seq_from in number default null
, p_seq_to in number default null
) is
begin
delete
from plsql_archive
where object_name like p_object_name
and object_type like p_object_type
and object_owner like p_object_owner
and nvl(branch, nvl(p_branch,'X')) like nvl(p_branch,nvl(branch,'X'))
and nvl(seq, 0) between nvl(p_seq_from,nvl(seq, 0)) and nvl(p_seq_to,nvl(seq, 0))
and creation_time between nvl(p_from_datetime,creation_time) and nvl(p_to_datetime,creation_time)
and nvl(priority, nvl(p_priority,'X')) like nvl(p_priority,nvl(priority,'X'))
and nvl(status, nvl(p_status,'X')) like nvl(p_status,nvl(status,'X'))
;
end purge;



function version_graph
( p_object_name in varchar2
, p_object_type in varchar2
, p_object_owner in varchar2
, p_show_version_label in varchar2 default 'Y' -- show the version label
, p_show_seq in varchar2 default 'N' -- show the seq value for each version (appended to the version label if that is requested too)
, p_show_datetime in varchar2 default 'N' -- display the timestamp of the creation of each version
, p_show_author in varchar2 default 'N' -- display the author of each version
, p_show_labels in varchar2 default 'N' -- display the labels or stripes a version is associated with
, p_show_status in varchar2 default 'N' -- display the status (VALID or INVALID) of the version
, p_show_comments in varchar2 default 'N' -- display the Comments for each version
) return string_table
is
l_graph string_table:= string_table('Version Graph for '||p_object_type||' '||p_object_name||' (schema '||p_object_owner||')');
l_line varchar2(32000);
/*
start with the MAIN branch, then the branch who entered the version history most recently
MAIN SECU PATCH2
1.0 1.0PTHC2_1.0
1.1
1.1SECU1.0
1.1SECU1.1 1.0PTHC2_1.1
1.2
1.2(2)
1.2(3)
*/
type branch_columns_type is table of string_table index by varchar2(32);
type version_history_type is table of plsql_archive%rowtype;
type branch_rec is record (branch varchar2(32), width number(4));
type branches_tbl_type is table of branch_rec index by binary_integer;
l_branches_tbl branches_tbl_type;
l_branch_columns branch_columns_type;
l_branch varchar2(32);
l_vh version_history_type;
idx number(5); -- index into l_vh collection
ctr number(6):=1;
l_next_branch varchar2(32);
l_vh_done boolean:= false;
l_more_on_branch boolean:= false;

function get_vh_tbl
( p_version in plsql_archive%rowtype
, p_column_width in number default 40
) return string_table
is
l_return string_table:= string_table();
l_comments_length number(5):= length(p_version.comments);

function ifThen
( p_test in boolean
, p_value in varchar2
) return varchar2
is
begin
if p_test then return p_value; else return ''; end if;
end ifThen;
begin
if p_show_version_label='Y' or p_show_seq='Y' or p_show_status='Y'
then
l_return.extend;
l_return(l_return.last):= p_version.version_label||' ('||p_version.seq||')'||ifThen(p_show_status='Y' and p_version.status ='INVALID',' *');
end if;
if p_show_author='Y'
then
l_return.extend;
l_return(l_return.last):= p_version.author;
end if;
if p_show_datetime='Y'
then
l_return.extend;
l_return(l_return.last):= to_char(p_version.creation_time,'DD-MON HH24:MI:SS');
end if;
if p_show_labels='Y' and length(nvl(p_version.label,''))> 0
then
l_return.extend;
l_return(l_return.last):= 'Labels: '||p_version.label;
end if;
if p_show_comments='Y' and l_comments_length>0
then
for i in 1..trunc(l_comments_length/40)+1 loop
l_return.extend;
l_return(l_return.last):= substr(p_version.comments, 1 + (i-1)*p_column_width, least(p_column_width, l_comments_length - (1 + (i-1)*p_column_width)));
end loop;
end if;
-- p_show_labels
l_return.extend;
l_return(l_return.last):= ' |';
return l_return;
end get_vh_tbl;
procedure add
( p_string in varchar2)
is
begin
l_graph.extend;
l_graph( l_graph.last) := p_string;
end add;
begin
-- get branches
for branch in (select distinct
nvl(branch, 'MAIN') branch
, first_value(creation_time) over (partition by branch order by creation_time) start_branch
, max(length(version_label)) over (partition by branch) longest_version_label
, max(length(to_char(seq))) over (partition by branch) longest_seq
from plsql_archive
order
by start_branch
) loop
l_branches_tbl(ctr).branch:= branch.branch ;
l_branches_tbl(ctr).width:= greatest( 40, branch.longest_version_label+branch.longest_seq+6, length(branch.branch)+3) ;
l_line:= l_line||rpad(l_branches_tbl(ctr).branch, l_branches_tbl(ctr).width, ' ');
ctr:= ctr+1;
end loop; -- branch
add(l_line);
select *
bulk collect into l_vh
from plsql_archive
where object_name = p_object_name
and object_type = p_object_type
and object_owner = p_object_owner
order
by creation_time
;



idx:= l_vh.first;
l_branch_columns(nvl(l_vh(idx).branch,'MAIN')):= get_vh_tbl(l_vh(idx));
loop
l_line:='';
-- loop over branches and for each branch column, if there is a line to write: write it!
l_more_on_branch:= false;
for i in 1..l_branches_tbl.count
loop
-- add('outside'||l_branches_tbl(i).branch);
if l_branch_columns.exists(l_branches_tbl(i).branch) and l_branch_columns(l_branches_tbl(i).branch).count > 0
then
-- add('inside'||l_branches_tb l(i).branch||' count '||l_branch_columns(l_branches_tbl(i).branch).count);
l_line:=l_line|| rpad(l_branch_columns(l_branches_tbl(i).branch)(l_branch_columns(l_branches_tbl(i).branch).first),l_branches_tbl(i).width,' ');
l_branch_columns(l_branches_tbl(i).branch).delete(l_branch_columns(l_branches_tbl(i).branch).first);
else
l_line:=l_line|| rpad(' ',l_branches_tbl(i).width,' ');

end if;
if l_branch_columns.exists(l_branches_tbl(i).branch) and l_branch_columns(l_branches_tbl(i).branch).count > 0
then
l_more_on_branch:= true;
end if;
--add('end of');
end loop;
add(l_line);
-- if there are more versions left to process
if l_vh.exists(l_vh.next(idx))
then
l_next_branch:= nvl(l_vh(l_vh.next(idx)).branch,'MAIN');
-- if there is room for another branch
if not l_branch_columns.exists(l_next_branch)
or l_branch_columns(l_next_branch).count = 0
or l_branch_columns( l_next_branch) is null
then
idx:= l_vh.next(idx);
l_branch_columns(l_next_branch) := get_vh_tbl(l_vh(idx), 40); -- instead of 40 here we should indicate the width for column associated with branch l_next_branch
l_more_on_branch:= true;
end if;
end if;
ctr:=ctr+1; -- failsafe, to not run infinitely in this loop
l_vh_done := l_vh.next(idx) is null;
exit when l_vh_done and not l_more_on_branch;
exit when ctr>20000;
end loop;
-- now we have to go through a couple of rounds to have all branch_columns completely wash out their stacks
return l_graph;
exception
when others
then add(sqlerrm||'ctr = '||ctr);
return l_graph;

end version_graph;
--
function get_code
( p_name in varchar2
) return clob
is
l_code clob:='';
begin
for src in (SELECT text FROM user_source WHERE name=p_name ORDER BY line) loop
l_code:= l_code||src.text;
end loop;
return l_code;
end get_code;
--
function get_errors
( p_name in varchar2
, p_type in varchar2
) return clob
is
-- based on the example in chapter 15 of Advanced Oracle PL/SQL - Programming with packages
last_line INTEGER := 0;
l_errors clob:='';
CURSOR err_cur
IS
SELECT line, text
FROM user_errors
WHERE name = UPPER (p_name)
AND type = UPPER (p_type)
ORDER BY line;
/* Local Modules */
procedure add
( p_text in varchar2
) is
begin
l_errors:= l_errors||p_text;
end add;
PROCEDURE err_put_line
(prefix_in IN VARCHAR2, text_in IN VARCHAR2)
IS
BEGIN
add
(RTRIM (RPAD (prefix_in, 8) || text_in, CHR(10)));
END;
--
PROCEDURE display_line (line_in IN INTEGER)
IS
CURSOR src_cur
IS
SELECT S.line, S.text
FROM user_source S
WHERE S.name = UPPER (p_name)
AND S.type = UPPER (p_type)
AND S.line = line_in;
src_rec src_cur%ROWTYPE;
BEGIN
OPEN src_cur;
FETCH src_cur INTO src_rec;
IF src_cur%FOUND
THEN
err_put_line (TO_CHAR (line_in), src_rec.text);
END IF;
CLOSE src_cur;
END;
--
PROCEDURE display_err (line_in IN INTEGER)
IS
CURSOR err_cur
IS
SELECT line, position, text
FROM user_errors
WHERE name = UPPER (p_name)
AND type = UPPER (p_type)
AND line = line_in;
err_rec err_cur%ROWTYPE;
BEGIN
OPEN err_cur;
FETCH err_cur INTO err_rec;
IF err_cur%FOUND
THEN
add ('ERR' || LPAD ('*', err_rec.position+5));
err_put_line ('ERR', err_rec.text);
END IF;
CLOSE err_cur;
END;
--
BEGIN
/* Main body of procedure. Loop through all error lines. */
FOR err_rec IN err_cur
LOOP
/* Show the surrounding code. */
FOR line_ind IN err_rec.line-2 .. err_rec.line+2
LOOP
IF last_line < line_ind
THEN
display_line (line_ind);
display_err (line_ind);
END IF;
last_line := GREATEST (last_line, line_ind);
END LOOP;
END LOOP;
dbms_output.put_line(substr(l_errors,1,255));
return l_errors;
END get_errors;
--
end plsql_archiver;
/
Share this on .. Tweet about this on TwitterShare on LinkedIn0Share on Facebook0Share on Google+1Email this to someoneShare on Tumblr0Buffer this page
1 2

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 & PL/SQL), Service Oriented Architecture, BPM, ADF, JavaScript, Java in various shapes and forms and many other things. Author of the Oracle Press books: Oracle SOA Suite 11g Handbook and Oracle SOA Suite 12c Handbook. Frequent presenter on conferences such as JavaOne and Oracle OpenWorld. 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.