RE: timestamp refused by sql*loader

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
_at_jloracle

________________________________________
From: Jose Soares Da Silva [jose.soares_at_sferacarta.com]
Sent: 15 September 2015 09:48
To: Jonathan Lewis; oracle-l_at_freelists.org
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
> _at_jloracle
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jose Soares Da Silva [jose.soares@sferacarta.com]
> Sent: 15 September 2015 09:22
> To: oracle-l_at_freelists.org
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>

--