Convert Hex to Ascii in Oracle?

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Mon, 04 Feb 2008 13:30:07 -0500
Message-ID: <RQIpj.5269$l93.4903@en-nntp-02.dc1.easynews.com>

I'm using dbms_crypto to do an SHA-1 Mac. So far so good, but Oracle returns the Hex value of the signature and I need the base64 encoding:

> select
> DBMS_CRYPTO.MAC (
> UTL_I18N.STRING_TO_RAW ('data', 'AL32UTF8'),
> 2 ,
> UTL_I18N.STRING_TO_RAW ('key', 'AL32UTF8')
> )
> from dual;

104152C5BFDCA07BC633EEBD46199F0255C9F49D So far so good, but I need the base64 encoding of this hex value ( i.e. EEFSxb/coHvGM+69RhmfAlXJ9J0=
  which can be verified at http://www.hcidata.info/base64.htm )

The utl_encode.BASE64_ENCODE method gets me close, but not quite there:

> select
> utl_encode.BASE64_ENCODE(
> DBMS_CRYPTO.MAC (
> UTL_I18N.STRING_TO_RAW ('data', 'AL32UTF8'),
> 2 ,
> UTL_I18N.STRING_TO_RAW ('key', 'AL32UTF8')
> )
> )
> from dual;

4545465378622F636F4876474D2B363952686D66416C584A394A303D

This is the hex representation of the string I'm looking for. So, how to convert this hex string into the common base64 string? Or is there a way to get utl_encode to return the string instead of the hex represention?

I'm not finding a way to convert hex to ascii in the docs. Yes, I could write my own, but would prefer not to go there.

W2k3 Oracle 10.2

Thanks.

//Walt Received on Mon Feb 04 2008 - 12:30:07 CST

Original text of this message