0 0
Read Time:2 Minute, 6 Second

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');

 

Stream output
Table_Output

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;

Transaction Block

Verify the data into the tables and Stream:

Stu_Dempgraphic
Stu_Profile
Stream_Output

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *