Objective: We have hundreds of tables in an Oracle database and I want a Talend job that will load the data into a second Oracle database table by table.
Ideal State: I developed two jobs (export and import), one job queries each table from the source Oracle database and pushes a CSV file into an AWS bucket. The second job pulls each CSV file from the S3 bucket and loads the data directly into the target Oracle database. This is all controlled through a file that tells the first job which table(s) that need to be extracted and is completely automated.
Problem: After I load the data from the CSV file and attempt to insert into the target database I run into issues with tOracleOutput component. Because I am automating this entire process I must use a dynamic schema within the tOracleOutput. However the tOracleOuput forces you to select one and only one DB Type for the entire table (why it wouldn't still be dynamic is beyond me). At this point I get errors for fields such as date since they are not varchar2. Just a side note, if all of the fields in my table are varchar2 than this process works flawlessly. My issue is between the different database types and how to get Talend to properly handle them.
If you'd like any screenshots or have any questions please let me know. I am trying to avoid going through each table and specifying the exact data types for all of the columns. Automating this process would be such a great success for my team.
Wouldn't that mean that I would have to set the tSetDynamicSchema specific to each table? I'm not sure if this would work since I am parsing through hundreds of tables with differing structures and data types.
My colleague @groupproductmanagement has already created a good article to dynamically load the data to multiple Oracle tables.
I hope your use case can be satisfied with minor changes to this article's code base.
Edit Note:- Link removed as I had accidentally posted internal link before publishing of the article. Once the article is published, we will share the public link
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
