Re: UTL_RAW binary encoding

From: ddf <oratune_at_msn.com>
Date: Tue, 9 Jul 2013 17:00:21 -0700 (PDT)
Message-ID: <cffce36f-c59e-4840-b29e-20d45c5876af_at_googlegroups.com>

On Tuesday, July 9, 2013 11:28:46 AM UTC-6, neilsolent wrote:
> Hi
>
> I need to get data from a query of an integer column into a client, stored as an array of 8 bytes. In the course of getting Oracle to return the data in the required format I am using the UTL_RAW functions, but I am struggling to understand what Oracle is returning. For example, can anyone explain these results?
>
>
>
> SQL> select utl_raw.cast_from_number(5000) from dual;
>
>
>
> UTL_RAW.CAST_FROM_NUMBER(5000)
>
> --------------------------------------------------------------------------------
>
> C233
>
>
>
> SQL> select utl_raw.cast_from_number(131) from dual;
>
>
>
> UTL_RAW.CAST_FROM_NUMBER(131)
>
> --------------------------------------------------------------------------------
>
> C20220
>
>
>
> I am wanting a function that returns something like this:
>
>
>
> 20 -> 14
>
> 131 -> 83
>
> 5000 -> 1388
>
>
>
> thanks

UTL_RAW returns HEX values of binary strings; it just happens that numbers generate hex strings startng with C (at least with all of the numbers I have tried). These are not HEX numbers, but HEX strings, much different than you would expect. What you want to do is use to_Char() to get the values you expect:

SQL> select to_char(20, 'FMXXXX') from dual;

TO_CH

---

14

SQL> c/20/131
  1* select to_char(131, 'FMXXXX') from dual SQL> / TO_CH

---

83

SQL> c/131/5000
  1* select to_char(5000, 'FMXXXX') from dual SQL> / TO_CH

---

1388

SQL> David Fitzjarrell Received on Wed Jul 10 2013 - 02:00:21 CEST