How to call Oracle MD5 hash function

Question

I have below code  I am using Oracle 11g   SELECT DBMS OBFUSCATION TOOLKIT md5  input   gt  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

User · Answer

I would do   select DBMS CRYPTO HASH rawtohex  foo    2  from dual    output   DBMS CRYPTO HASH RAWTOHEX  FOO   2  -------------------------------------------------------------------------------- ACBD18DB4CC2F85CEDEF654FCCC4A4D8

User · Answer

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   gt  UTL RAW cast to raw  foo    as hash key         length dbms obfuscation toolkit md5            input   gt  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  -    Yes you can call or execute the sql statement directly in sqlplus or sql developer   I tested the sql statement in both clients against 11g  You can use any C  C   Java or other programming language that can send a statement to the database   It is the database on the other end of the call that needs to be able to understand the sql statement   In the case of 11 g  the code will work   tbone provides an excellent warning about the deprecation of the dbms obfuscation toolkit   However  that does not mean your code is unusable in 12c   It will work but you will want to eventually switch to dbms crypto package   dbms crypto is not available in my version of 11g

User · Answer

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

User · Answer

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

[sql] How to call Oracle MD5 hash function?

Examples related to sql

Examples related to oracle

Examples related to plsql

Examples related to oracle11g