Dominic,
I may have missed something - but the OP seems to have two lines where the
fractional seconds and time-zone are missing, but one loads and the other
doesn't.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Dominic Brooks [dombrooks@xxxxxxxxxxx]
Sent: 15 September 2015 10:19
To: jose.soares@xxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: timestamp refused by sql*loader
There are no fractional seconds on your timestamp.
There's no time zone specified either.
In which case,
1. Why use timestamp with time zone?
2. Why use timestamp over date (which contains a time element)?
Try doing a simple select to_timestamp_tz from dual with your supplied value
and format and it should fail.
The two dates will fail with different reasons which points you to the fact
that Oracle often does things with dates and timestamps implicitly in an effort
to try to get conversions to work, with unexpected results.
Sent from my Windows Phone
________________________________
From: Jose Soares Da Silva<mailto:jose.soares@xxxxxxxxxxxxxx>
Sent: 15/09/2015 09:23
To: oracle-l@xxxxxxxxxxxxx<mailto: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
