Re: Does Oracle Have a Way to Store Historical Changes to Records?

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Fri, 05 Aug 2005 20:50:11 +0800
Message-ID: <42F36083.7C2C@yahoo.com>

Noons wrote:

> 
> > c) streams
> 
> streams?  Pray tell?

Here's a top to bottom taken from "Mastering PL/SQL"...

REM
REM Streams demo
REM conn / as sysdba

alter system set aq_tm_processes = 2;

pro
pro Must be ARCHIVELOG
pro

select log_mode from v$database;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE , DBA   TO streams IDENTIFIED BY streams;

ALTER USER streams DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

GRANT EXECUTE ON DBMS_APPLY_ADM        TO streams ;
GRANT EXECUTE ON DBMS_AQ               TO streams ;
GRANT EXECUTE ON DBMS_AQADM            TO streams ;
GRANT EXECUTE ON DBMS_CAPTURE_ADM      TO streams ;
GRANT EXECUTE ON DBMS_FLASHBACK        TO streams ;
GRANT EXECUTE ON DBMS_STREAMS_ADM      TO streams ;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM  TO streams ;

GRANT SELECT ON DBA_APPLY_ERROR TO streams ; GRANT EXECUTE ON DBMS_LOCK TO streams;
GRANT EXECUTE ON DBMS_PIPE to streams;

grant execute on dbms_flashback to scott;

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

    privilege    => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, 
    grantee      => 'streams', 
    grant_option => FALSE);

END;
/

BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(

    privilege    => DBMS_RULE_ADM.CREATE_RULE_OBJ, 
    grantee      => 'streams', 
    grant_option => FALSE);

END;
/

create table scott.audit_trail_1
(change_id number,
 command_type varchar2(1),
 table_name varchar2(30),
 user_name varchar2(30),
 change_date date);

create table scott.audit_trail_2

(change_id       number,
 column_name     varchar2(30),
 actual_data_new sys.anydata,
 actual_data_old sys.anydata);


GRANT ALL ON scott.audit_trail_1 TO streams;

GRANT ALL ON scott.audit_trail_2 TO streams; GRANT ALL ON scott.emp TO streams;

create sequence scott.data_audit_seq;
grant select on scott.data_audit_seq to streams;

create table scott.audit_temp ( scn number, scn_date date, scn_user varchar2(30));
grant all on scott.audit_temp to streams;

alter table scott.emp add supplemental log group log_group_emp_pk (empno) always;

conn streams/streams

begin
  dbms_streams_adm.set_up_queue(

   queue_table=>'streams.queue_table',
   queue_name=>'streams_queue',
   queue_user=>'streams');

end;
/

begin
  dbms_streams_adm.add_table_rules(
    table_name=>'SCOTT.EMP',
    streams_type=>'capture',
    streams_name=>'capture_emp');

  dbms_streams_adm.add_table_rules(
    table_name=>'SCOTT.EMP',
    streams_type=>'apply',
    streams_name=>'apply_emp');
end;
/

variable site1 varchar2(128);
variable scn number;

EXECUTE SELECT global_name INTO :site1 FROM global_name; EXECUTE :scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();

BEGIN
 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(source_object_name => 'scott.emp',

                                            source_database_name  =>
:site1,
                                            instantiation_scn  => :scn);

END;
/

create table T ( x varchar2(100));
create procedure P ( m varchar2) is
  pragma autonomous_transaction;
begin
  insert into T values (m) ; commit;
end;
/

create or replace
PROCEDURE dml_handler(in_any IN SYS.ANYDATA) IS

   lcr          SYS.LCR$_ROW_RECORD;
   rc           PLS_INTEGER;
   oldlist      SYS.LCR$_ROW_LIST;
   newlist      SYS.LCR$_ROW_LIST;
   command      varchar2(32);
   tname        varchar2(30);
   v_scn        number;
   v_user       varchar2(30);
   v_date       date;
   v_change_id  number;
   newdata      sys.AnyData;

BEGIN  

• Access the LCR rc := in_any.GETOBJECT(lcr); command := lcr.GET_COMMAND_TYPE(); tname := lcr.GET_OBJECT_NAME; v_scn := lcr.GET_SCN; oldlist := lcr.GET_VALUES('old'); newlist := lcr.get_values('new');
  • lookup the info that we associated with this scn
  • and insert into audit_trail_1 insert into scott.audit_trail_1 (change_id, command_type, table_name, user_name,change_date) select scott.data_audit_seq.nextval,substr(command,1,1), tname,scn_user,scn_date from scott.audit_temp where scn = v_scn;
  • write audit_trail_2 IF command = 'DELETE' then FOR i IN 1..oldlist.COUNT LOOP insert into scott.audit_trail_2 (change_id, column_name,actual_data_old) values (scott.data_audit_seq.currval, oldlist(i).column_name,oldlist(i).data); END LOOP; ELSIF command = 'INSERT' then FOR i IN 1..newlist.COUNT LOOP insert into scott.audit_trail_2 (change_id, column_name,actual_data_new) values (scott.data_audit_seq.currval, newlist(i).column_name,newlist(i).data); END LOOP; ELSIF command = 'UPDATE' then FOR i IN 1..oldlist.COUNT LOOP newdata := lcr.get_value('new',oldlist(i).column_name); if newdata is null then newdata := oldlist(i).data; end if; insert into scott.audit_trail_2 (change_id, column_name,actual_data_new,actual_data_old) values (scott.data_audit_seq.currval, oldlist(i).column_name,newdata,oldlist(i).data); END LOOP; END IF;

END;
/

begin
  dbms_apply_adm.set_dml_handler(
    object_name=>'SCOTT.EMP',
    object_type=>'TABLE',
    operation_name=>'UPDATE',
    error_handler=>false,
    user_procedure=>'streams.dml_handler',     apply_database_link=>NULL);
end;
/

create or replace trigger scott.empar
after delete or update or insert
on scott.emp
for each row
declare
  old_scn number;
begin
  old_scn := dbms_flashback.get_system_change_number;   insert into scott.audit_temp
  values (old_scn,sysdate,user);
end;
/

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'apply_emp');
END;
/

BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
    capture_name => 'capture_emp');
END;
/

conn scott/tiger

update scott.emp
set job = 'ENGINEER', deptno=10
where ename = 'SMITH';

commit;

rem
rem wait for a few seconds and then the records should appear rem

select * from scott.audit_trail_1;
select * from scott.audit_trail_2;

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------

Received on Fri Aug 05 2005 - 07:50:11 CDT