PL/SQL vs Stored Procedure Vs ORM Vs Scripting language. Which one is the best choice to write data migration script??

Question

I need to write a data migration script. Need to read records from some tables using join and migrate them to another table resides in a different schema. I am using Oracle database 11g. There are 30000+ records only now. By surfing Google I came across some terms like PL/SQL, Stored Procedure or a stand-alone application using ORM or any scripting language. I only want to know which one is the most efficient way to write the migration script and the precise pros and cons behind the pick. Pardon my English! Also if the question seems redundant and boring !! .. :) Seeking your suggestion. Thank you

Answer 1

That's not too much data to transfer; I'd try with an INSERT over the database link; something like this:

create database link dbl_rookie
  connect to remote_user
  identified by its_password
  using 'tns_admin alias for the remote database';

insert into remote_table@dbl_rookie
  (id, name, address, phone)
  (select a.id, b.name, b.address, a.phone
   from person a join details b on a.id = b.id
   where cb_active = 1
  );

Unless I made a typo, that would be as simple as that.

Alternatively, you could create a table using the CTAS (Create Table As Select) using the same SELECT statement I wrote above, as

create table to_be_moved as
  select a.id, b.name, ...;

Export it here (using the original EXP utility, as it creates a DMP file locally) and import it there (using the IMP utility). Say if you need additional help with it.

Or, you could even spool the result of the SELECT statement into a TXT file and then load it into the remote database using the SQL*Loader utility.

As you can see, quite a few options, and we didn't even move away from pure SQL (not to mention other options someone else might suggest).