Using Java stored procedure to read other databases

Oracle provides a great set of tools called “gateways” to connect an Oracle database to other data sources such as SQL Server or DB2. Most of these gateways are separately licensed though. There is a no-cost ODBC-based gateway but that might not fit your needs and you may want to explore using other technologies to read or modify data in a remote source. Alternately, you may already have java classes from a legacy integration that you want to use from within the Oracle database.

Fortunately, Oracle supports java stored procedures. So, if you load the appropriate jdbc drivers you can have your Oracle sql and pl/sql integrate with other database platforms. In the examples below I’ll use a SQL Server instance running the WideWorldImporters sample database as my remote source. To read data from it, I’ll create a schema to hold the needed jdbc classes, write a java class using them to read the remote data, and then publish the class for use through a pl/sql function interface.

1 – Create the schema to own the jdbc classes. The schema does not need create session privilege and can be locked. If you’re using 18c or higher, you can take it a step further and create the user without a password.

CREATE USER sqljdbc IDENTIFIED BY pa55w0rd
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT LOCK;

ALTER USER sqljdbc QUOTA UNLIMITED ON users;

GRANT CREATE PROCEDURE TO sqljdbc

2 – The schema needs to be granted Java privileges to connect and resolve the host and port of each external database.

DECLARE
     keynum   NUMBER;
 BEGIN
     sys.DBMS_JAVA.grant_permission(
           grantee             => 'SQLJDBC',
           permission_type     => 'SYS:java.net.SocketPermission',
           permission_name     => 'my_remote_server:1433',
           permission_action   => 'connect,resolve',
           key                 => keynum);
 END;
 /

3 – Load the java classes, use the grant option to allow them to be used by an application schema. You may get errors on the KeyVaultCredential and SQLServerColumnEncryptionAzureKeyVaultProvider classes; these aren’t needed for these examples.

loadjava -genmissing -schema sqljdbc -resolve -grant sds -user system@testdb sqljdbc42.jar

4 – Create a java class in your application schema to read some of the remote data.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED wwi_lookup 
RESOLVER(( * sds )( * sqljdbc )( * public ))
    AS 
import java.sql.*;
import com.microsoft.sqlserver.jdbc.*;

public class wwi_lookup {
    public static int get_population(
                            String p_server,
                            String p_instance,
                            int p_port,
                            String p_database,
                            String p_user,
                            String p_password,
                            String p_city,
                            String p_state) throws Exception
    {
       SQLServerDataSource ds = new SQLServerDataSource();
       ds.setServerName(p_server);
       if (p_instance != null) { ds.setInstanceName(p_instance); }           
       ds.setPortNumber(p_port);
       ds.setDatabaseName(p_database);
       ds.setUser(p_user);
       ds.setPassword(p_password);

       Connection conn = ds.getConnection();

       PreparedStatement pstmt = conn.prepareStatement(
               "SELECT c.LatestRecordedPopulation "
             + "  FROM      Application.Cities c "
             + " INNER JOIN Application.StateProvinces s "
             + "    ON s.StateProvinceID = c.StateProvinceID "
             + " WHERE c.CityName = ? AND s.StateProvinceCode = ?");

       pstmt.setString(1, p_city);
       pstmt.setString(2, p_state);

       ResultSet rs = pstmt.executeQuery(); 

       int population = -1; 
       if (rs.next()) {
          population = rs.getInt("latestrecordedpopulation");
       }
       rs.close();

       conn.close();

       return population; 
    }
}
/

5 – Publish the class through a pl/sql interface.

CREATE OR REPLACE FUNCTION get_population(p_server     IN VARCHAR2,
                                          p_instance   IN VARCHAR2,
                                          p_port       IN NUMBER,
                                          p_database   IN VARCHAR2,
                                          p_user       IN VARCHAR2,
                                          p_password   IN VARCHAR2,
                                          p_city       IN VARCHAR2,
                                          p_state      IN VARCHAR2)
   RETURN PLS_INTEGER
IS
   LANGUAGE JAVA
   NAME 'wwi_lookup.get_population(java.lang.String, java.lang.String, int, 
                                   java.lang.String, java.lang.String, java.lang.String,
                                   java.lang.String, java.lang.String
                                  ) return int' ;
 /

6 – Grant the application owner Java privileges to connect and resolve the remote host and port. These are the same as the jdbc schema needed.

DECLARE
     keynum   NUMBER;
 BEGIN
     sys.DBMS_JAVA.grant_permission(
           grantee            => 'SDS',
           permission_type    => 'SYS:java.net.SocketPermission',
           permission_name    => 'my_remote_server:1433',
           permission_action  => 'connect,resolve',
           key                => keynum);
 END;
 /

7 – The function should now be usable within your Oracle applications as any other function.

SQL> select get_population('my_remote_server',null,1433,'WideWorldImporters',
   2                        'wwi','pa55w0rd',
   3                        'New York','NY') city_population
   4    from dual;

 CITY_POPULATION
     8175133

Using the same general form, you can create additional procedures and functions to utilize any functionality exposed by the remote system; including inserting, updating, or deleting remote data and invoking remote stored procedures.

If you need to do a lot of integrations with heterogeneous systems, then you may want to invest in the Oracle Gateway technologies; but for smaller, isolated use cases, a few small Java/jdbc objects may be more than enough.