topic Re: Oracle bind variables - again... in Talend Studio

Oracle bind variables - again...

mq123 — Wed, 03 Aug 2022 16:47:54 GMT

I see this subject has been kicking around for quite a few years with no obvious resolution. Basically Oracle really likes to see things like

SELECT dept_name FROM DEPTS

WHERE dept_id = :bind_variable;

rather than seeing an actual literal value being passed

SELECT dept_name FROM DEPTS

WHERE dept_id = 99;

Can Talend do anything like this

"out of the box"

for tOracleInput yet ? If not, what workarounds are people connecting to Oracle databases using ?

Thanks

Re: Oracle bind variables - again...

Anonymous — Thu, 04 Aug 2022 04:53:38 GMT

Hello,

You are able to add a context variable to a WHERE Clause and you need to keep in mind that the SQL you put in your component is actually a Java String.

So you need to build it like a Java String. For example, if you want to add a context variable to a WHERE Clause, you need to do something like below....

"SELECT dept_name FROM DEPTS

WHERE dept_id == '"+context.id+"'"

Feel free to let us know if it helps.

Best regards

Sabrina

Re: Oracle bind variables - again...

mq123 — Thu, 04 Aug 2022 07:02:57 GMT

We seem to be going round in the same circles that come up every time this question is asked.

My understanding is that Talend really just does a "find and replace" with its variables before execution so Oracle would still see a literal value - and thus treat each different value substitution as a different SQL statement.

What I'd like (in jdbc terms) is a prepared statement like

SELECT dept_name FROM DEPTS

WHERE dept_id = ? ;

with context.id passed in as a parameter.