Saturday, November 13, 2010

Basic Team Coding Extension for Oracle

Our development group is complaining about override each other 's codes by recompiling the same PLSQL program unit that is actively in development with any other developer. TOAD has builtin Team Coding functionality but there are developers not using TOAD in the development department.

I developed a tiny Team Coding functionality works in the database with using a DDL trigger. By this BEFORE CREATE Trigger developers are not able to alter any program unit by without first checking it out. You can find a small example below.

Without first declaring in which schema you want Team Coding should work everything works as they should be.



connect SYSTEM/***@ORCL11G

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--FUNCTION FNC_TEST compiled



After declaring the schema you want to enable Team Coding.



exec PCK_TCODE_CONTROL.set_param('SCHEMA_NAME','SYSTEM','INSERT');
--anonymous block completed

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--ORA-20501: You are not allowed to compile the code! 
--           First PCK_TCODE_CONTROL.CHECKOUT the unit.

exec PCK_TCODE_CONTROL.checkout('FNC_TEST','SYSTEM');
--anonymous block completed

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;
--FUNCTION FNC_TEST compiled

connect u_epeker/***@ORCL11G

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--ORA-20500: Unit Locked by another user! 
--           Check PCK_TCODE_CONTROL.CHECK_AVAILIBILITY function

select PCK_TCODE_CONTROL.check_avail('FNC_TEST','SYSTEM') from dual;
/*
"PCK_TCODE_CONTROL.CHECK_AVAIL('FNC_TEST','SYSTEM')"
"TCODE: SYSTEM.FNC_TEST is checked out by SYSTEM on 13.11.2010 19:26:30"
*/

connect SYSTEM/***@ORCL11G

exec PCK_TCODE_CONTROL.checkin('FNC_TEST','SYSTEM');
--anonymous block completed

connect U_EPEKER/***@ORCL11G

select PCK_TCODE_CONTROL.check_avail('FNC_TEST','SYSTEM') from dual;
--null

exec PCK_TCODE_CONTROL.checkin('FNC_TEST','SYSTEM');
--anonymous block completed

select PCK_TCODE_CONTROL.check_avail('FNC_TEST','SYSTEM') from dual;
/*
"PCK_TCODE_CONTROL.CHECK_AVAIL('FNC_TEST','SYSTEM')"
"TCODE: SYSTEM.FNC_TEST is checked out by U_EPEKER on 13.11.2010 19:33:53"
*/

create or replace function SYSTEM.FNC_TEST return date as 
begin
return sysdate;
end;

--FUNCTION FNC_TEST compiled



As you can see people are not able to override their codes hence this functionality exists. You can add this functionality to you database by running the following script. This small script does not remember the versions of the program units. Anyone can develop version history functionality on top of this small script and repository or maybe I can handle this version history in another version.

You can find the script here