sqlload question

Hi!

I have the following syntax in my control file.

LOAD DATA
INFILE "mydatafile"
DISCARDFILE "mydatafile.dis"
DISCARDMAX 2000
REPLACE
INTO TABLE mytable FIELDS TERMINATED BY X'13' -- X'13' is ascii for TAB
(

  field1 char,
  field2 char,
  field3 char
)

begindata

abc	01 345 789	cba
def	 1   56789      fed
ghi     0        9      ihg

where field1 is atmost 3 characters, field2 is always 10 characters
(including most ascii characters i.e spaces,:;"'~!@#$%^|&*()_+ 0-9a-zA-Z)
and field 3 can be 25 characters long. Now the problem is that since almost all ascii characters are allowed for field2, my separator is a TAB
(which I am guaranteed will never appear in field2 or any field for that
matter).

My question is this:

  How can I write the instruction for the control file so that sqlload   will do the right thing i.e put 'abc' into field1, '01 345 789' into   field2, and 'cba' into field3 oy mytable?

I have tried using PRESERVE BLANKS, TRAILING NULLCOLS but with no luck. The error that I get is the following:

  Record 1: Rejected - Error on table MYTABLE, column FIELD1.   Column not found before end of logical record (use TRAILING NULLCOLS)   ...

If I use the following,

LOAD DATA
INFILE "mydatafile"
DISCARDFILE "mydatafile.dis"
DISCARDMAX 2000
REPLACE
PRESERVE BLANKS
INTO TABLE mytable FIELDS TERMINATED BY X'13' TRAILING NULLCOLS
(

... rest is the same..

)

Record 1: Rejected - Error on table MYTABLE. ORA-01401: inserted value too large for column

Problem with using FIELDS TERMINATED BY WHITESPACE is that sqlload does not distinguish between a single space, a tab character etc. Regardless, since field2 contains a space, this will fail.

Any suggestions are appreciated. Thanks

-Ajay Received on Mon Oct 26 1998 - 20:44:05 CST