The beauty of Java stored procedures

Since 8i you can write stored procedures also in Java. But what is the advantage of writing a procedure in Java? Well the first advantage is that you’re now able to write complex solutions and use them in PL/SQL. You can write your own package for file I/O, email reading/sending, OS watching, an API for ZIP-files and many more. You’ve all the possibilities from Java now available in PL/SQL! And another advantage: It runs in the Oracle JVM which means it have access to the huge SGA and can do DB operations with better performance than outside it! And it’s easy to write it. For example a little Java stored procedure which shows you the properties of the Oracle JVM:

The first step is to write the java source which looks like this:

create or replace and compile java source named “JavaProps” as

And now continue with normal Java code:

public class props
{
public static void GetProps( )
{
System.getProperties().list(System.out);
}
}
/

Well this now creates the Java source inside the database. Now we create the stored procedure over it to use it inside PL/SQL:

create or replace procedure JavaProps
as language java name ‘props.GetProps()’;
/

And that it was! Now run the procedure in an anonymous PL/SQL block:

set serveroutput on size 10000;
begin
dbms_java.set_output (10000);
JavaProps;
end;
/

So you see, writing Java stored procedures isn’t difficult and allows you to do now powerful operations inside of PL/SQL!

Author: Gerald

Developer, Oracle expert, performance enthusiast and genuine technology geek.

2 thoughts on “The beauty of Java stored procedures”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s