Extraction logic

[Anonymous]

 Extraction logic: Extract the data from oracle to stage table.

1) what is best way to write below code in talend.

SELECT f_amt,
l_amt,
NULL flag,
NULL id,
batch_no batch_no,
ac_branch t_br_code,
rela_acc related_acc,
drcr_ind drcr_ind
FROM rec_hist a, rec_tab_hist s
WHERE a.trn_ref_no = s.trn_ref_no
AND a.trn_dt = (SELECT MAX(trunc(starttime)) FROM xyz)
AND a.ac_no IN (SELECT VALUE
FROM rec_tab_process_param
WHERE pro_name = 'MTN'
AND param_name = 'CODE')
UNION
SELECT f_amt,
l_amt,
NULL flag,
NULL id,
batch_no batch_no,
ac_branch t_br_code,
rela_acc related_acc,
drcr_ind drcr_ind
FROM rec_hist a
WHERE a.trn_ref_no = s.trn_ref_no
AND a.trn_dt = (SELECT MAX(trunc(starttime)) FROM xyz)
AND a.ac_no IN (SELECT VALUE
FROM rec_tab_process_param
WHERE pro_name = 'MTN'
AND param_name = 'CODE')

Thanks 

[Anonymous]

Take the whole SQL statement and put it in tOracleInput component.  If it need parameterization, then use context variables or globalMap variables to build the whole string.  But put it in the query part of the tOracleInput.  This way you leverage your DB power to extract only what you need.  That will be the fastest way.  And turn on the cursors in the Oracle component.

[Anonymous]

Thanks for valuable input.

Could you please give me example on parameterization using context variables or globalMap variables. 

and  one more point as you said,  turn on the cursors in the Oracle component.

In advance setting "Use cursor" ? How it will faster. Can you please elaborate few points on this option.

Thanks

Shridhar

[Anonymous]

Hi,

The query statement is just a string.  You will build your string with variables concatenated to it

"Select blabla from table where column=" + context.somevariable

Turning on the cursors is 1 checkbox away:-)  You can easily test it yourself on your system to identify the performance difference. Depending on what your are doing, you will see the performance difference.  With Oracle, our experience is that using cursors is faster.

[Anonymous]

Thanks for your suggestion.

Using context variable or globalMap variables, which is best way to do any examples.

Regards

Shridhar

[Anonymous · Accepted solution]

globalMap if you want keep control of the variables within the Job.  Context variable if you want to allow extenal values to be passed to the job when it is started.  I generally recommend globalMap to avoid having too many context variables.  Context variables should be kept to a minimum.  As per defensive programming, if you don't want sql injection issues, then you will use globalMap and control the values in your variables:-)  

[Anonymous]

Thanks for your time and inputs.

Lot of valuable information.

Regards

Shridhar