tOracleInput & bind variables

Five Stars

tOracleInput & bind variables

Hi,

Have a job with an tOracleInput combined with a tLoop. The loop is for each year.

Sql in tOracleInput would be something like:

"Select ... From ... Where YEAR= " + ((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))

 

When you look in Oracle, you can see in the session that this passed to Oracle hardcoded (e.g. Year =2017) and Oracle is not using bind variables (e.g. Year = ?). This makes that the sql needs to be parsed for each iteration.

 

Is there any way that Talend can pass the sql to Oracle using bind variables?

 

Thanks

Community Manager

Re: tOracleInput & bind variables

If you are using a tLoop then the Oracle component is being initialised for each loop. As such it might be tricky to get this working with a tLoop. However, I'm not sure of your full requirement and haven't actually examined this requirement, so I'd be interested to know what you find.

What you might like to try is using a tOracleRow for your query. They are much better for scenarios like this where you are wanting to use transactions. Take a look here....
https://help.talend.com/reader/WWQ40R_iTE5~~9VkUQrjgQ/CCdn1_oSeiCZt4Q2dZ1RSg
Five Stars

Re: tOracleInput & bind variables

In tOracleRow I could write PL/SQL with an input parameter, but then I am complicating things. Best design is try to keep things simple :-).

 

 

 

 

 

 

Community Manager

Re: tOracleInput & bind variables

I'd argue that reading and writing SQL should be a prerequisite of being involved in Data Integration. While you can make Talend incredibly simple, an incredibly simple Talend job is not going to be the most efficient Talend job. When working with a database it is always going to be more efficient to deal with a lot of your filtering (for example) in the database. If writing PL/SQL sorts your problem with efficiency (which is what this question was originally about), there is absolutely nothing wrong with using it with a Talend job.

Five Stars

Re: tOracleInput & bind variables

'keep things simple' doesn't mean that you shouldn't know sql and understand what is happening when you write sql.

tOracleRow combined with PL/SQL would surely do the trick, was just hoping that is there was some option in tOracleInput that I missed but apparently that's not the case.

 

Maybe an option for the future. Following article mentiones that Java has PreparedStatement to use bind: https://www.akadia.com/services/ora_bind_variables.html

 

 

 

Community Manager

Re: tOracleInput & bind variables

Definitely recommend this as an improvement if you feel Talend could benefit. You should be able to log in to Talend's Jira board using your Community ID (https://jira.talendforge.org). Having said that, this *may* have changed with the new forum. It's certainly a good place to research bugs

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog