RE: timestamp refused by sql*loader
Date: Tue, 15 Sep 2015 09:25:53 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A3E48_at_EXMBX01.thus.corp>
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
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Dominic Brooks [dombrooks_at_hotmail.com] Sent: 15 September 2015 10:19
To: jose.soares_at_sferacarta.com; oracle-l_at_freelists.org 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_at_sferacarta.com> Sent: ý15/ý09/ý2015 09:23
To: oracle-l_at_freelists.org<mailto: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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 15 2015 - 11:25:53 CEST