Q:

oracle raw type iteration

I am working with a table that contains a raw(200) field. From my client application I manage to get the value and store it in a byte[] and so that I can loop over it and get all the samples.
My raw data would be like ...
2C2B2E2B2D2C2933283030332B2F2D302F2B272F312E2B2F2F28242A2F322E

... and from there I would like to go from hex to decimal values and get an array such as 44,43,46,43
However, I would like to do a similar thing in a procedure but I don't know how to iterate over a raw field or how to cast it to byte array.
I tried with UTL_RAW.CAST_TO_BINARY_INTEGER but that would only give me the first sample

A:

Given this data ...
SQL> select col1
  2  from t23
  3  /

COL1
--------------------------------------------------------------------------------
32433242324532423244324332393333323833303330333332423246324433303246324232373246
33313245324232463246323832343241324633323245

SQL>

... a SELECT like this will produce the requisite output...
SQL> select regexp_substr(utl_raw.cast_to_varchar2(col1), '([A-Z0-9]{2})', 1, level)
  2  from t23
  3  connect by level <= ceil(utl_raw.length(col1)/2)
  4  /

REGEXP_SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(COL1),'([A-Z0-9]{2})',1,LEVEL)
--------------------------------------------------------------------------------
2C
2B
2E
2B
...
2B
2F
2F
28
24
2A
2F
32
2E

31 rows selected.

SQL> 

Use TO_NUMBER with the 'XX' mask to convert the hex into decimal ...
SQL> select to_number(
  2      regexp_substr(utl_raw.cast_to_varchar2(col1), '([A-Z0-9]{2})', 1, level)
  3          , 'XX')
  4  from t23
  5  connect by level <= ceil(utl_raw.length(col1)/2)
  6  /

TO_NUMBER(REGEXP_SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(COL1),'([A-Z0-9]{2})',1,LEVEL),
--------------------------------------------------------------------------------
                                                                              44
                                                                              43
                                                                              46
                                                                              43
                                                                              45
                                                                              44
                                                                              41
...

Finally, to populate an array, and populate it in PL/SQL with the bulk collection syntax:
create type int_nt as table of integer
/

declare
    ints int_nt;
begin
  select to_number(
          regexp_substr(utl_raw.cast_to_varchar2(col1), '([A-Z0-9]{2})', 1, level)
             , 'XX')
  bulk collect into ints
  from t23
  connect by level <= ceil(utl_raw.length(col1)/2);
end;
/

