Solved

How to convert  varchar2 value to hex

Posted on 2009-04-01
4
5,969 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 73

Accepted Solution

by:
sdstuber earned 100 total points
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility
well done. thx.
I just don't know syntax and api of pl sql well.
0
 
LVL 73

Expert Comment

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In this era, as you know, cybercrime and other sorts of frauds using the internet has increased day by day. We should protect our information assets and confidential information from getting exploiting by the attacker or intruders. Most of the fraud…
There are many reasons malware will stay around and continue to grow as a business.  The biggest reason is the expanding customer base.  More than 40% of people who are infected with ransomware, pay the ransom.  That makes ransomware a multi-million…
Via a live example, show how to take different types of Oracle backups using RMAN.
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

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now