topic Re: tOracleInput & bind variables in Talend Studio

tOracleInput & bind variables

grizmi — Thu, 21 Sep 2017 14:13:59 GMT

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

Re: tOracleInput & bind variables

Anonymous — Thu, 21 Sep 2017 15:37:30 GMT

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

Re: tOracleInput & bind variables

grizmi — Mon, 25 Sep 2017 10:38:06 GMT

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

Re: tOracleInput & bind variables

Anonymous — Mon, 25 Sep 2017 10:48:32 GMT

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.

Re: tOracleInput & bind variables

grizmi — Mon, 25 Sep 2017 13:05:04 GMT

'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

Re: tOracleInput & bind variables

Anonymous — Mon, 25 Sep 2017 13:09:44 GMT

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