Five Stars

Dynamic Schema for tFileInputDelimited and tOracleOutput

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

1 ACCEPTED SOLUTION

Accepted Solutions
Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

Francois Denis

Don't forget to tag when it's "solved"!

14 REPLIES
Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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)

Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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.

Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

try to insert into a new table.

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

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)

 

Thanks

 

Regards,

Vivek

Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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,

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

But we are able to insert record from Oracle side.

Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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.

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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.

Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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.

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

Nine Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

Francois Denis

Don't forget to tag when it's "solved"!

Five Stars

Re: Dynamic Schema for tFileInputDelimited and tOracleOutput

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

@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