tOracleInput & bind variables

[grizmi]

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

[Anonymous]

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

[grizmi]

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

[Anonymous]

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.

[grizmi]

'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

[Anonymous]

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