How to use a CSV File as input to an Oracle query

[Anonymous]

Hi,

I am trying to build a job that takes in a file of ID's and then uses those IDs to query rows from an Oracle database

This is the flow in Talend 7.0.1.

How can i connect the file to the database table?

Job Flow

Thanks

Neil

[TRF]

You cannot connect these components.

What you can do is contruct a dynamic where clause based on the file content using a tJavaFlex component connected to the tFileInputDelimited component. This where clause will be a global variable reused into the tOracleInput component.

If the input is too large to construct this where clause, you can you a tMap after the tFileInputDelimited to filter the rows which are not in the input file.

[manodwhb]

@Neil_Emery,you need to design a job in below way.

[iamabhishek]

@Neil_Emery - 
If you want to run your query on the oracle database multiple number of times that is one run for each of the id you get from file then you need to iterate on the id's fetched from file - you could consider the solution provided by @manodwhb
But, if you want to run your query only once for all the id's fetched from your file then you need to go with the solution provided by @TRF. You have to get all the id's and store it temporarily - concat it as a comma separate string maybe then pass it to your Oracle component through global variable. 

[Anonymous]

Thanks for this.

How do I tie the ID from the flow component to the ID in the query 

Thanks,

Neil

[iamabhishek · Accepted solution]

Your query should look something like this - 

SELECT Col1, Col2
FROM [TableName]
WHERE ID  IN " + ((String)globalMap.get("sqlCode")) + "