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.
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
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.
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
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
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
