How to Call Oracle Md5 Hash Function

Different Output with same Input for ORACLE MD5 Function

All we have about the data changing (or not) and the hash changing (or not) is your assertion.

You could create and populate a log table:

create table hash_log (
   sample_time timestamp,
   hashed_string varchar2(200),
   hashed_string_dump varchar2(200),
   hash_value varchar2(200)
   );

Then on a daily basis:

insert into hash_log values 
  (select systimestamp,
          source_column,
          dump(source_column),
          STANDARD_HASH(source_column , 'MD5' )
   from source_table
   );

Then, to spot changes:

select distinct      hashed_string ||
                     hashed_string_dump ||
                     hash_value 
from hash_log;

Oracle Hash MD5 decryption

MD5 is a hashing algorithm, not really intended for encryption or decryption.
So, no, there is no way to get the start-string again. Actually, given a hash, there would be many potential start-strings it could come from.

MD5 in Oracle (DBMS_OBFUSCATION_TOOLKIT.MD5)

It's a peculiarity of Oracle PL/SQL that stored procedure parameters and function return types cannot be limited. That is, we cannot have a procedure with a signature like this:

SQL> create or replace procedure my_proc (p1 in varchar2(30))
  2  is
  3  begin
  4      null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/34     PLS-00103: Encountered the symbol "(" when expecting one of the
         following:
         := . ) , @ % default character
         The symbol ":=" was substituted for "(" to continue.

SQL> create or replace procedure my_proc (p1 in varchar2)
  2  is
  3  begin
  4      null;
  5  end;
  6  /

Procedure created.

SQL>

Sure we can define the procedure's parameter using a SUBTYPE but Oracle will ignore it. Same goes for function return types...

SQL> create or replace package my_subtypes as
  2      subtype ltd_string is varchar2(30);
  3  end;
  4  /

Package created.

SQL> create or replace function my_func return my_subtypes.ltd_string
  2  is
  3  begin
  4      return lpad('a', 4000, 'a');
  5  end;
  6  /

Function created.

SQL> select length(my_func) from dual
  2  /

LENGTH(MY_FUNC)
---------------
           4000

SQL>

The only way of limiting parameters and return types is to declare variables using subtypes within the stored procedure. Use the variables within the package, and assign them to the OUT paramters (or RETURN the variable for functions).

Which is a long-winded way of saying, you can use DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM in your code confident that it won't prevent your function returning 32 characters.

However, it will confuse developers who will lookup the SUBTYPE declaration. In the worst case these people will use the subtype to declare their own working variables with the following tragic result:

SQL> declare
  2      v my_subtypes.ltd_string;
  3  begin
  4      v := my_func;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

SQL>

So, it is better not to use an inappropriate subtype. Instead declare your own.

Is there an MD5 Sum function in PL/SQL

See this Tahiti Link. Under MD5 Procedures and Functions it says These subprograms generate MD5 hashes of data. The MD5 algorithm ensures data integrity by generating a 128-bit cryptographic message digest value from given data.

Also note, that DBMS_OBFUSCATION_TOOLKIT is deprecated and can/should be replaced with DBMS_CRYPTO, see this Tahiti Link

Does Oracle have any built-in hash function?

Yes: hashing and encrypting (related but not exactly the same) are all done via the SYS package DBMS_CRYPTO.

Simple SHA-1 Hashing

l_hash := dbms_crypto.hash( l_src, dbms_crypto.HASH_SH1 );

Simple MD5 Hashing

l_hash := dbms_crypto.hash( l_src, dbms_crypto.HASH_MD5 );

Overview of dbms_crypto.hash()

The hash() function is overloaded to accept the following types: RAW, BLOB, and CLOB. According to the implicity data conversions for raw acceptable input types are RAW, CHAR, VARCHAR2, NCHAR, NVARCHAR2, LONG, BLOB. All other data types (DATE, TIMESTAMP, etc) not covered under RAW/implicit RAW conversion, BLOB, and CLOB will have to be passed through TO_CHAR() first.

It is worth noting that dbms_crypto.hash() supports the following hashing algorithms:

HASH_MD4
HASH_MD5
HASH_SH1

Passwords: Just In Case

If you are storing passwords, I suggest that you use a password storage hash (bcrypt, PBKDF2, or scrypt) instead of a cryptographic hash (md5, sha-1, etc). The difference is that password storage hashes are meant to take time to break while cryptographic hashes are meant to be done quickly. When attacking a system's password list via brute force it orders of magnitude more time intensive when attempting to break a salted value that is passed through a cryptographic algorithm. Consider that using a password hash on a single value can take ~100ms (not much for a single authentic login), but very slow for a brute force (millions/billions of attempts per password) over your entire password list.

Oracle Hates Password Hashes

For what its worth I am not aware of any packages from Oracle that provide password hashing support. You can however accomplish this by using 'loadjava' and putting a Java bcrypt implementation within the JVM that runs withing Oracle's RDBMS. You can then use a PL/SQL wrapper to call your Java class that implements bcrypt. If you are using a middle-tier you can use many other options available to you in that language (.NET, PHP, Perl, Ruby, Python, Java, etc) and skip trying to use 'loadjava'.

I meant encryption not hashes!

In case the hashing you need is not covered by dbms_crypto.hash(), you might be looking for encryption via dbms_crypto.encrypt which works very similarly except that it takes in the following types:

ENCRYPT_DES
ENCRYPT_3DES_2KEY
ENCRYPT_3DES
ENCRYPT_AES
ENCRYPT_PBE_MD5DES
ENCRYPT_AES128
ENCRYPT_AES192
ENCRYPT_AES256

Here is the full 11gR2 documentation on DBMS_CRYPTO. All other versions are available via tahiti.oracle.com. Just click on your version and then search for 'dbms_crypto'.

How to get same hash for numbers in Oracle and in C#?

To get the same hash value for a number in PL/SQL and in C#, you'll need to convert the number to a binary format first, which is identical in both languages.

The proper package in PL/SQL is DBMS_CRYPTO, which accepts RAW as datatype. Presumably, your sequential primary key has the datatype NUMBER, which has an Oracle defined internal structure.

You could choose for instance a hexadecimally formatted string as the common format:

CREATE TABLE t (id NUMBER);
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (10);
INSERT INTO t VALUES (100);
INSERT INTO t VALUES (1000);

SELECT id, HEXTORAW(to_char(id, 'fmXXXXXXXXXXX')) as hex,
       DBMS_CRYPTO.HASH(hextoraw(to_char(id, 'fmXXXXXXXXXXX')), 3) as sh1  
  FROM t ORDER BY id;

ID   HEX   SH1
1    01    BF8B4530D8D246DD74AC53A13471BBA17941DFF7
10   0A    ADC83B19E793491B1C6EA0FD8B46CD9F32E592FC
100  64    3C363836CF4E16666669A25DA280A1865C2D2874
1000 03E8  ABA6A1729AEA932DFB085F66C3D1E9882E275F7E

(this is SH1 as I am on 11.2 right now)

I would add this hash as a virtual column to the table (to have the hashing code at a central place). If you want to select by the hash, I'd add an index on this virtual column:

ALTER TABLE t ADD (secret RAW(2000) GENERATED ALWAYS AS(
  DBMS_CRYPTO.HASH(hextoraw(to_char(id, 'fmXXXXXXXXXXX')), 3)));

CREATE INDEX ti_hash ON t(secret);

SELECT * FROM t;

ID    SECRET
1     BF8B4530D8D246DD74AC53A13471BBA17941DFF7
10    ADC83B19E793491B1C6EA0FD8B46CD9F32E592FC
100   3C363836CF4E16666669A25DA280A1865C2D2874
1000  ABA6A1729AEA932DFB085F66C3D1E9882E275F7E

SELECT * 
  FROM t  
 WHERE secret=hextoraw('ABA6A1729AEA932DFB085F66C3D1E9882E275F7E');

To get this code working, your schema user (the owner of the table) needs the privilege directly (not via a role):

GRANT EXECUTE ON DBMS_CRYPTO TO my_schema_owner;