Thursday, May 17, 2012

Calling Java procedure from database

Oracle Database has had the possibility to run Java code inside the database for a long time. It's a very rare occasion when you need to use it but still. Here is one example I used to download content from HTTPS website that required user certificates for authentication. Please take the code below more as an example how to put simple Java code inside the database, not as a solution for user certificates authentication, because UTL_HTTP can do the same thing (although I wasn't successful in implementing it under 11.2.0.2).

First, load the Java source into database. The code below shows:

  • How to return simple datatype (int) from Java function - makeConnection
  • How to return Oracle CLOB datatype from Java - makeConnectionClob
  • How to execute SQL from Java, in the same calling session
Note that method main is just added for testing from command line.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HttpsHandler" as
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;

import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.SSLSocketFactory;
import javax.net.ssl.SSLSession;

import java.sql.Connection;
import java.sql.PreparedStatement;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;

public class HttpsHandler {
  
  
  public static CLOB makeConnectionClob(String keyStorePath, String keyStorePass, String trustStorePath, String httpsUrl, String proxyHost, String proxyPort) throws Exception {
    int i = makeConnection(keyStorePath, keyStorePass, trustStorePath, httpsUrl, proxyHost, proxyPort);
    String s = Integer.toString(s);
    OracleDriver driver = new OracleDriver();
    Connection dbconn = driver.defaultConnection();
    CLOB clob = CLOB.createTemporary(dbconn, false, CLOB.DURATION_CALL);
    clob.setString(1, s);
    return clob;
  }
  
  public static int makeConnection(String keyStorePath, String keyStorePass, String trustStorePath, String httpsUrl, String proxyHost, String proxyPort) throws Exception {
      //
      System.setProperty("javax.net.ssl.keyStore", keyStorePath);
      System.setProperty("javax.net.ssl.trustStore", trustStorePath);
      //System.setProperty("javax.net.debug", "ssl");
      System.setProperty("javax.net.ssl.keyStorePassword", keyStorePass);
      
      if (proxyHost != null && proxyPort != null) {
        System.setProperty("https.proxyHost", proxyHost);
        System.setProperty("https.proxyPort", proxyPort);
      }
      
      //
      SSLSocketFactory sslsocketfactory = (SSLSocketFactory) SSLSocketFactory.getDefault();
      URL url = new URL(httpsUrl);
      HttpsURLConnection conn = (HttpsURLConnection)url.openConnection();
      conn.setConnectTimeout(8000);
      conn.setSSLSocketFactory(sslsocketfactory);
      // Do not verify that hostname matches the certificate
/*      conn.setHostnameVerifier(new HostnameVerifier() {        
          public boolean verify(String hostname, SSLSession session)  {  
        return true;
          }
      });*/
      // Set request header
      conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
      InputStream inputstream = conn.getInputStream();
      InputStreamReader inputstreamreader = new InputStreamReader(inputstream);
      BufferedReader bufferedreader = new BufferedReader(inputstreamreader);
      
      OracleDriver driver = new OracleDriver();
      Connection dbconn = driver.defaultConnection();
      PreparedStatement dml_stmt = dbconn.prepareStatement("INSERT INTO https_output (num, line) VALUES (?,?)"); 
      
      String s = "";
      int linecount=0;
      while ((s = bufferedreader.readLine()) != null) {
        linecount++;
        dml_stmt.setInt(1, linecount);
        dml_stmt.setString(2, s);
        dml_stmt.executeUpdate();
      }
      dml_stmt.close();
      
      return linecount;
  }
  
  public static void main(String[] args) {
    try {
      int i = makeConnection("/path/to/keystore.jks", "keystore_pass", "/path/to/truststore.jks", "https://site.that.requires.user.cert/authentication/", null, null);
      System.out.println(Integer.toString(i));
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

};

Then you need to create a wrapper package in database. This declares the PL/SQL wrapper function names and input/output parameters.

CREATE OR REPLACE package https_user_cert_wrapper as

  FUNCTION make_request(keyStorePath IN varchar2, keyStorePass IN varchar2, trustStorePath IN varchar2, httpsUrl IN varchar2, proxyHost IN varchar2, proxyPort IN varchar2)
  RETURN number AS LANGUAGE JAVA 
  NAME 'HttpsHandler.makeConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return java.lang.int';

  FUNCTION make_request_clob(keyStorePath IN varchar2, keyStorePass IN varchar2, trustStorePath IN varchar2, httpsUrl IN varchar2, proxyHost IN varchar2, proxyPort IN varchar2)
  RETURN clob AS LANGUAGE JAVA 
  NAME 'HttpsHandler.makeConnectionClob(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return oracle.sql.CLOB';

end;
/

Download the source: java_source.java and PL/SQL wrapper.sql.

When you first execute the code, you will most likely get some privilege errors, but the error message will tell you how to grant the needed privileges. For example, for this code the following grants were needed:

exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStore', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStorePassword', 'write' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', 'site.that.requires.user.cert', 'resolve' );
exec dbms_java.grant_permission( 'OWNER', 'SYS:java.net.SocketPermission', '1.2.3.4:443', 'connect,resolve' );

No comments:

Post a Comment