How to do Incremental Load using PL/SQL

[orabalu]

Hi Guys,

Can you give me some examples for Incremental load in PL/SQL for Datawarehouse projects.

Regards,
Balu

[debasisdas]

Hi
orabalu
Welcome to TSDN.

You have reached the right place for knowledge shairing.

Here you will find a vast resource of related topics and code.

Feel free to post more doubts/questions in the forum.

But before that give a try from your side and if possible try to post what/how you have approached to solve the problem.

It will help Experts in the forum in solving/underestanding your problem in a better way.

Please follow the posting guidelines in every new post/reply.

Please specify your post/question clearly.

Dont expect others to guess and solve your problem.

Regards

Debasis

[toranjeet]

Hi,

There are some better and effective way to achieve Incremental Data Load using PL/SQL (if you have control over your sources)...
# Create the similar schema as in Source (that means if source has 100 tables that is going to be used to populate target, create similar table structures in this replica schema...you should add some audit columns into this new replica schema like: DML_Flag, Processed_Flag...etc.)
# This replica schema can be kept near by your data warehouse schema.
# Create After Insert/After Update Triggers on all the source tables, this will not affect your source system performance.
# This After Insert/After Update Triggers will push all the newly inserted record and updated record to the Replica Schema.
# Mark DML_Flag as 'I' for Insert records; 'U' for updated records; 'D' for deleted records (it can be a soft delete).
# Now based on the DML_Flag, take the record from this Replica Schema one-by-one.
# First take Insert records then Update and Delete etc.
# Update 'Processed_Flag' column to 'P' ('P' for processed) for corresponding table of Replica Schema simultaneously.
# You can do the Error Handling and Erroneous Data Handling here (if required I can discuss the error_log and erroneous data log mechanism as well using PL/SQL in a separate session)
# Please note that if you are doing error handling and erroneous data handling here, then you should mark even erroneous records too as 'P-Processed'. It is given that you fix the error, based on your error_log, directly into the Source system.....

Hope it certaily help you.

Thanks and Regards,
Ranjeet

[tabla]

Hi Balu,
You can use merge statement where u can put a Flag for new updated or inserted records and maintain track of updated records using trigger in history table.

Warm regards,
Tabla

[toranjeet]

Dear Tabla,

Could you please elaborate that how can you use merge for incremental data load... it will be really of great help if you can give one example that how to use the suggested update or insert flag while using merge...

thanks and rgds,
ranjeet

[tabla]

Hi balu,
Kindly find the example.
In step 1) Create tables --> the target table for incremental load(MERGE_TEST ) and one for history(MERGE_TEST_HIS)

Step 2) Create trigger to store data which will updat in next day load but already present in table.

step 3) In this cursor will have current data to be loaded. The merge statement will check whether the part_id from cursor is available or not if yes it will update the changes in MERGE_TEST tables and maintains history in MERGE_TEST_HIS table. If the current cursor is totally new then it will insert the as it is. The flag = Y in MERGE_TEST table will indicate that this records are updated and and trans_Date will give on which day load these records are updated.

step 4) select * from merge_Test; will give you total records with current history from the loading has started.

select * from MERGE_TEST_HIS will give the OLD value of updated records with trans_Date on which these records are loaded in MERGE_TEST.

/******step 1)*******************************/
CREATE TABLE MERGE_TEST
as
select /*+parallel(a 10)*/PART_ID, DATE_OF_BIRTH, SURNAME , sysdate trans_Date
From cp_partners a
where part_id > 3623099 and part_id < 3623200

alter table MERGE_TEST add flag varchar2(10);

create table MERGE_TEST_his as select * from MERGE_TEST where 1=2

/******step 2)*******************************/

create or replace trigger merge_Test_trig
AFTER update or delete on merge_test for each row
begin
if (:old.part_Id = :new.part_id) or (:old.DATE_OF_BIRTH <> :new.DATE_OF_BIRTH) or (:old.SURNAME <> :new.SURNAME)
then
insert into merge_test_his
values (:old.part_Id ,:old.DATE_OF_BIRTH ,:old.SURNAME,null,sysdate);
else
null;
end if;
end;

/******step 3)*******************************/

declare
cursor c1 is
select a.PART_ID, sysdate DATE_OF_BIRTH, a.SURNAME
from (select *
From cp_partners
where (part_id > 3623099 and part_id < 3623200)
or (part_id > 3623200 and part_id < 3623300)) a,(select * from MERGE_TEST) b
where a.part_id = b.part_id (+) ;

begin
for z in c1 loop
merge into MERGE_TEST
using dual
on (part_id =z.part_id)
when matched then
update
set DATE_OF_BIRTH = z.date_of_birth,flag = 'Y',trans_Date = sysdate+1
when not matched then
insert (PART_ID, DATE_OF_BIRTH, SURNAME,trans_Date)
values (z.PART_ID, z.DATE_OF_BIRTH, z.SURNAME, sysdate+1);

end loop;
end;

/******step 4)*******************************/
select * from merge_Test;

select * from merge_test_his;

Regards
Tabla