Making a sha1-hash of a row in Oracle

Question

I'm having a problem with making a sha1-hash of a row in a select on an Oracle database. I've done it in MSSQL as follows:

SELECT *,HASHBYTES('SHA1',CAST(ID as varchar(10)+
  TextEntry1+TextEntry2+CAST(Timestamp as varchar(10)) as Hash
FROM dbo.ExampleTable
WHERE ID = [foo]

However, I can't seem to find a similar function to use when working with Oracle. As far as my googling has brought me, I'm guessing dbms_crypto.hash_sh1 has something to do with it, but I haven't been able to wrap my brain around it yet...

Any pointers would be greatly appreciated.

Answer 1

Hi Prometheus,

The package DBMS_CRYPTO is the correct package to generate hashes. It is not granted to PUBLIC by default, you will have to grant it specifically (GRANT EXECUTE ON SYS.DBMS_CRYPTO TO user1).

The result of this function is of datatype RAW. You can store it in a RAW column or convert it to VARCHAR2 using the RAWTOHEX or UTL_ENCODE.BASE64_ENCODE functions.

I don't have a database to test right now but to generate a hash you would use something like this:

DECLARE
   x RAW(20);
BEGIN
   SELECT sys.dbms_crypto.hash(col1||col2||to_char(col3), 
                               sys.dbms_crypto.hash_sh1) 
     INTO x 
     FROM t;
END;

you will find additional information in the documentation of DBMS_CRYPTO.hash