Re: SQLLDR
From: The Magnet <art_at_unsu.com>
Date: Fri, 21 May 2010 07:52:41 -0700 (PDT)
Message-ID: <3def27ef-1a83-4b4a-88e6-9f13014eac95_at_40g2000vbr.googlegroups.com>
On May 21, 9:40 am, "S. Anthony Sequeira" <nob..._at_127.0.0.1> wrote:
> On 21/05/10 15:35, The Magnet wrote:
>
>
>
> > This one has me going nuts. We are using SQLLDR to load a data file.
> > Certain records are getting kicked with an ORA-01438 (value larger
> > than specified precision allowed for this column)
>
> > Problem the column is a DATE: price_date date "MM/DD/YYYY",
>
> > How do you get a precision with a date column?? The format is
> > correct: 05/03/2010.
>
> > It's a lot of data, so unless someone wants to see it, I will not post
> > the record(s) and control file. But again, strange error for a date
> > column. And, the fields/columns match up fine.
>
> The controlfile would be useful + (at least some of) the bad file, and
> relevant logfile snippets. Also the sql*loader command line used.
>
> Also your NLS settings on server and client
>
> --
> S. Anthony Sequeira
> ++
> Q: What is purple and commutes?
> A: An Abelian grape.
> ++
q0q3_dchg,
q0q3_grwth,
ticker,
shares_out,
)
Date: Fri, 21 May 2010 07:52:41 -0700 (PDT)
Message-ID: <3def27ef-1a83-4b4a-88e6-9f13014eac95_at_40g2000vbr.googlegroups.com>
On May 21, 9:40 am, "S. Anthony Sequeira" <nob..._at_127.0.0.1> wrote:
> On 21/05/10 15:35, The Magnet wrote:
>
>
>
> > This one has me going nuts. We are using SQLLDR to load a data file.
> > Certain records are getting kicked with an ORA-01438 (value larger
> > than specified precision allowed for this column)
>
> > Problem the column is a DATE: price_date date "MM/DD/YYYY",
>
> > How do you get a precision with a date column?? The format is
> > correct: 05/03/2010.
>
> > It's a lot of data, so unless someone wants to see it, I will not post
> > the record(s) and control file. But again, strange error for a date
> > column. And, the fields/columns match up fine.
>
> The controlfile would be useful + (at least some of) the bad file, and
> relevant logfile snippets. Also the sql*loader command line used.
>
> Also your NLS settings on server and client
>
> --
> S. Anthony Sequeira
> ++
> Q: What is purple and commutes?
> A: An Abelian grape.
> ++
Ok, it was just the the records are long and such. But here is the record and control file:
464287135|892800|824296|100331|805223|091231|656665|090930|863228| 090630|58.05|05/03/2010|44691447403|0.00|0|0.00|NY| 0|||||||||||||||||||||||||44736541000|45093597|37038266|40904152
LOAD DATA
INFILE *
DISCARDFILE vhold.dsc
TRUNCATE INTO TABLE vhold
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
cusip,
inst_num,
q0_shares, q0_yymmdd, q1_shares, q1_yymmdd, q2_shares, q2_yymmdd, q3_shares, q3_yymmdd, price, price_date "TO_DATE(:price_date,'MM/DD/YYYY')", q0q1_dchg, q0q1_grwth,
q0q3_dchg,
q0q3_grwth,
ticker,
shares_out,
Q0_SHPRN, Q0_PUTCALL, Q0_INVSOLE, Q0_INVSHAR, Q0_INVOTH, Q0_MANAGER, Q1_SHPRN, Q1_PUTCALL, Q1_INVSOLE, Q1_INVSHAR, Q1_INVOTH, Q1_MANAGER, Q2_SHPRN, Q2_PUTCALL, Q2_INVSOLE, Q2_INVSHAR, Q2_INVOTH, Q2_MANAGER, Q3_SHPRN, Q3_PUTCALL, Q3_INVSOLE, Q3_INVSHAR, Q3_INVOTH, Q3_MANAGER, Q0_REPORTED_VALUE, Q1_REPORTED_VALUE, Q2_REPORTED_VALUE, Q3_REPORTED_VALUE
)
NLS INFO:
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.2.0.1.0Received on Fri May 21 2010 - 09:52:41 CDT