[sql]– Change table example code
— by Jon Emmons
— www.lifeaftercoffee.com
— NOTE: This code is provided for educational purposes only! Use at your
— own risk. I have only used this code on Oracle 9.2 Enterprise Edition.
— Due to the way variables are handled, this should be run one command at
— a time, but must be run all in the same SQLPlus session.

— Connect as a priveleged user
conn system

— Create scott if he doesn’t already exist
create user scott identified by tiger
default tablespace users temporary tablespace temp
quota unlimited on users;

— Grant scott appropriate priveleges
grant connect to scott;
grant execute_catalog_role to scott;
grant select_catalog_role to scott;
grant create trigger to scott;

— Connect up as scott
conn scott/tiger

— Create Table
CREATE TABLE scott.classes
(
class_id NUMBER,
class_title VARCHAR2(30),
class_instructor VARCHAR2(30),
class_term_code VARCHAR2(6),
class_credits NUMBER,
CONSTRAINT PK_classes PRIMARY KEY (class_id )
);

— Load some data
insert into classes values
(100, ‘Reading’, ‘Jon’, ‘200510’, 3);

insert into classes values
(101, ‘Writing’, ‘Stacey’, ‘200510’, 4);

insert into classes values
(102, ‘Arithmetic’, ‘Laurianne’, ‘200530’, 3);

commit;

— Confirm current data
select * from classes;

— Set up the change table
exec dbms_logmnr_cdc_publish.create_change_table –
(‘scott’, ‘classes_ct’, ‘SYNC_SET’, ‘scott’, ‘classes’, –
‘class_id NUMBER, –
class_title VARCHAR2(30), –
class_instructor VARCHAR2(30), –
class_term_code VARCHAR2(6), –
class_credits NUMBER’, –
‘BOTH’, ‘Y’, ‘N’, ‘N’, ‘Y’, ‘N’, ‘Y’, ‘N’, NULL);

— Subscribe to the change table
variable subhandle NUMBER;

execute dbms_logmnr_cdc_subscribe.get_subscription_handle –
(CHANGE_SET => ‘SYNC_SET’, –
DESCRIPTION => ‘Changes to classes table’, –
SUBSCRIPTION_HANDLE => :subhandle);

execute dbms_logmnr_cdc_subscribe.subscribe –
(subscription_handle => :subhandle, –
source_schema => ‘scott’, –
source_table => ‘classes’, –
column_list => ‘class_id, class_title, class_instructor, class_term_code, class_credits’);

execute dbms_logmnr_cdc_subscribe.activate_subscription –
(SUBSCRIPTION_HANDLE => :subhandle);

— Now modify the table in a few different ways
update classes set class_title=’Math’ where class_id=102;

insert into classes values
(103, ‘Computers’, ‘Ken’, ‘200510’, 1);

insert into classes values
(104, ‘Racketball’, ‘Matt’, ‘200530’, 2);

update classes set class_credits=3 where class_id=103;

delete from classes where class_title=’Reading’;

commit;

— Confirm current data
select * from classes;

— Now lets check out the change table
variable viewname varchar2(40)

execute dbms_logmnr_cdc_subscribe.extend_window –
(subscription_handle => :subhandle);

execute dbms_logmnr_cdc_subscribe.prepare_subscriber_view –
(SUBSCRIPTION_HANDLE => :subhandle, –
SOURCE_SCHEMA => ‘scott’, –
SOURCE_TABLE => ‘classes’, –
VIEW_NAME => :viewname);

print viewname

— This little trick will move the bind variable :viewname into the
— substitution variable named subscribed_view
column myview new_value subscribed_view noprint
select :viewname myview from dual;

— Examine the actual change data. You could also look at the table in a
— browser such as TOAD for easier viewing.
select * from &subscribed_view;

— Close the subscriber view
execute dbms_logmnr_cdc_subscribe.drop_subscriber_view –
(SUBSCRIPTION_HANDLE => :subhandle, –
SOURCE_SCHEMA => ‘scott’, –
SOURCE_TABLE => ‘classes’);

— Purge the window
execute dbms_logmnr_cdc_subscribe.purge_window –
(subscription_handle => :subhandle);

— If done altogether, end the subscription
execute dbms_logmnr_cdc_subscribe.drop_subscription –
(subscription_handle => :subhandle);

— drop the change table
exec dbms_logmnr_cdc_publish.drop_change_table(‘scott’, ‘classes_ct’, ‘N’);

— Delete the table
drop table scott.classes;
[/sql]

4 Responses to “Oracle Change Data Capture”

  1. Life After Coffee » Oracle Change Tables - An Example Says:

    […] Here is an example of how to create and use Oracle Change tables. This is part of what Oracle refers to as “change data capture” or CDC. […]

  2. ME Says:

    EXCELLENT EXAMPLE! HELPED ME A LOT! THANKS!

  3. Mohamed Says:

    Brilliant!!! Thanks 🙂

  4. Vikas Says:

    when I am using your demo to create the change table I am getting below error.

    ERROR at line 1:
    ORA-00001: unique constraint (.) violated
    ORA-06512: at “SYS.DBMS_CDC_PUBLISH”, line 309
    ORA-06512: at “SYS.DBMS_CDC_PUBLISH”, line 619
    ORA-06512: at line 2

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>