Re: Convert Hex to Ascii in Oracle?

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Mon, 04 Feb 2008 14:18:53 -0500
Message-ID: <xyJpj.5271$l93.226@en-nntp-02.dc1.easynews.com>


Walt wrote:
>
> 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

And the answer is:
utl_raw.cast_to_varchar2()

Thanks!

//Walt Received on Mon Feb 04 2008 - 13:18:53 CST

Original text of this message