topic Re: How to use a CSV File as input to an Oracle query in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 07:41:27 GMT

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

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

TRF — Mon, 10 Sep 2018 11:46:57 GMT

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.

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

manodwhb — Tue, 11 Sep 2018 11:45:56 GMT

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

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

iamabhishek — Tue, 11 Sep 2018 11:55:21 GMT

@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.

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

Anonymous — Wed, 12 Sep 2018 15:10:36 GMT

Thanks for this.

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

Thanks,

Neil

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

iamabhishek — Wed, 12 Sep 2018 19:39:42 GMT

Your query should look something like this -

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