RAW values are plain bytes, and UTL_RAW is the package for working with them: taking pieces, joining, comparing, and combining them bit by bit with AND, OR, XOR, and NOT. Bit operations make one byte hold up to eight yes-or-no flags, such as the services included in a cabin class.
Code for This Guide
The main examples are in the examples/pkg-raw-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
utl_raw.bit_and(r1, r2) utl_raw.bit_or(r1, r2) utl_raw.bit_xor(r1, r2) utl_raw.bit_complement(r) utl_raw.substr(r, pos, len) utl_raw.concat(r1, r2, ...) utl_raw.length(r) utl_raw.compare(r1, r2)
RAW Basics
CAST_TO_RAW turns text into its bytes, and CAST_TO_VARCHAR2 turns bytes back into text, so the results can be read.
Example:
declare
v_raw raw(100) := utl_raw.cast_to_raw('NIMBUS');
procedure show (p_label varchar2, p_value varchar2) is
begin
dbms_output.put_line(rpad(p_label, 10) || p_value);
end;
begin
show('bytes:', rawtohex(v_raw) || ' (' || utl_raw.length(v_raw) || ')');
show('text:', utl_raw.cast_to_varchar2(v_raw));
show('substr:', utl_raw.cast_to_varchar2(utl_raw.substr(v_raw, 2, 3)));
show('reverse:', utl_raw.cast_to_varchar2(utl_raw.reverse(v_raw)));
show('concat:', utl_raw.cast_to_varchar2(
utl_raw.concat(v_raw, utl_raw.cast_to_raw(' AIR'))));
show('overlay:', utl_raw.cast_to_varchar2(
utl_raw.overlay(utl_raw.cast_to_raw('**'), v_raw, 3)));
show('copies:', rawtohex(utl_raw.copies('00FF', 3)));
show('xrange:', rawtohex(utl_raw.xrange('41', '46')));
show('compare:', utl_raw.compare(v_raw, utl_raw.cast_to_raw('NIMBLE')));
end;
/Output:
bytes: 4E494D425553 (6) text: NIMBUS substr: IMB reverse: SUBMIN concat: NIMBUS AIR overlay: NI**US copies: 00FF00FF00FF xrange: 414243444546 compare: 5 PL/SQL procedure successfully completed.
NIMBUS is the six bytes 4E494D425553. SUBSTR, REVERSE, CONCAT, and OVERLAY work on bytes, COPIES repeats a value, XRANGE lists every byte between two values, and COMPARE returns the position of the first byte that differs, 5, or 0 when the values are equal.
Flags in a Byte
Each service is one bit: 01 meal, 02 Wi-Fi, 04 lounge, 08 priority boarding. Business has all four, 0F; economy has meal and Wi-Fi, 03.
Example:
declare
-- one bit per service: 01 = meal, 02 = Wi-Fi, 04 = lounge, 08 = priority boarding
v_business raw(1) := hextoraw('0F');
v_economy raw(1) := hextoraw('03');
v_wifi raw(1) := hextoraw('02');
procedure show (p_label varchar2, p_value varchar2) is
begin
dbms_output.put_line(rpad(p_label, 21) || p_value);
end;
begin
show('economy has Wi-Fi?', case when utl_raw.bit_and(v_economy, v_wifi) = v_wifi
then 'yes' end);
show('business only:', rawtohex(utl_raw.bit_xor(v_business, v_economy)));
show('either cabin:', rawtohex(utl_raw.bit_or(v_business, v_economy)));
show('not in economy:', rawtohex(utl_raw.bit_complement(v_economy)));
show('1000, big-endian:', rawtohex(utl_raw.cast_from_binary_integer(1000)));
show('1000, little-endian:',
rawtohex(utl_raw.cast_from_binary_integer(1000, utl_raw.little_endian)));
show('and back:', utl_raw.cast_to_binary_integer(hextoraw('000003E8')));
show('NUMBER 2.5 inside:', rawtohex(utl_raw.cast_from_number(2.5)));
end;
/Output:
economy has Wi-Fi? yes business only: 0C either cabin: 0F not in economy: FC 1000, big-endian: 000003E8 1000, little-endian: E8030000 and back: 1000 NUMBER 2.5 inside: C10333 PL/SQL procedure successfully completed.
- BIT_AND with 02 keeps only the Wi-Fi bit, so economy has Wi-Fi.
- BIT_XOR gives the bits that differ, 0C: lounge and priority boarding are business only.
- BIT_OR combines both cabins, and BIT_COMPLEMENT flips every bit.
The last lines cast numbers to bytes, which is covered in its own guide.
Filter by a Flag in SQL
Example:
-- services as bits: 01 meal, 02 Wi-Fi, 04 lounge, 08 priority boarding
with cabins (cabin, services) as (
select 'First', hextoraw('0F') from dual union all
select 'Business', hextoraw('0F') from dual union all
select 'Premium economy', hextoraw('0B') from dual union all
select 'Economy', hextoraw('03') from dual
)
select cabin, rawtohex(services) as bits,
case when utl_raw.bit_and(services, hextoraw('04')) = hextoraw('04')
then 'yes' else 'no' end as lounge
from cabins;Output:
CABIN BITS LOUNGE __________________ _______ _________ First 0F yes Business 0F yes Premium economy 0B no Economy 03 no
BIT_AND with 04 tests the lounge bit for each cabin, so only First and Business have it.
Things to Know
- When the two values differ in length, the extra bytes of the longer one are copied to the result unchanged, so keep flags the same length.
- Write constants with HEXTORAW, as a RAW literal, to avoid implicit conversions.
- For numbers, BITAND in SQL does the same for AND on integer values.
Related Guides
Conclusion
UTL_RAW handles RAW values byte by byte and bit by bit. Use SUBSTR, CONCAT, and COMPARE for bytes, and BIT_AND, BIT_OR, BIT_XOR, and BIT_COMPLEMENT to store and test flags packed into a few bytes.
