Source Save

 Save the Source Code and Table and Index Structures using PL/SQL

After having gone through some papers on this subject, the need for a robust system has to be developed plugging all the holes, is felt and the result is this.

The basic idea and the first paper that has been seen is that of TOM, the great.

I also have consulted him on some aspects of this small database I have in my mind and he gave valuable suggestions. I am proud to acknowledge them.

Schema Owners
(1) SOURCE_SAFE owns the tables that hold the data.
(2) SECUITY owns the stored procedures and triggers that generate and insert data into those tables.

create table source_code_versions (
username varchar2 (30),
os_user varchar2 (50),
terminal varchar2 (16),
version number,
date_of_change date,
line number,
name varchar2 (30),
owner varchar2 (30),
text varchar2 (4000),
type varchar2 (12))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage (
initial 65536
minextents 1
maxextents 2147483645
)
nocache;

grant delete on source_code_versions to security;
grant insert on source_code_versions to security;
grant select on source_code_versions to security;
grant update on source_code_versions to security;

create table all_views_code_versions (
username varchar2 (30) default user,
os_user varchar2 (30),
terminal varchar2 (16),
version number,
date_of_change date default sysdate,
owner varchar2 (30) not null,
view_name varchar2 (30) not null,
text_length number,
text varchar2 (4000))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage (
initial 65536
minextents 1
maxextents 2147483645
)
nocache;

grant delete on all_views_code_versions to security;
grant insert on all_views_code_versions to security;
grant select on all_views_code_versions to security;
grant update on all_views_code_versions to security;

create table all_tab_col_verions (
username varchar2 (30),
os_user varchar2 (30),
terminal varchar2 (16),
version number,
date_of_change date,
owner varchar2 (30),
table_name varchar2 (30),
column_name varchar2 (30),
data_type varchar2 (106),
data_length number,
data_precision number,
data_scale number,
nullable varchar2 (1),
column_id number,
tablespace_name varchar2 (32))
tablespace system
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage (
initial 65536
minextents 1
maxextents 2147483645
freelists 1 freelist groups 1 )
nocache;

grant delete on all_tab_col_verions to security;
grant insert on all_tab_col_verions to security;
grant select on all_tab_col_verions to security;
grant update on all_tab_col_verions to security

create table all_ind_col_vesrions (
username varchar2 (30) default user,
os_user varchar2 (30),
terminal varchar2 (16),
version number,
date_of_change date default sysdate,
index_owner varchar2 (30),
index_name varchar2 (30),
table_owner varchar2 (30),
table_name varchar2 (30),
column_name varchar2 (30),
column_position number,
tablespace_name varchar2 (32))
tablespace users
pctfree 10
initrans 1
maxtrans 255
storage (
initial 65536
minextents 1
maxextents 2147483645
)
nocache;

grant delete on all_ind_col_vesrions to security;
grant insert on all_ind_col_vesrions to security;
grant select on all_ind_col_vesrions to security;
grant update on all_ind_col_vesrions to security;

CREATE OR REPLACE procedure SECURITY.sp_all_ind_col as

v_version number;
v_tablespace_name dba_segments.tablespace_name%type;

begin

select nvl(max(version),0)
into v_version
from source_safe.ALL_ind_col_vesrions
;

v_version:=v_version+1;

if ora_dict_obj_type = 'INDEX' then

select tablespace_name
into v_tablespace_name
from dba_segments
where segment_name=ora_dict_obj_name
and segment_type='INDEX'
and owner=ora_dict_obj_owner;

insert into source_safe.ALL_ind_col_vesrions
select USER,
a.OSUSER,
TERMINAL,
v_VERSION,
sysdate ,
INDEX_OWNER ,
INDEX_NAME ,
TABLE_OWNER ,
TABLE_NAME ,
COLUMN_NAME ,
COLUMN_POSITION,
v_tablespace_name
from v$session a,
sys.dba_ind_columns b
where b.index_NAME=ora_dict_obj_name
and b.index_OWNER=ora_dict_obj_owner
and a.sid=(select sid from v$mystat where rownum=1);
end if;

end;
/

CREATE OR REPLACE procedure SECURITY.sp_all_tab_col as
v_version number;
v_tablespace_name dba_segments.tablespace_name%type;

begin

select nvl(max(version),0)
into v_version
from source_safe.ALL_TAB_COL_VERIONS
;

v_version:=v_version+1;

if ora_dict_obj_type = 'TABLE' then

select tablespace_name
into v_tablespace_name
from dba_segments
where segment_name=ora_dict_obj_name
and segment_type='TABLE'
and owner=ora_dict_obj_owner;

insert into source_safe.ALL_TAB_COL_VERIONS
select username,
osuser,
terminal,
v_version,
sysdate,
OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
NULLABLE,
COLUMN_ID,
v_tablespace_name
from v$session a,
sys.dba_tab_cols b
where b.TABLE_NAME=ora_dict_obj_name
and b.OWNER=ora_dict_obj_owner
and a.sid=(select sid from v$mystat where rownum=1);
end if;

end;
/
CREATE OR REPLACE procedure SECURITY.sp_create_all_view_version as
v_version number;
v_text dba_views.TEXT%type;

begin

select nvl(max(version),0)
into v_version
from source_safe.all_views_code_versions
;

v_version:=v_version+1;

if ora_dict_obj_type = 'VIEW'
THEN

SELECT text
INTO v_text
FROM dba_views
WHERE view_name = ora_dict_obj_name
and owner=ora_dict_obj_owner;

insert into source_safe.all_views_code_versions
( USERNAME,
OS_USER,
TERMINAL,
VERSION,
DATE_OF_CHANGE,
OWNER,
VIEW_NAME,
TEXT_LENGTH,
TEXT
)
select user,
a.osuser,
a.terminal,
v_version,
sysdate,
b.OWNER,
b.VIEW_NAME,
b.TEXT_LENGTH,
v_text
from v$session a,
dba_views b
where a.sid = (select sid from v$mystat where rownum=1)
and b.owner = ora_dict_obj_owner
and b.view_name = ora_dict_obj_name;
end if;
end;
/

CREATE OR REPLACE procedure SECURITY.sp_source_safe_all_source as

v_version number;

begin

select nvl(max(version),0)
into v_version
from source_safe.ALL_TAB_COL_VERIONS
;

v_version:=v_version+1;

if (ora_dict_obj_type in
('PCKAGE','PACKEGE BODY','PROCEDURE','FUNCTION')) THEN
insert into source_safe.source_code_versions
select s.USERNAME,
s.OSUSER,
s.TERMINAL,
v_version,
sysdate,
so.LINE,
so.NAME,
so.OWNER,
so.TEXT,
so.TYPE
from sys.dba_source so,
sys.V_$SESSION s
where so.name=ora_dict_obj_name
and so.type=ora_dict_obj_type
and s.sid = (select sid from v$mystat where rownum=1);
end if;

end;
/
CREATE OR REPLACE TRIGGER security.after_create_index_trg
after create on database
begin
begin
sp_all_ind_col;
end;
end;
/
CREATE OR REPLACE TRIGGER security.after_create_tab_trg
after create on database
begin
begin
sp_all_tab_col;
end;
end;
/
CREATE OR REPLACE TRIGGER security.all_ind_col_trg
before alter or drop on database
begin
begin
sp_all_ind_col;
end;
end;
/
CREATE OR REPLACE TRIGGER security.all_tab_col_trg
before alter or drop on database
begin
begin
sp_all_tab_col;
end;
end;
/
CREATE OR REPLACE TRIGGER security.create_all_source_code_trigger
before create or drop on database
declare
begin
security.sp_source_safe_all_source;
end;
/
CREATE OR REPLACE TRIGGER SECURITY.create_all_view_trigger
before create or drop on database
begin
begin
SECURITY.sp_create_all_view_version;
end;
end;
/