Ilmar Kerm

Oracle, databases, Linux and maybe more

I was looking for PL/SQL examples to use metadata and data filtering in datapump API, but I didn’t find any. So here is one example. It uses table reload_dev_tables to specify what schemas/tables should be exported using data pump and what where clause should be set.

Structure for reload_dev_tables:

 Name                                      Null?    Type                        
 ----------------------------------------- -------- -------------
 OWNER                                     NOT NULL VARCHAR2(40)                
 TABLE_NAME                                NOT NULL VARCHAR2(40)                
 IS_FULL                                   NOT NULL NUMBER(1)                   
 FILTER_PREDICATE                                   VARCHAR2(250)   

Here is the datapump code itself, tested in 11.2.0.3. This is just a demonstration how to use the datapump API, specifically the metadata and data filters.

  PROCEDURE export_data(p_directory IN VARCHAR2) IS
    CURSOR c_norows IS
      select owner, table_name from dba_tables WHERE owner in (
        select distinct owner from reload_dev_tables) and status='VALID' and temporary = 'N' 
        and secondary = 'N' and nested = 'NO' and dropped = 'NO' and iot_name is null
      minus
      select owner, table_name from reload_dev_tables;
    l_dp_handle       NUMBER;
    l_last_job_state  VARCHAR2(30) := 'UNDEFINED';
    l_job_state       VARCHAR2(30) := 'UNDEFINED';
    l_sts             KU$_STATUS;
    s varchar2(3000);
  BEGIN
    l_dp_handle := DBMS_DATAPUMP.open(
      operation   => 'EXPORT',
      job_mode    => 'SCHEMA',
      );

    DBMS_DATAPUMP.add_file(
      handle    => l_dp_handle,
      filename  => 'dev_dw.dmp',
      directory => p_directory);

    DBMS_DATAPUMP.add_file(
      handle    => l_dp_handle,
      filename  => 'dev_dw.log',
      directory => p_directory,
      filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

    --
    SELECT listagg(''''||owner||'''', ', ') WITHIN GROUP (ORDER BY owner) INTO s
    FROM (SELECT DISTINCT owner FROM dba_tables WHERE owner IN (SELECT distinct owner from reload_dev_tables));
    DBMS_DATAPUMP.metadata_filter(l_dp_handle, 'SCHEMA_LIST', s);

    -- Add query filters
    FOR rec IN (SELECT owner, table_name, filter_predicate FROM reload_dev_tables r 
        WHERE filter_predicate IS NOT NULL AND 
        EXISTS (SELECT 1 FROM dba_tables t WHERE t.owner = r.owner AND t.table_name = r.table_name 
          AND t.dropped = 'NO') ) LOOP
      DBMS_DATAPUMP.DATA_FILTER (
        handle => l_dp_handle,
        name  => 'SUBQUERY',
        value => 'WHERE '||rec.filter_predicate,
        table_name => rec.table_name,
        schema_name => rec.owner);
    END LOOP;
    -- Add tables without rows
    FOR rec IN c_norows LOOP
      DBMS_DATAPUMP.DATA_FILTER (
        handle => l_dp_handle,
        name  => 'INCLUDE_ROWS',
        value => 0,
        table_name => rec.table_name,
        schema_name => rec.owner);
    END LOOP;

    DBMS_DATAPUMP.start_job(l_dp_handle);

    DBMS_DATAPUMP.detach(l_dp_handle);
  END;

Starting from 11.2 its possible to use SSL client certificates to authenticate yourself to a remote web service using SSL client certificates. I did not find much information on it using Google or documentation, that is why I’m writing this post.

Please refer to this post by Tim Hall to get started on connecting to HTTPS service using UTL_HTTP, all of this is needed before continuing with SSL client certificate authentication.

The first thing you need is to generate user certificate request inside Oracle Wallet, sign it by CA and load the returned certificate back to Wallet. I’m not going to very detailed steps here, but basically (using Oracle Wallet Manager OWM):

  1. Open the wallet you created using Tim Hall’s post mentioned previously.
  2. Go to Operations > Add Certificate Request
  3. Fill in all the needed fields
  4. After certificate request has been created, go to Operations > Export Certificate Request
  5. Send the request to a Certification Authority (that the remote service trusts) for signing and wait for a reply (in a form of signed certificate)
  6. Import the signed certificate to wallet – go to Operations > Import User Certificate

If you are using 11g OWM/ORAPKI and when importing the user certificate to wallet OWM displays an error or ORAPKI corrupts your wallet, you can just use OWM/ORAPKI programs from 10gR2 database client. This is due to bug Bug 9395937: UNABLE TO IMPORT USER CERTIFICATE IN OWM 11.1, WORKS IN 10.2.

Next thing is to add ACL privileges inside the database. UTL_HTTP documentation requires the use of use-client-certificates ACL privilege. How to do that I’ll refer to Tim Hall’s post again Fine-Grained Access to Network Services in Oracle Database 11g Release 1. In the example below I already have ACL all_access.xml and I’m granting connect and use-client-certificates privileges to CLTEST schema.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','CLTEST', true, 'connect');

PL/SQL procedure successfully completed.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('all_access.xml','CLTEST', true, 'use-client-certificates');

PL/SQL procedure successfully completed.

Now the step that is not mentioned in UTL_HTTP documentation and got me stuck for weeks until I opened SR to Oracle Support. The network ACL needs also privileges on the Wallet file using DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL.

SQL> exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL('all_access.xml','file:/path/to/oracle/wallet');

PL/SQL procedure successfully completed.

After the privileges have been assigned, you can use UTL_HTTP to query remote web service like you do with normal HTTPS connection. If the remote web service requests client to be authenticated using certificates, UTL_HTTP automatically handles it in the background and uses the user certificate located in the wallet. For example:

SQL> SELECT utl_http.request('https://secure.service.com/status', '', 'file://path/to/oracle/wallet', 'WalletPassword') FROM dual;

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' );