Tip

Using Java stored procedures: AOL instant messaging from Oracle



Introduction

Have you been wondering how you might use Java stored procedures instead of Oracle's native PL/SQL stored procedures? This article discusses the pros and cons of Java stored procedures, guidelines on how to build them, the separation line for the database vs. App Server Java, performance considerations, troubleshooting, and rollout/security aspects. The final section of this paper includes a Java stored procedure example that allows you to send instant messages from the database.

Pros

Because Java stored procedures are written in Java (obviously), they provide the standard advantages that the Java language provides. These include things like Java is an open source language, it is object-oriented, there is a large development community, and so forth. Java is faster than PL/SQL for computation-intensive operations. Java can be used to develop code on all platforms (i.e. in the database, on the Application Server, and on presentation layer -- JSPs, and the client -- Applets). You can also do things with Java that you can't do with PL/SQL (i.e. call AOL instant messaging, receive/read email, read files larger than 32K, etc).

Cons

It wouldn't be fair to simply list the good things about Java without discussing the other side. Java is slower than PL/SQL for data-intensive operations like batch data sets. PL/SQL is Oracle's native language, so Java is not as well integrated into the database. To call Java stored procedures,

    Requires Free Membership to View

you must declare the Java procedures in PL/SQL. In other words, you can't use 100 percent-pure Java. For example, database triggers must call PL/SQL. Those PL/SQL procedures can be created as Java however. Another issue with the Java in the database is that it uses an older Java Virtual Machine (JVM) than does the Application Server. Java is currently at version 1.4. Versions 8.1 and 9.0.1 of the database use Java 1.2 and 9i Release 2 uses Java 1.3. The JVM match the capabilities of the 9iAS JVM.

Guidelines on how to build them

Building Java stored procedures is easy. One method is to use your favorite editor to create your Java program. You can load the java source directly into the database using the loadjava command. Loading the Java program into the database also compiles the program. You can test the Java source using the Java suite's compiler (javac).

A preferred method to creating your Java programs is by using Oracle's JDeveloper Integrated Development Environment (IDE). JDeveloper allows you to edit, debug and compile your Java program into a class file. JDeveloper also loads the Java program into the database using its one-click deployment methods. Another method of loading class files into the database is using Oracle's loadjava program.

Once you create your Java program, it's time to test, debug and troubleshoot it. For your Java program to work inside the database, you may need to load other libraries into the database. Specifically, if you used a Java library that isn't standard to the database's JVM, you'll need to use loadjava to load those Java libraries too. Once the Java procedures are in the database, you must create a PL/SQL wrapper for the Java. This is demonstrated below.

Database vs. App Server Java

At first it might seem difficult to know when to put the Java in the database vs. when to put the Java code on the application server. However, it's really not all that difficult. The bottom line is that if you can perform the action outside the database, then do so. Some things can't be done outside the database, however. For example, anything that requires a pipeline table function -- such as a Java function that acts like an Oracle table -- must be done within the database. Also table triggers; when data changes and data-based events can't be performed outside of the database. In the example below, we want to have database events trigger instant messages to user; this can't be done outside the database.

Performance

In my testing, I've discovered that there is about a 100ms overhead to call a Java procedure. This isn't a huge issue, but there is a cost associated with testing.I've also noticed that Java procedures don't load into the SGA as nicely as PL/SQL procedures, they are slower. In my testing, I found that there can be a 30-60 second delay for the first call to a Java procedure.

Troubleshooting

Troubleshooting your Java stored procedures can be challenging. I've noticed that many times after I load my stored procedures, the Java objects appear to not be loaded. At least that what the errors lead me to believe. However, when I executed a "Select * from all_objects where object_name like '%new_database_object%'" I discovered that the object was simply not valid. This can be true for a number of reasons, but first you must figure out why your Java procedure is invalid. You can do so by executing "alter java class "{all_objects_name}" compile" in SQL*Plus, which will attempt to recompile your Java procedure. Next, type "Show errors" -- you'll likely find that your Java class couldn't find a specific library. Did you forget to load another Java class? Is it valid? Attempt to compile the object that it says it can't find and show errors once again. Keep doing this until you figure out the library that's missing. When it says it can't find the object, that's the missing object.

Once I get my Java procedure working, I debug it using System.err.println statements, which write to the trace file. This can be very helpful for debugging your Java procedures.

Rollout and Security

If you want all procedures and packages to reside in a different schema (with only create package privilege, no table creation this is not a problem. The JDBC driver connects to the database using the 'default' connection, which is the same schema as the PL/SQL user would connect to. So you have the same database privileges in Java that you do in PL/SQL.

The Java stored procedures reside in the schema that you loaded them into using loadjava. To migrate Java procedures from one machine or database to another, simply export the schema and import it accordingly.

Example

It's time for a Java stored procedure example. I wanted to be able to send AOL instant messages from the database. This could be used for a variety of operations, such as table-based triggers (i.e. insert, update, delete), monitoring database conditions (i.e. things a DBA might want to know), or for error logic (for PL/SQL or Java). I searched the Web and found the JavaTOC library at http://www.jeffheaton.com/im. TOC stands for Talk to OsCar, which is AOL's instant messaging API.

The following steps make this Java stored procedure work:

  1. Load the JavaTOC library into the database using the following statement:
  2. loadjava -thin -user user/pass JavaTOC.jar
    
  3. Write a Java stored procedure for the JavaTOC library
  4. Write the PL/SQL wrapper
  5. Test it all out

Java stored procedure that calls JavaTOC

After loading the JavaTOC library into the database, I needed to write a Java stored procedure that would make use of this library. I called this Java program AIM. You'll noticed that I included a number of debugging statements (System.err.println) in my code. I can view the trace file to see the results of these statements. This Java program includes three methods: login (to log into AIM), send (to send instant messages, once you're logged in) and logout (to log off of AIM). Here is my Java stored procedure, called AIM:

import java.io.*;
import java.util.*;
public class AIM {
  public    static Chatable    session;
  public    static JavaTOC toc = new JavaTOC(session);
  public static String login(
    String username, String password) {
    System.err.println("Before login");
    try {
      if (toc.login(username, password)) {
          System.err.println("Logged in successfully");
          return "OK";
      } else {
          System.err.println("NOT Logged in successfully");
          return "Not OK";
    }} catch (IOException e) {return "Not OK";}
  }
  public static String send(String to, String msg) {
    toc.send(to,msg);
    System.err.println("Sent message");
    return "OK";
  }
  public static String logout() {
    toc.logout();
    System.err.println("Logged out");
    return "OK";
  } 
} // end of class

PL/SQL Wrapper

Next I needed to write my PL/SQL wrapper for my Java stored procedure. As mentioned above, I feel this is a goofy step required for Java stored procedures - I think the loadjava step should automate the creation of these statements. Note that this wrapper calls each of the above methods (login, send and logout). Here is my PL/SQL wrapper, which I called AIM_API:

CREATE OR REPLACE PACKAGE AIM_API is
 FUNCTION login (username IN varchar2, password IN varchar2) RETURN VARCHAR2;
 FUNCTION send (who_to IN varchar2, msg IN varchar2) RETURN VARCHAR2;
 FUNCTION logout RETURN VARCHAR2;
 END;
/

CREATE OR REPLACE PACKAGE BODY AIM_TAPI AS

 FUNCTION login (username IN varchar2, password IN varchar2)
 RETURN VARCHAR2
 AS LANGUAGE JAVA
 NAME 'AIM.login(java.lang.String, java.lang.String) return java.lang.String';

 FUNCTION send (who_to IN varchar2, msg IN varchar2)
 RETURN VARCHAR2
 AS LANGUAGE JAVA
 NAME 'AIM.send(java.lang.String, java.lang.String) return java.lang.String';

 FUNCTION logout
 RETURN VARCHAR2
 AS LANGUAGE JAVA
 NAME 'AIM.logout() return java.lang.String';
END;
/

Test it Out

It's now time to test out the Java stored procedure. I could create a table-based trigger or a database event containing the desired statements. Note that each of the Java methods I created are functions (they return a string), so I've used DBMS_OUTPUT to display the results of each function. For testing purposes, I simply executed the following statements in SQL*Plus:

Begin
dbms_output.put_line(aim_api.login('brownb6483','password'));
dbms_output.put_line(aim_api.send('my_dba','How are you?'));
dbms_output.put_line(aim_api.logout);
end;

When I view DBMS_OUTPUT, I see the following text:

OK
OK
OK

My Java stored procedure worked. When I looked into my trace file directory, I noticed a new trace file, which contained the following statements:

C:oracleadminorcludumpORA03656.TRC
*** 2002-06-25 10:40:26.000
*** SESSION ID:(7.48) 2002-06-25 10:40:25.000
Before login
Logged in successfully
Sent message
Logged out

The real proof is in the instant messenger window, which looked like this:

Implementing the Java stored procedure

Once testing is complete, you can remove your debugging code (i.e. the System.err.println statements - you may also want to do something with the status rather than simply ignoring it) and it's time to write your triggers, procedures, etc,. making use of your new Java stored procedure. For example, to create a database trigger that sends an instant message any time a record is inserted into the queries table, you might use the following code:

CREATE OR REPLACE TRIGGER QUERIES_INSERT
after insert ON queries
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
  status varchar2(2000);
begin
  status := aim_api.login('brownb6483','password');
  status := aim_api.send('BradleyDBrown',
                         'New Record Added to Queries Table');
  status := aim_api.send('BradleyDBrown',
                         'Source Query> ' || :new.source_query);
  status := aim_api.send('BradleyDBrown',
                         'Passed Query>' || :new.passed_query);
  status := aim_api.logout;
end;

When I added a record to my QUERIES table, I received the following instant message:

Conclusion

Java stored procedures provide you with power and flexibility that you wouldn't otherwise have with PL/SQL. This is cool stuff, isn't it?!

About the Author

Bradley D. Brown is chairman of the board, chief architect and co-founder of TUSC, a leading Oracle consultancy. His most recent book is Oracle9i Web Development.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, relational model, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in August 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.