Binary file formats and network protocols store numbers as bytes: four-byte integers, eight-byte doubles, in big-endian or little-endian order. UTL_RAW converts between PL/SQL numbers and those byte forms, so PL/SQL can read and write binary data that other programs produce.
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.cast_from_binary_integer(n [, endianess]) utl_raw.cast_to_binary_integer(r [, endianess]) utl_raw.cast_from_binary_double(d [, endianess]) utl_raw.cast_to_binary_double(r [, endianess]) utl_raw.cast_from_binary_float(f [, endianess]) utl_raw.cast_to_binary_float(r [, endianess]) utl_raw.cast_from_number(n) utl_raw.cast_to_number(r)
ENDIANESS is UTL_RAW.BIG_ENDIAN, the default, UTL_RAW.LITTLE_ENDIAN, or UTL_RAW.MACHINE_ENDIAN.
Cast Each Number Type
Example:
declare
v_raw raw(32);
begin
v_raw := utl_raw.cast_from_number(123.45);
dbms_output.put_line('NUMBER 123.45: ' || rawtohex(v_raw)
|| ' -> ' || utl_raw.cast_to_number(v_raw));
dbms_output.put_line('1000 big-endian: '
|| rawtohex(utl_raw.cast_from_binary_integer(1000)));
dbms_output.put_line('1000 little-end.: ' || rawtohex(
utl_raw.cast_from_binary_integer(1000, utl_raw.little_endian)));
v_raw := utl_raw.cast_from_binary_integer(-1);
dbms_output.put_line('integer -1: ' || rawtohex(v_raw)
|| ' -> ' || utl_raw.cast_to_binary_integer(v_raw));
v_raw := utl_raw.cast_from_binary_double(1.5d);
dbms_output.put_line('BINARY_DOUBLE 1.5: ' || rawtohex(v_raw)
|| ' -> ' || utl_raw.cast_to_binary_double(v_raw));
v_raw := utl_raw.cast_from_binary_float(1.5f);
dbms_output.put_line('BINARY_FLOAT 1.5: ' || rawtohex(v_raw)
|| ' -> ' || utl_raw.cast_to_binary_float(v_raw));
end;
/Output:
NUMBER 123.45: C202182E -> 123.45 1000 big-endian: 000003E8 1000 little-end.: E8030000 integer -1: FFFFFFFF -> -1 BINARY_DOUBLE 1.5: 3FF8000000000000 -> 1.5E+000 BINARY_FLOAT 1.5: 3FC00000 -> 1.5E+000 PL/SQL procedure successfully completed.
- 1000 is the four bytes 000003E8 in big-endian order and E8030000 in little-endian order, the order most PC file formats use.
- Oracle NUMBER 123.45 is C202182E, Oracle's internal format, which only Oracle reads.
- -1 as a binary integer is FFFFFFFF, in two's complement.
- BINARY_DOUBLE and BINARY_FLOAT use the IEEE 754 formats, eight and four bytes, which other languages read directly.
Things to Know
- Use the same byte order to read a value as was used to write it.
- Binary integers are four bytes and signed; larger whole numbers need two values or a double.
- For exchanging numbers with other systems, use the IEEE formats, not CAST_FROM_NUMBER.
Related Guides
Conclusion
UTL_RAW casts PL/SQL integers, doubles, floats, and Oracle numbers to bytes and back, in the byte order you choose. Use it to read and write binary formats, with IEEE types and an agreed byte order for anything shared with other programs.
