2

I would like to be able to connect an Oracle database with a SQL server database. I am aware of DG4ODBC and HSODBC but i cant use those drivers for several reasons.

I understand that it is possible to call java code from within PL/SQL as described here http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/chthree.htm

I want to write a PL/SQL procedure which will invoke a Java method in a Java Stored Procedure. The processing of the Java method is to connect to a SQL server database and inserts some data. The Java method will connect to the SQL Server using a JDBC driver that will be situated on the Unix server where Oracle will be running.

Is the above possible? How is the driver location defined? Are there any disadvantages to the above approach? Any tutorials or examples to be found anywhere?

ziggy
  • 15,677
  • 67
  • 194
  • 287
  • Do you really need your PL/SQL to connect to SQL Server? I would try to maintain my databases independent of each other, and have a process at the application server level connect to them both to do whatever is needed. – gpeche Jun 06 '11 at 14:47
  • i am trying to separate the functionality of an existing pl/sql module. Part of it now requires talking to a SQL server database which is why i want to separate the sql server bit but keep everything else as is. Thanks – ziggy Jun 07 '11 at 14:53

2 Answers2

3

So long as you can load the SQL Server JDBC driver into the Oracle database using the loadjava utility, yes, that should be possible. That will depend on the version of Oracle (different versions of the database have different versions of the internal JVM) and the JVM version that your SQL Server JDBC driver requires, but I wouldn't expect that to be too difficult. If you are using Oracle 10.2 (I'm guessing based on the version of the Java Developer's Guide you linked to), you'd just need to ensure that you're using a version of the SQL Server JDBC driver that is compatible with a 1.4 JVM.

This will undoubtedly be less efficient than using Heterogeneous Services and the Transparent Gateway for ODBC. And it is likely a fair bit more work to develop. But it should work.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
0

If you really need to do this, you can try loading the jTDS driver in your database via loadjava. Then you should give appropiate permissions with dbms_java.grant_permission.

Some pointers:

http://forums.oracle.com/forums/thread.jspa?messageID=1102281 http://download.oracle.com/docs/cd/B19306_01/java.102/b14187/chthree.htm#CACJJHGI

gpeche
  • 21,974
  • 5
  • 38
  • 51