Talend community forum / How To Handle Oracle's Global Temporary Tables?

tpk · 2012-05-05 08:00:06

Hi All,

I am having a Global Temporary table which will have one row and that contains date columns. I want to truncate the table and insert a new value in to the table. To accomplish this i have a simple truncate statement and followed by an Insert statement.

To do this initially i had used 2 tOracleRow's components, one having Truncate statement and the other one having Insert Statement and linked with OnSubjobOk from first component to second component. Now i run the job, the job executes successfully with out any errors but when i check the Global Temporary table my desired out put is not present.

I am not getting where to debug, there is no error in job but the new row is not inserted. After reading some topics in our forum i had gone for another approach i had created a stored procedure compiled it successfully and used tOracleSP and had given the SP name in the component and executed my job but the same result.

My point is i am not getting any error then why the desired out put is not shown in my table finally when the job is completed

Any Ideas please?, Kindly help me.

Code:

TRUNCATE TABLE edw.temp_daily_finance_rpt_cal; (Global Temporary Table)

INSERT INTO edw.temp_daily_finance_rpt_cal
SELECT time_id, calendar_date FROM edw.dim_time WHERE calendar_date >= to_date(trunc(sysdate)) - 1 AND calendar_date <= to_date(trunc(sysdate)) - 1;  (dim_time --- just a look up table which will have date and years)

The above are the statements which i use in my SP or tOracleRow components

Thanks and Regards,
Pavan

Last edited by tpk (2012-05-05 08:08:36)

tpk · 2012-05-06 10:36:47

Hi All,

Any Updates please?

Thanks and Regards,
Pavan

tpk · 2012-05-07 18:01:51

Hi all,

Any Updates please?

Thanks and Regards,
Pavan

tpk · 2012-05-09 15:28:56

Hi All,

Any Ideas please?

Thanks and Regards,
Pavan

janhess · 2012-05-09 15:44:05

Do you need a commit?

tpk · 2012-05-09 16:00:16

Hi Janhess,

Even if there is a commit it doesn't make much diff, because i any way Truncate the table first before inserting the new record.
But how to handle my situation do you have any idea or have you faced any issue like this earlier?

Kindly help me

Code:

Do you need a commit?

Thanks and Regards,
Pavan

janhess · 2012-05-09 16:11:54

Have you got a reject link to a tLogRow from the second tOracleRow to check that the insert isn't rejected.

Does the select statement return anything?

Last edited by janhess (2012-05-09 16:30:10)

tpk · 2012-05-09 16:33:45

Hi Janhess,

I tried putting a reject link from the second tOracleRow to tLogRow, but no rows are rejected.

Thanks and Regards,
Pavan

janhess · 2012-05-09 16:45:19

Must be the select not returning anything then.

tpk · 2012-05-09 17:00:19

Hi Janhess,

Yup the Select is not returning any value, but id i run the same Select in SQL developer out put is coming. Why?

How to fix this in Talend?

Thanks and Regards,
Pavan

janhess · 2012-05-09 17:55:18

I'd check it again in SQL developer. It doesn't look right.

tpk · 2012-05-10 10:25:13

Hi Janhess,

After Hit and Trail i had a work around to print the Values of Select Statement, I have a simple work around, I used tOracleInput, tMap,tOracleOutput and tLogRow.

In tMap i had taken schema of Source table DIM_Table which had multiple columns and in the right hand side i had schema of target table which had only two columns, So i mapped the two columns which are needed for my target table.

In tOracleOutput i had selected "Default Insert" action for the target table so the iput coming from the source will be inserted, you know but it is not happening in the target table, the value is shown in the tLogRow output after the job is executed but it is not getting inserted in the target table. I am not able to find out solution since from the past one month, One Month is a big time for me for this small job.

Is there a way in this forum to notify to the Talend team about my situation/problem i am facing.

Is this a problem with Oracle for Global Temporary Tables or with Talend? I don't think it is not a problem with Oracle because the same queries work very well in SQL developer.

Kindly help me out.

Thanks and Regards,
Pavan

shong · 2012-05-10 11:41:45

Hi
Not sure it was a problem with Oracle or with Talend. However, you can execute multiple SQL statements in one tOracleRow in the same session and commit the changes. For example:

"begin
execute immediate 'TRUNCATE TABLE edw.temp_daily_finance_rpt_cal';
INSERT INTO edw.temp_daily_finance_rpt_cal
SELECT time_id, calendar_date FROM edw.dim_time WHERE calendar_date >= to_date(trunc(sysdate)) - 1 AND calendar_date <= to_date(trunc(sysdate)) - 1;
commit;
end;"

Best regards
Shong

janhess · 2012-05-10 12:12:51

WHERE calendar_date >= to_date(trunc(sysdate)) - 1 AND calendar_date <= to_date(trunc(sysdate)) - 1

Is this actually going to find anything?

tpk · 2012-05-11 13:35:31

Hi Shong,

I am sorry i am late on this reply, actually i was out for a vacation, I will try this and will let you know. But basically i have one small doubt, is the sysdate going to do any difference, i mean if the client which i run this query is located in some place and the oracle server is located in some other place so there might be a time lag when the query executes and hence is there a chance that the data gets updated late in the server and hence when i write a Select to check whether the data is updated, the data is not present is this might be reason?

Thanks and Regards,
Pavan

shong wrote:
Hi
Not sure it was a problem with Oracle or with Talend. However, you can execute multiple SQL statements in one tOracleRow in the same session and commit the changes. For example:

"begin
execute immediate 'TRUNCATE TABLE edw.temp_daily_finance_rpt_cal';
INSERT INTO edw.temp_daily_finance_rpt_cal
SELECT time_id, calendar_date FROM edw.dim_time WHERE calendar_date >= to_date(trunc(sysdate)) - 1 AND calendar_date <= to_date(trunc(sysdate)) - 1;
commit;
end;"

Best regards
Shong

tpk · 2012-05-11 13:51:38

Hi Janhess,

This will not do any thing, we can change the query to yield the same output.

Thanks and Regards,
Pavan

janhess wrote:
WHERE calendar_date >= to_date(trunc(sysdate)) - 1 AND calendar_date <= to_date(trunc(sysdate)) - 1

Is this actually going to find anything?

Announcement

Unanswered posts

#1 2012-05-05 08:00:06

How To Handle Oracle's Global Temporary Tables?

Code:

#2 2012-05-06 10:36:47

Re: How To Handle Oracle's Global Temporary Tables?

#3 2012-05-07 18:01:51

Re: How To Handle Oracle's Global Temporary Tables?

#4 2012-05-09 15:28:56

Re: How To Handle Oracle's Global Temporary Tables?

#5 2012-05-09 15:44:05

Re: How To Handle Oracle's Global Temporary Tables?

#6 2012-05-09 16:00:16

Re: How To Handle Oracle's Global Temporary Tables?

Code:

#7 2012-05-09 16:11:54

Re: How To Handle Oracle's Global Temporary Tables?

#8 2012-05-09 16:33:45

Re: How To Handle Oracle's Global Temporary Tables?

#9 2012-05-09 16:45:19

Re: How To Handle Oracle's Global Temporary Tables?

#10 2012-05-09 17:00:19

Re: How To Handle Oracle's Global Temporary Tables?

#11 2012-05-09 17:55:18

Re: How To Handle Oracle's Global Temporary Tables?

#12 2012-05-10 10:25:13

Re: How To Handle Oracle's Global Temporary Tables?

#13 2012-05-10 11:41:45

Re: How To Handle Oracle's Global Temporary Tables?

#14 2012-05-10 12:12:51

Re: How To Handle Oracle's Global Temporary Tables?

#15 2012-05-11 13:35:31

Re: How To Handle Oracle's Global Temporary Tables?

shong wrote:

#16 2012-05-11 13:51:38

Re: How To Handle Oracle's Global Temporary Tables?

janhess wrote:

Board footer