1

I have an Oracle 10G database running on a Unix environmnent and have a requirement to write a PL/SQL job that will be running on the Oracle database that will populate tables in another database which is a microsoft SQL Server database running on a Windows platform.

Having looked around, it looks like this is possible via database links. Has anyone got any more information on this? For example how reliable is the connection and are there any disadvantages in having such a setup?

Chances are that i would have limited access to the SQL Server database. Is the above possible without having to make any changes to the SQL server database? (Assuming the DBA on the SQL server has configured the database to be accessed from other databases)

Thanks

ziggy
  • 15,677
  • 67
  • 194
  • 287

1 Answers1

1

Use the Oracle feature DG4ODBC, it is as reliable as any normal DBLINK setup, to the SQL Server side it will look like any other sort of client connection. You only need to do anything more elaborate if you are planning to do distributed transactions between Oracle and SQL Server (i.e. 2 Phase Commit). You will probably also want UnixODBC and FreeTDS.

Gaius
  • 2,556
  • 1
  • 24
  • 43
  • I wont have access to the SQL server database other than the ability to execute INSERT statements. – ziggy May 17 '11 at 11:11
  • That's fine, all the configuration is done on the Oracle side, so long as you don't need your transactions to span both DBs. To SQL Server it just looks like another ODBC connection (because that's what it is). – Gaius May 17 '11 at 12:23
  • i am having some trouble setting up the configuration for the above. See this post if you can help http://stackoverflow.com/questions/6112848/connecting-oracle-to-sql-server-via-database-link – ziggy May 25 '11 at 09:42