Oracle CDC (Change Data Capture) with Materialized View Log

With materialized view log we can capture changed data in a table. Below there is simple implementation of this.

Assume that we have table that has structure below.
CREATE TABLE customer (
    cust_id NUMBER,
    cust_name VARCHAR2(50),
    cust_type NUMBER,
    birth_date DATE
);

First we should create materialized view log on this table.
CREATE MATERIALIZED VIEW LOG ON customer WITH
    ROWID, SEQUENCE (cust_id, cust_name, cust_type, birth_date)
    INCLUDING NEW VALUES

After this a table named MLOG$_customer will be automatically created. And every dml statement on customer table will be captured in materialized log table.
Lets execute som dml statements.
INSERT INTO customer(cust_id, cust_name, cust_type, birth_date)
     VALUES (1,'John', 1, TO_DATE ('25/02/1978', 'DD/MM/YYYY'))

INSERT INTO customer(cust_id, cust_name, cust_type, birth_date)
     VALUES (2,'Gabriel', 5, TO_DATE ('25/02/1985', 'DD/MM/YYYY'))

UPDATE customer
   SET cust_type = 7,
       birth_date = TO_DATE ('25/02/1982', 'DD/MM/YYYY')
 WHERE cust_id = 1


DELETE FROM customer
      WHERE cust_id = 2

We can easily see changing records in materialized view log table by querying tables below.
select * from customer
select * from MLOG$_customer


And finally with writing the below statement we can determine dml statements according to execution order.
SELECT   a1.sequence$$, a1.dmltype$$, a1.cust_id, a1.cust_name, a1.cust_type,
         a1.birth_date, a2.cust_id cust_id_n, a2.cust_name cust_name_n,
         a2.cust_type cust_type_n, a2.birth_date birth_date_n
    FROM mlog$_customer a1, mlog$_customer a2
   WHERE a1.dmltype$$ = 'U'
     AND a1.old_new$$ = 'U'
     AND a2.dmltype$$ = 'U'
     AND a2.old_new$$ = 'N'
     AND a1.m_row$$ = a2.m_row$$
    AND a1.sequence$$ + 1 = a2.sequence$$
UNION
SELECT   a1.sequence$$, a1.dmltype$$, a1.cust_id, a1.cust_name, a1.cust_type,
         a1.birth_date, NULL, NULL, NULL, NULL
    FROM mlog$_customer a1
   WHERE dmltype$$ = 'I'
UNION
SELECT   a1.sequence$$, a1.dmltype$$, a1.cust_id, a1.cust_name, a1.cust_type,
         a1.birth_date, NULL, NULL, NULL, NULL
    FROM mlog$_customer a1
   WHERE dmltype$$ = 'D'
ORDER BY sequence$$

You can use this method to transport data from an oltp system to a staging area(Datawarehouse).
Materealized view log uses triggers to feed log table.