Using DATA IS BIG ENDIAN Clause For External Tables Does Not Work On Little Endian Platforms

(Doc ID 1133503.1)

Last updated on JUNE 15, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to 10.2
Linux x86
Microsoft Windows (32-bit)
Oracle Solaris on x86 (32-bit)
Oracle Solaris on x86-64 (64-bit)
Microsoft Windows x64 (64-bit)
Linux x86-64

Symptoms

When creating an external table that references binary data using the DATA IS BIG ENDIAN clause on a little endian platform such as Intel CPU's, the data is returned incorrectly.  For example the following code illustrates the problem:

DROP TABLE X;

--  File 'testdata.dat' containing four characters and
--  just 4 bytes long, e.g.:
--  0123
--
--  File's data in HEX:
--  0x30 0x31 0x32 0x33

CREATE TABLE X
(
   c INTEGER,
   r RAW(4)
) ORGANIZATION external
(
   TYPE oracle_loader
   DEFAULT DIRECTORY tmp
   ACCESS PARAMETERS
   (
      RECORDS FIXED 4
      CHARACTERSET WE8EBCDIC1047E
      DATA IS BIG ENDIAN
      NOBADFILE NODISCARDFILE NOLOGFILE
      FIELDS
      (
         c (1:4) UNSIGNED INTEGER(4),
         r (1:4) RAW(4)
      )
   )
   LOCATION ('testdata.dat')
);
/

SELECT c, r FROM x;

-- Result on 10.2.0.4 little endian platforms is:

858927408 30313233

-- Expected Result is:

808530483 30313233

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms