SQLLDR [Oracle]

Prev: How to make DBA_AUDIT_TRAIL’s status to VALID
Next: Requirement:SAp JNET

From: The Magnet on 21 May 2010 10:35

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.

From: S. Anthony Sequeira on 21 May 2010 10:40

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.
++

From: The Magnet on 21 May 2010 10:52

On May 21, 9:40 am, "S. Anthony Sequeira" <nob...(a)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.0

From: The Magnet on 21 May 2010 11:03

From: S. Anthony Sequeira on 21 May 2010 11:26

On 21/05/10 16:03, The Magnet wrote:
> On May 21, 9:40 am, "S. Anthony Sequeira"<nob...(a)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
>>
[...]
>
> Whatever, strange, weird. I removed the decimal percition on the
> numeric columns, and wham, everything loaded. Go figure.
>

Cool.
--
S. Anthony Sequeira
++
Don't look now, but there is a multi-legged creature on your shoulder.
++

| Next | Last
Pages: 1 2
Prev: How to make DBA_AUDIT_TRAIL’s status to VALID
Next: Requirement:SAp JNET