Implement an MD5sum function in Oracle

A few days ago, I was looking for a way to get an MD5sum function to work directly in Oracle, like the MD5() function in MySQL. After some searching I found out that there was no direct way to generate an MD5 checksum in Oracle. However, the function exists in the Oracle supplied package dbms_obfuscation_toolkit, so here’s the way to make a usable function that will call this package and get the checksum.

CREATE OR REPLACE FUNCTION USER.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;
/

This simple function uses 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 dual;

! md5 -s 'foo'
MD5 ("foo") = acbd18db4cc2f85cedef654fccc4a4d8

Both commands return acbd18db4cc2f85cedef654fccc4a4d8, which is the MD5 result we were expecting.

Tags:

Forcing ASMM component to shrink

#oracle #database

Here's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more

20 Mar 2019 - 2 minute read

How to extract Oracle user DDL

#database #oracle

In some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more

20 Feb 2018 - 1 minute read

Restart a hung Oracle database

#oracle

On rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more

20 May 2017 - 1 minute read