I'm trying to compose a function to obtain MD5 hashes from bits I've gathered here and there. I want to obtain the lower-case hexadecimal representation of the hash. I have this so far:
CREATE OR REPLACE FUNCTION MD5 (
CADENA IN VARCHAR2
) RETURN DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM
AS
BEGIN
RETURN LOWER(
RAWTOHEX(
UTL_RAW.CAST_TO_RAW(
DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => CADENA)
)
)
);
END;
I'm not sure about the return type of the function. DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM
looks like the appropriate choice and as far as I can tell it works as expected but the package definition for dbms_obfuscation_toolkit
as displayed by SQL Developer shows this:
SUBTYPE varchar2_checksum IS VARCHAR2(16);
The output has 32 characters so I must be doing something wrong. My questions:
RETURN
statement?Here you go:
create or replace function getMD5(
in_string in varchar2)
return varchar2
as
cln_md5raw raw(2000);
out_raw raw(16);
begin
cln_md5raw := utl_raw.cast_to_raw(in_string);
dbms_obfuscation_toolkit.md5(input=>cln_md5raw,checksum=>out_raw);
-- return hex version (32 length)
return rawtohex(out_raw);
end;
The 32 length is because it is a hex representation of the raw(16) value. Or, modify above to output the raw version and store the raw in a RAW column (less space used, but you'll be doing future rawtohex and hextoraw conversions, believe me).
Cheers