1) ORA_HASH() Function.
ORA_HASH( expr, max_bucket, seed_value)
- The optional
max_bucket
argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 4294967295. The default is 4294967295. - The optional
seed_value
argument enables Oracle to produce many different results for the same set of data. Oracle applies the hash function to the combination of expr
and seed_value
. You can specify any value between 0 and 4294967295. The default is 0.
ORA_HASH function returns a NUMBER
value.
SQL> select ora_hash('Hello World !') from dual;
ORA_HASH('HELLOWORLD!')
-----------------------
3256043700
2) STANDARD_HASH ( expr, ‘method’), which is available from 12.1 onward.
Valid method algorithms are SHA1
, SHA256
, SHA384
, SHA512
, and MD5
. If you omit this argument, then SHA1
is used.
SQL> select STANDARD_HASH ( 'Hello World !') from dual;
STANDARD_HASH('HELLOWORLD!')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6
SQL> select STANDARD_HASH ( 'Hello World !', 'SHA1') from dual;
STANDARD_HASH('HELLOWORLD!','SHA1')
----------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6
SQL> select STANDARD_HASH ( 'Hello World !', 'MD5') from dual;
STANDARD_HASH('HELLOWORLD!','MD5
--------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006
SQL> select STANDARD_HASH ( 'Hello World !', 'SHA256') from dual;
STANDARD_HASH('HELLOWORLD!','SHA256')
----------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47
SQL> select STANDARD_HASH ( 'Hello World !', 'SHA384') from dual;
STANDARD_HASH('HELLOWORLD!','SHA384')
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8
SQL> select STANDARD_HASH ( 'Hello World !', 'SHA512') from dual;
STANDARD_HASH('HELLOWORLD!','SHA512')
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E
3) DBMS_OBFUSCATION_TOOLKIT.MD5() function.
SQL> CREATE OR REPLACE FUNCTION toolkit_md5 (v_input VARCHAR2)
RETURN dbms_obfuscation_toolkit.varchar2_checksum
AS
BEGIN
RETURN dbms_obfuscation_toolkit.md5(input_string => v_input);
END toolkit_md5;
/
Function created.
SQL> select
RAWTOHEX( UTL_RAW.CAST_TO_RAW( toolkit_md5('Hello World !'))) from dual;
RAWTOHEX(UTL_RAW.CAST_TO_RAW(TOOLKIT_MD5('HELLOWORLD!')))
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006
4) DBMS_CRYPTO.HASH() function.
The predefined constants HASH_MD4, HASH_MD5 and HASH_SH1 were already available in previous Oracle releases. In Oracle 12c, three additional constants for different lengths of SHA-2 (256, 384 and 512 bits) were added:
HASH_MD4 CONSTANT PLS_INTEGER := 1;
HASH_MD5 CONSTANT PLS_INTEGER := 2;
HASH_SH1 CONSTANT PLS_INTEGER := 3;
HASH_SH256 CONSTANT PLS_INTEGER := 4;
HASH_SH384 CONSTANT PLS_INTEGER := 5;
HASH_SH512 CONSTANT PLS_INTEGER := 6;
CREATE OR REPLACE
FUNCTION CRYPTO_HASH (v_input VARCHAR2, v_hash_typ BINARY_INTEGER)
RETURN RAW DETERMINISTIC
AS
PRAGMA UDF;
BEGIN
RETURN dbms_crypto.hash(utl_raw.cast_to_raw(v_input), v_hash_typ);
END CRYPTO_HASH ;
/
Function created.
-- MD4
--
SQL> select CRYPTO_HASH('Hello World !',1) from dual;
CRYPTO_HASH('HELLOWORLD!',1)
--------------------------------------------------------------------------------
BBA87E44C59377A32F26033CC0A9203B
-- MD5
--
SQL> select CRYPTO_HASH('Hello World !',2) from dual;
CRYPTO_HASH('HELLOWORLD!',2)
--------------------------------------------------------------------------------
B9BE3EF4018BE19F248F6F8E63B9E006
-- SH1
SQL> select CRYPTO_HASH('Hello World !',3) from dual;
CRYPTO_HASH('HELLOWORLD!',3)
--------------------------------------------------------------------------------
F95BE4341EDDB92DDE5F14D045978F9A4EC6C1A6
-- SH256
--
SQL> select CRYPTO_HASH('Hello World !',4) from dual;
CRYPTO_HASH('HELLOWORLD!',4)
--------------------------------------------------------------------------------
07F2BDEF34ED16E3A1BA0DBB7E47B8FD981CE0CCB3E1BFE564D82C423CBA7E47
-- SH384
--
SQL> select CRYPTO_HASH('Hello World !',5) from dual;
CRYPTO_HASH('HELLOWORLD!',5)
--------------------------------------------------------------------------------
67E60F9CE837CAA3CA82550F0DFCBDE1B8B8A7C1605FA8D115BCC2314204FD95F5F607306622C38C
0205DE7DF6D426D8
-- SH512
--
SQL> select CRYPTO_HASH('Hello World !',6) from dual;
CRYPTO_HASH('HELLOWORLD!',6)
--------------------------------------------------------------------------------
FEAB0028F1142D420A1425D1DD5B518225B4523AA1CFF63385ECE3411318819F5EC83042CCB79D81
F20E4A243866886CA3AE3026153ACFF8E126C0E89631502E