Executing Java Programs in Oracle Database

With this blog post I am trying to give a overview of a very powerful feature of Oracle, but mostly not used or unknown to developers. Oracle Database can store a Java Byte Code and run Java stored procedures in the same way as PL/SQL stored procedures.

I am picking a very simple java code, which takes 2 integer arguments and returns sum of them in integer.

1) Create a file "math_java.java" as follows
public class math_java 
{
    public static int sum_java(int a, int b)
    {
        int sum;
        sum = a+b;
        return sum;
    }
}

2) Compile the "math_java.java" file using Java compiler to generate class file
javac math_java.java

3) Load "math_java.class" on database server using loadjava
loadjava -user scott math_java.class

4) Now we need to publish above JAVA class in database by create a mapping function
CREATE or replace FUNCTION sum_java(a number, b number) RETURN number AS LANGUAGE JAVA NAME 'math_java.sum_java(java.lang.int, java.lang.int) return java.lang.int';

All done :) Now we can use this Java Stored Procedure anywhere in our Oracle Database. Lets give a try.

SQL> select sum_java(5,7) from dual;
       SUM
----------
        12

Now one can ask what is the benefit of storing a JAVA code in Oracle Database. I personally saw following main benefits
- Power of JAVA in your hand
- Performance is very high
- Easy to use


Please look into Oracle Documentation it you want to read more about it.

Related Posts:
- JSON in Oracle Database with Examples
- Oracle Database 12c New Features for Developers
- SQL Interview Question Answers

9 comments:

  1. Cards for your blog good contribution to the community

    ReplyDelete
  2. Yeah, that is a very interesting feature, but unfortunately, the last time I tried to use it I found out that there is no way to change the Java version that the database internally uses, and I was shocked when I discovered that it only supported JDK 5 (I was using 11g R1, I think), and I needed support for Java 6 at least. I spent some hours loading some of the classes I needed until I hit a wall and decided to stop trying to do it.

    But for basic/general Java code, it is a great feature.

    ReplyDelete
  3. yes, but whatever Java can do inside database, PL/SQL can do it better/easier/not to mention - faster!
    Believe me, i am working as an Oracle DBA for 15 years, and as a PL/SQL programmer for 3 years.
    I still did not run into any task/problem that PL/SQL could not solve in some way.
    I remember one (and only one) time when we had to create Java function wrapped inside pl/sql procedure ... it was a need to measure miliseconds inside some table and it was some 15 years ago. It was Oracle 8i. In the next relese they introduced timestamp columns and functions, hence there was no need for Java inside Oracle DB anymore.
    But, your article is very welcome , of course!

    ReplyDelete
  4. I just used a bit of this feature and found that it is really powerful. Combining pl/sql and Java running on Oracle DB, we can do almost everything. My problem was that I need to find out the list of files in a directory. It is very easy in Java but i don’t know how to do it in pl/sql so the solution is to use Java for it and wrap the java method in a pl/sql procedure

    ReplyDelete
  5. I worked at Oracle Support and supported java in the database when Java was introduced in the database. There is very little you could/should do in java that cannot be done in PL/SQL. There may be code in java to implement a encryp/decrypt that you want the same code in and out of the db. But mostly just use PL/SQL

    I will also warn though you can load jars into the db, resist the urge.

    ReplyDelete
  6. Very good post. Your blog is excellent! Thanks

    ReplyDelete
  7. Nice one ;-)
    Yes in this case this is a good idea.
    You should give a look at OS_COMMAND and perhaps HOST.

    ReplyDelete
  8. Its a nice functionality, good to know its there, but performace is realy not a good argument.
    Whenever it solvable in SQL or PL/SQL it should be solved in those.
    Performance is an argument againts, not for.

    ReplyDelete
  9. nice one :-)...

    appreciated for your discovery .. did a great job

    ReplyDelete