Oracle MD5

Introduction to Oracle MD5

An Oracle MD5 function is a hash function which is used to access data integrity. MD5 stands for Message Digest Algorithm 5. MD5 is a cryptographic hash function which is commonly used to calculate checksum of enter value and generating a 128 bit (16 Byte) hash value.

Points of Concentration:

• MD5 algorithm was developed in 1991 by cryptographer Ronald Rivest in 1991.
• MD5 function uses Message Digest Algorithm 5.
• MD5 function is commonly used in Cryptographic Hash Function.
• MD5 function generates 128 bit (16 Byte) Hash value.
• MD5 function returns 32 characters of Hexadecimal digits.
• MD5 function reduces cache memory usage at run time.
• MD5 has used in a large variety of CRYPTOGRAPHIC applications.
• MD5 function calculate checksum of enter value.
• MD5 function returns NULL if input is NULL.
• MD5 function is a cryptographic algorithm that takes an input of arbitrary length and produces a message digest that is 128 bits long.

Syntax

SELECT DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT =>
UTL_RAW.CAST_TO_RAW (Column Name)) [Alias Name] FROM Table Name;

Explanation:

• DBMS_OBFUSCATION_TOOLKIT: It is an Oracle inbuilt package which provides capability to encrypt and decrypt data.
• MD5: It’s (Message Digest Algorithm 5) a function which can be accessed using DBMS_OBFUSCATION_TOOLKIT inbuilt package.
• UTL_RAW: This is a package. UTL denotes that it’s not specific to the database environment but it can be used in other environments as well.
• CAST_TO_RAW: This function casts a VARCHAR value to a VARBINARY value.
• Column Name: The column data that will be encrypted
• [Alias Name]: It’s an optional. Alias name can be used for understand.
• Table Name: It’s a name of the table that will be used.

Code:

SELECT Ename, DBMS_OBFUSCATION_TOOLKIT.MD5 (INPUT =>
UTL_RAW.CAST_TO_RAW (Ename)) HASH_VALUE FROM Emp;

Output:

Examples to Implement Oracle MD5

Below are the examples mentioned:

Example #1

In this section we’ll see the implementation of Oracle MD5Functionand its behavior. For that, we will use the below sample table (Employee) with 14 records to understand the Oracle MD5Function behavior.

Code:

SELECT * Employee;

Output:

Example #2

Oracle MD5 function on NUMBER data type column

Code:

SELECT Salary, DBMS_OBFUSCATION_TOOLKIT.MD5 (input =>
UTL_RAW.CAST_TO_RAW (Salary)) HASH_KEY FROM Employee;

Output:

Explanation: In the above example, MD5 function generated cryptographic hash value for Salary column data which is number data type.

Example #3

Oracle MD5 function on VARCHAR2 data type column

Code:

SELECT Name, DBMS_OBFUSCATION_TOOLKIT.MD5 (input =>
UTL_RAW.CAST_TO_RAW (Name)) HASH_KEY FROM Employee;

Output:

Explanation: In the above example, MD5 function generated cryptographic hash value for Name column data which is VARCHAR2 data type.

Example #4

Oracle MD5 function on Date data type column

Code:

SELECT DOJ, DBMS_OBFUSCATION_TOOLKIT.MD5 (input =>
UTL_RAW.CAST_TO_RAW (DOJ)) HASH_KEY FROM Employee;

Output:

Explanation: The above examples clearly show that the MD5 function can be applied on any data type. But MD5 function behavior gets changed with NULL or the column which consist NULL value. See some examples below

Example #5

Oracle MD5 function on NULL

Code:

SELECT NULL, DBMS_OBFUSCATION_TOOLKIT.MD5 (input =>
UTL_RAW.CAST_TO_RAW (NULL)) HASH_KEY FROM DUAL;

Output:

Explanation: The above example throws an error when MD5 finds the null value. But here Oracle throws 3 errors WHY?Because 1st error ORA-28231 says comes out because function expecting not NULL value but it’s getting NULL value. 2nd and 3rd error are the same but occurring at different levels or lines that’s why the package “DBMS_OBFUSCATION_TOOLKIT” throwing twice. These two errors are the same because it consists of the same ORA number it means these errors for same reason.

Example #6

Oracle MD5 function with Miscellaneous value (NULL and NOT NULL value)

Code:

SELECT BONUS, DBMS_OBFUSCATION_TOOLKIT.MD5 (input =>
UTL_RAW.CAST_TO_RAW (Bonus)) HASH_KEY FROM Employee;

Output:

Explanation: The above example also throws the same error as the previous example because in this example “BONUS” column consist NULL value as well and MD5 function throws error when it gets NULL value. But the error can be prevented using Oracle function.

Example #7

Oracle MD5 function with Oracle NVL function to prevent the NULL value error

Code:

SELECT BONUS, DBMS_OBFUSCATION_TOOLKIT.MD5 (input =>
UTL_RAW.CAST_TO_RAW (NVL(Bonus, 0))) HASH_KEY FROM Employee;

Output:

Explanation: NULL value error can be prevented by using appropriate Oracle functions. In the above example, NVL function prevents the NULL value error because NVL provides zero “0” instead of NULL because HASH_KEY of Zero is exactly the same for NULL value in Bonus column.

Example #8

Oracle MD5 function HASH value Length

Code:

SELECT BONUS, DBMS_OBFUSCATION_TOOLKIT.MD5(input =>
UTL_RAW.CAST_TO_RAW(NVL(Bonus,0))) HASH_KEY,
LENGTHB (DBMS_OBFUSCATION_TOOLKIT.MD5(input=>
UTL_RAW.CAST_TO_RAW (NVL (Bonus,0)))) HASH_KEY_LENGTH FROM
Employee;

Output:

Tips:

• MD5 function is a good option to compare data or files between two or more data files.
• Oracle functions can be used with the MD5 function.
• MD5 Function is available in Oracle 12C version but under the package.
• As mentioned above that UTL_RAW is not specific to the database environment.

Conclusion

Oracle MD5 function is used to produce a HASH value of given data. That HASH value is a valuable key for assessing data integrity. MD5 is a cryptographic hash function which can be a good option to calculate a checksum of enter value and in generating a 128 bit (16 Byte) hash value. This is also a good option to compare files without inspecting the contents of those files.

Recommended Articles

This is a guide to Oracle MD5. Here we discuss an introduction to Oracle MD5, syntax with examples, and explanation. You can also go through our other related articles to learn more –

1. Oracle Full Outer Join
2. Left Join in Oracle
3. GROUP BY in Oracle
4. Oracle Window Functions
5. Oracle While Loop | How to Works?
6. Oracle CARDINALITY | How to Work?