topic Re: Dynamic Schema for tFileInputDelimited and tOracleOutput in Talend Studio

Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Mon, 11 Jun 2018 11:51:38 GMT

Hi ,

We have requirement where we need to load data from input file and load into Oracle table.However input file schema should not be hard coded.By using tfilelist system should pick file one by one and according to data file structure ,system should create dynamic schema for tFileInputDelimited and tOracleOutput components and load data into mapped tables.

As the no of input files is more (around 300 +) and each file may have different structure.Thus it is not possible to create schema for each file.

We are looking for an appropriate solution and for tFileInputDelimited and tOracleOutput to read data dynamically and insert into tables.

Thanks

Regards,

Vivek

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Mon, 11 Jun 2018 14:28:13 GMT

if you are not on the free version you can use dynamic as row type. This type replace all columns and keep the type.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Thu, 14 Jun 2018 14:04:05 GMT

I got following error when i used tinputfiledelimited and toracleoutput components with 'Dynamic' schema type.

My Graph design : tinputfiledelimited -> tOracleOutput

Exception in component tOracleOutput_1
java.sql.SQLException: Missing IN or OUT parameter at index:: 96
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1821)
at oracle.jdbc.driver.OraclePreparedStatement.addBatch(OraclePreparedStatement.java:10010)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.addBatch(OraclePreparedStatementWrapper.java:1358)
at artemis_dev.transt_stg_load_param_0_1.Transt_Stg_Load_Param$1tAsyncIn_tOracleOutput_1_ParallelThread.run(Transt_Stg_Load_Param.java:2801)

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Thu, 14 Jun 2018 14:20:07 GMT

You are using stored procédure!
ones you have db trigger with trouble.
or you are not ussing the good component .
to use store proc use tOracleRow

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Fri, 15 Jun 2018 06:44:35 GMT

Oracleoutput is used to insert data into oracle table.We would like to make a graph which will read data from file and inserting into oracle table with dynamic schema.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Fri, 15 Jun 2018 08:45:37 GMT

try to insert into a new table.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Tue, 19 Jun 2018 08:45:05 GMT

Didn't work diff options....Giving background of this issue again.Can anybody please suggest how to achieve this in Talend.

Hi ,

We have requirement where we need to load data from different input files and load into Oracle tables.As input files structures are different for each files thus schema cannot be hardcoded.By using tfilelist system should pick file one by one and according to data file structure ,system should create dynamic schema for tFileInputDelimited and tOracleOutput components and load data into mapped tables.

As the no of input files is more (around 300 +) and each file will have different structure.Thus it is not possible to create schema for each file.

We are looking for an appropriate solution and for tFileInputDelimited and tOracleOutput to read data dynamically and insert into tables.

We tried tinputfiledelimited (to read file) and toracleoutput (to load data into oracle table) components with 'Dynamic' schema type.

My Graph design : tinputfiledelimited -> tOracleOutput

However we got below exception

Thanks

Regards,

Vivek

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Tue, 19 Jun 2018 09:35:36 GMT

it's an oracle prolem try to create and insert data into table with the same user.
check global and table triggers
missing in out parameter is an package or procedure error.
good luck,

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Tue, 19 Jun 2018 10:14:49 GMT

But we are able to insert record from Oracle side.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Tue, 19 Jun 2018 10:28:03 GMT

are you using same user/pwd?
is there db triggers?
is there table triggers?
is there package procedures or functions?
are they all compilled?
try to insert one line from a new talend job. recreating connection.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Tue, 19 Jun 2018 10:38:04 GMT

Same graph is working when we use static schema (defined all 99 columns and their data type in design time) type...

So look like the problem with dynamic schema type.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Tue, 19 Jun 2018 11:10:11 GMT

so try dynamics adding column by column.
a data type or a data may lock the process.
when I do so I prefer to creat table in a first job to format column name and to set all as string. in a second time load data and then check and format data on db side.

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Tue, 19 Jun 2018 11:54:55 GMT

Can you please elaborate more.. or possible to put graph image.

That will us to understand and implement properly.

Thanks for your prompt response.

Regards,

Vivek

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

fdenis — Tue, 19 Jun 2018 12:11:27 GMT

create one job executing a create table using tfilelist tfile input (read only 1 line) tmap or tjava to create sql create query (define all columns as string) and tOraclerow to execute query.
I'm used to use sys table to check tables and columns name and type and update them using alter table.
In a second time fill table with data. tfilelist toracleoutput

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

Anonymous — Wed, 20 Jun 2018 08:38:20 GMT

is there any other way to achieve this in Talend ?

@fdenis wrote:
create one job executing a create table using tfilelist tfile input (read only 1 line) tmap or tjava to create sql create query (define all columns as string) and tOraclerow to execute query.
I'm used to use sys table to check tables and columns name and type and update them using alter table.
In a second time fill table with data. tfilelist toracleoutput