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".

1	public class JavaOracleDemo {

2

3	public static String hello (String name) {

4	return ("Hello " + name);

5

}

6

}

PL/SQL Wrapper Package:

1	create or replace package DEMO_JAVA_WRAPPER is

2

3	function hello(p_name in varchar2) return varchar2 as

4	language java name 'JavaOracleDemo.hello(java.lang.String) return java.lang.String';

5

6

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

CHAR, CHARACTER, LONG, STRING, VARCHAR, VARCHAR2	oracle.sql.CHAR	java.lang.String
RAW, LONG RAW	oracle.sql.RAW	byte[]
BINARY_INTEGER, NATURAL, NATURALN, PLS_INTEGER, POSITIVE, POSITIVEN, SIGNTYPE, INT, INTEGER	oracle.sql.NUMBER	int
DEC, DECIMAL, NUMBER, NUMERIC	oracle.sql.NUMBER	java.math.BigDecimal
DOUBLE PRECISION, FLOAT	oracle.sql.NUMBER	double
SMALLINT	oracle.sql.NUMBER	int
REAL	oracle.sql.NUMBER	float
DATE	oracle.sql.DATE	java.sql.Timestamp
TIMESTAMP TIMESTAMP WITH TZ TIMESTAMP WITH LOCAL TZ	oracle.sql.TIMESTAMP oracle.sql.TIMESTAMPTZ oracle.sql.TIMESTAMPLTZ	java.sql.Timestamp
ROWID, UROWID	oracle.sql.ROWID	oracle.sql.ROWID
BOOLEAN	boolean	boolean
CLOB	oracle.sql.CLOB	java.sql.Clob
BLOB	oracle.sql.BLOB	java.sql.Blob
BFILE	oracle.sql.BFILE	oracle.sql.BFILE
SQL and PL/SQL Data Type	Oracle Mapping	Java Mapping

 

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

01	import java.util.ArrayList;

02	import java.util.List;

03

04	// Declaration of the iterator to grab the data from the stack

05	// SQLJ Iterator Deklaration

06	#sql iterator StackIterator (String field1);

07

08	public class JavaOracleDemo {

09

10	public static void getSetStackDemo(Integer sessionId) throws Exception  {

11

12	// internal definition of an array to store the strings

13	ArrayList stack = new ArrayList();

14

15	// Definition of the iterators (=Cursor) to get the data from the stack (SQLJ)

16	StackIterator stackIterator;

17

18	#sql stackIterator = {select field1 from sys_java2plsql_para_tb where session_id = :sessionId };

19

20	// get the data from the stack and store it into an array

21	while (stackIterator.next()) {

22	String stackEntry = stackIterator.field1();

23

24	stack.add(stackEntry);

25

}

26

27	// clear the stack

28	#sql { delete from sys_java2plsql_para_tb where session_id = :sessionId };

29

30	// put the modified data back on the stack

31	for (String stackEntry : stack) {

32	String stackEntryModified = stackEntry + " Java was here";

33	#sql { insert into SYS_JAVA2PLSQL_PARA_TB (session_id, field1) values

34	(:sessionId, :stackEntryModified) };

35

}

36

}

37

}

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.

01	-- Java Wrapper Function

02	procedure getSetStackDemoW(p_session_id in number) as

03	language java name 'JavaOracleDemo.getSetStackDemo(java.lang.Integer) ';

04

05	procedure getSetStackDemo(p_string_tab in out t_tab) is

06	PRAGMA AUTONOMOUS_TRANSACTION;

07

08	l_session_id number;

09	idx          number := 0;

10

11	cursor c_stack(ci_session_id in number) is

12

select *

13	from sys_java2plsql_para_tb a

14	where a.session_id = ci_session_id;

15

16

begin

17

18	l_session_id := common_seq.nextval;

19

20	-- read the collection and put each string on the stack

21	for i in 1 .. p_string_tab.count loop

22	insert into sys_java2plsql_para_tb

23	(session_id, field1)

24

values

25	(l_session_id, p_string_tab(i));

26

end loop;

27

28	-- call the wrapper funktion

29	getSetStackDemoW(l_session_id);

30

31	-- get the stack back and transfer it into the collection

32	for c1 in c_stack(l_session_id) loop

33	idx := idx + 1;

34	p_string_tab(idx) := c1.field1;

35

end loop;

36

37

rollback;

38

39

end;

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

01

declare

02	l_strings demo_java_wrapper.t_tab;

03

begin

04	l_strings(1) := 'Hello';

05	l_strings(2) := 'World';

06	l_strings(3) := 'Oracle';

07

08	DEMO_JAVA_WRAPPER.getSetStackDemo (l_strings);

09

10	for i in 1..l_strings.count loop

11	dbms_output.put_line (l_strings(i));

12

end loop;

13

end;

... and this is the expected output:

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

 

Access the FileSystem with Java

Oracle already provides with the package UTL_FILE a few simple functions to access OS File System for reading or writing files. But if you want to read the content of a whole directory, it may become difficult. This is perfect for Java. Here we have all capabilities and all the comfort, for which Java is known for.

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:

select * from USER_JAVA_POLICY  where grantee_name = '<Schema-Name>'

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

1	-- Java Grants (als Benutzer SYS)

2

begin

3	dbms_java.grant_permission('','java.io.FilePermission','<<ALL FILES>>','read,write,execute,delete');   -- Alll permissions on all diretories

4	dbms_java.grant_permission('','java.io.FilePermission','C:\temp','read');                              -- only read permission on c:\temp

5	dbms_java.grant_permission('','java.lang.RuntimePermission','*','writeFileDescriptor');

6	dbms_java.grant_permission('','java.net.SocketPermission','*','connect, resolve');

7	dbms_java.grant_permission('','java.util.PropertyPermission','*','read');

8

commit;

9

end;

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

01	-- ACL Permissions

02

BEGIN

03	DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'dbserver.xml',

04	description => 'DB Server ACL',

05	principal   => 'PUBLIC',     -- if necessary, restrict it to a certain user (instead of public)

06	is_grant    => true,

07	privilege   => 'connect');

08	DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'dbserver.xml',

09	principal => 'PUBLIC',    -- if necessary, restrict it to a certain user (instead of public)

10	is_grant  => true,

11	privilege => 'resolve');

12	DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'dbserver.xml',

13	host => '<Server>');   -- Caution: you can allow all servers with the asterisk sign ("*"), instead of <Server>

14

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.

01	package starit.utils;

02	import java.io.File;

03	import java.io.FileInputStream;

04

[...]

05

/**

06	* Tools for File-IO

07

*

08	* @author Stefan Armbruster

09

*

10

*/

11	public class FileUtils {

12

[...]

13	public static File[] getDirectoryList(String directoryPath, String filterPattern) throws Exception {

14	File directory;

15	File[] fileList = null;

16	FilenameFilter filter;

17

try {

18	directory = new File(directoryPath);

19	// ------------ if a filter is given, use it

20	// dem Verzeichnis filtern

21	if (filterPattern != null) {

22	filter = new FileFilter(filterPattern);

23	fileList = directory.listFiles(filter);

24

} else {

25	fileList = directory.listFiles();

26

}

27	} catch (Exception ex) {

28	throw new Exception(ex.getLocalizedMessage());

29

}

30	return (fileList);

31

}

32

[...]

33

34

}

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.

 

01	package starit.wrapper;

02	import java.io.*;

03	import java.sql.*;

04	import starit.utils.FileUtils;

05

06	/** Class: OWFileUtils

07	* Wrapper Class to access File-Utils

08	* Autor: Stefan Armbruster, 12.2014

09

*

10

*/

11

12	public class OWFileUtils {

13	public static void OWgetDirectoryList(String directoryPath,

14	String filterPattern, Integer sessionId) throws Exception {

15	File[] fileList = null;

16	String fileName;

17	String fileType;

18	Long fileSize;

19	String accessType = "DIRECTORY_LIST";

20

21	// here we call the origin function

22	fileList = FileUtils.getDirectoryList(directoryPath, filterPattern);

23

24	// store each file in the stack

25	for (int i = 0; i < fileList.length; i++) {

26	if (fileList[i].isDirectory()) {

27	fileType = "DIR";

28

} else {

29	fileType = "FILE";

30

}

31	fileName = fileList[i].getName();

32	fileSize = fileList[i].length();

33

34	// SQRJ: put the result back to the stack table

35	#sql { insert into SYS_JAVA2PLSQL_PARA_TB (session_id, access_type, field1, field2, field3, field4) values

36	(:sessionId, :accessType, :directoryPath, :fileName, :fileType, :fileSize)

37

};

38	// SQRJ: End

39

}

40

}

41

}

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.

01	create or replace package body JAVA_WRAPPER is

02	-------------------------------------------------------------------------------------------------------------------------------

03	-- PL/SQL - Java Wrapper Function / just for internal use

04	-------------------------------------------------------------------------------------------------------------------------------

05	procedure JWgetDirectoryList(p_directory  in varchar2,

06	p_filter     in varchar2,

07	p_session_id in number) as

08	language java name 'starit.wrapper.OWFileUtils.OWgetDirectoryList(java.lang.String, java.lang.String, java.lang.Integer )';

09

10

[...]

11

12

13	-------------------------------------------------------------------------------------------------------------------------------

14	-- PL/SQL Function to get the list of files of a directory

15	-------------------------------------------------------------------------------------------------------------------------------

16	function get_dir_list(p_directory in varchar2, p_filter in varchar2)

17	return o_tab_dir_list is

18	PRAGMA AUTONOMOUS_TRANSACTION;

19

20	l_session_id number;

21	l_rec_dir    o_rec_dir_list_entry;

22	l_tab_dir    o_tab_dir_list;

23	l_idx        number := 0;

24

25	cursor c_data(ci_session_id in number) is

26

select *

27	from SYS_JAVA2PLSQL_PARA_TB a

28	where a.session_id = ci_session_id;

29

30

begin

31

32	l_rec_dir := o_rec_dir_list_entry(null, null, null);

33	l_tab_dir := o_tab_dir_list();

34

35	l_session_id := common_seq.nextval;

36

37	-- Call of the PL/SQL - Java Wrapper

38	JWgetDirectoryList(p_directory, p_filter, l_session_id);

39

40	-- Transfer all entries from the parameter table to the collection

41	for c1 in c_data(l_session_id) loop

42	l_idx              := l_idx + 1;

43	l_rec_dir.fileName := c1.field2;

44	l_rec_dir.fileType := c1.field3;

45	l_rec_dir.fileSize := to_number(c1.field4);

46	--l_tab_dir(l_idx) := l_rec_dir;

47	l_tab_dir.extend;

48	l_tab_dir(l_tab_dir.last) := l_rec_dir;

49

end loop;

50

51	--  Since we are in one database session, we can do a rollback instead of deleting the data.

52

rollback;

53

54	return(l_tab_dir);

55

56

exception

57	when others then

58

rollback;

59	raise_application_error(-20001, 'File not found');

60

61

end;

62

[...]

 

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.

01	-- Pipelined function

02	function get_dir_list_pipe(p_directory in varchar2) return o_tab_dir_list

03	pipelined is

04

05	l_rec_dir o_rec_dir_list_entry;

06	l_tab_dir o_tab_dir_list;

07

08

begin

09

10	l_tab_dir := get_dir_list(p_directory, null);

11

12	for i in 1 .. l_tab_dir.count loop

13	l_rec_dir := l_tab_dir(i);

14	pipe row(o_rec_dir_list_entry(l_rec_dir.fileName,

15	l_rec_dir.fileType,

16	l_rec_dir.fileSize));

17

18

end loop;

19

20

return;

21

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.