Using PL/SQL to invoke a java method that uses JDBC to connect to a SQL server database

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开发者_JAVA技巧. 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?

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.

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