Determining Endian format for your database server

The servers of your Oracle databases will be of either Big or Little endianness. That is, the ordering of bytes within values. For the most part, the Oracle APIs insulate your code from ever needing to know which method your system is using.

However, there are cases, such as transportable tablespaces or working with binary numbers where the endianness of your server can come into play. Fortunately, Oracle’s data dictionary provides a convenient list of platforms and their corresponding endian configuration (starting with 10gR1.)

SQL>   SELECT platform_name, endian_format, platform_id
  2      FROM v$transportable_platform
  3  ORDER BY platform_name;

PLATFORM_NAME                       ENDIAN_FORMAT  PLATFORM_ID
----------------------------------- -------------- -----------
AIX-Based Systems (64-bit)          Big                      6
Apple Mac OS                        Big                     16
Apple Mac OS (x86-64)               Little                  21
HP IA Open VMS                      Little                  19
HP Open VMS                         Little                  15
HP Tru64 UNIX                       Little                   5
HP-UX (64-bit)                      Big                      3
HP-UX IA (64-bit)                   Big                      4
IBM Power Based Linux               Big                     18
IBM zSeries Based Linux             Big                      9
Linux IA (32-bit)                   Little                  10
Linux IA (64-bit)                   Little                  11
Linux x86 64-bit                    Little                  13
Microsoft Windows IA (32-bit)       Little                   7
Microsoft Windows IA (64-bit)       Little                   8
Microsoft Windows x86 64-bit        Little                  12
Solaris Operating System (x86)      Little                  17
Solaris Operating System (x86-64)   Little                  20
Solaris[tm] OE (32-bit)             Big                      1
Solaris[tm] OE (64-bit)             Big                      2

20 rows selected.

With a simple join to v$database you can extract your system’s platform id and lookup which endian format your system uses.

So, for example, on one of my Linux servers I run this query and I can see my database is running on a Little endian system.

SQL> SELECT p.endian_format
  2    FROM v$transportable_platform p
  3    INNER JOIN
  4         v$database d
  5      ON p.platform_id = d.platform_id;

ENDIAN_FORMAT
--------------
Little

One caveat to these queries is you need extra privileges to run them. By default they are not open to PUBLIC. So, one option, obviously, you can simply ask your DBA to look up the values for you; but, if you’re in 9i or higher, you can also derive the endian format yourself by converting binary values and comparing to big and little values to see which one you get.

Using the UTL_RAW package (which has PUBLIC execute) we can choose big, little, or native conversion. So, by converting a value to all 3 we can check if our native conversion matches the big value or the little value and we’ll know which endian format our system uses. As expected the Little and Machine formats are the same.

SQL> SELECT UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 1) big,
  2         UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 2) little,
  3         UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 3) machine
  4    FROM DUAL;

       BIG     LITTLE    MACHINE
---------- ---------- ----------
       291       8961       8961

We can make the output a little friendlier with a CASE statement on the results and once again we see my test system is Little endian.

SQL> SELECT CASE WHEN machine = big THEN 'Big'
  2              WHEN machine = little THEN 'Little'
  3         END endian
  4    FROM (SELECT UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 1) big,
  5                 UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 2) little,
  6                 UTL_RAW.cast_to_binary_integer(HEXTORAW('123'), 3) machine
  7            FROM DUAL);

ENDIAN
------
Little

It’s always nice as a developer to have escalated privileges, but not having them doesn’t mean all information necessarily remains hidden. Hopefully this little trick will prove handy for you if/when you need it.

And last, 11gR2 added the GET_ENDIANNESS function to the DBMS_UTILITY package.  Similar to the UTL_RAW constants,  the function will return 1 for big-endian and 2 for little-endian.   In a quirk of absence though, these return values are not mentioned in the PL/SQL Packages and Types Reference; instead simply stating:

RETURN VALUES
The endianness of the database platform.

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_util.htm#ARPLS73246

This oversight was corrected in the 12.1 and later documentation.

RETURN VALUES
A NUMBER value indicating the endianness of the database platform: 1 for big-endian or 2 for little-endian.

https://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73246

Even though the values are the same there is no mention of the correlation between the UTL_RAW constants and the DBMS_UTILITY function return values, nor does DBMS_UTILITY define constants of its own. I’ve not needed to do the lookup often; but when I have, I referenced the UTL_RAW constants rather than using the literal values or creating new constants. Functionally there is no special reason to pick one way or the other; to me though, using the existing package constants seemed more authoritative.