May 3, 2016 at 1:12 pm
Hi friends,
We will need to copy few tables from SQL server to Oracle database on a weekly basis. We are looking at effective options to do this. One option is to do a BCP export to txt files and use SQL Loader to load them in Oracle. Another option is to use linked server to copy data into Oracle. But I believe linked server is not efficient enough in exceptions and with error handling meaning it doesn't say where the error is etc when compared to BCP/SQL Loader.
We will have few CLOBS and BLOBS in the data as well to be copied. So I'm not sure if BCP or Linked server handles large objects that well.. Could you please shed us some light on the best way to do this.
Thanks a lot
May 3, 2016 at 4:38 pm
Because you have CLOB/BLOB data my advise is to use a C# program that reads from sql server and loads to Oracle directly using the OracleBulkCopy http://docs.oracle.com/html/E10927_01/OracleBulkCopyClass.htm
Note that with it you can create a datatable and populate it from SQL Server and then supply the datatable directly to the OracleBulkCopy object (see http://www.codeproject.com/Questions/228101/oracle-data-bulk-insert for a very simple example).
May 4, 2016 at 11:12 am
Thanks for your response but we dont have C# expertise to wrote the code..
Will it be a problem to export clobs using bcp?
Thank you
May 4, 2016 at 11:44 am
it is such a small program that any programmer will be able to do it - and if not then hire one to do it either in c# or vb.net won't take more than a few hours to do it and test it.
you can do it with bcp to extract - but not simple.
and you can use sqlldr to load - again not simple.
As both situations are well documented and with plenty of examples on the net I advise you to google for each step individually. this if you choose not to go with the easiest solution of the c# code.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply