Using Java inside PL/SQL procedures

2006, May 11 - 11:37 — müzso

Long ago I wanted to measure durations at the microsecond level, but Oracle 8i lacked support for this ... or at least in PL/SQL. I digged into the subject and found that the only way to do it was to use some external code/library. The easiest way is to use Java, since Java classes (and even the source) can be stored inside the DB.

You can create a class using the following "SQL" syntax:

CREATE JAVA SOURCE NAMED "Hello" AS
  public class Hello {
    public static String hello() {
      return "Hello World";
    }
  }
/

After that you can assign a PL/SQL function to the Java function:

CREATE OR REPLACE FUNCTION hello RETURN VARCHAR2 IS
LANGUAGE JAVA
NAME 'Hello.hello return String'
/

It's very simple, isn't it?

You can store the Java source as an Oracle object in the database and the DB-enginge compiles this automatically into a class for you. These will be two separate objects in the DB (the source and the compiled class - you can check in USER_OBJECTS), but Oracle handles them together. If you drop the source object, then the class is dropped as well.

You can also use standard J2SE functions without writing a single line of Java source. You can bind PL/SQL functions to them, eg. using the sleep method of the Thread class is just this easy:

PROCEDURE jsleep(p_nMsecs IN NUMBER) IS
LANGUAGE JAVA
NAME 'java.lang.Thread.sleep ( long )'
/

Actually a number of builtin dbms_* packages use the embedded JRE already.

monline

Flickr photos

Recent links

Recent comments

Using Java inside PL/SQL procedures