CODEWITHSUNDEEP
sqloracle-databaseoracle11gmd5
Dave
Dave

Reputation: 7400

converting varchar to md5

I have the following sql code in oracle client 11g. I would like to convert the "ssno" to md5 hash. I've read other posts but none of them specifically say where to put the code. thanks!

 SELECT FS_HIRES."rsa",
        FS_HIRES."ssno",
        FS_HIRES."lname",
        FS_HIRES."series",
        FS_HIRES."grade",
        FS_HIRES."pos_title",
        FS_HIRES."ethnicity",
        FS_HIRES."disability",
        FS_HIRES."type_appt",
        FS_HIRES."Perm_Temp",
        FS_HIRES."gender",
        FS_HIRES."age",
        FS_HIRES."age_categories",
        FS_HIRES."los",
        FS_HIRES."date_apnt",
        FS_HIRES."mm_apnt",
        FS_HIRES."yy_apnt",
        FS_HIRES."apnt_noa",
        FS_HIRES."apnt_auth",
        FS_HIRES.L2_DESC,
        FS_HIRES.L3_DESC,
        FS_HIRES.L4_DESC,
        FS_HIRES.L5_DESC,
        FS_HIRES."fy"
   FROM FS_HIRES

Upvotes: 0

Views: 1271

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

The HASH_MD5 constant can't be referred to directly from SQL, so a statement like:

 SELECT FS_HIRES."rsa",
        DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(FS_HIRES."ssno", 'AL32UTF8'),
          DBMS_CRYPTO.HASH_MD5),
        ...

will get an error like "ORA-06553: PLS-221: 'HASH_MD5' is not a procedure or is undefined". You can either use the internal value for that constant, which is 2:

 SELECT FS_HIRES."rsa",
        DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW(FS_HIRES."ssno", 'AL32UTF8'), 2),
        ...

Or if you don't want to rely on a constant that could potentially change in a future release, define your own function:

create or replace function my_md5(p_str varchar2) return raw is
begin
  return dbms_crypto.hash(utl_i18n.string_to_raw(p_str, 'AL32UTF8'),
    dbms_crypto.hash_md5);
end my_md5;
/

... and then call that:

 SELECT FS_HIRES."rsa",
        MY_MD5(FS_HIRES."ssno", 'AL32UTF8') AS "ssno",
        ...

If your database character isn't AL32UTF8, you may need to do more conversion as mentioned in the documentation, and it'll be easier to hide that in the function too.

Upvotes: 3

tbone
tbone

Reputation: 15473

Try this:

select 
'123456789' as ssno,
rawtohex(
DBMS_CRYPTO.Hash (
    UTL_I18N.STRING_TO_RAW ('123456789', 'AL32UTF8'),
    2)
) as ssno_md5
from dual;

Output:

SSNO    SSNO_MD5
123456789   25F9E794323B453885F5181F1B624D0B

Upvotes: 1

Related Questions