I am looking for a function to MD5 values for concatenated columns in the table. I plan to use this function on before insert/update trigger to store this values as one of the column (md5_hash) in the same table. The intention is to use this column for change data capture to load the changes to data warehouse system. Following is the trigger.

create or replace trigger biu_trigger
before insert or update on TableA for each row
begin
:new.etl_load_date:=sysdate;
:new.md5_hash := generate_md5(:NEW.concernid||:NEW.COLUMN1||:NEW.COLUMN2||TO_CHAR(:NEW.creationdate,'yyyymmddhh24miss';
end biu_trigger
;
/

Based on google search, found the following code to get MD5 hash.

CREATE or REPLACE FUNCTION HASH_MD5 (
psINPUT IN VARCHAR2
) RETURN VARCHAR2 AS
rHash RAW (16);
BEGIN
rHash := DBMS_CRYPTO.HASH (TO_CLOB (psINPUT), DBMS_CRYPTO.HASH_MD5);
RETURN (LOWER (RAWTOHEX (rHash)));
END HASH_MD5;

This seems to work fine input values up to 4000 characters. But does not seems to work if concatenated columns have data more that 4000 thousand characters.

So I will appreciate if you can suggest how to get MD5 for strings more than 4000 characters long.

I am using 11.2.

Thanks.

SQL> create table t (
  2    c1 varchar2(4000),
  3    c2 varchar2(4000),
  4    hash varchar2(4000)
  5    );

Table created.

SQL>
SQL> CREATE or REPLACE FUNCTION HASH_MD5 (
  2  psINPUT IN VARCHAR2
  3  ) RETURN VARCHAR2 AS
  4  rHash RAW (16);
  5  BEGIN
  6  rHash := DBMS_CRYPTO.HASH (TO_CLOB (psINPUT), DBMS_CRYPTO.HASH_MD5);
  7  RETURN (LOWER (RAWTOHEX (rHash)));
  8  END HASH_MD5;
  9  /

Function created.

SQL>
SQL> create or replace
  2  trigger trg
  3  before insert on  t
  4  for each row
  5  begin
  6  :new.hash := HASH_MD5(:new.c1||:new.c2);
  7  end;
  8  /

Trigger created.

SQL>
SQL> insert into t (c1,c2) values (
  2    rpad('x',4000,'x'),
  3    rpad('x',4000,'x')
  4  );

1 row created.

SQL>
SQL> select hash from t;

HASH
--------------------------------------------------------------------------
d49d36f757d863f1f12fedf91a893e19