topic Re: Using Oracle function in toracleoutput/Additional column in Talend Studio

Using Oracle function in toracleoutput/Additional column

borrisbeck — Wed, 20 Feb 2019 16:28:05 GMT

I posted a thread yesterday and the issue got resolved, but as an alternative approach, i would like to know how to use oracle functions in the Advanced setting>Additional columns in toracleoutput component for the incoming columns from tmap, can someone shed some light on how to do it? normal operations like sequence have examples on the web, but i'm not able to find examples on using functions like the one below

"UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(link.input_column))"

Thanks

Re: Using Oracle function in toracleoutput/Additional column

akumar2301 — Wed, 20 Feb 2019 17:03:24 GMT

As per my understanding , you have to use tDbRow

create your INSERT statement as String and execute it in tDBRow

Re: Using Oracle function in toracleoutput/Additional column

Anonymous — Thu, 21 Feb 2019 04:01:57 GMT

Hi BB,

Abhishek is correct as tOracleOutput is mainly used for normal data output to target database. If you are having customized functions in your target insert statement or other DB operations like calling a stored procedure function or package, the best place to use them is through tDBRow(Oracle).

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Using Oracle function in toracleoutput/Additional column

akumar2301 — Tue, 26 Feb 2019 14:15:59 GMT

Normally Sql Expression of Additional Field doesn't have access to link resultset but

I released that it can be possible using global variable.

1) 1st check your expression work directly on Databse

"UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE('xxxxxxxxx'))"

2) if OK , in your job after your input put tflowtoItrate and the itrate to tfixedflowinput and the attach tfixedflowinput to tOracleOutput

2) in additional column , use Replace as position , Reference Column and Name should be same datatype.

and in sql expression

"UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE('" + ((String)globalMap.get("variablename")) + "'))"