Missed out the bit about if you lose the .FF in the format it should work. Or
specify .0 in the file.
Sent from my Windows Phone
________________________________
From: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>
Sent: 15/09/2015 10:19
To: jose.soares@xxxxxxxxxxxxxx<mailto:jose.soares@xxxxxxxxxxxxxx>;
oracle-l@xxxxxxxxxxxxx<mailto: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
