Java Stored Procedure remotely connecting to another database

I very rarely use JavaSP but I had a request to connect to a remote database from a JavaSP. My first thought was to use a database link but the request required a direct connection to the remote database using JDBC/THIN driver. here is how I got it working.

1. Create a class as follows which contains one method to connect to a remote database.

import java.sql.Connection;

import java.sql.SQLException;

import oracle.jdbc.pool.OracleDataSource;

public class RemoteDBTest
{
public static String remoteConnection()
 throws SQLException
{
  StringBuffer sb = new StringBuffer();
  String userId = "scott";
  String password = "tiger";
  String url =
   "jdbc:oracle:thin:@acampanaro-pc2.au.oracle.com:1521:linux102";
  Connection conn = null;

  OracleDataSource ods = new OracleDataSource();
  ods.setUser(userId);
  ods.setPassword(password);
  ods.setURL(url);
  conn = ods.getConnection();

  sb.append("Auto commit = " + conn.getAutoCommit());
  conn.close();

  return sb.toString();
}
}

2. Load the class into the database and create a call specification, I used JDeveloper 10.1.3.4 as this makes light work of the task as shown by the output below. The class was loaded as user SCOTT.

Invoking loadjava on connection 'scott-linux102' with arguments:
-order -resolve -thin
Loadjava finished.
Executing SQL Statement:
CREATE OR REPLACE FUNCTION remoteConnection RETURN VARCHAR2 AUTHID CURRENT_USER AS LANGUAGE JAVA NAME 'RemoteDBTest.remoteConnection() return java.lang.String';
Success.
Publishing finished.

3. Now I need to grant some privileges to enable me to remotely connect from my database to another database using JAVA. There 2 are as follows, which must be executed as SYS.

SYS@LINUX10G> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', 'acampanaro-pc2.au.oracle.com', 'resolve');

PL/SQL procedure successfully completed.

SYS@LINUX10G> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '10.187.80.13:1521', 'connect,resolve');

PL/SQL procedure successfully completed.

4. Now we can call our JavaSP and verify if we have successfully connected.

SCOTT@LINUX10G> select remoteconnection from dual;

REMOTECONNECTION
------------------------------------------------------------

Auto commit = true

What you will find is if you don't execute the required privileges then you will be told when you try to run the JavaSP that you don't have required permissions to do this. His an example of what error you may receive. Lucky for me oracle gives me the command I need to run which made this easy to setup.

SCOTT@LINUX10G> select remoteconnection from dual;
select remoteconnection from dual
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
(java.net.SocketPermission 10.187.80.13:1521 connect,resolve) has not been granted to SCOTT. The PL/SQL to grant this
is dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '10.187.80.13:1521', 'connect,resolve' )