0

I need to do an Oracle data migration from 11g to 12c where schema changes are abundant. I have an excel sheet which describes all the schema changes. Excel sheet has the columns for 'old_table_name', 'old_column_name', 'old_value' and same for the new tables. Some values can be directly copied to the new table and some cannot be done that way.

For example I have to transform the old column value when it is moved to the new table. Some transformation are complex and they cannot be simply mapped. They should be transformed by joining with other tables in the old database. I was trying the Talend Open Studio Data Integration tool for this and found it is a bit complex to go ahead with that tool in my case. Does anyone have an idea of getting this done using Talend or any other tool? What is the ideal approach when doing a migration like this? I have included a sample of the excel sheet below which only has simple transformations.

enter image description here

asthiwanka
  • 437
  • 2
  • 6
  • 17
  • to me it looks to be a usecase where you can read this excel file in talend and then you should generate a custom insert SQL for inserting data into new table in oracle 12c. There are components available in talend where you can execute custom sql (tOracleRow) and other components available where you can use java code to generate the custom insert sql. – garpitmzn May 22 '14 at 05:22
  • It seems more like a copy rather than a migration; if you confirm this, Talend is definitively a good choice. Last preliminary question: how many kinds of transformation do you have? You talk about a complex join-aware kind of transformation, too... – Gabriele B May 22 '14 at 06:37
  • 1
    @Gabriele B, Its actually not a direct copy over since the data is going to be transformed while the migration. – asthiwanka May 22 '14 at 08:42

1 Answers1

1

The kind of converions shown in the spreadsheet can all be performed on the table itself using rename statements and/or basic ddl and dml statements. I would load the old table into the new database and perform these statement on the table.

alter table
   old_table_one
rename to
   new_table_one;

alter table
   new_table_one
rename column
   old_col_one 
to
   new_col_one;

update new_table_one
set new_col_one = 'A_NEW'
where new_col_one = 'A';

etc.

Rene
  • 10,391
  • 5
  • 33
  • 46