Oracle STANDARD_HASH Function: SHA256, Syntax and Examples

Use Oracle STANDARD_HASH(expr [, 'method']) to compute a digest for an expression. It returns a RAW value and defaults to SHA1; you can also choose SHA256, SHA384, SHA512, or MD5. See the Oracle SQL Language Reference for the full argument and type rules.

Use STANDARD_HASH for checksums and stable data fingerprints. It is not encryption, and its interface has no password-specific salt or work-factor options. Do not use it to store passwords; use a dedicated password-hashing scheme with a unique salt and adjustable cost, as described in the OWASP Password Storage Cheat Sheet.

For numeric bucket values or random sampling, see the separate ORA_HASH function.

STANDARD_HASH is also not the hash function Oracle uses internally for hash partitioning. Use it when you need a documented digest such as SHA-256; do not use its output to predict which partition Oracle will choose. See Oracle’s STANDARD_HASH reference.

Oracle STANDARD_HASH() Syntax

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

STANDARD_HASH(expr [, 'method' ])

Hash methods and output sizes

Oracle returns the digest as RAW. The byte lengths below are fixed by each algorithm; SQL clients commonly display the bytes as two hexadecimal characters each.

MD5 is available for compatibility, but the IETF advises against using it where collision resistance is required. See RFC 6151.

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. This is a digest, not ciphertext; the original input cannot be recovered from it.

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

Conclusion

Use STANDARD_HASH to create a deterministic digest with one of Oracle’s supported methods. For inputs that need a bucket number instead of a cryptographic digest, use ORA_HASH.