Re: timestamp refused by sql*loader

From: Jose Soares Da Silva <jose.soares_at_sferacarta.com>
Date: Tue, 15 Sep 2015 11:46:39 +0200
Message-ID: <55F7E8FF.2050106_at_sferacarta.com>

I dumped this data from a database as it is (as you can see below), some rows has the timezone others doesn't. I don't think the problem is in timezone because it works with dates 2009-07-08 00:00:00 but 2009-05-31 00:00:00

6021|476.320|3538|ACNopQ9tgThybjR+KU/oKA==|2015-07-23|2009-07-08 
11:30:54.662617|
6041|376.401|3558|0C1cV4ikOKgmWjxZDZ06GQ==|2015-07-24|2009-07-08 
12:28:54.213583|
6042|376.400|3559|iYB9Ay3HjWN5J66aT3pSzw==|2015-07-24|2009-07-08 
12:32:56.399992|
6043|376.399|3560|yt2NxnxJxj7BGpzDGtMAbQ==|2015-07-24|2009-07-08 
12:36:27.685771|
6061|#3578|3578||2015-07-30|2009-07-08 13:56:52.795157|
6081|#3598|3598||2015-08-12|2009-07-08 08:09:30.684979|
6101|#3618|3618||2015-08-20|2009-07-08 07:06:29.731902|
6121|#3638|3638||2015-08-26|2009-07-08 11:19:39.587792|

6141|#3658|3658||2015-08-28|2009-07-08 12:52:14.914062| 6161|819.151|3678|3PgF2Ng6sy+RVEYhM6g6Hg==|2015-09-04|2009-07-08 10:19:22.580832|
6181|#3698|3698||2015-09-08|2009-07-08 07:59:06.275072|

2|176.083|214|2RDlR5suhE3wqacOKWyu/Q==|2014-07-31|2009-07-08 00:00:00|
1|176.082|213|le+AQLMSN2SAeGJKvnVNEg==|2009-05-29|2009-07-08 00:00:00|
3|176.084|215|Ls+iLp458MDYudSlyHWPkw==|2014-07-14|2009-07-08 00:00:00|
4|176.085|216|BNDezg80+8/um1MxfVeyrA==|2009-05-29|2009-07-08 00:00:00|
5|176.086|217|3hw/VTiG/3bWoYB5UoulnA==|2009-05-29|2009-07-08 00:00:00|
6|176.087|218|95x0yvpF3Mbzkf6jUgL7Vw==|2009-05-29|2009-07-08 00:00:00|
7|176.088|219|SM638PrziCCbj+hyVB/u8A==|2009-05-29|2009-07-08 00:00:00|
8|176.089|220||2009-05-29|2009-07-08 00:00:00|

On 15/09/2015 11:19, Dominic Brooks wrote:
> 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:46:39 CEST