0
0
During this post we will discuss the use of TRANSACTION Block with the STREAMS. A stream object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes. Consider a scenario where stream is defined on one of the tables. As per the need this single stream needs to be consumed in two different tables. A stream advances the offset only when it is use in a DML transaction. By default, when a DML statement executes, an autocommit transaction is implicitly start and the transaction gets commit at the completion of the statement.
To consume the same streams from multiple statements which are accessing the same change records. Surround them with an explicit transaction statement (BEGIN .. COMMIT). This locks the stream. Stream does not get update until the explicit transaction statement commits and the existing change data is consume.
Lets see the below technical implementation to cater the above requirement.
Create the below three tables:
Firstly, Table to hold the complete Student details .
create or replace table student_profile ( id number(8) not null, name varchar(255) default null, age number(3) null, location varchar(100), profession varchar(100) );
Secondly, Table Hold Demographic information only .
create or replace table stud_demographic ( id number(8) not null, name varchar(255) default null, age number(3) null, location varchar(100) );
To hold student profile only .
create or replace table stud_profile ( id number(8) not null, profession varchar(100) );
Create stream on student_profile table
create or replace stream student_profile_stream on table student_profile;
insert into student_profile (id,name,age,location,profession)
values
(1,'Joe',10,'Delhi','Engineer'),(2,'Jane',20,'Goa','Doctor'),(3,'George',20,'Pune','Lawyer'),
(4,'Betty',30,'Delhi','Scientist'),(5,'Sally',30,'Mumbai','Doctor');
Now Fetch the data from the Stream and insert into multiple tables:
begin;
insert into stud_demographic (id,name,age,location) select id,name,age,location from student_profile_stream where metadata$action = 'INSERT';
insert into stud_profile (id,profession) select id,profession from student_profile_stream where metadata$action = 'INSERT';
commit;
Verify the data into the tables and Stream:
