15

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!

N West
  • 6,768
  • 25
  • 40
jomaora
  • 1,656
  • 3
  • 17
  • 26

2 Answers2

24

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 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. – jomaora Apr 08 '11 at 15:14
  • 3
    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). – Sanders the Softwarer Jan 15 '16 at 14:15
  • 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. –  Feb 10 '17 at 16:42
  • @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. – Justin Cave Feb 11 '17 at 03:05
  • 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. –  Feb 11 '17 at 04:08
  • @JustinCave - I just Googled "ORA_HASH on clob" and it seems this wheel has been invented and reinvented plenty of times. –  Feb 11 '17 at 04:14
  • 1
    ...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**. –  Feb 11 '17 at 14:30
16

In Oracle 12c, you can use the function STANDARD_HASH.

In Oracle 11g and lower, 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.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
N West
  • 6,768
  • 25
  • 40