Call Java Methods from ApEx

For those new to ApEx it may not be immediately obvious that it is possible to execute Java Methods from ApEx. Newbies to Oracle database programming may also be surprised to know that you can actually embed Java in the database at all! The fact that this is possible make the Oracle Database Server very powerful making almost everything possible. If you cant code it in PL/SQL then try Java, If you can't use Java then perhaps you are doing something wrong (or need to go back to basics).

Why would you want to call Java from ApEx applications?

PL/SQL is great but there are occasions when you may need to perform complex processing that simply is not possible with PL/SQL, is too complex to implement or requires the use of third party APIs. One such example may be an image conversion process, which converts a GIF to a JPEG programmatically (granted I know this is possible with Oracle Intermedia).

As regular readers know I’m currently involved in a piece of work which requires various third party Java APIs which can strip out user entered data from a PDF to XML. We then process this relationally using good old pl/sql and relational views over xml. Apologies to those who thought this post might be looking at that topic, I am still writing it up and need to prepare suitable example, which can be downloaded and played with, so please bear with me.

Pre-requisites

• Oracle Database with Java Support ( See Product Matrix )
• A DB schema with relevant schema privileges to execute Java.
• ApEx (pretty much any version will do)

How do you do it?

Calling Java from the Database is a fairly trivial task typically consisting of the following 3 steps.

• Writing the Java Classes
• Loading the classes/code into a target database schema
• Creating a PL/SQL Wrapper round the Java Method you wish to call.

If you need to load pre-compiled java classes into your schema you can use a very useful tool called loadjava. You will need to use this utility if you have existing JAR files and APIs. Hopefully I’ll explore loadjava in a later post, for the moment I would like to concentrate on demonstrating a very simple example of calling a Java method from within Apex. I’m afraid that means it is a “hello world” example as it does not require any pre-compiled classes to be loaded into the database.

Step 1. Create Java Class

The following code creates and compiles a java class called HelloWorldExample. Notice that any method you wish to call from pl/sql must be declared as static. You can copy and paste the following code into sqlplus or sqldeveloper.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "HelloWorldExample"
AS
public class HelloWorldExample
{
HelloWorldExample hello;

public HelloWorldExample()
{
hello = new HelloWorldExample();
}
public static void sayHello(String forename, String surname
                      , String outString[])
{
outString[0] = "HELLO WORLD more specifically hello" + forename
          + " " + surname;
}
public static void main(String[] args)
{
//no method
}
};
/
show errors java source "HelloWorldExample";
/

You should see :

AND COMPILE Compiled.
No Errors.

Step 2: Create a pl/sql call spec/wrapper

In order to use your Java method from Apex or PL/SQL you must create a pl/sql call specification wrapper round each Java method you wish to run from PL/SQL.

Again copy and past the following code into sqlplus/sqldeveloper:

CREATE OR REPLACE PROCEDURE HELLO_WORLD(p1_forename in VARCHAR2
, p2_surname in varchar2, p3_outString out varchar2)
AUTHID CURRENT_USER AS LANGUAGE
JAVA NAME  'HelloWorldExample.sayHello(java.lang.String
, java.lang.String , java.lang.String[])';
/
show err;
/

Step 3: Execute to test

Executing this Hello World example is as easy as declaring an anonymous block and calling the method from within within. For example:

declare
p_forename varchar2(4000) := 'KRISTIAN';
p_surname  varchar2(4000) := 'JONES';
p_msg      varchar2(4000) := null;
begin
hello_world(p_forename, p_surname, p_msg);
dbms_output.put_line(p_msg);
end;

Again if completed correctly you will see :

Anonymous block completed
HELLO WORLD more specifically hello KRISTIAN JONES

Hopefully this has served as a gentle introduction into embedding Java classes in the database. You can call this from procedure from ApEx page as a pl/sql process just substitute in your page items. If anyone has any queries please leave a comment. I'll do my best to reply.