topic tOracleOutput doesn't "Truncate Insert" values into target table in Talend Studio

tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 06:17:41 GMT

Hi All,
I have a problem which is not making me sleep, kindly if any one knows please help me out. I will explain you my situation below
Database racle 11g
Oracle SQl developer:Version 3.1.06
OS:Windows Server 2008 R2 Datacenter
TOS for DI:5.0.1.r74687
Scenario:
It's asimple job but making me not to sleep from last two days, here are the details of the situation
1. I have two Oracle tables one Source and another destination table. I want to move the data from source to target.
2. I have used tOracleInput(Source Table name IM_TIME) and tOracleOutput(Target Table Name:TEMP_DAILY_FINANCE_RPT_CAL).
3. Source table contains many columns but i want only two columns data (time_id (Number), calander_date(Date)).
4. Target table contains only two columns with same column definition as of source table (time_id (Number), calander_date(Date)).
5. In Source table i am using a condition in select statement to retieve data which is part of our business logic, the logic is working correctly and even i am
able to print the output of "Select" statement in tJava component (Testing Purpose).
6. So every thing is fine till here now, so i have given connection to my Target table from Source and i have put "Truncate Insert" in Target table
7. Saved my job and run my job(F6), job gets executed successfully with our errors, which means
as per the job it should Truncate the Target table and insert the output of Select statement in to Target table, but it doesn't happen at all

tOracleInput -------> tOracleOutput
I tried to attach the Screen Shots of my job so that it will make you better understand my situation but i think there is problem with uploading images. My all images were below 200KB. I could only attach only one image i had really good images for the above situation explained but this is very embrassing that i could not upload images, since i am not able to upload images i will explain in detail the SQL statement used in Source table (tOracleInput) below,
"SELECT time_id, calendar_date
FROM edw.dim_time
WHERE
calendar_date ='"+TalendDate.formatDate("dd-MMM-yy", TalendDate.addDate(TalendDate.parseDate("dd-MMM-yy HH:mm:ss",
TalendDate.getDate("dd-MMM-yy HH:mm:ss")), -2, "dd"))+"'"

The above mentioned SQL statement is the one which i use in Source Table(tOracleInput).

Kindly help me with a solution, please correct me if i am wrong any where. Waiting for reply.
Thanks and Regards,
Pavan

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 06:47:14 GMT

Hi Pavan
You'd better check this job step by step.
No.1: Link tOracleInput with tLogRow. Can you see the output rows occur?
If the result is 0 row, it means the query is wrong.
No.2: Code in tJava as follow.

String date=TalendDate.formatDate("dd-MMM-yy", TalendDate.addDate(TalendDate.parseDate("dd-MMM-yy HH:mm:ss",
TalendDate.getDate("dd-MMM-yy HH:mm:ss")), -2, "dd"));
System.out.println(date);

Is this time correct?
Because the DB is Oracle, you'd better use to_date(), SYSDATE() methods which are predefined in Oracle instead of TalendDate.

trunc(sysdate)-2 
or
to_char(sysdate-2,'dd-MMM-yy')

Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 07:08:55 GMT

Hi Pedro,
Thanks for the quick reply, i tried both things mentioned by you and both are working perfectly and i am getting output when i use tLogrow.
I am attaching the image of tLogrow output which i got. But i think you understood my problem the selected values are not getting inserted in target table, first of all the target table itself is not getting truncated
Thanks and Regards,
Pavan

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 07:30:35 GMT

Hi Pavan
Now I think this is your requirement.

1. I have two Oracle tables one Source and another destination table. I want to move the data from source to target.
2. I have used tOracleInput(Source Table name:DIM_TIME) and tOracleOutput(Target Table Name:TEMP_DAILY_FINANCE_RPT_CAL).
3. Source table contains many columns but i want only two columns data (time_id (Number), calander_date(Date)).
4. Target table contains only two columns with same column definition as of source table (time_id (Number), calander_date(Date)).

Why don't you create a job as follow?
tOracleInput--main-->tFilterColumn--main-->tOracleOutput
The function of tFilterColumn is to get time_id (Number), calander_date(Date) only.
Why do you use "Truncate Table" here?
If you want to do ETL from one Oracle Table to another one, you'd better choose "Create table if not exist".
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 08:04:43 GMT

Hi Pedro,
you mean to say that instead of using the entire Talend expression, use simply trunc(sysdate)-2 or to_char(sysdate-2,'dd-MMM-yy') pre-defined oracle functions in the select query which i had written in tOracleInput, am i correct?
And coming to your question why Truncate Insert instead of "Create table if not exists" option, the explanation is as follows
The target table definition is already defined and created. I use Truncate Insert because the value which gets inserted in the target table will be used by another tables for updating values.
And thank you for your suggestion to use tFilter, I would rather use tMap where i have control over mapping.
But if this " trunc(sysdate)-2 or to_char(sysdate-2,'dd-MMM-yy')" will work out in place of the Talend stmt which i used in select query that will make my job more easier
What do you suggest?
Thanks and Regards,
Pavan

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 08:25:36 GMT

Hi Pavan
Now forget all the methods about to_date() or TalendDate.
Please show me the issue you got. A screenshot is preferred.
Or I may misunderstand your requirement.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 08:41:59 GMT

Hi Pedro,
OK, let me put in single statement, "The job executes perfectly but my purpose of doing that job is not served"
After the job gets executed the target table should be truncated and insert the values in the target table based on the select query output
I attach image of the output that the target table currently has, the output shows it has one value in the table, so what i want now is if i run my talend job it should truncate the old value and insert new value i.,e TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00, but this is not happening
Forgive me if my English confuses you, i am sorry for that.
Thanks and Regards,
Pavan

Hi Pavan
Now forget all the methods about to_date() or TalendDate.
Please show me the issue you got. A screenshot is preferred.
Or I may misunderstand your requirement.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 08:50:27 GMT

Hi Pedro,
I tried to attach the image but the system is not accepting. I think now you should be clear with the problem that i am facing which i mentioned below
Thanks and Regards,
Pavan

Hi Pedro,
OK, let me put in single statement, "The job executes perfectly but my purpose of doing that job is not served"
After the job gets executed the target table should be truncated and insert the values in the target table based on the select query output
I attach image of the output that the target table currently has, the output shows it has one value in the table, so what i want now is if i run my talend job it should truncate the old value and insert new value i.,e TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00, but this is not happening
Forgive me if my English confuses you, i am sorry for that.
Thanks and Regards,
Pavan
Hi Pavan
Now forget all the methods about to_date() or TalendDate.
Please show me the issue you got. A screenshot is preferred.
Or I may misunderstand your requirement.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 09:24:29 GMT

Hi Pavan
The size of this uploaded image should be less then 1024x768 pixels.
How many rows in target table before you run the job?
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 09:30:16 GMT

Hi Pedro,
The target table at any time contains only one row. Before i run my job also only one row is present, but the values should change after running the job. Here you go with the image attached

Hi Pavan
The size of this uploaded image should be less then 1024x768 pixels.
How many rows in target table before you run the job?
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 09:47:06 GMT

Hi
Got it.
I think you misunderstood the usage of "Truncate Table".
Because TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00 and TIME_ID:20120404, CALENDAR_DAY:04-APR-2012 00:00:00 are not the same record.
You'd better choose "Drop and create table" instead of "Truncate Table".
Here is the usage of Truncate Taleb.
The data in target table.
TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00
The date in source table.
TIME_ID:20120403, CALENDAR_DAY:04-APR-2012 00:00:00
You will see these two records have the same TIME_ID and different CALENDAR_DAY.
Then your previous job will work.
In short, the records in source and target which you want to truncate should own the same key.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 10:36:42 GMT

Hi
Got it.
I think you misunderstood the usage of "Truncate Table".
Because TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00 and TIME_ID:20120404, CALENDAR_DAY:04-APR-2012 00:00:00 are not the same record.
You'd better choose "Drop and create table" instead of "Truncate Table".
Here is the usage of Truncate Taleb.
The data in target table.
TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00
The date in source table.
TIME_ID:20120403, CALENDAR_DAY:04-APR-2012 00:00:00
You will see these two records have the same TIME_ID and different CALENDAR_DAY.
Then your previous job will work.
In short, the records in source and target which you want to truncate should own the same key.
Regards,
Pedro

Hi Pedro,
This information is useful and makes sense to me, i forgot that the source table is having the primary key on TIME_ID column. I have one more quick question if i use drop and create option for target table will the old structure of the target table be created or the structure of source table will be created?

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 10:40:41 GMT

Hi
The table will be created based on the schema of tOracleOutput.
Always it is the same with the target table.
One more thing. Don't forget to type "Length" of each column in tOracleOutput(Image 1).
Or you will get error.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 10:46:08 GMT

Hi Pedro,
Thanks, Thank you very much this information is really useful, sure i will keep in mind to print the length of each column.
Have a wonderful day ahead!
Thanks and Regards,
Pavan

Hi
The table will be created based on the schema of tOracleOutput.
Always it is the same with the target table.
One more thing. Don't forget to type "Length" of each column in tOracleOutput.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 10:47:15 GMT

Hi Pavan
Glad to help you.
Regards,
Pedro

Re: tOracleOutput doesn't "Truncate Insert" values into target table

Anonymous — Fri, 06 Apr 2012 11:16:13 GMT

Hi Pedro,
Can you help me how to run winzip.exe using talend?. I tried to use tFileUnarchive but it doesn't work since my file format is .tar.gz. Do you have any sample job to run winzip.exe using talend.
Thanks and Regards,
Pavan

Hi Pavan
Glad to help you.
Regards,
Pedro