Solved

How to convert  varchar2 value to hex

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

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql Encryption with PHP 8 94
Oracle - Stored Procedure Privilge access 3 37
case statement in where clause with not exist 15 46
SQL query question 8 28
As a financial services provider, your business is impacted by two of the strictest federal regulations on record: the Sarbanes-Oxley Act and the Gramm-Leach-Bliley Act. Correctly implementing faxing into your organization to provide secure, real-ti…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

947 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

19 Experts available now in Live!

Get 1:1 Help Now