EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Oracle MD5

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Oracle Tutorial » Oracle MD5

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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:

Oracle MD51

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:

Oracle MD52

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:

Oracle MD53

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:

Oracle MD54

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

Popular Course in this category
Sale
Oracle Training (14 Courses, 8+ Projects)14 Online Courses | 8 Hands-on Projects | 120+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,290 ratings)
Course Price

View Course

Related Courses
Oracle DBA Database Management System Training (2 Courses)All in One Financial Analyst Bundle - 250+ Courses, 40+ Projects

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:

Date data type column

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:

Oracle MD56

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:

Miscellaneous value

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:

NULL value

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:

HASH value

Note: All the above note and examples executed in Oracle 11g Version. In Oracle 12C syntax of MD5 function may change.

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?

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - Oracle Course Training Learn More