Oracle version control and change control products? Oracle Database Tips by Donald Burleson

Question:  Are there tools for managing a PL/SQL change control and version control system?  I want to track versions and also track who changed a PL/SQL stored procedure and when the PL/SQL was changed.

Answer:  There are a variety of third-party Oracle change control and version control tools

• GIT is a great tool for software configuration management (SCM) and version control.  Git is the distributed source control management (SCM) system originally written by Linus Torvalds and used for the Linux Kernel.
  
  

• Open source tools such as SCCS (Source Code Control System) for Oracle.  This is superseded by the Gnu CSSC version control software.
   

• OEM change control pack from Oracle
   

• Oracle's SQL Developer also has an interface for third-party change control and version control.
   

• Merant PVCS (Serena) version control for Oracle
   

• Mercury ITG ( Formally Kintana) for Oracle revision, change control and Oracle version control.

Many folks use open-source tools such as the Source Code Control System (SCCS), which has a check-in, check-out routine.  Oracle's SQL Developer also has an interface for change control and version control:

"With 1.1, users can now access these files from their version control systems," Harper said. "They open them up in SQL Developer, and then they can edit those files and return them to their version control system."

Oracle also has SCM (Software control Manager) that has version control for repository objects with check-in, check out.  The version control is enabled within SCM in the Repository Administration Utility, where you choose "Options > Enable Version Support".

Also, Kishore Pagadala has published this code snippet for tracking PL/SQL code changes:

-- Create history table
CREATE TABLE SOURCE_HIST
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;

-- Store code in hist table
CREATE OR REPLACE TRIGGER change_hist
AFTER CREATE ON SCOTT.SCHEMA
-- Change SCOTT to your schema name

DECLARE
BEGIN
IF DICTIONARY_OBJ_TYPE
IN ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') THEN

-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.*
FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/

 

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.