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
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;
/