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;

4545465378622F636F4876474D2B363952686D66416C584A39 4A303D

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

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;
>
> 4545465378622F636F4876474D2B363952686D66416C584A39 4A303D
>
> 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