Oracle STANDARD_HASH Function: A Simple Guide

The STANDARD_HASH function in Oracle SQL is a powerful security function. It computes a "hash value" (also known as a "fingerprint" or "checksum") for a given expression.

Unlike ORA_HASH, which is used for random sampling, STANDARD_HASH is used for security and data integrity. It uses well-known, standardized cryptographic algorithms like SHA1, SHA256, and MD5 to create a unique and verifiable hash.

This is useful for:

• Verifying Data Integrity: You can hash a row's data and store the hash. If you re-calculate the hash later and it's different, you know the data has been tampered with.
• Digital Signatures & Authentication: It's a key component in many security applications.
• Indexing: It can be used to create an index on complex data types.

The function always returns a RAW value.

STANDARD_HASH Function Syntax

The syntax for STANDARD_HASH is as follows:

STANDARD_HASH(expr [, method])

Let's break that down:

• expr: The expression, column, or data you want to hash. This can be a string, number, or date.
• [method] (Optional): A string specifying which hash algorithm to use. If you omit this, the default is 'SHA1'. Other valid options include:
  • 'SHA256'
  • 'SHA384'
  • 'SHA512'
  • 'MD5'

Oracle STANDARD_HASH Function Examples

Here are two practical examples of how to use STANDARD_HASH.

Example 1: Calculating a Hash for a Simple String using STANDARD_HASH

This example calculates the hash for the string 'Hello World' using the default method (SHA1) and the MD5 method. The result is a RAW value, which looks like a hexadecimal string.

Query:

SELECT
  STANDARD_HASH('Hello World') AS "SHA1_Hash",
  STANDARD_HASH('Hello World', 'MD5') AS "MD5_Hash"
FROM DUAL;

Result: (The hash value is a fixed-length RAW string)

SHA1_Hash                          MD5_Hash
---------------------------------- --------------------------------
0A4D55A8D778E5022FAB701977C5D840B0                           B10A8DB164E0754105B7A99BE72E9FEA
04F038F3                           

Example 2: Creating a Row "Checksum" using STANDARD_HASH

This is a common use for STANDARD_HASH. Let's say you want to generate a single hash value (a "checksum") for an entire row to make sure it hasn't changed. You can concatenate the columns together (with a separator) and hash the result.

Query: (We'll hash the last_name and job_id together for an employee)

SELECT
  last_name,
  job_id,
  STANDARD_HASH(last_name || '|' || job_id, 'SHA256') AS "Row_Checksum"
FROM
  employees
WHERE
  employee_id = 101; -- For 'Neena Kochhar'

Result: (This RAW checksum represents the state of that row's data. If job_id changes, the checksum will be completely different.)

LAST_NAME                 JOB_ID     Row_Checksum
------------------------- ---------- ----------------------------------------------------------------
Kochhar                   AD_VP      0C81F57E7A3A6A552084D06CAC5A17A4D441D994D481A72101340156515828E4