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?
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 :-).
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.
'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
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
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema