Re: SQL*Loader Column Widths

From: <jstiehm_at_my-deja.com>
Date: Thu, 02 Dec 1999 21:03:20 GMT
Message-ID: <826mpl$s61$1@nnrp1.deja.com>

I've used this fix in the past as well. How do you truncate data that is too long, though?

LOAD DATA
TRUNCATE INTO TABLE MY_TABLE
FIELDS TERMINATED BY X'09'
trailing nullcols
(

Eventleadnum          integer external,
QuestionVersion       integer external,

QuestionText
QuestionName
AnswerName
AdditionalInfo char(255) "substr(:AdditionalInfo,1,10)" )

The field ADDITIONALINFO in MY_TABLE is defined as VARCHAR2(255).

I have a data file with 500 records. One record is continually rejected with the error:

Record 128: Rejected - Error on table MY_TABLE, column ADDITIONALINFO. Field in data file exceeded maximum specified length

The length of ADDITIONALINFO in this record is 372 bytes.

The funny thing is that the other records are successfully truncated to 10 characters.

Anyone see anything like this before? How did you resolve it?

Thanks.

Joseph Stiehm
Cisco Systems

In article <80h4e1$i2e$1_at_supernews.com>,   "Pete Kolton" <peter.kolton_at_nospambtinternet.com> wrote:
> Thanks Mark. That's fixed it.
>
> Mark Prebilic wrote in message <382B1339.63648F09_at_winstar.com>...
> >Pete,
> >I've run across this problem before and I was able to solve it by
> explicitly
> >indicating a size in the fields clause of the control file. I'm not
sure
> why
> >that works, but who can argue with success? Here's an example where
the
> >where_clause field is larger than 257:
> >
> >LOAD DATA
> >INFILE *
> >DISCARDFILE 'ld_mfsc_mapping_layout.dis'
> >LOG YES
> >INTO TABLE LKP_MAPPING_LAYOUT
> >APPEND
> >FIELDS TERMINATED BY '#' TRAILING NULLCOLS
> >(
> >-- FIELD_ID (Note: using SEQUENCE in the database)
> > RECORD_TYPE_ID INTEGER EXTERNAL,
> > INVOICE_TYPE_CODE,
> > FILE_NAME,
> > CT_ID_COL,
> > CID_ID_COL,
> > CDD_TEXT_COL,
> > CD_TEXT_COL,
> > CC_TEXT_COL,
> > CO_ID_COL,
> > CT_COL_ID_COL,
> > IMPORT_VALUE_COL,
> > IIR_VALUE,
> > DRIVING_TABLE,
> > DB_TABLES,
> > WHERE_CLAUSE char(500),
> > WHERE_TEXT,
> > GROUP_BY_TEXT,
> > HINT_TEXT
> >)
> >BEGINDATA
> >
> >Mark Prebilic
> >PrebSon Consulting
> >
> >Pete Kolton wrote:
> >
> >> I can't believe I've not come across this problem before, but it is
a
> while
> >> since I used Loader and maybe I never used it to load large
columns,
> but...
> >>
> >> It seems that I can only load fields that are up to 257 characters
long,
> >> despite the fact that the column is varchar2(1000).
> >>
> >> Any one know why this would be?
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Dec 02 1999 - 15:03:20 CST