MD5 function in Oracle

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!

22 comments to “MD5 function in Oracle”

Thanks man you save my life

thank you very much

You’re welcome!

thanks a lot for posting it online.

really helpful tnks for this post

thanks, is really helpful

Thanks a lot ….great post

Great solution man!

Thanks a lot! Very useful

[...] MD5 function in Oracle [...]

really helpful tnks for this post

Really good one, simple but efficient.
Well explained.

Gracias… totales!
Or should I say… Thanks…A lot!

Thanks

You saved me hours of research.
Many Thanks

Ur encrypt function has helped me very much..How to decrypt the data using Md5

Same question that Arunkumar

Thanks

MD5 is a so called one-way encryption. This means that you can only encrypt, not decrypt. In order to check the value you would have to feed the MD5 function with data resulting in the same hash.

For more info see http://en.wikipedia.org/wiki/MD5

Thanks Michiel… it helped me to meet my requirement.

Thanks Michiel for posting this.

Excelente, le diste en el punto exacto que necesitaba, saludos

So Great solution…THANKS.

Leave a reply