Re: SQL*Load file format question

dscott_at_is.net (David Scott) wrote:
>In article <49m0ud$3of_at_bronto.minn.net>, mtharp_at_minn.net (Michael Tharp) wrote:
>
>> John Moriarty <jmm_at_wang.com> wrote:
>>
>>
>> > I am attempting to load a fixed format file
>> >into an Oracle database using the SQL*Loader under HPUX.
>> >The problem is the rows are not terminated by
>> >carriage returns. It's a single file with one very
>> >large physical line in it. SQL*Load doesn't know where
>> >the end of the record is and subsequently reports that
>> >row is greater than 64K and terminates.
 

>> > Is there a way to specify the total length of the record?
 

>> > thanks for any info,
 

>> > John Moriarty
>> > john.moriarty_at_wang.com
>If I remember correctly (who knows, I'm at home), you can specify fix=nnnn
>(where nnnn is your record length) in your OPTIONS string to arbitrarily
>split the streamed record into fixed records.

This newsgroup is overloaded already. It REALLY would help if people would not post incorrect answers from home.

The actual syntax is "fix nnnn". I don't think that the presence of the double quotes or the absence of the equals sign would be easy to guess.

This is Unix specific, and is documented on page 14-3 of the Tools Admin Guide for Unix.

Here is an example. This loads Solaris 2 accounting summaries in binary.

LOAD DATA
INFILE 'ptacct' "fix 52"
INTO TABLE acct
( unix_uid INTEGER,

   id CHAR(8) "UPPER(RTRIM(:id,CHR(0)))",

   p_cpu    FLOAT,
   np_cpu   FLOAT,
   p_kcore  FLOAT,
   np_kcore FLOAT,
   p_con    FLOAT,
   np_con   FLOAT,
   du       FLOAT,
   pc       INTEGER,
   sc       SMALLINT "MOD(:sc+65536,65536)",
   dc       SMALLINT "MOD(:dc+65536,65536)",
   fee      SMALLINT "MOD(:fee+65536,65536)")