Updated May 19, 2023
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 the checksum of enter value and generate 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 Functions.
- MD5 function generates a 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 been used in a large variety of CRYPTOGRAPHIC applications.
- MD5 function calculates the checksum of enter value.
- MD5 function returns NULL if the 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 that can encrypt and decrypt data.
- MD5: It’s (Message Digest Algorithm 5) a function that 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 can also be used in other environments.
- 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 optional. The alias name can be used to understand.
- Table Name: It’s the 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 MD5Function and its behavior. We will use the sample table (Employee) below 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 provided example, the MD5 function produced a cryptographic hash value for the data in the Salary column, which is a 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, the MD5 function generated a cryptographic hash value for Name column data which is the 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 show that the MD5 function can be applied to any data type. The MD5 function’s behavior can change when encountering NULL values or columns that contain NULL values. 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 the function is not expecting a NULL value but getting a NULL value. 2nd and 3rd errors are the same but occur at different levels or lines; that’s why the package “DBMS_OBFUSCATION_TOOLKIT” throws twice. These two errors are the same because they consist of the same ORA number, which means they are for the 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 consists NULL value as well, and the MD5 function throws an error when it gets a NULL value. You can prevent the error by using the 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: Using appropriate Oracle functions can help prevent NULL value errors. In the above example, the NVL function prevents the NULL value error because NVL provides zero “0” instead of NULL. After all, the HASH_KEY of Zero is precisely the same as the NULL value in the 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 an excellent option to compare data or files between two or more data files.
- You can use Oracle functions in conjunction with the MD5 function.
- MD5 Function is available in the Oracle 12C version but under the package.
- As mentioned above that UTL_RAW is not specific to the database environment.
Conclusion
Use the Oracle MD5 function to produce a HASH value for the given data. That HASH value is a valuable key for assessing data integrity. MD5 is a cryptographic hash function that can be an excellent option to calculate a checksum of entering the value and generating a 128-bit (16 Byte) hash value. This is also a perfect 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 –
