topic Re: Map value returned by Oracle function into a table column in Talend Studio

Map value returned by Oracle function into a table column

cd160 — Mon, 11 Mar 2019 08:06:00 GMT

I have designed a simple job where I'm using tMap to insert new rows into Oracle table Table2. Most column values are basically taken from another table Table1.

tOracleInput (Table1) --> tMap --> tOracleOutput (Table2).

However, one column value (Column_Date) in Table2 is to be taken from a function with multiple parameters as shown below. This is where I got stuck.

Package_Name.Function_Name(
PARAM1 = Table3.columnX,
PARAM2 = Table1.ColumnY,
PARAM3 = "string_value1"
PARAM4 = Number,
PARAM5 = "string_value2",
PARAM6 = null,
PARAM7 = null)

Suggestions??

Thanks.

Re: Map value returned by Oracle function into a table column

akumar2301 — Mon, 11 Mar 2019 09:42:57 GMT

Did you try to use tOracleSP , to call the procedure and get the column value before updating.

https://help.talend.com/reader/wDRBNUuxk629sNcI0dNYaA/1gQHLDcbpjnw_HLOYcAcGA

Re: Map value returned by Oracle function into a table column

fdenis — Mon, 11 Mar 2019 10:19:38 GMT

if it's a scalar function you can run it from a tinputbd using Select function(x,y,z) from Table or dummy.
then witn a tmap maque a loockup using parameters.
it's good if you have only some rows.
the other way is to use tOracleRow.
insert function into your insert query.
insert into table2 (a,b,c) (select a,b,function(c,d,b) from table2)
good luck

Re: Map value returned by Oracle function into a table column

cd160 — Tue, 19 Mar 2019 15:22:10 GMT

I did, but it returns nothing. The examples I found are of functions with only one parameter or parameter has a set value instead of table column. Also some of the parameter values in my case are based on sql query (Select Col1 from Table1 when Table1.KeyCol=Table2.KeyCol)

Re: Map value returned by Oracle function into a table column

fdenis — Wed, 20 Mar 2019 10:10:08 GMT

what is the function return type?
did you success calling it into select query on sqleditoe plsql or toad or …