diff -r 31bd41f81ea7 src-db/database/model/excludeFilter.xml
--- a/src-db/database/model/excludeFilter.xml Mon Mar 27 10:52:11 2017 +0200
+++ b/src-db/database/model/excludeFilter.xml Mon Mar 27 12:41:10 2017 +0200
@@ -5,6 +5,7 @@
+
diff -r 31bd41f81ea7 src-db/database/model/prescript-Oracle.sql
--- a/src-db/database/model/prescript-Oracle.sql Mon Mar 27 10:52:11 2017 +0200
+++ b/src-db/database/model/prescript-Oracle.sql Mon Mar 27 12:41:10 2017 +0200
@@ -107,8 +107,18 @@
END GET_UUID;
/-- END
-create or replace
-FUNCTION AD_DB_MODIFIED(p_Update CHAR) RETURN CHAR
+
+declare
+ cnt int;
+begin
+ select count(*) into cnt from user_tables where table_name ='DB_CHECKSUMS';
+ if (cnt = 0) then
+ execute immediate 'create table db_checksums (obj_type varchar2(500),obj_name varchar2(500),old_value clob,old_crc varchar2(32),new_value clob,new_crc varchar2(32))';
+ end if;
+end;
+/-- END
+
+create or replace FUNCTION AD_DB_MODIFIED(p_Update CHAR) RETURN CHAR
AS
/*************************************************************************
@@ -133,17 +143,93 @@
v_Modified char(1);
TYPE RECORD IS REF CURSOR;
c1 RECORD;
- PRAGMA AUTONOMOUS_TRANSACTION; --To allow DML within a function in a select
+ PRAGMA AUTONOMOUS_TRANSACTION; --To allow DML within a function in a select
+ currentTrigger varchar2(60):=null;
+ currentTriggerTxt clob;
+ currentTriggerCrc varchar(32);
+ cnt int;
BEGIN
+ if p_update = 'Y' then
+ delete from db_checksums;
+ end if;
v_md5:='';
-for c1 in (select text from user_source where not (type = 'TRIGGER' and name like 'AU\_%' escape '\') order by name,line) loop
+
+for c1 in (select name, text from user_source where not (type = 'TRIGGER' and name like 'AU\_%' escape '\') order by name,line) loop
+ if currentTrigger is null then
+ currentTrigger := c1.name;
+ currentTriggerTxt := ' ';
+ currentTriggerCrc :=null;
+ end if;
+ if c1.name != currentTrigger then
+ if p_update = 'Y' then
+ insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+ else
+ select count(*) into cnt from db_checksums where obj_type = 'trg' and obj_name = currentTrigger;
+ if cnt = 0 then
+ insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+ else
+ update db_checksums
+ set new_value = currentTriggerTxt,
+ new_crc = v_md5
+ where obj_type = 'trg' and obj_name = currentTrigger;
+ end if;
+ end if;
+
+ currentTrigger := c1.name;
+ currentTriggerTxt := ' ';
+ end if;
+ currentTriggerTxt := currentTriggerTxt || c1.text;
+
v_md5 := dbms_obfuscation_toolkit.md5(input_string => v_md5||c1.text);
end loop;
-for c1 in (select * from user_tab_cols order by table_name, column_id) loop
+ if p_update = 'Y' then
+ insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+ else
+ select count(*) into cnt from db_checksums where obj_type = 'trg' and obj_name = currentTrigger;
+ if cnt = 0 then
+ insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('trg', currentTrigger, currentTriggerTxt, v_md5);
+ else
+ update db_checksums
+ set new_value = currentTriggerTxt,
+ new_crc = v_md5
+ where obj_type = 'trg' and obj_name = currentTrigger;
+ end if;
+ end if;
+
+
+for c1 in (select * from user_tab_cols where table_name!='DB_CHECKSUMS' order by table_name, column_id) loop
v_md5 := dbms_obfuscation_toolkit.md5(input_string => v_md5||c1.column_name||c1.data_type||c1.data_length||c1.nullable);
+ if p_update = 'Y' then
+ insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('col', c1.table_name||'.'||c1.column_name, c1.column_name||c1.data_type||c1.data_length||c1.nullable, v_md5);
+ else
+ select count(*) into cnt from db_checksums where obj_type = 'col' and obj_name = c1.table_name||'.'||c1.column_name;
+ if cnt = 0 then
+ insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('col', c1.table_name||'.'||c1.column_name, c1.column_name||c1.data_type||c1.data_length||c1.nullable, v_md5);
+ else
+ update db_checksums
+ set new_value = c1.column_name||c1.data_type||c1.data_length||c1.nullable,
+ new_crc = v_md5
+ where obj_type = 'col' and obj_name = c1.table_name||'.'||c1.column_name;
+ end if;
+ end if;
+
end loop;
for c1 in (select * from user_views order by view_name) loop
v_md5 := dbms_obfuscation_toolkit.md5(input_string => v_md5||c1.view_name||c1.text);
+
+ if p_update = 'Y' then
+ insert into db_checksums (obj_type, obj_name, old_value, old_crc) values ('view', c1.view_name, c1.view_name||c1.text, v_md5);
+ else
+ select count(*) into cnt from db_checksums where obj_type = 'view' and obj_name = c1.view_name;
+ if cnt = 0 then
+ insert into db_checksums (obj_type, obj_name, new_value, new_crc) values ('view', c1.view_name, c1.view_name||c1.text, v_md5);
+ else
+ update db_checksums
+ set new_value = c1.view_name||c1.text,
+ new_crc = v_md5
+ where obj_type = 'view' and obj_name = c1.view_name;
+ end if;
+ end if;
end loop;
@@ -157,20 +243,17 @@
else
v_Modified := 'Y';
end if;
- BEGIN
+
IF p_Update = 'Y' THEN
UPDATE AD_SYSTEM_INFO
SET LAST_DBUPDATE = NOW(),
DB_CHECKSUM = v_md5;
END IF;
- END;
COMMIT;
+
RETURN v_Modified;
- EXCEPTION
- WHEN OTHERS THEN
- RETURN 'N';
-END AD_DB_MODIFIED
-;
+
+END AD_DB_MODIFIED;
/-- END
BEGIN