COLLECTION CHECKSUM

Hi,

I am working on development of Messaging Hub which will store the accounts and contacts data received from client system.We are storing Objects data as objects internally and storing their relations for teh account in a collection type column.We are calcualting checksum on individual contact details based on teh ascii values.But we are facing challenge while doing so for the collection .Is there any cleaner way by which we can calculate teh checksum of the collection.

Please advice.
Object to store contact_details ---
create or replace TYPE ACTOR_TYP AS OBJECT
(
  FULL_NAME  varchar2(100),
  FIRST_NAME varchar2(60),
  MIDDLENAME varchar2(1),
  LAST_NAME  varchar2(60),
  SALUTATION  varchar2(100),
  TITLE varchar2(40),
  PREFIX varchar2(10),
  SUFFIX varchar2(10),
  ADDR1 varchar2(60),
  ADDR2 varchar2(60),  
  ADDR3 varchar2(60),
  ADDR4 varchar2(60),
  CITY varchar2(40),
  STATE varchar2(40),
  COUNTRY varchar2(40),
  CITIZENCOUNTRY varchar2(40),
  ZIP_CODE  varchar2(20),
  PHONE   varchar2(20),
  MOBILE varchar2(20),
  FAX varchar2(20),
  EMAIL varchar2(100),
  CATEGORY VARCHAR2(20),
  CORRESPONDENCE VARCHAR2(20),
  CORRESPONDENCEVIAEMAIL VARCHAR2(20),
  CLIENT_ID VARCHAR2(100),
  CLIENTIDTYPE VARCHAR2(500),
  HONORIFIC VARCHAR2(20),
  BIRTHDATE DATE,
  RETIREDATE DATE,
  ADMINNAME VARCHAR2(100),
  ADMINPHONENO VARCHAR2(20),
  ADMINEMAIL VARCHAR2(100),
  STATECODE  VARCHAR2(20)
)  not final
 
TABLE for contact_details ----
  CREATE TABLE "MANAGEDACCOUNTS"."ACTORS" 
   (	"VERSION" NUMBER(6,0), 
	"META_ACTOR_ID" VARCHAR2(20 BYTE), 
	"ACTOR" "MANAGEDACCOUNTS"."ACTOR_TYP" , 
	"CHECKSUM" NUMBER, 
	"EXT_CONTACT_ID" VARCHAR2(20 BYTE)
   ) 
 
OBject and Table for contact relationship with account (A single account can have multiple contacts .
create or replace TYPE CTCTTYPE_TYP AS OBJECT
(
  meta_actor_id varchar2(20),
  TYPE varchar2(20),
  MAP MEMBER FUNCTION compare return varchar2
)  not final
 
create or replace TYPE BODY CTCTTYPE_TYP
AS
   MAP MEMBER FUNCTION compare RETURN varchar2
   IS
   BEGIN
      RETURN checksum(meta_actor_id||type);
   END Compare;
END;
 
 CREATE TABLE "MANAGEDACCOUNTS"."ACT" 
   (	"VERSION" NUMBER(6,0), 
	"META_ACCOUNT_ID" VARCHAR2(20 BYTE), 
	"ACT_TYPE" VARCHAR2(20 BYTE), 
	"META_ACT_ID" VARCHAR2(20 BYTE), 
	"ACTORS" "MANAGEDACCOUNTS"."ACTOR_ID_TYP" , -- This is a collection column on which checksum is required
	"ACT_CKSUM" NUMBER, 
	"EXT_ACCOUNT_ID" VARCHAR2(20 BYTE)
   )

Open in new window

LVL 3
mpaladuguAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
>> Is there any cleaner way by which we can calculate teh checksum of the collection.
What algorithm are you using for the checksum?
Why is this checksum required?
What is the current method of calculating the checksum?
0
mpaladuguAuthor Commented:
Hi Sujit,
Let me eloborate my question with some sample data,
We get data from a source system daily, i need to identify the changed data from pervious load and only insert the changed data into target system. i am using SCD type 2 technique to track changes, and to identify the changed data i am using checksums on each record.
i.e when i load a record first tme , i am calculating a check sum on the source record save it in  the target system, when i am doing the second day load i will again calculate the souce record check sum comapre against the saved checksum from the taget, if they match skip the record else inset the whole record with different version number.

Sample data:  Contact feed from source sytem.
Accout_ID         Contact_ID           Type                            Other Details..........................
Acct1                Cntc1                   Primary_Cntc              ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Acct1                Cntc2                   Secndary_Cntc          ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Acct2                Cntc3                   Primary_Cntc              ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Acct2                Cntc1                  Primary_Cntc               ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

target system have a differnt structure.....it has
Contacts table which saves all the distinct of contacts from the feed
i.e.......1.  Contact Table................(named actors in target system)
Contact_ID      Other details,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Cntc1              ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Cntc2              ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Cntc3              ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

2.    Relation Table...................(nmed as Act in the target system)
It specifies the relation b/w Conact, Account
Sample data:
Account_ID       Relation_Collection                                                      Checksum   version
Acct1                {[Cntc1, Primary_Cntc], [Cntc2, Secndary_Cntc]}       564564            1
Acct2                {[Cntc3, Primary_Cntc] , [Cntc1, Primary_Cntc]}          456456            1

so in the second day load or version 2 load..if there is a new contact added or its relatin changed, i want to comute checksum on the sorce records and compare it with the above checksum, so that i can insert a new record in the relation table for the same account with changed contact details as version 2.

Now what i want is.....how to calculate this checksum.
for other entities where there is a one-to-one source to target rows, i am using a function whcich takes a concatenated columns as string and returns me the checksum(32-bit CRC).

0
SujithData ArchitectCommented:
>>if they match skip the record else inset the whole record with different version number
From the description it looks like an SCD type 1.

If it is a TYpe 2:
- Why dont you go for a join with the keys to find the changed records instead of the checksum logic(I didnt really understand why checksums are requried here).
- Use a simple inner joins between the two tables to do the change data capture.
- It will be something like this.


YOu have source table
tbl1(col1, col2, col3, col4, col5....colN) - where col1 is the key and (col2, col3) are the attributes
target table
tbl2(col21, col22, col23) - where col21 is the key and (col22, col23) are the attributes

do this join for change data capture


select <col list>,
case when col22 is null and col23 is null then 'NEW'
     when col2 = col22  and col3 = col23  then 'NO CHANGE'
     else 'MODIFIED' end
from
tbl1 LEFT OUTER JOIN tbl2
ON tbl1.col1 = tbl2.col21
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.