topic Re: SQL Spool file on CMD Component exports more Data than expected in Talend Studio

SQL Spool file on CMD Component exports more Data than expected

Anonymous — Tue, 26 Feb 2019 17:28:59 GMT

i have a Oracle Table "Sales" with columns ID,Sales,TIMESTAMP. Data looks like this:

ID  Sales TimeStamp1    30   2018-08-20 00:00:00.989900 +02:00 
1    35   2018-08-21 05:00:00.989900 +02:00
...
1    35   2018-08-27 05:00:00.989900 +02:00

i created a Talend Job to execute a SQL Spool file in CMD mode to export a Query into csv. The Spoolfile look like this:

alter session set NLS_TIMESTAMP_TZ_FORMAT ='YYYY-MM-DD HH24:mi:ss.ff6 TZH:TZM';
alter session set NLS_TIMESTAMP_FORMAT ='YYYY-MM-DD HH24:mi:ss.ff6';
alter session set NLS_DATE_FORMAT ='YYYY-MM-DD';
alter session set NLS_NUMERIC_CHARACTERS ='.,';spool C😕test.csvSET ECHO OFF
SET ...
SELECT * FROM Sales where timestamp< to_timestamp('2018-08-25 00:00:00.0000000','YYYY-MM-DD HH24:mi:ss:ff66 TZH:TZM')

when the TalendJob runs the Query on CMD mode, it gives me more data than expected with the Data to '2018-08-25 01:00:00'.

when i execute the SQL Query on Oracle Server manually, it gives correct Data to '2018-08-25 00:00:00'

==> Query on CMD on Talend give 1 hours of Data more than expected.

i don't really understand why that Problem happens. My assumption is the Problem Timestamp in the Query "'2018-08-25 00:00:00.0000000'". this Timestamp has no time zone. but i am not sure.

can you please help me with this Problem? Thankyou.

Re: SQL Spool file on CMD Component exports more Data than expected

akumar2301 — Tue, 26 Feb 2019 22:28:33 GMT

does your server timezone is different than database timezone ?

I am asking because you are comparing timestamp with timezone datatype with timestamp datatype.

i suggest you to use to_timestamp_tz instead and specify fix time zone e.g. TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MIS TZH:TZM')

Re: SQL Spool file on CMD Component exports more Data than expected

Anonymous — Wed, 27 Feb 2019 03:50:20 GMT

Hi,

Could you please advise the rationale to use the spool instead of tOracleInput?

You can configure the format of date and timestamp columns in tOracleInput also according to Java format.

Please refer the details below for formats.

https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

After extracting, you can push it to a file path of your choice.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂