Is there any hash function in PL/SQL?

Question

I'm looking for a Hash function in PL/SQL, to get the hash of a varchar. I found a package in Oracle 10 called dbms_crypto with a function dbms_crypto.hash and even other package dbms_sqlhash.getHash, however where I called them, I've got a message like it cannot find them...

Does somebody know how can I call them?? Is there any other package?

Here's my code

DECLARE
 l_textToHash VARCHAR2(19) := 'toto123';
 l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_textToHash);
 l_encrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('CC:  ' || l_ccn_raw);
  l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, 3);
  dbms_output.put_line('SH1: ' || l_encrypted_raw);
END;
/

Here's the message

Error starting at line 1 in command:
DECLARE
 l_textToHash VARCHAR2(19) := 'toto123';
 l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_textToHash);
 l_encrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line('CC:  ' || l_ccn_raw);
  l_encrypted_raw := dbms_crypto.hash(l_ccn_raw, 3);
  dbms_output.put_line('SH1: ' || l_encrypted_raw);
END;
Error report:
ORA-06550: line 7, column 22:
PLS-00201: identifier 'DBMS_CRYPTO' must be declared
ORA-06550: line 7, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Thanks!

Solution 1

Make sure that you have the appropriate permissions granted to the user that you are connecting with. Talk to your DBA to add the execute permission on the SYS.DBMS_CRYPTO package.

Oracle provides a nice guide on working with hashed and encrypted data using the oracle database.

If you are on an older version of the database that doesn't support DBMS_CRYPTO, you can also try DBMS_OBFUSCATION_TOOLKIT.

In Oracle 12c you can use the function STANDARD_HASH.

Solution 2

Depending on why you're trying to generate the hash, the built-in function ORA_HASH may be sufficient,

SQL> select ora_hash( 'fuzzy bunny' ) from dual;

ORA_HASH('FUZZYBUNNY')
----------------------
            2519249214

I wouldn't try to use this if you need a cryptographically secure hash function. But if you just need a simple hash, this should be sufficient.

Comments

[jomaora]

Hi, yes, actually that was the problem, the architect didn't give me the permissions, thanks!!

[jomaora]

I have already tried that function; but it was not what I'm looking for, the problem was that I hadn't acces to the library because of permissions.

[Sanders the Softwarer]

Unfortunately, built-in function ORA_HASH available for SQL but not for PL/SQL.It's required to use something like SELECT ORA_HASH(DATA) INTO variable FROM DUAL if we wish to call it from PL/SQL (at Oracle 10g at least).

[mathguy]

From what I can tell, ORA_HASH is NOT a hash function in the proper sense of the term; different calls using exactly the same parameters do not return the same value. Rather, ORA_HASH seems to randomly (and non-repeatably) assign inputs to buckets.

[Justin Cave]

@mathguy - Do you have a test case for that? I just re-ran my query from 6 years ago and it returns the same result today in a different database than it did 6 years ago.

[mathguy]

Yes... but it is more interesting than I thought. I was looking for a hash function to test an idea about deduplication. In that problem I was using the ORA_HASH overload for CLOBs. After I read your comment I tried on a "normal" string and indeed I get the same value each time. So here is what is happening: run select ORA_HASH('X', 10000, 0) a few times in a row, you get the same value. However, run select ORA_HASH(to_clob('X'), 10000, 0) a few times in a row - different values each time. This is really weird and even worse than I thought.

[mathguy]

@JustinCave - I just Googled "ORA_HASH on clob" and it seems this wheel has been invented and reinvented plenty of times.

[mathguy]

...and looking more closely at the documentation: for Oracle 10.1 they say "There are no restrictions on the type or length of data represented by expr." The documentation for 11.1 says exactly the same thing, but then it immediately adds: The expr cannot be a LONG or LOB type.