Home » RDBMS Server » Server Utilities » Loading binary data (Oracle 10g)
Loading binary data [message #298282] Tue, 05 February 2008 09:56 Go to next message
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 #298289 is a reply to message #298282] Tue, 05 February 2008 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your data file what if the format? string or binary?

Regards
Michel
Re: Loading binary data [message #298294 is a reply to message #298289] Tue, 05 February 2008 10:27 Go to previous messageGo to next message
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 #298295 is a reply to message #298294] Tue, 05 February 2008 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know what S9(04) means but if this is binary number data and your target server OS has not the same number presentation than the source one, there is no way to load the data.
Otherwise you can use of the Nonportable Datatypes.

Regards
Michel
Re: Loading binary data [message #298333 is a reply to message #298282] Tue, 05 February 2008 13:42 Go to previous messageGo to next message
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 Cool = 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
Re: Loading binary data [message #298335 is a reply to message #298333] Tue, 05 February 2008 14:17 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.
In addition, Oracle supported platforms and their endianess is listed in V$DB_TRANSPORTABLE_PLATFORM.

Regards
Michel
Previous Topic: Import export
Next Topic: need IMP.exe file
Goto Forum:
  


Current Time: Fri Dec 09 23:34:26 CST 2016

Total time taken to generate the page: 0.16855 seconds