Forum Stats

  • 3,853,137 Users
  • 2,264,181 Discussions
  • 7,905,260 Comments

Discussions

ORACLE DATABASE AUDITTING/ORACLE TABLE HISTORY

Syed Mustafa
Syed Mustafa Member Posts: 2 Red Ribbon
edited Mar 3, 2022 5:55AM in Database Ideas - Ideas

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!

Tagged:
Syed MustafaReddappa Reddy Marumolla-OracleUser_06AZR
3 votes

Active · Last Updated