How to Do Bit Operations with UTL_RAW

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

• Oracle BITAND Function: A Simple Guide
• Oracle HEXTORAW Function: A Simple Guide

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.