How to call the hash function of Oracle MD5?

Question

I have the code below. I am using Oracle 11g.

SELECT DBMS_OBFUSCATION_TOOLKIT.md5 (input => UTL_RAW.cast_to_raw( FIRST_NAME ||LAST_NAME )) md5_key , FIRST_NAME , LAST_NAME FROM C_NAME_TAB WHERE PKEY='1234' 

How can I name this code? Can I directly execute this code in sqldeveloper?

Answer 1

In Oracle 12c, you can use the STANDARD_HASH function. It does not require additional privileges.

 select standard_hash('foo', 'MD5') from dual; 

---

dbms_obfuscation_toolkit is deprecated (see note here ). You can directly use DBMS_CRYPTO:

 select rawtohex( DBMS_CRYPTO.Hash ( UTL_I18N.STRING_TO_RAW ('foo', 'AL32UTF8'), 2) ) from dual; 

Output:

 ACBD18DB4CC2F85CEDEF654FCCC4A4D8 

Add a lower function call if necessary. Learn more about DBMS_CRYPTO .

Answer 2

I would do:

 select DBMS_CRYPTO.HASH(rawtohex('foo') ,2) from dual; 

exit:

 DBMS_CRYPTO.HASH(RAWTOHEX('FOO'),2) -------------------------------------------------------------------------------- ACBD18DB4CC2F85CEDEF654FCCC4A4D8 

Answer 3

@ user755806 I do not believe that your question has been answered. I took your code, but used the example string "foo", added the bottom function, and also found that the hash length was back. In sqlplus or Oracle sql developer Java database client, you can use this to call the md5sum value. The column format clears the presentation.

 column hash_key format a34; column hash_key_len format 999999; select dbms_obfuscation_toolkit.md5( input => UTL_RAW.cast_to_raw('foo')) as hash_key, length(dbms_obfuscation_toolkit.md5( input => UTL_RAW.cast_to_raw('foo'))) as hash_key_len from dual; 

Result set

 HASH_KEY HASH_KEY_LEN ---------------------------------- ------------ acbd18db4cc2f85cedef654fccc4a4d8 32 

is the same value returned by the Linux md5sum command.

 echo -n foo | md5sum acbd18db4cc2f85cedef654fccc4a4d8 - 

• Yes, you can invoke or execute the sql statement directly in the sqlplus or sql developer. I tested sql expression in both clients against 11g.
• You can use any C, C #, Java or other programming languages ​​that can send instructions to the database. This is the database at the other end of the call, which should be able to understand the sql statement. In the case of 11 g, the code will work.
• @tbone provides an excellent dbms_obfuscation_toolkit obsolescence warning. However, this does not mean that your code is not applicable in 12c. It will work, but you will want to eventually switch to the dbms_crypto package. dbms_crypto is not available in my version 11g.

Answer 4

To calculate the MD5 hash of the CLOB content field with my desired encoding without explicitly transcoding the contents in AL32UTF8, I used this code:

 create or replace function clob2blob(AClob CLOB) return BLOB is Result BLOB; o1 integer; o2 integer; c integer; w integer; begin o1 := 1; o2 := 1; c := 0; w := 0; DBMS_LOB.CreateTemporary(Result, true); DBMS_LOB.ConvertToBlob(Result, AClob, length(AClob), o1, o2, 0, c, w); return(Result); end clob2blob; / update my_table t set t.hash = (rawtohex(DBMS_CRYPTO.Hash(clob2blob(t.content),2)));