I did say change the 07-08 in both cases, not change the second line - but
we've still got more evidence that it's not an oddity with the formatting being
misinterpreted.
I'd try
swapping the two lines - to see if it's the line, or the position of the line
dumping the lines - in case there's a hidden character buried in the second
one.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: Jose Soares Da Silva [jose.soares@xxxxxxxxxxxxxx]
Sent: 15 September 2015 09:48
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: timestamp refused by sql*loader
07-13 was loaded but
13-08 was rejected:
Record 2: Rejected - Error on table TG_USER, column CREATED.
ORA-26041: DATETIME/INTERVAL datatype conversion error
On 15/09/2015 10:27, Jonathan Lewis wrote:
A couple of little tests I'd run:
Change 07-08 to 07-13 to see if it fails.
Change 07-08 to 13-08 to see if it passes.
Your first reported experiment hints at a possible problem with SQL*Load
using yyyy-dd-mm as the input despite your attempt to override it, it would
be nice to do firm that up.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Jose Soares Da Silva [jose.soares@xxxxxxxxxxxxxx]
Sent: 15 September 2015 09:22
To: oracle-l@xxxxxxxxxxxxx
Subject: timestamp refused by sql*loader
Hi all,
I' trying to load a db using sql*loader
my table is defined as:
name | type | length| nullable| default
-------------+ ---------------------------+ ------+ --------+
------------------
user_id | number | 38 | N | NULL
user_name | nvarchar2 | 40 | N | NULL
operatore_id | number | 38 | N | NULL
password | nvarchar2 | 200 | Y | NULL
data_password| date | 11 | N | CURRENT_DATE
created | timestamp(6) with time zone| 13 | N | CURRENT_TIMESTAMP
take a look at the following rows, the first one was loaded with no problem
but the second one was refused...
47|670.004|248|hvAlXzaOQG1f4pyLN+W5VA==|2009-07-08|2009-07-08 00:00:00|
46|276.005|239|I8XmKteQSXJnHmnyE2slFA==|2009-05-30|2009-05-30 00:00:00|
here the error message:
Record 46: Rejected - Error on table TG_USER, column CREATED.
ORA-26041: DATETIME/INTERVAL datatype conversion error
---------
then I tried this: I replaced the value of CREATED from 2009-05-30 to
2009-07-8 and then it was loaded.
I used the following commands to set my environment:
export NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF'
export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FFTZD'
What's wrong?
j
--
//www.freelists.org/webpage/oracle-l
