Java IoT Authors: Yeshim Deniz, Pat Romanski, Liz McMillan, Elizabeth White, Paul Simmons

Related Topics: Java IoT

Java IoT: Article

Strategies for Writing Java Stored ORACLE Databases

Strategies for Writing Java Stored ORACLE Databases

As of V8.i, Oracle developers can now write stored procedures, functions, packages and triggers in Java instead of PL/SQL (Oracle's proprietary procedural language), which provides some appealing options:

  • We don't have to learn a proprietary (and thus limited-use) language to write stored objects for Oracle databases.
  • We can get performance improvements over PL/SQL that make stored objects much more usable than they've been in the past.
  • We can write code that's at least somewhat migratable to other database platforms should we wish to do so.
I'll provide guidelines and strategies for the effective use of Java within Oracle databases, and a brief overview of how to write stored procedures, functions, packages and triggers for Oracle Databases (V8.i) for readers who aren't familiar with these new features of Oracle. A basic knowledge of Java and Oracle database concepts (including SQL and the definitions of stored procedure, function, trigger and package) is assumed.

Java Stored Procedure Overview
Review of Capabilities
Stored objects written in Java use JDBC to access Oracle databases the same way Java programs outside the database do. It's also common to embed SQLJ (a preprocessor that inserts generated JDBC code in your program) within Java stored procedures. The only coding difference with Java stored procedures is a change in how we initiate a database connection. There are also a couple of utilities that define Java programs to an Oracle database as well as changes to the CREATE PROCEDURE, FUNCTION, PACKAGE BODY and TRIGGER statements. If you're familiar with the capabilities of PL/SQL, stored objects in Java can do anything PL/SQL can do.

Oracle JVM Specifics
Because of Aurora's tight integration with the Oracle database kernel, it isn't pluggable. No upgrade to Java 1.2 was available at the time of this writing (though I'd expect one at some point).

Loadjava and dropjava Utilities
The two-step process to define Java stored objects is (1) load the Java class, and (2) expose its methods. Java classes are loaded into the database by either a CREATE JAVA statement or via the loadjava utility, which is typically executed from an operating system command prompt. In reality, the loadjava utility issues a CREATE JAVA statement behind the scene. I find the loadjava utility easier to use.

As an illustration, I've written a short class that'll determine a unique number for an identifier field of a table. Frequently, in Oracle-based applications, sequences that generate unique numbers are used to generate a unique number for use as a key field in a table. Unfortunately, the sequence that generates the number has no formal association with the field in the table using the number. This means programmers have to check for the possibility that the generated number isn't unique. My program centralizes this logic in one class so no one else has to code it (see Listing 1).

Java classes can be loaded as source, class or JAR files. Java source is compiled by the JVM in the Oracle database engine. The ability to load class and JAR files is nice because we can conceivably load purchased components (provided they aren't GUI components) into the database. An example of a loadjava statement follows:

loadjava -u derek/[email protected]:1521:ORA81a -thin -v -f -r -t

The -v option produces detailed messages about the steps loadjava is going through to compile and load my Java class. The -f forces the loading of this Java class even though it's already present, which means I don't have to issue a dropjava command first. The -u option specifies the connection string in thin-driver format for the database in which this class is being loaded. The -r option designates that all external references are to be resolved at load-time instead of runtime. The -t option designates that the "thin" JDBC drivers are to be used for any database communication during the load process. The last argument specifies my Java source.

Similarly, I can remove my class with the dropjava utility. The command arguments are similar. The -v and -u options mean the same thing as with the loadjava utility. An example follows:

dropjava -u derek/[email protected]:1521:ORA81a -v -t OracleProcs

Once the classes have been loaded, we must expose individual methods with CREATE PROCEDURE, FUNCTION and PACKAGE BODY statements. It should be noted that the Java language libraries, JDBC libraries and ORB class libraries are already present in the database. No need to load them again.

While the loadjava utility will associate Java classes with the database, none of the methods associated with those classes are callable until you register them. Methods of Java classes are registered (or "wrapped") by issuing CREATE PROCEDURE, FUNCTION or PACKAGE BODY statements. After registration, they can be called in the same manner as PL/SQL procedures, functions and packages.

An example of a CREATE FUNCTION statement that registers a Java method is presented below:

create or replace function getID(
TableName varchar2,
ColumnName varchar2,
SequenceName varchar2)
return number
as language java
name 'OracleProcs.getUniqueIdentifier(java.lang.String, java.lang.String, java.lang.String)
return double';

The "AS LANGUAGE JAVA" clause also works with CREATE PROCEDURE and CREATE PACKAGE BODY statements.

Note: You must fully qualify the argument passed if it isn't a native Java data type. As many of you know, strings aren't a native data type in the Java language. The definition of a string is obtained from the Java.lang import library. Hence, we must fully qualify the object type being passed.

Surprisingly, only a few alterations are needed to define a Java program as a stored object under V8.i. All Java stored objects use JDBC for database access.

Java Requirements
All Java methods executed from a database connection must be declared as static. This makes sense as these methods are essentially being invoked from outside the JVM, and hence must be runnable (as method main always is). Once invoked, methods can instantiate nonstatic objects and use them, but these object allocations disappear after the method completes.

If you wish to retain information within the Java class between method calls, you must store them in a static-defined variable.

Another major difference: within a Java stored object we initiate a JDBC connection differently. With stored procedures we'll typically use the connection created by the process that invoked the stored procedure as opposed to opening up a separate connection. An example of how to specify the default connection is:

Connection dbConnect = new OracleDriver().defaultConnection();

Issues to Be Considered When Introducing Java Stored Procedures
Consider the following issues before deploying Java stored procedures in your organization:

  • Platform compatibility requirements for your applications
  • Developer training
  • System management and source control
  • Software licensing
  • Application performance
You need to identify the target Oracle database platform for your applications before migrating stored objects to Java. Java stored objects are new with V8.i; they're not supported in V8.0.5 and earlier. Applications that need to support earlier versions of Oracle software won't be able to migrate immediately.

As Java stored procedures use standard JDBC to issue SQL statements, Java is easier to migrate to other database platforms should that become necessary. I have a number of clients who would like to migrate an application from one database platform to another but can't because stored procedures are written in a proprietary, nonportable language. Stored procedures written in Java have a significant chance of being portable to a non-Oracle platform without a complete rewrite.

Developer training is usually a significant issue when adopting new technologies. For shops already developing in Java, introducing Java stored objects would be easy and inexpensive. No additional training would be necessary. For shops that don't use Java, training costs could be significant, but comparable to other languages. In addition, database administrators would also have to learn Java at a basic level in order to provide developer support.

Many Oracle environments use object ownership to distinguish between environments. It's common to define testing tables and indexes using one user ID and to create a development environment using another. For example, user DEV might own our development tables, indexes, and so on, while user TEST owns our testing environment in the same database. Oracle's JVM, like other JVMs, doesn't have a native ownership/object security model. Only one version of a class can be present in Oracle's JVM, so I can't have a development and testing version of the same class in a database. These environments must now be separated into different databases. Robust source control procedures are needed with Java stored procedures to avoid conflicts.

There's a software licensing issue with using Java stored procedures. At the time of this writing, Oracle's JVM was licensed separately and has its own cost component. From a strictly technical point of view, writing stored procedures in Java instead of PL/SQL has many advantages. However, the cost of Oracle's JVM may not be worth the benefits for some applications.

Performance Issues
Java stored procedures are much faster than PL/SQL. My tests indicated that Java stored procedures that do ordinary SQL statements, such as selects, updates, inserts and deletes, can be improved 20-40% if written in Java instead of PL/SQL. Additionally, procedures written in Java that don't issue SQL statements execute nine or 10 times faster than PL/SQL.

Java outperformed PL/SQL by 20-40% for SQL operations by allowing more flexible array processing and write batching. To get a simple SQL operation test, I wrote a Java and PL/SQL procedure to select and loop through all object names in the DBA_OBJECTS system view. For those that are interested, DBA_OBJECTS identified all objects existing in an Oracle database. At the time of my test there were 11,668 objects in my database.

Out of curiosity, I wrote the method to take the array size as an argument. Oracle allows array processing on select statements; array processing allows Oracle to retrieve rows in batches (e.g., 100 at a time) for efficiency. PL/SQL doesn't support array processing. Oracle's JDBC drivers set the array size to 10 by default. The Java source for this method can be found in Listing 2. The PL/SQL source for this method can be found in Listing 3. My results are in Listing 4.

The results show that by default (array size of 10) Java was about 18% faster than PL/SQL. However, if you employ array processing (which is easy to do with Java), you can get significant performance improvements for read operations.

As an aside, there are diminishing returns to increasing the array size - performance improves more if the array size is increased from 1 to 10 than from 100 to 200. To set the array size, use the setDefaultRowPrefetch method of the OracleConnection class. An example of how to do this is contained in Listing 1.

Strategies for Effectively Using Java Stored Procedures
Once you've decided to write stored procedures in Java instead of Oracle's native PL/SQL, you need to decide which Java classes should be deployed as stored procedures and which should be deployed normally (as part of an application, applet, CORBA service, etc.). The issues to think about when deciding whether to use Java stored procedures are:

  • JVM currency
  • Application design
  • Performance
Oracle's JVM, Aurora, is currently in V1.1.6 and not pluggable. Java deployed as a stored procedure won't have access to features in later releases of the JVM (e.g., V1.2.x). While I'd expect Oracle to keep Aurora relatively current, it'll never be at the same level as the latest and greatest Java release.

From an application-design point of view, the fact that all methods called from a database connection need to be declared "static" tends to limit the role of Aurora to that of a "function loader." A class in this context is just an arbitrary collection of methods. While you can instantiate and use classes within a method call, any memory you allocate won't be available for future method calls. If you wish to retain information for future method calls, you must store this information in a statically defined and allocated variable. With this restriction it's hard to keep a purely object-oriented design for this section of the application.

Usage Guidelines
In my experience, Java code deployed as a normal application outside Aurora performs four to eight times faster than Java deployed as a stored procedure.

Because of the various performance issues involved, I tend to deploy Java code outside the database as part of an application, applet, servlet, CORBA service, and so on. However, I do use Java stored procedures to implement the following items:

  • Database triggers
  • Custom SQL column functions
Database triggers execute code when INSERT, UPDATE or DELETE statements are issued. They're defined on a per-table basis. Triggers are used to enforce business rules that the database can't enforce via referential integrity constraints. For instance, I use triggers to execute the unique identifier generator that was reviewed in the first section of the article. An example of such a trigger definition follows:

create or replace trigger BEER_TR
before insert on beer
for each row
when (new.beer_id is null)
:new.beer_id :=

Another place that Java stored procedures can be used effectively is in custom-column functions. Most developers are familiar with COUNT, SUM, AVERAGE and other native column functions that most databases provide. Using Oracle, it's possible to write custom column functions. I've used it in the past to format numbers (such as 999) into a currency format (such as $999.00).

Note: As Oracle Corporation is constantly tweaking its products, my usage guidelines for Java stored procedures may change for future versions of Oracle.

More Stories By Derek Ashmore

Derek Ashmore is a consultant and the author of the J2EE
Architect's Handbook, available at www.dvtpress.com.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

@ThingsExpo Stories
In his Opening Keynote at 21st Cloud Expo, John Considine, General Manager of IBM Cloud Infrastructure, led attendees through the exciting evolution of the cloud. He looked at this major disruption from the perspective of technology, business models, and what this means for enterprises of all sizes. John Considine is General Manager of Cloud Infrastructure Services at IBM. In that role he is responsible for leading IBM’s public cloud infrastructure including strategy, development, and offering m...
DXWorldEXPO LLC announced today that All in Mobile, a mobile app development company from Poland, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. All In Mobile is a mobile app development company from Poland. Since 2014, they maintain passion for developing mobile applications for enterprises and startups worldwide.
DXWorldEXPO LLC announced today that ICC-USA, a computer systems integrator and server manufacturing company focused on developing products and product appliances, will exhibit at the 22nd International CloudEXPO | DXWorldEXPO. DXWordEXPO New York 2018, colocated with CloudEXPO New York 2018 will be held November 11-13, 2018, in New York City. ICC is a computer systems integrator and server manufacturing company focused on developing products and product appliances to meet a wide range of ...
Headquartered in Plainsboro, NJ, Synametrics Technologies has provided IT professionals and computer systems developers since 1997. Based on the success of their initial product offerings (WinSQL and DeltaCopy), the company continues to create and hone innovative products that help its customers get more from their computer applications, databases and infrastructure. To date, over one million users around the world have chosen Synametrics solutions to help power their accelerated business or per...
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
Dion Hinchcliffe is an internationally recognized digital expert, bestselling book author, frequent keynote speaker, analyst, futurist, and transformation expert based in Washington, DC. He is currently Chief Strategy Officer at the industry-leading digital strategy and online community solutions firm, 7Summits.
Founded in 2000, Chetu Inc. is a global provider of customized software development solutions and IT staff augmentation services for software technology providers. By providing clients with unparalleled niche technology expertise and industry experience, Chetu has become the premiere long-term, back-end software development partner for start-ups, SMBs, and Fortune 500 companies. Chetu is headquartered in Plantation, Florida, with thirteen offices throughout the U.S. and abroad.
Bill Schmarzo, author of "Big Data: Understanding How Data Powers Big Business" and "Big Data MBA: Driving Business Strategies with Data Science," is responsible for setting the strategy and defining the Big Data service offerings and capabilities for EMC Global Services Big Data Practice. As the CTO for the Big Data Practice, he is responsible for working with organizations to help them identify where and how to start their big data journeys. He's written several white papers, is an avid blogge...
I think DevOps is now a rambunctious teenager - it's starting to get a mind of its own, wanting to get its own things but it still needs some adult supervision," explained Thomas Hooker, VP of marketing at CollabNet, in this SYS-CON.tv interview at DevOps Summit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"MobiDev is a software development company and we do complex, custom software development for everybody from entrepreneurs to large enterprises," explained Alan Winters, U.S. Head of Business Development at MobiDev, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA.
Major trends and emerging technologies – from virtual reality and IoT, to Big Data and algorithms – are helping organizations innovate in the digital era. However, to create real business value, IT must think beyond the ‘what’ of digital transformation to the ‘how’ to harness emerging trends, innovation and disruption. Architecture is the key that underpins and ties all these efforts together. In the digital age, it’s important to invest in architecture, extend the enterprise footprint to the cl...
Data is the fuel that drives the machine learning algorithmic engines and ultimately provides the business value. In his session at Cloud Expo, Ed Featherston, a director and senior enterprise architect at Collaborative Consulting, discussed the key considerations around quality, volume, timeliness, and pedigree that must be dealt with in order to properly fuel that engine.
Two weeks ago (November 3-5), I attended the Cloud Expo Silicon Valley as a speaker, where I presented on the security and privacy due diligence requirements for cloud solutions. Cloud security is a topical issue for every CIO, CISO, and technology buyer. Decision-makers are always looking for insights on how to mitigate the security risks of implementing and using cloud solutions. Based on the presentation topics covered at the conference, as well as the general discussions heard between sessio...
IoT is rapidly becoming mainstream as more and more investments are made into the platforms and technology. As this movement continues to expand and gain momentum it creates a massive wall of noise that can be difficult to sift through. Unfortunately, this inevitably makes IoT less approachable for people to get started with and can hamper efforts to integrate this key technology into your own portfolio. There are so many connected products already in place today with many hundreds more on the h...
No hype cycles or predictions of zillions of things here. IoT is big. You get it. You know your business and have great ideas for a business transformation strategy. What comes next? Time to make it happen. In his session at @ThingsExpo, Jay Mason, Associate Partner at M&S Consulting, presented a step-by-step plan to develop your technology implementation strategy. He discussed the evaluation of communication standards and IoT messaging protocols, data analytics considerations, edge-to-cloud tec...
DXWorldEXPO LLC announced today that the upcoming DXWorldEXPO | CloudEXPO New York event will feature 10 companies from Poland to participate at the "Poland Digital Transformation Pavilion" on November 12-13, 2018.
Digital Transformation is much more than a buzzword. The radical shift to digital mechanisms for almost every process is evident across all industries and verticals. This is often especially true in financial services, where the legacy environment is many times unable to keep up with the rapidly shifting demands of the consumer. The constant pressure to provide complete, omnichannel delivery of customer-facing solutions to meet both regulatory and customer demands is putting enormous pressure on...
CloudEXPO | DXWorldEXPO are the world's most influential, independent events where Cloud Computing was coined and where technology buyers and vendors meet to experience and discuss the big picture of Digital Transformation and all of the strategies, tactics, and tools they need to realize their goals. Sponsors of DXWorldEXPO | CloudEXPO benefit from unmatched branding, profile building and lead generation opportunities.
The best way to leverage your CloudEXPO | DXWorldEXPO presence as a sponsor and exhibitor is to plan your news announcements around our events. The press covering CloudEXPO | DXWorldEXPO will have access to these releases and will amplify your news announcements. More than two dozen Cloud companies either set deals at our shows or have announced their mergers and acquisitions at CloudEXPO. Product announcements during our show provide your company with the most reach through our targeted audienc...
@DevOpsSummit at Cloud Expo, taking place November 12-13 in New York City, NY, is co-located with 22nd international CloudEXPO | first international DXWorldEXPO and will feature technical sessions from a rock star conference faculty and the leading industry players in the world.