Oracle-L: Endianness using External Tables

From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
Date: Thu, 20 Feb 2003 06:09:16 -0800
Message-ID: <F001.00552D6D.20030220060916@fatcity.com>

Hi everybody

I'm experiencing some problems with the endian byte order parameter while processing data files from a mainframe platform (OS/390). The data contains integers (smallint, 2 bytes long), which come in big endian format (high byte first). I'm importing the data on a Linux (PC) platform (Oracle 9.2.0.1.0 on SuSE 7.2).

I used the following parameters:

TDOMAIN:

DROP TABLE SHINFRA.X_TDOMAIN; CREATE TABLE SHINFRA.X_TDOMAIN
(

  ZID_DOMAIN NUMBER (15,0),
  ZHI_ORGEINHEIT NUMBER (15,0),
  ZHI_SYSEINDAT CHAR (26),
  ZHI_SYSERSDAT CHAR (26),
  ZHI_SYSGUADAT DATE,
  ZHI_SYSGUBDAT DATE,
  ZHI_SYSMSGNR CHAR (26),
  ZHI_STATUS INTEGER,
  ZID_DOMAINBEZ NUMBER (15,0),

  ZDOMAINNAME CHAR(14)
)
ORGANIZATION EXTERNAL
(

  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY SHINFRADATA
  ACCESS PARAMETERS
  (
    RECORDS FIXED 138
    CHARACTERSET='WE8EBCDIC500'
    "DATA IS BIG ENDIAN"
    FIELDS
    REJECT ROWS WITH ALL NULL FIELDS
    (

      ZID_DOMAIN  POSITION(1:8)  DECIMAL(15,0),
      ZHI_ORGEINHEIT  POSITION(9:16)  DECIMAL(15,0),
      ZHI_SYSEINDAT  POSITION(17:42)   CHAR(26),
      ZHI_SYSERSDAT  POSITION(43:68)   CHAR(26),
      ZHI_SYSGUADAT  POSITION(69:78)   CHAR(10)  DATE_FORMAT DATE MASK
"DD.MM.YYYY",
      ZHI_SYSGUBDAT  POSITION(79:88)   CHAR(10)  DATE_FORMAT DATE MASK
"DD.MM.YYYY",
      ZHI_SYSMSGNR  POSITION(89:114)   CHAR(26),
      ZHI_STATUS  POSITION(115:116)  INTEGER,
      ZID_DOMAINBEZ  POSITION(117:124)  DECIMAL(15,0),
      ZDOMAINNAME  POSITION(125:138)  CHAR(14)

    )
  )
  LOCATION ('TDOMAIN')
)
REJECT LIMIT UNLIMITED; The field of interest here is ZHI_STATUS. If it is let's say 00 01 in Hex format in the original data file, I'll get a 256 decimal in the Oracle database, which points to a byte order problem, because that would be 01 00. I tried all combinations like DATA IS BIG ENDIAN, LITTLE ENDIAN or nothing. I get the following log file entries, but the result remains the same (256 instead of 1):

LOG file opened at 02/20/03 13:53:53

Field Definitions for table X_TDOMAIN
Record format FIXED, record length 138 Data in file is in big endian format
Reject rows with all null fields

Fields in Data Source:

.......

LOG file opened at 02/20/03 13:54:33

Field Definitions for table X_TDOMAIN
Record format FIXED, record length 138 Data in file is in little endian format Reject rows with all null fields

Fields in Data Source:

.......

LOG file opened at 02/20/03 13:55:26

Field Definitions for table X_TDOMAIN
Record format FIXED, record length 138 Data in file has same endianness as the platform Reject rows with all null fields

Fields in Data Source:

It looks like the DATA IS .... ENDIAN parameter doesn't do anything. Is there a mistake / misunderstanding on my side ? I already checked Metalink, but couldn't find anything pointing to a bug related to external tables and endianness or sql*loader and endianness.

Any ideas ? I'm getting pretty desperate here.

TIA, Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: stefan.jahnke_at_nospam.bov.de
Please remove nospam to contact me via email.

visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp

Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:andrea.palluck_at_bov.de.

Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen.

As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stefan Jahnke
  INET: Stefan.Jahnke_at_bov.de

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Thu Feb 20 2003 - 08:09:16 CST