sqlload Problem

From: Michael J. Loeffler <mloeffler_at_teletrade.com>
Date: Wed, 14 Jul 1999 22:51:41 GMT
Message-ID: <378d1474.95303622_at_news.albany.net>

Hi -
[Quoted]   I am having a problem with SQLLOAD. The problem I am having DID NOT occur in Oracle v7.0, but is happening on Oracle v8.0.5

I use sqlload to load in flat files from our system (fixed format), and many of these files contain binary data, or rather native data types intrinsic to the system. For example, one field is a word sized integer.
OS BTW is OpenVMS, but the concept of a raw integer being in a data file is not really specific to VMS. A word sized integer here being composed of two bytes, and the example that is giving me problems is when the number 778 (as ONLY one example) is expressed as a word-sized integer becomes a LF and ETX set of ASCII characters. This may be different on some systems depending on if it's a big or little endian scheme, etc.

So a record that looks something like:

Michael(LF)(ETX)BlahBlah

Where (LF) is the SINGLE Line-feed character Where (ETX) is the SINGLE End of Text character Both together comprising the integer 778.

When sqlload is used on Ora7.0, it works perfectly fine and treats it as one record. On Ora8.0, it pukes on the embedded LF character and thinks that it's 2 records. BTW: If the number were something else that didn't cause a LF character to be imbedded, Oracle *DOES* treat it properly.

My control file looks something like this:

load data
infile 'orc_temp:export.txt'
badfile 'orc_temp:export.bad'
replace
into table card
(

     field1 position(1:7) char,
     id       position(8:9) smallint,
     field3 position(10:18) char

)

I've tried specifiying FIXED 18 under the 'infile' parameter. I've tried keeping records together by the addition of continueif this (8) = X'0A'

These changes seemingly have no effect fixing my problem. I know that I could write an intermediate program to change the native data types to a string and then load from this file, but this would mean that I have a *LOT* of programs to write. Surely, there must be a better solution, or a fix for this problem? Thanks in advance.

---

All opinions expressed are my own, and not necessarily those of my company.

Michael J. Loeffler
Manager of Information Systems
Teletrade, Inc.

MichaelL_at_Teletrade.com Received on Thu Jul 15 1999 - 00:51:41 CEST