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.

For example:

SELECT md5hash('foo')
FROM tbl

This returns acbd18db4cc2f85cedef654fccc4a4d8, the MD5 result we were expecting.

Feel free to use it!