Auto-backups of PLSQL source

I saw this on an ideas forum today

image

and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository.

So the idea has some merit… and maybe we can roll our own without too much fuss. I make no claims that this is a ‘complete’ solution, but it should get you going Smile



SQL> set timing off
SQL> drop trigger plsql_trigger;

Trigger dropped.

SQL>
SQL> drop table plsql_params purge;

Table dropped.

SQL> drop table plsql_history cascade constraints purge;

Table dropped.

SQL> drop table plsql_history_source cascade constraints  purge;

Table dropped.

SQL> drop table plsql_log cascade constraints  purge;

Table dropped.

SQL>
SQL>
SQL> create table plsql_params
  2  as select 3 versions_kept from dual;

Table created.

SQL>
SQL> create table plsql_history (
  2     TSTAMP     TIMESTAMP
  3    ,OWNER      VARCHAR2(128)
  4    ,NAME       VARCHAR2(128)
  5    ,TYPE       VARCHAR2(12)
  6    ,constraint plsql_history_pk primary key ( tstamp,owner,name,type)
  7  )
  8  organization index;

Table created.

SQL>
SQL> create table plsql_history_source (
  2     TSTAMP     TIMESTAMP
  3    ,OWNER      VARCHAR2(128)
  4    ,NAME       VARCHAR2(128)
  5    ,TYPE       VARCHAR2(12)
  6    ,LINE       NUMBER
  7    ,TEXT       VARCHAR2(4000)
  8    ,constraint plsql_history_source_pk primary key ( tstamp,owner,name,type,line)
  9    ,constraint plsql_history_source_fk foreign key ( tstamp,owner,name,type) references plsql_history ( tstamp,owner,name,type )
 10  );

Table created.

SQL>
SQL> create table plsql_log
  2  (
  3     TSTAMP     TIMESTAMP
  4    ,MSG        varchar2(1000)
  5  );

Table created.

SQL>
SQL>
SQL> create or replace trigger plsql_trigger
  2  before create on SCHEMA
  3  declare
  4    l_owner   varchar2(128) := ora_dict_obj_owner;
  5    l_name    varchar2(128) := ora_dict_obj_name;
  6    l_type    varchar2(128) := ora_dict_obj_type;
  7    l_archived timestamp := systimestamp;
  8    l_tstamp_to_clear timestamp;
  9
 10    procedure logger(m varchar2) is
 11      pragma autonomous_transaction;
 12    begin
 13      insert into plsql_log values (systimestamp,m);
 14      commit;
 15    end;
 16  begin
 17    if l_type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY') then
 18       insert into plsql_history values (l_archived, l_owner, l_name, l_type);
 19
 20       insert into plsql_history_source
 21       select l_archived, owner, name, type, line, text
 22       from   dba_source
 23       where  owner = l_owner
 24       and    name = l_name
 25       and    type = l_type;
 26
 27       logger('Archived '||l_type||'-'||l_owner||'.'||l_name);
 28
 29       select max(case when tot > versions_kept and seq = tot - versions_kept then tstamp end)
 30       into   l_tstamp_to_clear
 31       from   plsql_params,
 32              ( select ph.*,
 33                       row_number() over ( order by tstamp ) as seq,
 34                       count(*) over () as tot
 35                from plsql_history ph
 36              )
 37       where  owner = l_owner
 38       and    name = l_name
 39       and    type = l_type;
 40
 41       if l_tstamp_to_clear is not null then
 42         logger('Clearance timestamp for '||l_type||'-'||l_owner||'.'||l_name||' is '||l_tstamp_to_clear);
 43
 44         delete from plsql_history_source where tstamp <= l_tstamp_to_clear;
 45         delete from plsql_history        where tstamp <= l_tstamp_to_clear;  46         logger('Cleared '||sql%rowcount||' versions for '||l_type||'-'||l_owner||'.'||l_name);  47       end if;  48    end if;  49  end;  50  / Trigger created. SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 1
  4  end;
  5  /

Procedure created.

SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 2
  4  end;
  5  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 3
  4  end;
  5  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 4
  4  end;
  5  /

Procedure created.

SQL>
SQL>
SQL> create or replace procedure P_TEST is
  2  begin
  3    null; -- version 5
  4  end;
  5  /

Procedure created.

SQL> select * from plsql_history;

TSTAMP                             OWNER        NAME         TYPE
---------------------------------- ------------ ------------ ---------------
02-FEB-16 12.12.42.280000 PM       MCDONAC      P_TEST       PROCEDURE
02-FEB-16 12.12.42.306000 PM       MCDONAC      P_TEST       PROCEDURE
02-FEB-16 12.12.42.339000 PM       MCDONAC      P_TEST       PROCEDURE

SQL>
SQL> select tstamp, text from plsql_history_source;

TSTAMP                             TEXT
---------------------------------- ------------------------------------------------------------
02-FEB-16 12.12.42.280000 PM       procedure P_TEST is
02-FEB-16 12.12.42.280000 PM       begin
02-FEB-16 12.12.42.280000 PM         null; -- version 2
02-FEB-16 12.12.42.280000 PM       end;
02-FEB-16 12.12.42.306000 PM       procedure P_TEST is
02-FEB-16 12.12.42.306000 PM       begin
02-FEB-16 12.12.42.306000 PM         null; -- version 3
02-FEB-16 12.12.42.306000 PM       end;
02-FEB-16 12.12.42.339000 PM       procedure P_TEST is
02-FEB-16 12.12.42.339000 PM       begin
02-FEB-16 12.12.42.339000 PM         null; -- version 4
02-FEB-16 12.12.42.339000 PM       end;

12 rows selected.

SQL>
SQL> select * from plsql_log;

TSTAMP                             MSG
---------------------------------- --------------------------------------------------------------------------------
02-FEB-16 12.12.42.221000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.255000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.281000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.307000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.307000 PM       Clearance timestamp for PROCEDURE-MCDONAC.P_TEST is 02-FEB-16 12.12.42.181000 PM
02-FEB-16 12.12.42.313000 PM       Cleared 1 versions for PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.340000 PM       Archived PROCEDURE-MCDONAC.P_TEST
02-FEB-16 12.12.42.340000 PM       Clearance timestamp for PROCEDURE-MCDONAC.P_TEST is 02-FEB-16 12.12.42.254000 PM
02-FEB-16 12.12.42.340000 PM       Cleared 1 versions for PROCEDURE-MCDONAC.P_TEST

9 rows selected.

4 thoughts on “Auto-backups of PLSQL source

  1. We did something similar using a DDL trigger which logged the user, the time, the object name and the ddl into a table (ddl stored in a clob). Very useful when developers lose changes or when we are trying to find out who created or modified an object.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s