Solved

How to convert  varchar2 value to hex

Posted on 2009-04-01
4
6,183 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 100 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-02288: invalid OPEN mode 2 77
Row_number in SQL 6 45
How to drop system generated virtual column in a table in12c 15 103
UTL_FILE invalid file operation 5 45
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This paper addresses the security of Sennheiser DECT Contact Center and Office (CC&O) headsets. It describes the DECT security chain comprised of “Pairing”, “Per Call Authentication” and “Encryption”, which are all part of the standard DECT protocol.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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 …

685 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