Mega Code Archive
Audit package
<![CDATA[
SQL>
SQL> create table audit_trail
2 ( username varchar2(30),
3 pk number,
4 attribute varchar2(30),
5 dataum varchar2(255),
6 timestamp date
7 )
8 /
Table created.
SQL>
SQL> create or replace package audit_trail_pkg
2 as
3 function record( p_pk in number,
4 p_attr in varchar2,
5 p_dataum in number ) return number;
6 function record( p_pk in number,
7 p_attr in varchar2,
8 p_dataum in varchar2 ) return varchar2;
9 function record( p_pk in number,
10 p_attr in varchar2,
11 p_dataum in date ) return date;
12 end;
13 /
Package created.
SQL>
SQL> create or replace package body audit_trail_pkg
2 as
3
4 procedure log( p_pk in number,
5 p_attr in varchar2,
6 p_dataum in varchar2 )
7 as
8 pragma autonomous_transaction;
9 begin
10 insert into audit_trail values
11 ( user, p_pk, p_attr, p_dataum, sysdate );
12 commit;
13 end;
14
15 function record( p_pk in number,
16 p_attr in varchar2,
17 p_dataum in number ) return number
18 is
19 begin
20 log( p_pk, p_attr, p_dataum );
21 return p_dataum;
22 end;
23
24 function record( p_pk in number,
25 p_attr in varchar2,
26 p_dataum in varchar2 ) return varchar2
27 is
28 begin
29 log( p_pk, p_attr, p_dataum );
30 return p_dataum;
31 end;
32
33 function record( p_pk in number,
34 p_attr in varchar2,
35 p_dataum in date ) return date
36 is
37 begin
38 log( p_pk, p_attr,
39 to_char(p_dataum,'dd-mon-yyyy hh24:mi:ss') );
40 return p_dataum;
41 end;
42
43 end;
44 /
Package body created.
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace view emp_v
2 as
3 select empno , ename, job,mgr,
4 audit_trail_pkg.record( empno, 'sal', sal ) sal,
5 audit_trail_pkg.record( empno, 'comm', comm ) comm,
6 audit_trail_pkg.record( empno, 'hiredate', hiredate ) hiredate,
7 deptno
8 from emp
9 /
View created.
SQL> select empno, ename, hiredate, sal, comm, job from emp_v where ename = 'KING';
EMPNO ENAME HIREDATE SAL COMM JOB
---------- ---------- --------- ---------- ---------- ---------
7839 KING 17-NOV-81 5000 PRESIDENT
SQL>
SQL> drop table audit_trail;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
]]>