Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How the apex_collection.add_member generates the MD5 checksum?

PaoloMJun 29 2007 — edited Jun 29 2007

Hello,
I hope somebody can help me understand how the apex_collection.add_member function is calculating the MD5 checksum when the parameter p_generate_md5 is set to YES.

For example, if I add several items as a collection member and I generate its MD5 checksum, I get a certain MD5 sum, but if I run the wwv_flow_item.md5() function passing to it the single item values I used in the collection, I am not able to regenerate the MD5 checksum from the collection. Here is my example process:

DECLARE
  l_cksum VARCHAR2(32767);
  l_seq_id NUMBER(15);

  CURSOR C_data
  IS
    SELECT * FROM EMP
     WHERE ENAME = 'MARTIN';

  Curr_row C_data%ROWTYPE;
BEGIN
  -- fetch data
  OPEN C_data;
  FETCH C_data INTO Curr_row;
  CLOSE C_data;

  -- init fields
  APEX_UTIL.SET_SESSION_STATE('P1_EMPNO', Curr_row.EMPNO);
  APEX_UTIL.SET_SESSION_STATE('P1_ENAME', Curr_row.ENAME);

  -- create a collection
  apex_collection.create_or_truncate_collection('MYTEST');

  -- add a collection member and calculate its checksum
  l_seq_id := apex_collection.add_member(
                     p_collection_name => 'MYTEST'
                    ,p_c001 => v('P1_EMPNO')
                    ,p_c002 => v('P1_ENAME')
                    ,p_generate_md5  => 'YES');

  -- fetch the original checksum for the collection members
  SELECT md5_original
    INTO l_cksum
    FROM apex_collections
   WHERE collection_name = 'MYTEST'
     AND seq_id = l_seq_id;

  -- print it
  HTP.p('<BR>Checksum from query on collection: '||l_cksum);

  -- recalculate the current checksum for the collection members
  l_cksum := APEX_COLLECTION.GET_MEMBER_MD5(
                      p_collection_name => 'MYTEST',
                      p_seq             => l_seq_id);

  -- print it
  HTP.p('<BR>Checksum calculated from current collection: '||l_cksum);
  -- this is OK

  -- recalculate the checksum of the current items I used in the collection
  l_cksum := wwv_flow_item.md5(
                      v('P1_EMPNO'),
                      v('P1_ENAME') );

  -- print it again
  HTP.p('<BR>Checksum calculated with wwv_flow_item.md5(): '||l_cksum);
  -- this checksum is different: WHY?
END;

What am I doing wrong?

Thanks,
Paolo

Comments

joelkallman-Oracle
Hi Paolo,

The MD5 computation for a collection member is essentially a message digest of a series of concatenated message digests.

Why, you might ask? Because the entire concatenated value of the collection member attributes will exceed the input argument size to dbms_obfuscation_toolkit.md5. So we essentially need to compute individual hashes, concatenate those into a big string, and then compute the hash of that. As far as collections are concerned, the algorithm to do this is immaterial as long as it is consistent and will result in a uniquely generated hash.

In your case, it might be simplest to take your individual item values, update the member attributes of a particular collection member via apex_collection.update_member, and then call apex_collection.get_member_md5. If the newly computed MD5 is different than md5_original, then you know something changed.

And remember that collection manipulation is transactional - so you could do the above operations and rollback, if necessary.

I hope this helps.

Joel
PaoloM
Joel
thanks for your explanation, now I understand. I will try using a different approach with the update_member function and see if it works for me.

In the beginning I didn't think about it, because I really wanted my collection to retain the original values so I could simply use a NULLIF to pass only the items that were changed to my API call. In this case, the rollback will be probably very useful, if it's not interfering with anything else.

Or I could also add a new member and check its MD5 against the one of the first member...

Thanks again,
Paolo
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 27 2007
Added on Jun 29 2007
#apex-discussions
2 comments
1,030 views