Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Java Stored Procedures

, 5 Nov 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
Java stored procedures

Introduction

I am sure you are familiar with Stored Procedures. Almost every major database vendor supports it. They are program units that are precompiled and stored inside the database. Since the program is inside the database, it is tightly coupled with the database SQL language constructs. This and the precompiled nature of these programs, make the database access faster. So, even if you are writing external programs that access the database, you will benefit from coding and combining the database operations in stored procedures. This saves a lot of back and forth and thus network traffic. Since version 7, Oracle has support for Stored Procedures. These programs are coded a in native language called PL/SQL.

Since Oracle 8i, Oracle supports coding and running similar procedures (program units) written in Java. Like PL/SQL procedures, these programs are precompiled and stored inside the database and are known as Java Stored Procedures. The JAVASPs(1) are stored as classes (in blob fields) and when invoked, they are run inside a JVM that runs within Oracle database. Prior to Oracle 10g, they needed to be wrapped inside a PL/SQL procedure or package. Since Oracle 10g, you can actually invoke Java classes directly from SQL (just the same way you call a PL/SQL function in a SQL).

JavaSPs are different from regular Java programs in that they actually run inside a VM within the database. There are a few restrictions while coding a JAVASP.

Building and Deploying Java SP

To write and build the Java stored procedures, you can use your standard Java development environment. I use Eclipse to develop and Ant to build it.

build_LATAXSP.xml has steps to compile Java classes and build jar file.

Deploying Java SP

At this point, the jar file is ready to be deployed to Oracle. Typically, we pass this step onto the DBA who will then load the jar into Oracle Database instance. But during development, the developer can load these themselves using the loadjava utility. This is typically available on the machine where the database is running. (Remember, it is run by DBAs?). In our case, we have the Oracle databases running on Unix boxes, so we have loadjava utility available there. I upload the file to Unix and run loadjava. While uploading, make sure it’s in binary mode.

The below screenshot shows a sample run of loadjava command on Unix.

loadjava_results

In this example, I loaded all the classes in a jar file, to the database. As shown, there were 24 classes and 2 resources loaded and there were no errors. If the command failed to load the Java classes, you will see an error message here. The first time around, all the classes files are loaded. Next successive load of the same jar file will load only classes that have been modified since the last load.

Verifying the Load

To view the objects in Oracle, the following SQLs can be used:

select * from all_objects where object_type = ‘JAVA CLASS’ and owner = <owner>;

or

select * from user_objects where object_type = ‘JAVA CLASS’;

To see a little more detail about the Java stored procs, use

SELECT * FROM user_java_classes; — this lists java procs for the user.

Earlier, I posted about displaying contents of the resources files (text files) loaded above.

Notes

(1) Oracle actually refers to Java Stored Procedure as JSP. To avoid confusion with Java Server Pages, I prefer JavaSP.

loadjava results1 ant build1
Filed under: CodeProject, Databases, Java, Oracle

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sam Varadarajan
Software Developer (Senior) City of Los Angeles
United States United States
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
 
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
 
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 5 Nov 2013
Article Copyright 2013 by Sam Varadarajan
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid