Oracle STANDARD_HASH() Function

Oracle STANDARD_HASH() is a built-in function that computes the hash value for a given expression using one of the multiple hash algorithms defined and standardized by the National Institute of Standards and Technology (NIST) in the United States.

The Oracle STANDARD_HASH() function can be used for authentication and maintaining data integrity in secure applications such as digital signatures, checksums, and fingerprint recognition.

Oracle STANDARD_HASH() Syntax

Here is the syntax for the Oracle STANDARD_HASH() function:

STANDARD_HASH(expr [, 'method' ])

Parameters

expr

Required. It determines the data for which to calculate the hash value. The length of data represented by expr is unlimited and is usually interpreted as a column name. expr cannot be of LONG or LOB data type, or a user-defined object type. All other data types are supported for expr.

method

Optional. It allows you to specify the name of the hash algorithm to use. The valid algorithms are SHA1, SHA256, SHA384, SHA512, and MD5. If this parameter is omitted, SHA1 is used.

Return Value

The Oracle STANDARD_HASH() function returns a hash value of RAW data type.

Oracle STANDARD_HASH() Examples

Here are several examples that demonstrate the usage of the Oracle STANDARD_HASH() function.

Basic Usage

To compute the hash value of Hello, use the following statement:

SELECT
    STANDARD_HASH('Hello')
FROM dual;

Output:

STANDARD_HASH('HELLO')
___________________________________________
F7FF9E8B7BB2E09B70935A5D785E0CC5D9D0ABF0

MD5

To compute the hash value of Hello using the MD5 algorithm, use the following statement:

SELECT
    STANDARD_HASH('Hello', 'MD5')
FROM dual;

Output:

STANDARD_HASH('HELLO','MD5')
___________________________________
8B1A9953C4611296A827ABF8C47804D7

SHA256

To compute the hash value of Hello using the SHA256 algorithm, use the following statement:

SELECT
    STANDARD_HASH('Hello', 'SHA256')
FROM dual;

Output:

STANDARD_HASH('HELLO','SHA256')
___________________________________________________________________
185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969

SHA384

To compute the hash value of Hello using the SHA384 algorithm, use the following statement:

SELECT
    STANDARD_HASH('Hello', 'SHA384')
FROM dual;

Output:

STANDARD_HASH('HELLO','SHA384')
___________________________________________________________________________________________________
3519FE5AD2C596EFE3E276A6F351B8FC0B03DB861782490D45F7598EBD0AB5FD5520ED102F38C4A5EC834E98668035FC

SHA512

To compute the hash value of Hello using the SHA512 algorithm, use the following statement:

SELECT
    STANDARD_HASH('Hello', 'SHA512')
FROM dual;

Output:

STANDARD_HASH('HELLO','SHA512')
___________________________________________________________________________________________________________________________________
3615F80C9D293ED7402687F94B22D58E529B8CC7916F8FAC7FDDF7FBD5AF4CF777D3D795A7A00A16BF7E7F3FB9561EE9BAAE480DA9FE7A18769E71886B03F315

NULL Parameter

This function can accept a NULL parameter, and STANDARD_HASH() will return the hash value of NULL.

SET NULL 'NULL';
SELECT
    STANDARD_HASH(NULL)
FROM dual;

Output:

STANDARD_HASH(NULL)
___________________________________________
DA39A3EE5E6B4B0D3255BFEF95601890AFD80709

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Conclusion

Oracle STANDARD_HASH() is a built-in function that calculates the hash value for a given expression using one of the multiple hash algorithms defined and standardized by the National Institute of Standards and Technology (NIST) in the United States.