I have below code. 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 call this code? Can i directly execute this code in sqldeveloper?
In Oracle 12c you can use the function STANDARD_HASH. It does not require any additional privileges.
select standard_hash('foo', 'MD5') from dual;
The dbms_obfuscation_toolkit is deprecated (see Note here). You can use DBMS_CRYPTO directly:
select rawtohex(
DBMS_CRYPTO.Hash (
UTL_I18N.STRING_TO_RAW ('foo', 'AL32UTF8'),
2)
) from dual;
Output:
ACBD18DB4CC2F85CEDEF654FCCC4A4D8
Add a lower function call if needed. More on DBMS_CRYPTO.
To calculate MD5 hash of CLOB content field with my desired encoding without implicitly recoding content to AL32UTF8, I've 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)));
@user755806 I do not believe that your question was answered. I took your code but used the 'foo' example string, added a lower function and also found the length of the hash returned. In sqlplus or Oracle's sql developer Java database client you can use this to call the md5sum of a value. The column formats clean up 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;
The result set
HASH_KEY HASH_KEY_LEN
---------------------------------- ------------
acbd18db4cc2f85cedef654fccc4a4d8 32
is the same value that is returned from a Linux md5sum command.
echo -n foo | md5sum
acbd18db4cc2f85cedef654fccc4a4d8 -
I would do:
select DBMS_CRYPTO.HASH(rawtohex('foo') ,2) from dual;
output:
DBMS_CRYPTO.HASH(RAWTOHEX('FOO'),2)
--------------------------------------------------------------------------------
ACBD18DB4CC2F85CEDEF654FCCC4A4D8
Source: Stackoverflow.com