Java Stored Procedures (JSProcs)

With the introduction of the Oracle8 i Database, Oracle allows developers to create stored procedures using Java in addition to the native PL/SQL procedural language. Because Java is not proprietary to Oracle, and enjoys a high level of acceptance within the development community, the support for Java stored procedure development allows developers to take advantage of the power of the Oracle stored procedures without having to learn PL/SQL. In addition, Java is a better choice than PL/SQL for many types of tasks , such as intensive computations . Java s object-oriented architecture, combined with all of the standard libraries included in the Java Run-time Environment (JRE), makes Java a powerful application development tool.

For Java Stored Procedures (JSProcs) that access data in the database, create a Connection object using the Server-Side Internal driver where the driver type is kprb. There are two key differences between standalone JDBC applications and Java Stored procedures. In JSProcs, you do not close the Connection object, and you do not provide a main() method.

Note  

Do not confuse JSProcs with JSPs, which are JavaServer Pages, introduced later in this chapter and covered in detail in Chapter 14.

The following steps detail how to create and execute a JSProc within the database:

• Write the Java program

• Compile the Java program

• Load the class file into the Oracle database

• Create the PL/SQL call specification if client programs can execute the stored procedure or function

• Execute the JSProc

Write the Java Program

The first step is to create the Java program that will become a JSProc. For example, a JSProc accepts two parameters: a key value to the EMPLOYEES table, and a percentage to raise the employee s salary. The function updates the record and returns the new salary.

Prior to calling the function, the table contains the following data:

 SQL> SELECT * FROM employees; EMPLOYEE_ID     SALARY -            1      50000           2     100000 

Create a new Employees class with the static raiseSalary() method. Note that you must declare methods that become Java Stored Procedures as static.

 import java.sql.*; import oracle.jdbc.pool.*; public class Employees {    public static double raiseSalary(long employeeID,double raisePct) {       OracleDataSource  ods;       Connection        conn = null;       PreparedStatement stmt = null;       ResultSet         rs = null;       double            newSalary = 0.0;       try {          ods = new OracleDataSource();          ods.setDriverType("kprb");          conn = ods.getConnection();          stmt = conn.prepareStatement("UPDATE employees SET salary = " +               "(salary + (salary * (?/100.0))) WHERE employee_id = ?");          stmt.setDouble(1,raisePct);          stmt.setLong(2,employeeID);          stmt.executeUpdate();          stmt.close();          stmt = conn.prepareStatement("SELECT salary FROM employees WHERE employee_id = ?");          stmt.setLong(1,employeeID);          rs = stmt.executeQuery();          if (rs.next()) newSalary = rs.getDouble(1);          conn.commit();       } catch (SQLException e) {          System.err.println(e.getMessage());       } finally {          try {             if (rs != null) rs.close();             if (stmt != null) stmt.close();          } catch (SQLException e) {             System.err.println(e.getMessage());          }       }       return newSalary;    } } 

The try/catch/finally section of the code above is one of the coolest features of the Java language. For those programmers not familiar with it, it is a way to handle exceptional situations that happen at run time. This way, a programmer who knows an exceptional situation might happen during the execution of a piece of code can prepare for (and often recover from) it. It s important to note that the Java compiler doesn t care how the programmer handles the exception; it only cares that there is a way of handling it. In Java, the developer can raise specific exceptions that must be handled by the calling method; you can t do this in PL/SQL.

Compile the Java Program

JSProc are class files, just like other compiled Java applications. You can compile the JSProc source using the Java SDK with an IDE such as JDeveloper or at a command line prompt, or you can let the Oracle database compile the program. In general, it is easier to compile and test your Java program outside of the database, and then load the class file into the database.

Using CREATE JAVA SOURCE

You can execute the CREATE JAVA SOURCE SQL statement to compile and load the Java program within the database using SQL*Plus. Note the use of double quotation marks to maintain the case sensitivity of the class file.

 CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Employees" AS import java.sql.*; import oracle.jdbc.pool.*; public class Employees {    ... } / 

Load the Class File into the Database

If you choose to compile and test your Java program outside of the database, load the source, class, and resource files into the database using either the loadjava utility or a CREATE JAVA SQL statement.

Using loadjava

The loadjava utility uploads Java source, class, and resource files into the database, storing the objects as Java schema objects in a system-generated database table. The utility then executes CREATE JAVA [SOURCERESOURCECLASS] SQL statements to load the files into the database. Note that you do not need to upload the source code, but the Oracle database can automatically recompile dependent objects if the source is available in a schema object.

The following example loads the Employees.class file into the SCOTT schema within the database:

 prompt>loadjava user scott/tiger@oski:1521:orcl f r v Employees.class arguments: