Skip to Main Content

ORACLE DATABASE AUDITTING/ORACLE TABLE HISTORY

Syed MustafaMar 2 2022 — edited Mar 3 2022

Since I have been in oracle faced a lot of problem with auditing tables. Sometimes you have to make a table to record history and then create triggers on all tables to record history. Its good idea for the database where you have around 50 or 100 tables, but what if you have more than that like 500? Of course it’s a night mare for you and yes there will be repetition if you have any column changes on tables. I have come with a solution of Dynamic SQL to overcome on the problem. You just have to write one TABLE and a Package for that. A procedure will make all triggers for you guys!!!

So First Ceate A Table:

-- Create table
create table HRMS_HISTORY_LOG
(
column_name VARCHAR2(50),
new_value VARCHAR2(500),
old_value VARCHAR2(500),
user_name VARCHAR2(50),
entry_date DATE,
operation VARCHAR2(50),
session_id VARCHAR2(50),
table_name VARCHAR2(100),
page_id VARCHAR2(10)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);

session_id and page_id are usefull with oracle apex.

NOW CREATE PACKAGE:

---DEFINATION

CREATE OR REPLACE PACKAGE HISTORY_LOGS_PKG
AUTHID CURRENT_USER
AS

FUNCTION GENERATE_INSERT_LOG (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2)
return varchar2;

FUNCTION GET_HISTORY_TRIGGER (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2)
RETURN VARCHAR2;

PROCEDURE PRODUCE_HISTORY_TRIGGERS
(
V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2
) ;

END HISTORY_LOGS_PKG;

---BODY OF PACKAGE

CREATE OR REPLACE PACKAGE BODY HISTORY_LOGS_PKG
AS

FUNCTION GENERATE_INSERT_LOG (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2)
return varchar2 is
ALL_INSERT_LOGS varchar2(32767);
CURSOR C1 IS
(select '
INSERT INTO HRMS_HISTORY_LOG
(COLUMN_NAME, NEW_VALUE, OLD_VALUE, USER_NAME, ENTRY_DATE, OPERATION, SESSION_ID, TABLE_NAME, PAGE_ID)
'||
'values'||'('||''''||col.COLUMN_NAME||''''||',:NEW.'||col.COLUMN_NAME||',:OLD.'||col.COLUMN_NAME||',v_operation'||',v('||''''||'APP_USER'||''''||')'||',SYSDATE'||
',v('||''''||'APP_SESSION'||''''||')'
||','
||''''||col.TABLE_NAME||''''||',NV('||''''||'APP_PAGE_ID'||''''||')'||');'
AS A_QUERY
from all_tab_columns col
where col.TABLE_NAME=V_TABLE_NAME
and col.OWNER=SCHEMA_OWNER
and col.DATA_TYPE <> 'BLOB'
and col.COLUMN_NAME NOT IN ('PREPARED_BY','PREPARED_ON','UPDATED_ON','UPDATED_BY')
);
begin
FOR i IN C1
LOOP
ALL_INSERT_LOGS := ALL_INSERT_LOGS || i.A_QUERY;
END LOOP;
return(ALL_INSERT_LOGS);

END GENERATE_INSERT_LOG;

FUNCTION GET_HISTORY_TRIGGER (V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2 )
RETURN VARCHAR2 IS
V_SQL VARCHAR2(32767);
BEGIN

V_SQL:='CREATE OR REPLACE TRIGGER ' ||substr(V_TABLE_NAME,1,25) || '_LOGS
' ||
'BEFORE INSERT OR UPDATE OR DELETE ON ' ||V_TABLE_NAME ||
' FOR EACH ROW
DECLARE
v_operation varchar2(20);
BEGIN

IF (INSERTING OR UPDATING OR DELETING) THEN
IF INSERTING THEN
v_operation:='||''''||'INSERT'||''''||';
ELSIF UPDATING THEN
v_operation:='||''''||'UPDATE'||''''||';
ELSIF DELETING THEN
v_operation:='||''''||'DELETE'||''''||';
END IF;'
;

V_SQL:=V_SQL || HISTORY_LOGS_PKG.GENERATE_INSERT_LOG(V_TABLE_NAME,SCHEMA_OWNER);

V_SQL:=V_SQL|| 'END IF;
END;';
RETURN V_SQL;

END GET_HISTORY_TRIGGER;

PROCEDURE PRODUCE_HISTORY_TRIGGERS
(
V_TABLE_NAME VARCHAR2,
SCHEMA_OWNER VARCHAR2
)
IS
V_SQL varchar2(32767);

cursor cur IS
select c.OBJECT_NAME TABLE_NAME,c.OWNER from ALL_OBJECTS c
where c.OWNER=SCHEMA_OWNER

and c.OBJECT_NAME =NVL(UPPER(V_TABLE_NAME),c.OBJECT_NAME)
and c.object_type='TABLE'
ORDER BY c.OBJECT_NAME;

BEGIN

-----Start making Triggers---
FOR rec IN cur LOOP

V_SQL:= HISTORY_LOGS_PKG.GET_HISTORY_TRIGGER(UPPER(rec.table_name),rec.owner);

execute immediate V_SQL;

END LOOP;

END PRODUCE_HISTORY_TRIGGERS;

END HISTORY_LOGS_PKG;

Now run procedure as

BEGIN

HISTORY_LOGS_PKG.PRODUCE_HISTORY_TRIGGERS
(V_TABLE_NAME => :V_TABLE_NAME,
SCHEMA_OWNER => :SCHEMA_OWNER);

END;

Have FUN!

Comments
Post Details
Added on Mar 2 2022
0 comments
132 views