Quote from the article:
Beginning with version 8i, the Oracle database includes a fully functional Java Virtual Machine, Oracle JVM. Out of this close relationship between Oracle and Java emerges an important technology for Oracle developers: Java stored procedures. With Java stored procedures, developers have the ability to harness the power of Java when building database applications. This article provides an overview of this increasingly popular technology. Its intended audience is Java developers new to Oracle, and Oracle PL/SQL developers with a basic understanding of Java. After highlighting the benefits of Java stored procedures, I will demonstrate how to create and use them in your applications.
Benefits of Java Stored Procedures
There are a number of scenarios where it makes sense to use Java stored procedures. Given Java's popularity today, it is certainly possible that members of a development team are more proficient in Java than PL/SQL. Java stored procedures give Java programmers the ability to code in their preferred language. For experienced PL/SQL developers, using Java allows you to take advantage of the Java language to extend the functionality of database applications. Also, Java makes it possible to write database-neutral code. Better yet, it allows you to reuse existing code and dramatically increase productivity.
As you'll see, PL/SQL and Java can coexist quite nicely in the same application so there is no need to pick one or the other. PL/SQL is an excellent procedural language, highly optimized to work with an Oracle database. Java applications that execute in the Oracle database are also highly scalable. In addition, Java executed by the Oracle JVM benefits from efficient garbage collection and the server's thread management capabilities.
Java Stored Procedures, Step by Step
In a nutshell, Java stored procedures are Java classes, stored as Oracle schema objects, made accessible to Oracle SQL and PL/SQL through call specifications. Call specifications, as we will see, are simply PL/SQL declarations that 'wrap' methods of Java stored in the database. There are four necessary steps when developing a Java stored procedure. We will consider each of these steps now.
#1. Writing the Java Class
The beauty of this first step is that it really has little to do with the Oracle database. You simply develop your Java classes using your favorite IDE, such as Oracle's JDeveloper. Java methods must be public and they must be static if they are to be used as stored procedures.
You can write, compile, and even unit test your Java code before moving it into the Oracle database. In fact, for all but trivial applications, this is the preferred method because it will allow you to take advantage of your IDE's features, such as debugging and code generation. If you would prefer to compile your Java classes with Oracle's JVM, the loadjava utility, discussed later, can do this for you.
The following listing displays a simple Java class called EmpManager. For now, it contains a single method to insert an emp record into the database.
There is nothing out of the ordinary here. Well, almost nothing. In this method, the database connection URL is "jdbc:default:connection:". When writing Java that will execute inside the Oracle database, you can take advantage of a special server-side JDBC driver. This driver uses the user's default connection and provides the fastest access to the database.
import java.sql.*;
import oracle.jdbc.*;
public class EmpManager {
//Add an employee to the database.
public static void addEmp(int emp_id, String emp_f_name,
String emp_l_name,float emp_salary, int dept_id) {
System.out.println("Creating new employee...");
try {
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
String sql =
"INSERT INTO emp " +
"(emp_id,emp_f_name,emp_l_name,emp_salary,dept_id) " +
"VALUES(?,?,?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,emp_id);
pstmt.setString(2,emp_f_name);
pstmt.setString(3,emp_l_name);
pstmt.setFloat(4,emp_salary);
pstmt.setInt(5,dept_id);
pstmt.executeUpdate();
pstmt.close();
}
catch(SQLException e) {
System.err.println("ERROR! Adding Employee: "
+ e.getMessage());
}
}
}
#2. Loading Classes
Our Java class is to become a full-fledged schema object, so it needs to be moved into the database. Oracle provides a command-line utility called loadjava to accomplish this task. The loadjava utility essentially provides an interface for SQL CREATE JAVA statements, which also may be used to migrate Java-related files to the database.
Because we've yet to compile EmpManager.java, we'll ask loadjava to do this as part of the loading process. This is achieved by specifying the -resolve attribute on the utility.
$ loadjava -u scott/tiger -v -resolve EmpManager.java
In addition to the -resolve attribute, the -v instructs the utility to include verbose feedback, and the -u specifies the database user and password. Because we asked loadjava to compile the source file, both the source and class files become members of the SCOTT schema.
We can verify the status of the compilation and load with a simple query against USER_OBJECTS. If done correctly, the status is 'VALID'.
SELECT object_name, object_type, status
FROM user_objects WHERE object_type LIKE 'JAVA%';
object_name object_type status
EmpManager JAVA CLASS VALID
EmpManager JAVA SOURCE VALID
Conversely, if compilation fails, errors can be examined through the USER_ERRORS view.
If you choose to compile with an IDE, simply load the resulting class file. Then, the source can reside in version control on the file system. The loadjava utility accepts files with the extensions .sqlj (a sqlj source file), .properties, .ser, .jar, and .zip. In the case of .jar and .zip files, Oracle automatically extracts and stores each member as individual schema objects.
Before we move on, there's one more critical component to the load process that merits discussion: the Oracle JVM resolver. Typically, a JVM uses a classpath to locate Java classes that your program depends upon to run correctly. When storing Java in the database, a resolver accomplishes this.
You can simply think of a resolver as Oracle's version of classpath. Oracle stores core Java classes in the PUBLIC schema. PUBLIC, as well as your own schema, is automatically included in a default resolver. However, if you need to reference a class from another schema, you must provide your own 'resolver spec'. This is done by adding the -resolver attribute. As an example, loadjava -u scott/tiger@test -resolve -resolver "((* SCOTT) (* PUBLIC) (* ADMIN))" specifies that the SCOTT schema, as well as PUBLIC and ADMIN, should be searched when resolving class dependencies.
#3. Publishing Classes
The third step in this process is to publish the Java class. Any class that will be directly called from SQL or PL/SQL must be published. A Java class is published by creating and compiling a call specification for it. The call specification, often referred to as a call spec or even a PL/SQL wrapper, maps a Java method's parameters and return type to Oracle SQL types. Here's the call specification for the addEmp method:
CREATE OR REPLACE PROCEDURE add_emp (emp_id NUMBER,emp_f_name VARCHAR2,
emp_l_name VARCHAR2, emp_salary NUMBER, dept_id NUMBER)
AS LANGUAGE JAVA
NAME 'EmpManager.addEmp(int, java.lang.String, java.lang.String,
float, int)';
/
The add_emp procedure provides an SQL interface to the Java EmpManager.addEmp method. The Java method must be fully specified, including package name if relevant. Also, when developing a call specification, Java objects such as String must be fully qualified.
As a rule of thumb, Java methods with no return value are wrapped as procedures and those with return values become functions. Consider a second Java method in EmpManager that obtains a count of employees for a given department:
//Get the total number of employees for a given department.
public static int getEmpCountByDept(int dept_id) {
Connection conn =
DriverManager.getConnection("jdbc:default:connection:");
String sql = "SELECT COUNT(1) FROM emp WHERE dept_id = ?";
int cnt = 0;
//Code here to add ResultSet value to cnt, trap SQLException, etc.
return cnt;
}
Its call specification specifies that it returns a NUMBER.
CREATE OR REPLACE FUNCTION get_emp_count_by_dept (dept_id NUMBER)
RETURN NUMBER AS LANGUAGE JAVA
NAME 'EmpManager.getEmpCountByDept(int) return int';
/
By default, as with standard PL/SQL procedures, these program units execute with INVOKER rights. In other words, they execute with the privileges of the current user. By adding the keywords AUTHID DEFINER, you allow others to execute with the privileges of the creator.
Once executed, call specifications join the other files in the database as members of the SCOTT schema.
#4. Calling the Procedures
We have developed, loaded, and published our Java classes. The final step is to execute them. By default, Java output is written to trace files. The DBMS_JAVA package, an Oracle-supplied package with utilities for managing server-side Java, has a method for redirecting output to SQL*Plus.
SQL> SET SERVEROUTPUT ON
SQL> CALL dbms_java.set_output(2000);
Now, Java output will displayed upon execution.
SQL> EXECUTE add_emp(1,'Joe', 'Smith',40000.00,1);
Creating new employee...
PL/SQL procedure successfully completed.
As you can see, from the caller's perspective, there is no discernable difference between calls made to Java stored procedures and calls to a PL/SQL procedure or function.
VARIABLE x NUMBER;
CALL get_emp_count_by_dept(1) INTO :x;
Getting Number of Employees for Dept...
Call completed.
PRINT x
X
----------
1
The SQLException class has the getErrorCode() and getErrorMessage() methods to help report errors. Any uncaught exception in a Java Stored Procedure results in an 'ORA-29532 Java call terminated by uncaught Java exception' for the caller. How you choose to handle errors will vary by application. The addEmp method simply catches and displays the error. We receive an error message when we attempt to add an emp record with an invalid dept_id.
SQL> execute add_emp(2,'Tom', 'Jackson', 45000.00,2);
Creating new employee...
ERROR! Adding Employee : ORA-02291: integrity constraint
(OPS$AK4353.FK_DEPT_ID) violated -
parent key not found
Because there is a need to call Java from PL/SQL, it is reasonable to assume that we will also need a way to call PL/SQL from Java code. This is very easy to achieve by using a CallableStatement object in our Java methods.
CallableStatement cstmt = conn.prepareCall("{my_plsql_proc}");
Thus, it is possible to create a seamless environment of PL/SQL procedures calling Java and vice versa.
A Usage Scenario
The better your understanding of Java stored procedures, the easier it will be to decide how they best fit your development practices. A common approach is to use PL/SQL when writing programs primarily concerned with database access. Then, as requirements arise that are more easily satisfied by Java, classes can be developed, followed by the necessary call specifications.
Perhaps, for instance, that a database application needs to interact with operating system files and directories. Oracle provides limited functionality with the UTL_FILE package for accessing system files. However, Java has a far richer set of File IO capabilities, allowing developers to remove files, add directories, and so on. So, why not leverage this power? The user of a command-line PL/SQL program might want to place job parameters in a configuration file. You could write a Java method to read these parameters.
public static String readFile (String usrFile) {
String fileStr = new String();
try {
File file = new File(usrFile);
FileReader fr = new FileReader(file);
LineNumberReader lnr = new LineNumberReader(fr);
...
...
}
catch(Exception e) {
...
}
return fileStr;
}
Then, a PL/SQL package will define call specifications for this and any other FILE IO method you choose to write.
CREATE OR REPLACE PACKAGE my_java_utils IS
FUNCTION read_file (file VARCHAR2) RETURN VARCHAR2;
END my_java_utils;
/
CREATE OR REPLACE PACKAGE BODY my_java_utils IS
FUNCTION read_file (file VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'MyJavaUtils.readFile(java.lang.String) return java.lang.String';
END my_java_utils;
/
A PL/SQL procedure can invoke the Java stored procedure read_file and use a file's data as input. Using this best of both worlds approach, developers can develop a robust database application. It should be noted that in this particular scenario, certain permissions may be required to access the file system. Developers should consult Oracle's Java security documentation for further information.
No comments:
Post a Comment