How to track column level value change in oracle by a generic trigger

How to track column level value change in oracle by a generic trigger
======================================================
======================================================


1. First create a audit table
=============================

CREATE TABLE audit_table
( TIMESTAMP DATE,
user_name VARCHAR2(30),
table_name VARCHAR2(30),
column_name VARCHAR2(30),
OLD_value VARCHAR2(2000),
NEW_value VARCHAR2(2000),
os_user VARCHAR2(2000),
machine VARCHAR2(2000),
program_name VARCHAR2(2000),
application_user VARCHAR2(2000),
action varchar2(200) default 'DElect'
)
/



2. Create package specification
===============================


CREATE OR REPLACE PACKAGE audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE
);

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER
);
END;
/

3. Create package Body
========================


CREATE OR REPLACE PACKAGE BODY audit_pkg
AS
PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN VARCHAR2,
l_old IN VARCHAR2
)
IS
t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;


IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;

PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN DATE,
l_old IN DATE
)
IS
t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;

IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;


PROCEDURE check_val (
l_tname IN VARCHAR2,
l_cname IN VARCHAR2,
l_new IN NUMBER,
l_old IN NUMBER
)
IS

t_sessionid VARCHAR2(200);
t_osuser VARCHAR2(200);
t_machine VARCHAR2(200);
t_program VARCHAR2(200);
BEGIN
---need to permession 'grant select any dictionary to user_name '
SELECT USERENV ('SESSIONID')
INTO t_sessionid
FROM DUAL;

SELECT osuser, machine, NVL (program, 'NULL')
INTO t_osuser, t_machine, t_program
FROM v$session
WHERE audsid = t_sessionid;

IF ( l_new <> l_old
OR (l_new IS NULL AND l_old IS NOT NULL)
OR (l_new IS NOT NULL AND l_old IS NULL)
)
THEN
INSERT INTO audit_table(TIMESTAMP, USER_NAME, TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE,
OS_USER, MACHINE, PROGRAM_NAME, APPLICATION_USER)
VALUES (SYSDATE, USER, UPPER (l_tname), UPPER (l_cname), l_old,l_new
,t_osuser, t_machine, t_program,t_sessionid);
END IF;
END;
END audit_pkg;


4. create trigger on specific table by runing this script
==========================================================


SQL> @create_trigger.sql
---------------------------------
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
set echo off
spool tmp.sql

prompt create or replace trigger aud#&&1
prompt after update or delete or insert on &&1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&&1')


/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp
-----------------------------------