[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to convert  varchar2 value to hex

Posted on 2009-04-01
4
Medium Priority
?
6,625 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
  • 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Will you be ready when the clock on GDPR compliance runs out? Is GDPR even something you need to worry about? Find out more about the upcoming regulation changes and download our comprehensive GDPR checklist today !
When you put your credit card number into a website for an online transaction, surely you know to look for signs of a secure website such as the padlock icon in the web browser or the green address bar.  This is one way to protect yourself from oth…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month20 days, 8 hours left to enroll

868 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