Hi there,
I have following problem with tOracleRow and maybe someone on the forum knows an answer for it or maybe I am doing something wrong.
My work flow looks like this:
tOracleConnection --> tOracleRow(use connection/die on error no more changes) --> Commit/Rollback(on subjob Ok/Wrong)
Scripts in the tOracleRow works fine unless(and here is the problem) I join inside select in the merge script:
example that works fine:
MERGE into SCHEMA1.TABLE1 fsh
USING
(
SELECT
ID2,
CUSTOMER,
FORDNR,
INFO
FROM TABLE2
) FU
ON (fsh.ID1 = FU.ID2)
WHEN MATCHED THEN UPDATE SET
FSH.CUSTOMER_NR=FU.CUSTOMER,
FSH.Ford_NR=FU.FORDNR,
FSH.INFO_DESC=FU.INFO
WHEN NOT MATCHED THEN INSERT (FSH.ID1, FSH.CUSTOMER_NR, FSH.Ford_NR, FSH.INFO_DESC) VALUES ( FU.ID2, FU.CUSTOMER, FU.FORDNR, FU.INFO)
example with problem(join in select statement):
MERGE into SCHEMA1.TABLE1 fsh
USING
(
A.SELECT
A.ID2,
A.CUSTOMER,
A.FORDNR,
A.INFO
B.TEXT
FROM TABLE2 A
JOIN
TABLE3 B
ON A.ID2=B.ID
) FU
ON (fsh.ID1 = FU.ID2)
WHEN MATCHED THEN UPDATE SET
FSH.CUSTOMER_NR=FU.CUSTOMER,
FSH.Ford_NR=FU.FORDNR,
FSH.INFO_DESC=FU.INFO
FSH.TEXT=FU.TEXT
WHEN NOT MATCHED THEN INSERT (FSH.ID1, FSH.CUSTOMER_NR, FSH.Ford_NR, FSH.INFO_DESC, FSH.TEXT) VALUES ( FU.ID2, FU.CUSTOMER, FU.FORDNR, FU.INFO, FU.TEXT)
Problem:
If I run a tOracleRow with select join it runs forever(I kill the job after 20-30min ) the same script on database takes between 1-10seconds. Am I doing everything correct? DO you need any more information?
The difference is the way you call the script. If you run the script with tools like SQLDeveloper it runs fast and if you run the script inside the tOracleRow is hangs. Is this correct?
This should not happen!
Are you sure about the connection parameters?
Does your script have line comments (not as I can see in your post here but probably in the job?).
Talend does a potential dangerous thing: it removes all line breaks you see in the SQL source code in the component. Thats why line comments e.g. cuts up ALL the following sql.
Hi,
sorry for not answering for long time but I did not get any notification about your response.
The difference is the way you call the script. If you run the script with tools like SQLDeveloper it runs fast and if you run the script inside the tOracleRow is hangs. Is this correct?
- That is correct
Are you sure about the connection parameters?
- if I run any other script it works fine so the connection parameters should be fine
Does your script have line comments (not as I can see in your post here but probably in the job?).
- No it does not have any comments in the code
Any other ideas? did you try it by yourself? Do you have similar problem?
Idea what to check is very simple
See on Oracle side what happens
( In pl-sql developer it is in menu Sessions
in sql-developer - must be like this
)
See Actual execution plan of query
(Of course u should have rgiths on v$sql views )
Seems for some reason it is different
and compare it with plan from SQL-devdoper
One of usefull SQLs :
Select a.sql_id, a.module , a.ELAPSED_TIME tm_sec , a.FIRST_LOAD_TIME load1 , a.ROWS_PROCESSED row_pr , sql_text , a.SQL_FULLTEXT full1 ,a.DISK_READS , a.BUFFER_GETS --, a.BIND_DATA , a.IS_BIND_AWARE bind_aware , a.* from v$sql a where 1=1 -- SQL_TEXT like 'Select distinct(a.as_numoff) as as_numoff%' -- and module like 'PL_SQL%' -- queris from PL_SQL developer order by FIRST_LOAD_TIME desc
Thanks for your reply,
Unfortunately I don't have access/rights to system tables/views so I am not able to check that.
The solution for me was to split merge into two merges first with the first table and second for the second table but the whole situation is kinda weird...
Thanks again
Mike
