Tracking DB Changes

                                                                                                    -SatyaPrakash

How to Track PL/SQL code Changes in a Database?

When I say how to track PL/SQL code changes, the first question that comes into our mind is why we need to track those changes? The answer follows as, during the development phase, developers will be modifying the existing source code as well as existing database objects (like procedures, functions, packages or triggers). Tracking source code changes can be done using any of the versioning systems. But if we want to track all the changes that have been made to the Database objects during a particular period, it will be very difficult and sometimes developer may forget what modifications he did. Also some times we will be modifying DB objects for testing purpose and forgets to revert the changes made. So now one can agree that tracking DB changes is very important. Let’s see how can we achieve this:

All the PL/SQL objects we write (procedures, functions, packages, triggers etc) will be stored in a table called USER_OBJECTS which will hold Object name, Object type and creation time etc.
And the PL/SQL code for these objects will be stored in ALL_SOURCE table. This table holds Object name, Object type and the PL/SQL code.

Now one can easily think of the solution for tracking DB changes.
It’s simple!!!

• Create a table which holds the modified code for all the DB objects.
• Write a schema level trigger on the current schema. This trigger is responsible for inserting old revisions of PL/SQL code into the new table (created in first step).

That’s it!!! Simple yet powerful!

Let’s look at the following example to understand better.

Example:

1. Create a table called PROJ_OBJECT_HISTORY with the following columns.

a. Change Date
b. Owner (schema name)
c. Name
d. Type
e. Line (line number of the PL/SQL code)
f. Text (PL/SQL code)

Except ‘Change Date’ column remaining will be present in ALL_SOURCE table.
So we can create the new table with this following DDL statement.

CREATE TABLE PROJ_OBJECT_HISTORY (
Change_date DATE,
Owner VARCHAR2(30),
Name VARCHAR2(30),
Type VARCHAR2(12),
Line NUMBER,
Text VARCHAR2(4000)
);

Now the table is ready to use.


2. Let us now create a trigger TRACK_DBOBJECTS_HIST that will populate the old revisions of the PL/SQL code into PROJ_OBJECT_HISTORY table.

CREATE OR REPLACE TRIGGER TRACK_DBOBJECTS_HIST
AFTER CREATE ON < SCHEMA_NAME>.SCHEMA
DECLARE
BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY',
'TYPE', 'TYPE BODY')--Mention all the Object types you wish to track.
THEN

INSERT INTO PROJ_OBJECT_HISTORY
SELECT sysdate, all_source.* FROM ALL_SOURCE
WHERE TYPE = ORA_DICT_OBJ_TYPE
AND NAME = ORA_DICT_OBJ_NAME;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;


If you want to log the user who made the changes, you can achieve this by creating one more column in the above mentioned table and adding the OSUSER (from v$session table) in the insert statement of the trigger.

Enable the trigger and start using the DB changes tracking service!!!