What is the most dangerous Java library

Rudolf Jansen

Java in the database has existed for many years, specifically since Oracle 8i. The most frequently mentioned point of criticism was and is the performance of the Java applications running in the database. While this was completely justified in the first versions, Oracle has improved a few things over the last few years and database versions.

Almost every Java application requires access to database content. The default architecture provides for a strict separation between Java application and database on separate computers. In data-intensive applications, this can lead to a performance bottleneck due to frequent network access between the servers. One approach is to use an embedded Java database. This is completely written in Java and can therefore be integrated into the Java application as a JAR file.

Another solution to the problem can be by moving the application logic into the database. Several manufacturers offer such database-internal programming. If you use an Oracle database, you don't even have to do without Java as the programming language. Thanks to numerous improvements, especially in the performance area, the Oracle-internal JVM has reached a maturity in the latest database versions, which makes it a worthwhile alternative for certain applications.

In addition to the constant upgrade of the Java version used, the possibility of native compilation of the Java code using the tool supplied should also be mentioned. Those who use the new Oracle version 11g can even use the just-in-time compiler that has been added in this release.

An "exotic" construction?

From a database point of view, the system architectures of most (Java) applications are characterized by the fact that the database runs in a separate (server) area and therefore every access can be equated with network access. While this makes sense from an administration point of view and because databases are usually used by many applications at the same time, this architecture leads to performance problems in some cases, in which the frequent network transfer of data from the database to the Java application and back can lead to a bottleneck.

In such cases, an alternative is to integrate parts of the application logic into the database and thus only transfer the input data once when the internal database procedure is called to the database and, similarly, the results on the way back over the network to the Java application.

Anyone who uses an Oracle database automatically thinks of PL / SQL for this architecture. Algorithms have been implemented in this database-internal language since Oracle's beginnings. Therefore, in addition to many PL / SQL libraries supplied by Oracle, there are also some external PL / SQL libraries as well as sufficient PL / SQL know-how from most Oracle developers.

As of Oracle 8.1.5, Java has been available as an alternative to PL / SQL, namely a Java Virtual Machine (JVM) integrated into the database core. The database can thus run Java applications within a session. The supported version of the Java Standard Edition (Java SE) has since grown with the Oracle versions: Java 1.3 was available in Oracle 9.2, Java 1.4 in Oracle 10.2, and the current release 11.1 contains a full Java 5 VM.

Before the article uses a simple (HelloWorld) example to illustrate the procedure for implementing and calling up database-internal Java applications, the first question that arises is why one should use such a construction that appears "exotic" at first glance. As mentioned, the frequent network transfer of extensive database contents is a reason for performance problems during access. The left part of Figure 1 shows that, depending on the application logic, a large number of JDBC calls may be required. In each case, the transferred data must be converted into the appropriate data types: When an SQL or PL / SQL function is called, the conversion from Java to SQL data types takes place and vice versa when the results are returned to the Java application. In addition, there is the delay caused by the actual network transport.

In addition to these performance advantages, an implementation of an algorithm in database-internal Java code, in contrast to an external Java application, has the advantage that this implementation can also be called from other programming languages ​​and can therefore be reused. As the following example shows, this is because the Java code is not called directly, but a so-called PL / SQL wrapper. This is a PL / SQL function that in turn calls the Java code and, like "normal" PL / SQL functions, can be triggered from a C ++ application, for example.

Sample application

Hello world, hello Java

In principle, any Java application can also run within the database, provided that the Oracle version supports the corresponding Java. The following HelloWorld example shows how to proceed when developing and deploying Java applications in the Oracle database. The article then provides information on special features that must be observed in this runtime environment.

The following Java class with its two methods is available outside the database as a normal text file with names:

public class HelloWorld
{public static void hello () {
System.out.println ("Hello World");
public static String helloWithName (String pName) {
return "Hello" + pName;

The tool that the Oracle database comes with during installation transfers the file to the database as follows and compiles it directly:

loadjava -u / [@database] -r

In addition to source code files, the tool can also be used to import class files compiled outside the database and complete archives into the database. They are available there as database objects. The tool exists for deleting such (Java) database objects. Alternatively, the class could be created and compiled directly at the database level. In this case the call would - for example from sqlplus out - look like this:

create or replace java source named "HelloWorld_Test" as

public class HelloWorld {
public static void hello () {
System.out.println ("Hello World");
public static String helloWithName (String pName) {
return "Hello" + pName;

old java source "HelloWorld_Test" compile

The Java source code and the compiled Java class are now in the database, which can be checked using the following command:

SQL> select name, source from user_java_classes

NAME SOURCE ----------- --------------------------
HelloWorld HelloWorld_Test

How can you call the methods of these database-internal Java classes, in our example the two methods and? This is where the PL / SQL wrappers already mentioned come into play. These are "normal" PL / SQL functions that can be called in all ways known from the PL / SQL world. The delegation to the underlying Java classes can be recognized by the addition

is language java

The call functions for the above two methods from the class are, for example:

create or replace procedure print_hello
language java name
'HelloWorld.hello ()';
/ create or
replace function get_helloWithName (p_name in varchar2)
return varchar2 is language java name
'HelloWorld.helloWithName (java.lang.String) return
java.lang.String ';

Before calling the first method, note that the output of the method must be redirected to the current console:

set serveroutput.on size 10000
call dbms_java.set_output (10000)

Calling the PL / SQL wrapper function then produces the desired output:

execute print_hello;

Hello World

Real applications naturally work with database content and not with HelloWorld strings. How is the database content accessed from the database-internal Java application? As with external Java applications via JDBC (Java Database Connectivity), that is, JDBC source code that has been created outside of the database or has already been used can be reused.

A special feature to be noted is the connection setup from JDBC. A database-internal Java function is already called within a database session, that is, a connection to the database with user and password has already been established. The Java function runs within the session with the rights of the user who called it. For the JDBC source code, this means that a separate registration is no longer necessary. Instead, the login takes place via a special "Server Side Internal" JDBC driver and the following method:

Connection con = DriverManager.getConnection ("jdbc: default: connection:");

Another peculiarity is to be considered with the topic of threading. If you want to use the Java Thread API for creating multithreading applications, you can do so, but you should note that all database threads run within one operating system thread. If you want to achieve real multithreading, you should therefore create several database sessions with individual Java calls - for example using jobs. Whether you can actually achieve a significant gain in performance due to the additional latency must be checked on the basis of a specific application.


Who or what can do what?

As with all database applications, developers of database-internal Java applications should get together with their database administrator at an early stage and create the appropriate rights concept. Predefined roles are to be used. The most comprehensive and therefore the most dangerous role is JAVASYSPRIV. During development, it can still be very convenient, as it turns the rights holder into a kind of "Java DBA" whose Java classes are allowed to (almost) everything.

In particular with the option described below of being able to access operating system resources from the database via Java, you should define exactly what the Java methods are allowed to and what not. If, for example, methods create a file in the operating system, it belongs to the operating system user under whose account the Oracle database runs.

The package contains a number of functions for rights administration. The assignment of rights itself is based on the Java2 security concept, which is based on so-called policies in which rights are individually assigned or withdrawn according to specified rules. The main function in this package is:

grant_permission (grantee, permission_type,
permission_name, permission_action)

The following call gives the database user read access to the file, for example. This means that he could access the file contents using a corresponding Java method that reads from this file (provided, of course, that the file can also be read by the Oracle system user at the operating system level).

dbms_java.grant_permission (

When assigning rights, it should be noted that the caller's rights must be checked by default at runtime. If the database user X has created a Java class, the associated database objects (source code, class files and / or JAR archives) are in the database schema of user X. If X now grants database user Y Execute rights for the associated PL / SQL Wrapper functions, Y must have the appropriate rights at runtime.



With the tasks described in the application examples, it is always possible to implement this in the "standard architecture" with Java application and database on separate computers. However, if you need extensive and frequent database access and frequently have to switch between Java and SQL data types, the implementation as a database-internal Java application is an alternative for performance reasons. Other positive side effects are the extended range of functions of the Java Libraries in comparison to PL / SQL as well as the possibility of enabling access to the database-internal Java functions via PL / SQL wrappers and thus making them usable for non-Java applications. The subject of "source code reuse" should also be mentioned as an advantage for Java in the database, and that across programming language boundaries.

While Oracle has intensified the performance problem in recent years, one point of criticism remains. Anyone who implements more extensive database-internal Java applications has to compromise on the comfort of development environments known from the "normal" Java world. The applications can be fully developed outside of the database in Eclipse or other environments, but the tool described above must be used when importing into the database at the latest.

But that should not be a reason not to consider the architecture alternative "Java in the database" when creating Java-Oracle systems. It is certainly not the right approach when it comes to the architecture of complete enterprise Java solutions. That’s not what it’s meant for. The approach can clearly show its strengths where it has more to offer for (partial) requirements on the one hand - especially with regard to performance - than the separation of Java and database and on the other hand offers more functions than the implementation of database-internal algorithms database internal "competitor" PL / SQL.

Rudolf Jansen
lives in Aachen and works as a freelance software developer and journalist. His main areas of activity are in the areas of Java, C ++, XML and databases.

application areas

application areas

When should Java be used outside the database, when should Java be used in the database and when should the entire application logic be implemented directly in PL / SQL? General answers to these questions are not possible, as the answer depends on the application context and also on personal preferences. In the following there are some examples in which Java can be used in the database. In addition, the enormous range of functions of Java and external Java libraries, which can also be imported into the database using the tool, make other areas of application conceivable.

The Lucene Domain Index should be mentioned as a larger example. Lucene is known as a search engine for free text searches written entirely in Java and available as an open source Apache project. As part of the Lucene Domain Index, the framework has now been imported into the Oracle database and optimized there with "Java in the database" means for the special system environment in the Oracle database. The advantages over the "normal" Lucene are the performance and the possibility of access from other programming languages ​​via the respective PL / SQL wrappers. Compared to Oracle's own text tool, there are better indexing options and - for Java developers - easier functional expansion.

While the Lucene port is a larger project, the following examples show smaller functions that can usually be implemented better with Java tools than with existing PL / SQL features. The first example is about the display of directory contents. Simple file access is also possible using the PL / SQL package provided by Oracle. However, this brings with it restrictions, for example that directory contents cannot be displayed.

Java has more to offer for this requirement (the package), since you can find more detailed options here, for example access to file attributes or reading out directories. The same applies to the execution of operating system commands. There are already tools for this in the Oracle world (for example the PL / SQL package), but here, too, the whole thing is more convenient with Java support. Anyone who offers this option with Java in the database should urgently observe the administration options described above and, in particular, precisely enable access to operating system resources such as files and prevent more than the absolutely necessary rights from being available. All access to the operating system is carried out with the rights of the Oracle system user, and in many cases the user should be able to do much more at the operating system level than a user of database-internal Java applications should be allowed to do.

Another example that speaks for the use of Java compared to PL / SQL. is the zipping or unzipping of complete zip archives including the directory structures contained therein. With the PL / SQL package you can - in analogy to the Linux / Unix commands gzip or gunzip - edit individual files, but not complex archives. How to solve the problem using freely available Java libraries, which are to be imported into the Oracle database using the methods described above, can be seen on the German-language Oracle websites.

Other conceivable areas of application of Java in the database are primarily when there are solutions on the Java side, either directly through Java language features or through suitable external extension libraries, for which there are either no or only limited functions in PL / SQL. Examples include FTP transfer, generation of PDF files from database content, XML processing or HTTP / web service calls from the database.