Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> AW: Endianness using External Tables

AW: Endianness using External Tables

From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
Date: Fri, 21 Feb 2003 07:55:10 -0800
Message-ID: <F001.0055471B.20030221075510@fatcity.com> 





It's pretty inconsistent. Using just "DATA IS BIG ENDIAN" and "BYTEORDERMARK
NOCHECK", it usually doesnn't work, but I also got cases, where it actually
did work. The DDL scripts are generated and the data looks the same. 
I used SQL*LOADER now and it works. I guess we will raise a tar and before
that isn't completely solved, we'll just go with loader again.
 


Stefan
 



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 <http://www.bov.de/>  
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp


<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 <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.



-----Ursprüngliche Nachricht-----


Von: Godlewski, Melissa [mailto:Melissa.Godlewski_at_hq.doe.gov]
Gesendet: Freitag, 21. Februar 2003 15:06
An: Stefan Jahnke


Betreff: RE: Endianness using External Tables




Did you try changing the characterset to UTF16? 



The only other thing I can think of is to try another datatype instead of
integer try smallint, byteint, zoned or something to see if it will convert
appropriately.



-----Original Message-----


Sent: Friday, February 21, 2003 8:15 AM


To: Godlewski, Melissa


Cc: Oracle List Fatcity (E-Mail)




Hi Melissa
 


Yes, I tried to do the same on a Windows PC today, but the results are the
same (wrong byte order).


Any other ideas ? I already reached the point, where I wrote a function,
that converts the wrong integers back to hex and these back to the correct
integer. 


Shouldn't be the way to go.
 


Regards,


Stefan
 



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 <http://www.bov.de/>  
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp


<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 <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.



-----Ursprüngliche Nachricht-----


Von: Godlewski, Melissa [mailto:Melissa.Godlewski_at_hq.doe.gov]
Gesendet: Donnerstag, 20. Februar 2003 17:22
An: Stefan Jahnke


Betreff: RE: Endianness using External Tables





Stefan, 



Just as a test, have you tried this to a different OS system besides LINUX?




-----Original Message----- 


<mailto:Stefan.Jahnke_at_bov.de> ] 



Sent: Thursday, February 20, 2003 9:09 AM 
To: Multiple recipients of list ORACLE-L 




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: 




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 <http://www.bov.de>  
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp


<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 <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


<http://www.orafaq.net>  

-- 
Author: Stefan Jahnke 
  INET: Stefan.Jahnke_at_bov.de 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com


<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). 







 

-- 
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 Fri Feb 21 2003 - 09:55:10 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US