Stored Procedures with Java?

The most common language within the Oracle database is still PL/SQL. This is the prefered language to develop Stored Procedures, Packages or Triggers. In order to develop with Java, the Java sources must be uploaded into the database. From this, the classes are extracted and displayed in the Data Dictionary as a schema object. It allows Java sources (.java) and class files (.class) or archives (.jar, .zip) to be uploaded.

^{Fig 1: Java Resourcen within the Oracle database. Origin: Oracle Java Developers Guide}

The development IDE "PL/SQL Developer" also provides the ability to edit and compile Java Sources directly. These must be no longer uploaded cumbersome. Oracle points out, however, that the integrated JVM is not focused on development and that it is recommended, better to use external tools such as JDeveloper (Oracle) or Eclipse. In order to interact with the database (and the included tables), there is a server-side JDBC driver and SQLJ preprocessor integrated within the Oracle JVM.

Wrapper

To use the Java classes and methods as a stored procedure, only a small PL/SQL wrapper function must be written, which is responsible for communication with the Java method. Typically, such a wrapper is not more than 2-3 lines long. In the following example, a small Java method is developed, which returns a string. This is encapsulated by wrappers and can be used anywhere within the database.

The hello method of the JavaOracleDemo Class, takes a string as a parameter and returns it with the prefix "Hello".

public class JavaOracleDemo {
     
    public static String hello (String name) {
        return ("Hello " + name);
    }
}

PL/SQL Wrapper Package:

create or replace package DEMO_JAVA_WRAPPER is
 
  function hello(p_name in varchar2) return varchar2 as
    language java name 'JavaOracleDemo.hello(java.lang.String) return java.lang.String';
 
end;

In the end we can use this method everywhere in the Oracle database even in a plain SQL statement:

SQL> select demo_java_wrapper.hello('World') from dual;
 
DEMO_JAVA_WRAPPER.HELLO('WORLD
--------------------------------------------------------------------------------
Hello World
 
SQL> 

Session Handling

Each database session which uses a Java class, creates a seperate Java session with it's own JVM, memory management and garbage collection. See Fig 2.

^{Fig 2: Database & Java Session Handling. Origin: Oracle Java Developers Guide}

Passing Parameters

Passing parameters between Oracle (or the PL / SQL wrapper) and Java is sometimes a bit complicated. Scalar data types can be mapped very easy, as can be seen in the table below. So, for example, a VARCHAR2 in PL/SQL directly corresponds to a String class in Java. But when it comes to complex structures (eg, records or collections), the parameters can not simply pass. For those requirements, there is the Oracle tool "JPublisher", to which I will not go into more detail. For more information see the JPublisher Users Guide (Appendix). In the next chapter I will show a simple method to pass complex parameters (like records or collections) without using the JPublisher. I will explain how to manage this just with plain Oracle features.

Overview of Parameter Mappings

Passing complex Parameters

To pass complex parameters as like records or collections, one alternative is, to store them into a temporary table like a stack.  Before we call the Java method, we put the data on the stack and within the Java method we grab the data back. To pass the parameters back to PL/SQL, we simply do it vice versa. The only thing we need is a unique session-id to uniquely identify the parameters.

Example:

In the following Example, a Java method will get a collection of strings which will be returned with slightly modifications. Therefor we use a general parameter table (sys_java2plsql_para_tb) as a stack, which will be used within Java and PL/SQL to access the data (get and set).

Definition of the Java Class

import java.util.ArrayList;
import java.util.List;
 
// Declaration of the iterator to grab the data from the stack
// SQLJ Iterator Deklaration
#sql iterator StackIterator (String field1);
 
public class JavaOracleDemo {
 
  public static void getSetStackDemo(Integer sessionId) throws Exception  {
   
    // internal definition of an array to store the strings
    ArrayList stack = new ArrayList();
 
    // Definition of the iterators (=Cursor) to get the data from the stack (SQLJ)
    StackIterator stackIterator;
     
    #sql stackIterator = {select field1 from sys_java2plsql_para_tb where session_id = :sessionId };
     
    // get the data from the stack and store it into an array
    while (stackIterator.next()) {
      String stackEntry = stackIterator.field1();
       
      stack.add(stackEntry);
    }
     
    // clear the stack
    #sql { delete from sys_java2plsql_para_tb where session_id = :sessionId };
     
    // put the modified data back on the stack
    for (String stackEntry : stack) {
      String stackEntryModified = stackEntry + " Java was here";
      #sql { insert into SYS_JAVA2PLSQL_PARA_TB (session_id, field1) values
            (:sessionId, :stackEntryModified) };
        }
    }
}

Definition of the PL/SQL Functions

If we have complex parameters to pass, we should have two PL/SQL functions. First, we have the simple wrapper for the 1:1 communication with the corresponding Java method. This function just have the session-ID as the only parameter and will never used directly. The second function is to hide the complexity of the parameter passing. It gets the collection of VARCHAR2 and stores each string on the stack. In addition this function will determine the unique session-ID for a proper communication with the Java method. This session-ID will be passed to the simple wrapper function.

-- Java Wrapper Function
procedure getSetStackDemoW(p_session_id in number) as
  language java name 'JavaOracleDemo.getSetStackDemo(java.lang.Integer) ';
 
procedure getSetStackDemo(p_string_tab in out t_tab) is
  PRAGMA AUTONOMOUS_TRANSACTION;
   
  l_session_id number;
  idx          number := 0;
   
  cursor c_stack(ci_session_id in number) is
    select *
      from sys_java2plsql_para_tb a
     where a.session_id = ci_session_id;
 
begin
 
  l_session_id := common_seq.nextval;
 
  -- read the collection and put each string on the stack
  for i in 1 .. p_string_tab.count loop
    insert into sys_java2plsql_para_tb
      (session_id, field1)
    values
      (l_session_id, p_string_tab(i));
  end loop;
 
  -- call the wrapper funktion
  getSetStackDemoW(l_session_id);
 
  -- get the stack back and transfer it into the collection
  for c1 in c_stack(l_session_id) loop
    idx := idx + 1;
    p_string_tab(idx) := c1.field1;
  end loop;
 
  rollback;
 
end;

The following anonymous PL/SQL program shows how to use ...

declare
  l_strings demo_java_wrapper.t_tab;
begin
  l_strings(1) := 'Hello';
  l_strings(2) := 'World';
  l_strings(3) := 'Oracle';
   
  DEMO_JAVA_WRAPPER.getSetStackDemo (l_strings);
   
  for i in 1..l_strings.count loop
    dbms_output.put_line (l_strings(i));
  end loop;
end;

... and this is the expected output:

Hello Java was here
World Java was here
Oracle Java was here

Access the FileSystem with Java

Requirements

To enable Java, to access the FileSystem, we need certain permissions (aka Java Policies). It is important to restart the Database, after setting those permissions. It took me hours to figure out, why the hell Java was prohibited to read the content of the Temp-Directory. The next day, I was wondering what the problem could be, but before I spent more time in investigations, I checked the function again. And behold, it was working. The magic thing was, that the Database was rebooted over night. Lesson learned!

To check which permissions already has been set, use the following SQL:

The following permissions should be set: (execute as SYS)

-- Java Grants (als Benutzer SYS)
begin
  dbms_java.grant_permission('','java.io.FilePermission','<<ALL FILES>>','read,write,execute,delete');   -- Alll permissions on all diretories
  dbms_java.grant_permission('','java.io.FilePermission','C:\temp','read');                              -- only read permission on c:\temp
  dbms_java.grant_permission('','java.lang.RuntimePermission','*','writeFileDescriptor');
  dbms_java.grant_permission('','java.net.SocketPermission','*','connect, resolve');
  dbms_java.grant_permission('','java.util.PropertyPermission','*','read');
  commit;
end;

In addition, the following well known ACL permissions must be set: (execute as SYS)

-- ACL Permissions
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'dbserver.xml',
                                    description => 'DB Server ACL',
                                    principal   => 'PUBLIC',     -- if necessary, restrict it to a certain user (instead of public)
                                    is_grant    => true,
                                    privilege   => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'dbserver.xml',
                                       principal => 'PUBLIC',    -- if necessary, restrict it to a certain user (instead of public)
                                       is_grant  => true,
                                       privilege => 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'dbserver.xml',
                                    host => '<Server>');   -- Caution: you can allow all servers with the asterisk sign ("*"), instead of <Server>
END;

The Java Method to read a Directory

It is quite easy to read the content of a directory with Java. To put a bit more sense in this example, the Java method provides the functionality to filter the files with regular expressions. The challenge is to pass the results back to the PL/SQL-Function. But in the chapter above, I described a method to do that in a very easy way. In the download area, you will find a zip archive with the entire example or simply click here.

package starit.utils;
import java.io.File;
import java.io.FileInputStream;
[...]
/**
 * Tools for File-IO
 *
 * @author Stefan Armbruster
 *
 */
public class FileUtils {
[...]
  public static File[] getDirectoryList(String directoryPath, String filterPattern) throws Exception {
    File directory;
    File[] fileList = null;
    FilenameFilter filter;
    try {
      directory = new File(directoryPath);
      // ------------ if a filter is given, use it
      // dem Verzeichnis filtern
      if (filterPattern != null) {
        filter = new FileFilter(filterPattern);
        fileList = directory.listFiles(filter);
      } else {
        fileList = directory.listFiles();
      }
    } catch (Exception ex) {
      throw new Exception(ex.getLocalizedMessage());
    }
    return (fileList);
  }
[...]
 
}

After that, we need a simple Java Wrapper Function,  to encapsulate the parameter passing. The Wrapper-Method has three parameters:

• The Directory itself
• The regular expression
• The session-ID to synchronize the result with the PL/SQL function.

package starit.wrapper;
import java.io.*;
import java.sql.*;
import starit.utils.FileUtils;
 
/** Class: OWFileUtils
 * Wrapper Class to access File-Utils
 * Autor: Stefan Armbruster, 12.2014
 *
 */
 
public class OWFileUtils {
  public static void OWgetDirectoryList(String directoryPath,
      String filterPattern, Integer sessionId) throws Exception {
    File[] fileList = null;
    String fileName;
    String fileType;
    Long fileSize;
    String accessType = "DIRECTORY_LIST";
 
    // here we call the origin function
    fileList = FileUtils.getDirectoryList(directoryPath, filterPattern);
 
    // store each file in the stack
    for (int i = 0; i < fileList.length; i++) {
      if (fileList[i].isDirectory()) {
        fileType = "DIR";
      } else {
        fileType = "FILE";
      }
      fileName = fileList[i].getName();
      fileSize = fileList[i].length();
      
        // SQRJ: put the result back to the stack table
        #sql { insert into SYS_JAVA2PLSQL_PARA_TB (session_id, access_type, field1, field2, field3, field4) values
                                                  (:sessionId, :accessType, :directoryPath, :fileName, :fileType, :fileSize)
        };
        // SQRJ: End
        }
    }
}

Finally we need two PL/SQL Functions to invoke Java. First, the PL/SQL-Java Wrapper, for the core communication with Java. In this case, this wrapper function is just internal, because of the complexity of the parameter. Second, we need another PL/SQL function, which handles the parameter passing from Java to PL/SQL. This is the function we provide as an external interface, to use it everywhere within Oracle. To complete the example we should have two type definitions for the directory content.

create or replace package body JAVA_WRAPPER is
  -------------------------------------------------------------------------------------------------------------------------------
  -- PL/SQL - Java Wrapper Function / just for internal use
  -------------------------------------------------------------------------------------------------------------------------------
  procedure JWgetDirectoryList(p_directory  in varchar2,
                             p_filter     in varchar2,
                             p_session_id in number) as
    language java name 'starit.wrapper.OWFileUtils.OWgetDirectoryList(java.lang.String, java.lang.String, java.lang.Integer )';
 
[...]
   
   
  -------------------------------------------------------------------------------------------------------------------------------
  -- PL/SQL Function to get the list of files of a directory
  -------------------------------------------------------------------------------------------------------------------------------
  function get_dir_list(p_directory in varchar2, p_filter in varchar2)
    return o_tab_dir_list is
    PRAGMA AUTONOMOUS_TRANSACTION;
   
    l_session_id number;
    l_rec_dir    o_rec_dir_list_entry;
    l_tab_dir    o_tab_dir_list;
    l_idx        number := 0;
   
    cursor c_data(ci_session_id in number) is
      select *
        from SYS_JAVA2PLSQL_PARA_TB a
       where a.session_id = ci_session_id;
   
  begin
   
    l_rec_dir := o_rec_dir_list_entry(null, null, null);
    l_tab_dir := o_tab_dir_list();
   
    l_session_id := common_seq.nextval;
   
    -- Call of the PL/SQL - Java Wrapper
    JWgetDirectoryList(p_directory, p_filter, l_session_id);
   
    -- Transfer all entries from the parameter table to the collection
    for c1 in c_data(l_session_id) loop
      l_idx              := l_idx + 1;
      l_rec_dir.fileName := c1.field2;
      l_rec_dir.fileType := c1.field3;
      l_rec_dir.fileSize := to_number(c1.field4);
      --l_tab_dir(l_idx) := l_rec_dir;
      l_tab_dir.extend;
      l_tab_dir(l_tab_dir.last) := l_rec_dir;
    end loop;
   
    --  Since we are in one database session, we can do a rollback instead of deleting the data.
    rollback;
   
    return(l_tab_dir);
     
    exception
      when others then
        rollback;
        raise_application_error(-20001, 'File not found');
   
  end;
[...]

Pipelined Function

Now you can use the result of the get_dir_list function everywhere in PL/SQL. But because this function returns a collection, it is not possible to use it within a regular SQL statement. But exactly this is a functionality we would like to have and this requirement fits perfect for that. Therefor we use a Oracle technology as called Pipelined Functions. Each row within the collection will be returned sequentially to use it in a SQL statement.

-- Pipelined function
function get_dir_list_pipe(p_directory in varchar2) return o_tab_dir_list
  pipelined is
 
  l_rec_dir o_rec_dir_list_entry;
  l_tab_dir o_tab_dir_list;
 
begin
 
  l_tab_dir := get_dir_list(p_directory, null);
 
  for i in 1 .. l_tab_dir.count loop
    l_rec_dir := l_tab_dir(i);
    pipe row(o_rec_dir_list_entry(l_rec_dir.fileName,
                                  l_rec_dir.fileType,
                                  l_rec_dir.fileSize));
   
  end loop;
 
  return;
end;

Then, the content of the c:\temp directory will be showed like this:

SQL> SELECT * FROM TABLE(java_wrapper.get_dir_list_pipe('c:\temp'));
 
FILENAME                                 FILETYPE      FILESIZE   FILEDATE
---------------------------------------- ------------- ---------- -----------------------------
default.txt                              FILE                   7 05.12.14 09:13:01,000000
hello.txt                                FILE                6473 02.12.14 09:44:51,000000
JBoss                                    DIR                    0 01.07.13 10:03:00,000000
subdir                                   DIR                    0 05.12.14 12:04:02,000000
 
4 rows selected
 
SQL> 

Appendix

Definition of the Parameter Table

-- Create table
create table SYS_JAVA2PLSQL_PARA_TB
(
  session_id  NUMBER not null,
  access_type VARCHAR2(50),
  field1      VARCHAR2(1000),
  field2      VARCHAR2(1000),
  field3      VARCHAR2(1000),
  field4      VARCHAR2(1000),
[...]
  field19     VARCHAR2(1000),
  field20     VARCHAR2(1000)
);

Documents and Links

Java Developers Guide for Oracle 11g (11.2)

JPublisher Users Guide

Pipelined Table Functions

Download the source code of the Directory Listing example right here.