Talend community forum / tOraclesp doesn't perform well

tpk · 2012-04-28 15:44:00

Hi All,

I have a stored procedure which will truncate the Global Temporary table and Insert a new value in the Global Temporary table and based on that value in that table the procedure will do updates in other views. I have placed the Stored procedure in tOracleSP and executed the job but it doesn't work. The same Procedure i executed in SQL developer and it is working perfectly. I don't understand what is wrong in tOracleSP component and more intrestingly the job runs successfully with out showing any errors but if i see the data in the views it doesn't appear

Why is it so? is this some thing talend doesn't handle Global Temporary Views properly?

Code:

CREATE OR REPLACE PROCEDURE finance_daily_report AS
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;

-- UPDATE VISITOR DATA
UPDATE edw.fact_finance_daily fd
SET (visit_count, visitor_count) = ( 
  SELECT
    visit_count
    ,visitor_count
  FROM edw.finance_daily_visitors_view dv 
  WHERE
    fd.website_id = dv.catalog_id
    AND fd.time_id = dv.date_id
)
WHERE
  fd.website_id <> 'amazon'
  AND fd.time_id IN (SELECT time_id FROM edw.temp_daily_finance_rpt_cal)
;
commit;

The above is the SP which i am using. I have attached the image of my job for your reference

Thanks and Regards,
Pavan

Uploaded Images

Last edited by tpk (2012-04-28 15:49:12)

tpk · 2012-04-30 04:25:03

Hi All,

Any Ideas on the above mentioned scenario why tOracleSP is not able to truncate Global Temporary table and Insert the latest value in it?

Kindly Provide your valuable suggestions.

Thanks and Regards,
Pavan

tpk · 2012-04-30 07:39:04

Hi All,

Any Ideas?

Thanks and Regards,
Pavan

tpk · 2012-04-30 15:31:07

Hi All,

Any Updates please?

Thanks and Regards,
Pavan

tpk · 2012-04-30 18:13:07

Hi All,

Any ideas why the tOracleSP is not able to handle Global TemporaryTables properly? Is this a bug? or Is there any solution for this?

Thanks and Regards,
Pavan

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

Hi All,

I think this is a bug in my SQL developer the same procedure works perfectly, but in the tOracleSP component it doesn't work

Thanks and Regards,
Pavan

phobucket · 2012-05-01 15:44:25

Hi Pavan,

Here are a couple of thoughts for troubleshooting.

Does the procedure execute correctly in Oracle if called via the commandline?

Are you executing in the same environment via SQLDeveloper and Talend. SYSDATE is the time on the OS on the box the DB is running. If there are multiple environments, are they both set to the same date/time.

In the SP, this line seems odd. I usually don't see >= and <= used together for a range. Usually one side is inclusive and the other is exclusive.

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

Why wouldn't you use either of the following? I'm not sure if either of these would impact the outcome unless SQL Developer is interpreting the query differently.
WHERE calendar_date = to_date(trunc(sysdate)) - 1

OR

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

You may have a good reason for the >= and <= usage. If so, please let me know because I'd like to learn.

Thanks,
Ben

tpk · 2012-05-01 19:38:37

Hi Ben,

No it doesn't work in Command Line.

Code:

Does the procedure execute correctly in Oracle if called via the commandline?

Yes i am executing in the same environment via SQlDeveloper and Talend. But i didn't get your point regarding SYSDATE,

Code:

Are you executing in the same environment via SQLDeveloper and Talend.  SYSDATE is the time on the OS on the box the DB is running.  If there are multiple environments, are they both set to the same date/time.

There is no specific reason in using the below mentioned logic used in where clause, actually this was a query given by the onsite team so we are just using it

Code:

In the SP, this line seems odd.  I usually don't see >= and <= used together for a range.  Usually one side is inclusive and the other is exclusive.

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

Why wouldn't you use either of the following?  I'm not sure if either of these would impact the outcome unless SQL Developer is interpreting the query differently.
WHERE calendar_date = to_date(trunc(sysdate)) - 1

OR

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

You may have a good reason for the >= and <= usage.  If so, please let me know because I'd like to learn.

Thanks and Regards,
Pavan.

phobucket · 2012-05-01 19:51:41

Re: COMMAND LINE
Ok. Now we are getting somewhere. What error are you getting when you try to run the sp via commandline?
I vaguely remember running into something like this on a project I was on a couple years ago, but can't remember what the exact culprit was.

Re: SYSDATE
If you had one box Texas and another in India, they could have different SYSDATEs depending on the time of day you were executing. My understanding is that sysdate is the date on the database OS, current_date is the date on the machine where you are running the query. But clearly this is not the problem in this instance.

tpk · 2012-05-02 06:40:28

Hi Ben,

I don't get any error in both the ways either i run it from Command Line or Talend. The Job/Procedure executes perfectly but the desired out put is not seen. The desired output should be in the temporary table the date should be SYSDATE-1 but it does not happen and as it doesn't happen obviously the Updates are not going to be affected.

Code:

Re: COMMAND LINE
Ok.  Now we are getting somewhere.  What error are you getting when you try to run the sp via commandline?
I vaguely remember running into something like this on a project I was on a couple years ago, but can't remember what the exact culprit was.

And regarding SYSDATE there are no servers located in India we generally do our complete work in remote desktop and all our servers are located in US

Code:

Re: SYSDATE
If you had one box Texas and another in India, they could have different SYSDATEs depending on the time of day you were executing.  My understanding is that sysdate is the date on the database OS, current_date is the date on the machine where you are running the query.  But clearly this is not the problem in this instance.[/quote]

Thanks and Regards,
Pavan

tpk · 2012-05-02 12:16:50

Hi all,

Any Ideas how to trouble shoot this problem?

Thanks and Regards,
Pavan

tpk · 2012-05-02 19:09:20

Hi All,

Any Ideas about this situation? I am not able to debug where the issue is.

Thanks and Regards,
Pavan

tpk · 2012-05-03 12:24:36

Hi All,

Any Ideas please?

Thanks and Regards,
Pavan

tpk · 2012-05-04 16:48:35

Hi All,

Any Ideas why the desired output is not coming even though the job executes with out giving any errors. Because of this i am not able to schedule the job and even if i schedule the job i again run the Stored Procedure manually to effect the changes

Thanks and Regards,
Pavan

Announcement

Unanswered posts

#1 2012-04-28 15:44:00

tOraclesp doesn't perform well

Code:

#2 2012-04-30 04:25:03

Re: tOraclesp doesn't perform well

#3 2012-04-30 07:39:04

Re: tOraclesp doesn't perform well

#4 2012-04-30 15:31:07

Re: tOraclesp doesn't perform well

#5 2012-04-30 18:13:07

Re: tOraclesp doesn't perform well

#6 2012-05-01 11:07:51

Re: tOraclesp doesn't perform well

#7 2012-05-01 15:44:25

Re: tOraclesp doesn't perform well

#8 2012-05-01 19:38:37

Re: tOraclesp doesn't perform well

Code:

Code:

Code:

#9 2012-05-01 19:51:41

Re: tOraclesp doesn't perform well

#10 2012-05-02 06:40:28

Re: tOraclesp doesn't perform well

Code:

Code:

#11 2012-05-02 12:16:50

Re: tOraclesp doesn't perform well

#12 2012-05-02 19:09:20

Re: tOraclesp doesn't perform well

#13 2012-05-03 12:24:36

Re: tOraclesp doesn't perform well

#14 2012-05-04 16:48:35

Re: tOraclesp doesn't perform well

Board footer