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

5 REPLIES
Eleven Stars

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
Rilhia Solutions
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 :-).

 

 

 

 

 

 

Eleven Stars

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.

Rilhia Solutions
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

 

 

 

Eleven Stars

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

Rilhia Solutions