RE: timestamp refused by sql*loader

From: Dominic Brooks <dombrooks_at_hotmail.com>
Date: Tue, 15 Sep 2015 10:48:07 +0100
Message-ID: <DUB402-EAS805429A3101678863081AFA15C0_at_phx.gbl>

I don't know exactly.

But the format doesn't match the input but in spite of that with dates Oracle does its best, doesn't it?

If I try the 07-08 value in a select to_timestamp_tz() from dual with the specified format then I get ora-01857 not a valid timezone.

If I try 07-30 then I get ora-01850 hour must be between 0 and 23.

So this may or may not be relevant to the problem.

If it is relevant then it might just hint at what is going on and why one might end up working and the other not.

When the specified format doesn't match the supplied format, you can get unexpected results?

Sent from my Windows Phone

---

From: Jonathan Lewis<mailto:jonathan_at_jlcomp.demon.co.uk> Sent: ‎15/‎09/‎2015 10:29
To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: RE: timestamp refused by sql*loader

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:48:07 CEST