Transfer multiple records between SQL Server to Oracle 9i

[boondocksaint20]

Hello,

I want to write a java application that pulls multiple records from SQL Server and then updates records onto an Oracle 9i database. What is the best way to do this? Would it be best to call the SQL Server, write it to XML and then somehow iterate through the xml through an Oracle Stored Procedure, or can you call a different datasource within an Oracle Stored Proc?

A first attempt that I know is resource heavy bc it calls the database each time is to pull the results from SQL Server into a struct class, and then save each instance of it into an ArrayList, then iterate through the ArrayList, pull each struct object out and run an update query into the Oracle box for each record. I have successfully done this, but I want to know of a better way to do this.

Thanks in advance!

 

[SantaMufasa]

Saint,

Frankly, ODBC allows one to nimbly jump between virtually any database environment, treating the "foreign" database objects as "one of their own".

With the proper ODBC definition, you should be able to...

UPDATE <Oracle table> SET <Oracle-table column(s)> =
(SELECT <expression-list>
   FROM <SQL Server table (via ODBC connection)>
  WHERE <some-condition(s)>)
WHERE <some-condition(s)>;

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[boondocksaint20]

Sounds great! I have tried googling this and can't find a specific example...would you mind posting some sample syntax for the ODBC connection part? Can this work with a JDBC connection as well?

Thanks a lot!