| Loading binary data [message #298282] |
Tue, 05 February 2008 09:56  |
oracle_faq1_2008
Messages: 3
Registered: February 2008
Location: South Dakota, USA
|
Junior Member |
|
|
I'm trying to load data from a mainframe file, where the field is defined (in COBOL) as S9(04) COMP (which is binary)...
If you look at the hex representation of the data, it looks like: 08
0E
I'm using SQLLDR and have tried various way of defining both the field on the database, and the definition in the .ctl file.
How do I go about translating the data during the load so that it loads the decimal equivalent correctly?
|
|
|
|
|
|
| Re: Loading binary data [message #298294 is a reply to message #298289] |
Tue, 05 February 2008 10:27  |
oracle_faq1_2008
Messages: 3
Registered: February 2008
Location: South Dakota, USA
|
Junior Member |
|
|
I'm doing a direct load from the mainframe. As I said, the field is defined on the mainframe file as S9(04) COMP. This of course takes only 2 bytes in the file, and the data actually looks like: .. but when viewed in hex, it would be
00
0A
(different example than shown above)
|
|
|
|
|
|
| Re: Loading binary data [message #298333 is a reply to message #298282] |
Tue, 05 February 2008 13:42 |
oracle_faq1_2008
Messages: 3
Registered: February 2008
Location: South Dakota, USA
|
Junior Member |
|
|
Thanks for your help Michel.
I think I figured it out, but will post here in case someone else runs into the same issue.
How the COMP (binary) fields are stored:
These are actually being stored in a raw hexidecimal format. Because this is base 16, there are 16 unique digits -- 0 thru 9, and then A=10, B=11, C=12, D=13, E=14, and F=15.
Each positional value of the field is expressed as a factor of 16, so for a field that is defined as S9(4) COMP:
If you viewed the mainframe file in hex, the value might look like:
08
1E
the first position (the 0) = 0 * (16*16*16) = 0
(the value times 16 to the power of 3)
the 2nd position (the 1) = 1*(16*16) = 256
(the value times 16 to the power of 2)
the 3rd position (the  = 8*(16) = 128
(the value times 16 to the power of 1)
the 4th position (the E) = 14 = 14
(the value)
total value stored in this field = 398
The next thing that needs to be considered is the way that the different systems recognize the order of these bytes.
The 2-byte integer value 1 is written as 0x0001 on a big-endian system and as 0x0100 on a little-endian system.
Through trial and error, I discovered that the mainframe uses the big-endian order, while Oracle using the little-endian order. For example, I could view this data on the mainframe, in hex format:
00
0A ==> which would resolve to 10
I knew that this should resolve to a value of 10, but it was resolving to a value of 2560. The reason being, Oracle was reversing the order of the bytes, and seeing:
00
A0 ==> which would resolve to 10*(16*16) = 2560
So to get this to load correctly:
You must include this statement in your control file, prior to the INFILE statement: BYTEORDER BIG ENDIAN
Define the fields in the ctl file as SMALLINT to accomodate an S9(4) field
Define the fields in the ctl file as INTEGER to accomodate anything longer
Fields on the database can be defined as either VARCHAR2 or NUMBER, and they will still load correctly
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
