topic Re: Automating Oracle to Oracle data transfer in Talend Studio

Automating Oracle to Oracle data transfer

mbennitt — Sat, 16 Nov 2024 06:09:55 GMT

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.

Re: Automating Oracle to Oracle data transfer

akumar2301 — Wed, 03 Apr 2019 15:09:59 GMT

Did you already tried to set your schema defination using tSetDynamicSchema ?

https://help.talend.com/reader/8HHHqjH2oIXwCiEYmDpB2g/b8bqTFqcCd8o7JBzPWex8A

https://help.talend.com/reader/8HHHqjH2oIXwCiEYmDpB2g/1FqstMAYF90og1imy_W2tw

Re: Automating Oracle to Oracle data transfer

mbennitt — Wed, 03 Apr 2019 15:14:35 GMT

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.

Re: Automating Oracle to Oracle data transfer

akumar2301 — Wed, 03 Apr 2019 15:17:45 GMT

Yes , but it would be easy to prepare these file using Oracle all_tab_cols tables.

Re: Automating Oracle to Oracle data transfer

Anonymous — Thu, 04 Apr 2019 08:44:12 GMT

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 🙂

Re: Automating Oracle to Oracle data transfer

akumar2301 — Thu, 04 Apr 2019 08:53:21 GMT

Link is not accessible. Is it possible if you can share the gist of design
flow.

Re: Automating Oracle to Oracle data transfer

Anonymous — Tue, 09 Apr 2019 05:48:12 GMT

Hi,

Please find the below link of the article for dynamic ingestion of data to Oracle database.

https://community.talend.com/t5/Design-and-Development/Dynamic-ingestion-of-data-to-an-Oracle-database/ta-p/154657

Regards,

Pratheek NM