Recently I was struggling to find a function to get MD5 to work in Oracle, like the MD5() function in MySQL. After some searching I found out that there is no direct way to calculate an MD5 checksum in Oracle. Therefore I created the function below.
CREATE OR REPLACE FUNCTION USERINFO.md5hash (str IN VARCHAR2) RETURN VARCHAR2 IS v_checksum VARCHAR2(32); BEGIN v_checksum := LOWER( RAWTOHEX( UTL_RAW.CAST_TO_RAW( sys.dbms_obfuscation_toolkit.md5(input_string => str) ) ) ); RETURN v_checksum; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- Consider logging the error and then re-raise RAISE; END md5hash; /
What this basically does is use the
sys.dbms_obfuscation_toolkit.md5 function’s raw byte array, convert it to a hexadecimal string and convert that string to all lowercase characters. This way you can easily convert strings to MD5.
SELECT md5hash('foo') FROM tbl
acbd18db4cc2f85cedef654fccc4a4d8, the MD5 result we were expecting.
Feel free to use it!