?
Solved

How to convert  varchar2 value to hex

Posted on 2009-04-01
4
Medium Priority
?
6,382 Views
Last Modified: 2013-12-19
I use  dbms_obfuscation_toolkit package and create a MD5 hashed checksum of input data, but later on I would like to use the hex number (the MD5) not the unreadable Varchar2 equivalent.
Please get a look at the attachment,
Not Á¥)~!èùb¥íü i but  C1A5298F939E87E8.... etc. that is what I want to get.

create or replace function md5( input varchar2 ) return sys.dbms_obfuscation_toolkit.varchar2_checksum as
begin 
  return sys.dbms_obfuscation_toolkit.md5( input_string => input );
 end;
/
 
SELECT md5('Hi') from dual;

Open in new window

md5-Hi-hex.png
0
Comment
Question by:rusdemezale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 24040107
try this...


CREATE OR REPLACE FUNCTION md5(input VARCHAR2)
    RETURN VARCHAR2
AS
    v_temp   DBMS_OBFUSCATION_TOOLKIT.varchar2_checksum;
    v_result VARCHAR2(4000);
BEGIN
    v_temp := sys.DBMS_OBFUSCATION_TOOLKIT.md5(input_string => 'Hi');
 
    FOR i IN 1 .. LENGTH(v_temp)
    LOOP
        v_result := v_result || TO_CHAR(ASCII(SUBSTR(v_temp, i, 1)), 'fmXX');
    END LOOP;
    return v_result;
END;
/

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24040174
note if you go to 10g you can use dbms_crypto instead


RAWTOHEX(DBMS_CRYPTO.hash(TO_CLOB('Hi'), DBMS_CRYPTO.hash_md5))

or

RAWTOHEX(DBMS_CRYPTO.hash(utl_raw.cast_to_raw('Hi'), DBMS_CRYPTO.hash_md5))
0
 

Author Closing Comment

by:rusdemezale
ID: 31565330
well done. thx.
I just don't know syntax and api of pl sql well.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 24040280
why only a B?  what else could I have done to earn the A?
0

Featured Post

Threat Trends for MSPs to Watch

See the findings.
Despite its humble beginnings, phishing has come a long way since those first crudely constructed emails. Today, phishing sites can appear and disappear in the length of a coffee break, and it takes more than a little know-how to keep your clients secure.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
The well known Cerber ransomware continues to spread this summer through spear phishing email campaigns targeting enterprises. Learn how it easily bypasses traditional defenses - and what you can do to protect your data.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question