Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
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:[EMAIL PROTECTED].
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.
-----Urspr�ngliche Nachricht-----
Von: Godlewski, Melissa [mailto:[EMAIL PROTECTED]]
Gesendet: Donnerstag, 20. Februar 2003 17:22
An: Stefan Jahnke
Betreff: RE: Endianness using External TablesStefan,
Just as a test, have you tried this to a different OS system besides LINUX?
-----Original Message-----
From: Stefan Jahnke [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 20, 2003 9:09 AM
To: Multiple recipients of list ORACLE-L
Subject: Endianness using External Tables
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 fieldsFields 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 fieldsFields 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 fieldsFields 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: [EMAIL PROTECTED]
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.aspSicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED].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: [EMAIL PROTECTED]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: [EMAIL PROTECTED] (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).
