6 Publishing Java Classes With Call Specs

6.1 Understanding Call Specs

To publish Java methods, you write call specs. For a given Java method, you declare a function or procedure call spec using the SQL CREATE FUNCTION or CREATE PROCEDURE statement. Inside a PL/SQL package or SQL object type, you use similar declarations.

You publish value-returning Java methods as functions or procedures and void Java methods as procedures. The function or procedure body contains the LANGUAGE JAVA clause. This clause records information about the Java method including its full name, its parameter types, and its return type. Mismatches are detected only at run time.

As Figure 6-1 shows, applications call the Java method through its call spec, that is, by referencing the call-spec name. The run-time system looks up the call-spec definition in the Oracle data dictionary, then executes the corresponding Java method.

Figure 6-1 Calling a Java Method

As an alternative, use the native Java interface to directly invoke Java in the database from a Java client. See "Native Java Interface" for more information.

6.2 Defining Call Specs: Basic Requirements

A call spec and the Java method it publishes must reside in the same schema (unless the Java method has a PUBLIC synonym). You can declare the call spec as a:

• standalone (top-level) PL/SQL function or procedure

• packaged PL/SQL function or procedure

• member method of a SQL object type

A call spec exposes a Java method's top-level entry point to Oracle. Therefore, you can publish only public static methods—with one exception. You can publish instance methods as member methods of a SQL object type.

Packaged call specs perform as well as top-level call specs. So, to ease maintenance, you might want to place call specs in a package body. That way, you can modify them without invalidating other schema objects. Also, you can overload them.

Connection conn =

  DriverManager.getConnection("jdbc:default:connection:");

String sql = "DROP " + object_type + " " + object_name;

Statement stmt = conn.createStatement();

stmt.executeUpdate(sql);

String sql = "DELETE FROM dept WHERE deptno = ?";

PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, deptID);

pstmt.executeUpdate();

String sql = "SELECT COUNT(*) FROM " + tabName;

int rows = 0;

Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery(sql);

while (rset.next()) {rows = rset.getInt(1);}

// parameterless stored procedure

CallableStatement cstmt = conn.prepareCall("{CALL proc}");


// stored procedure

CallableStatement cstmt = conn.prepareCall("{CALL proc(?,?)}");


// stored function

CallableStatement cstmt = conn.prepareCall("{? = CALL func(?,?)}");

6.3 Writing Top-Level Call Specs

In SQL*Plus, you can define top-level call specs interactively using the following syntax:

CREATE [OR REPLACE]

{  PROCEDURE procedure_name [(param[, param]...)]

 | FUNCTION function_name [(param[, param]...)] RETURN sql_type}

[AUTHID {DEFINER | CURRENT_USER}]

[PARALLEL_ENABLE]

[DETERMINISTIC]

{IS | AS} LANGUAGE JAVA

NAME 'method_fullname (java_type_fullname[, java_type_fullname]...)

  [return java_type_fullname]';

where param stands for the following syntax:

parameter_name [IN | OUT | IN OUT] sql_type

The AUTHID clause determines whether a stored procedure executes with the privileges of its definer or invoker (the default) and whether its unqualified references to schema objects are resolved in the schema of the definer or invoker. You can override the default behavior by specifying DEFINER. (However, you cannot override the loadjava option -definer by specifying CURRENT_USER.)

The PARALLEL_ENABLE option declares that a stored function can be used safely in the slave sessions of parallel DML evaluations. The state of a main (logon) session is never shared with slave sessions. Each slave session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

The hint DETERMINISTIC helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled. For more information, see the statements CREATE INDEX and CREATE MATERIALIZED VIEW in the Oracle Database SQL Reference.

The NAME-clause string uniquely identifies the Java method. The Java full names and the call spec parameters, which are mapped by position, must correspond one to one. (This rule does not apply to method main. See Example 2.) If the Java method takes no arguments, code an empty parameter list for it but not for the function or procedure.

As usual, you write Java full names using dot notation. The following example shows that long names can be broken across lines at dot boundaries:

artificialIntelligence.neuralNetworks.patternClassification.

  RadarSignatureClassifier.computeRange()

Example 1

Assume that the executable for the following Java class has been loaded into the Oracle database:

import java.sql.*;

import java.io.*;

import oracle.jdbc.*;

 

public class GenericDrop {

  public static void dropIt (String object_type, String object_name)

  throws SQLException {

    // Connect to Oracle using JDBC driver

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    // Build SQL statement

    String sql = "DROP " + object_type + " " + object_name;

    try {

      Statement stmt = conn.createStatement();

      stmt.executeUpdate(sql);

      stmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

}

Class GenericDrop has one method named dropIt, which drops any kind of schema object. For example, if you pass the arguments 'table' and 'emp' to dropIt, the method drops database table emp from your schema. Let's write a call spec for this method.

CREATE OR REPLACE PROCEDURE drop_it (

  obj_type VARCHAR2,

  obj_name VARCHAR2)

AS LANGUAGE JAVA

NAME 'GenericDrop.dropIt(java.lang.String, java.lang.String)';

Notice that you must fully qualify the reference to class String. Package java.lang is automatically available to Java programs but must be named explicitly in call specs.

Example 2

As a rule, Java names and call spec parameters must correspond one to one. However, that rule does not apply to method main. Its String[] parameter can be mapped to multiple CHAR or VARCHAR2 call spec parameters. Suppose you want to publish the following method main, which prints its arguments:

public class EchoInput {

  public static void main (String[] args) {

    for (int i = 0; i < args.length; i++)

      System.out.println(args[i]);

  }

}

To publish method main, you might write the following call spec:

CREATE OR REPLACE PROCEDURE echo_input (

  s1 VARCHAR2,

  s2 VARCHAR2,

  s3 VARCHAR2)

AS LANGUAGE JAVA

NAME 'EchoInput.main(java.lang.String[])';

You cannot impose constraints (such as precision, size, or NOT NULL) on call spec parameters. So, you cannot specify a maximum size for the VARCHAR2 parameters, even though you must do so for VARCHAR2 variables, as in:

DECLARE

  last_name VARCHAR2(20);  -- size constraint required

Example 3

Next, you publish Java method rowCount, which returns the number of rows in a given database table.

import java.sql.*;

import java.io.*;

import oracle.jdbc.*;


public class RowCounter {

  public static int rowCount (String tabName) throws SQLException {

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "SELECT COUNT(*) FROM " + tabName;

    int rows = 0;

    try {

      Statement stmt = conn.createStatement();

      ResultSet rset = stmt.executeQuery(sql);

      while (rset.next()) {rows = rset.getInt(1);}

      rset.close();

      stmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

    return rows;

  }

}

In the following call spec, the return type is NUMBER, not INTEGER, because NUMBER subtypes (such as INTEGER, REAL, and POSITIVE) are not allowed in a call spec:

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER

AS LANGUAGE JAVA

NAME 'RowCounter.rowCount(java.lang.String) return int';

Example 4

Suppose you want to publish the following Java method named swap, which switches the values of its arguments:

public class Swapper {

  public static void swap (int[] x, int[] y) {

    int hold = x[0];

    x[0] = y[0];

    y[0] = hold;

  }

}

The call spec publishes Java method swap as call spec swap. The call spec declares IN OUT formal parameters because values must be passed in and out. All call spec OUT and IN OUT parameters must map to Java array parameters.

CREATE PROCEDURE swap (x IN OUT NUMBER, y IN OUT NUMBER)

AS LANGUAGE JAVA

NAME 'Swapper.swap(int[], int[])';

Notice that a Java method and its call spec can have the same name.

6.4 Writing Packaged Call Specs

A PL/SQL package is a schema object that groups logically related types, items, and subprograms. Usually, packages have two parts, a specification (spec) and a body (sometimes the body is unnecessary). The spec is the interface to your applications: it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, thereby implementing the spec. (For details, see the PL/SQL User's Guide and Reference.)

In SQL*Plus, you can define PL/SQL packages interactively using this syntax:

CREATE [OR REPLACE] PACKAGE package_name

  [AUTHID {CURRENT_USER | DEFINER}] {IS | AS}

  [type_definition [type_definition] ...]

  [cursor_spec [cursor_spec] ...]

  [item_declaration [item_declaration] ...]

  [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]

END [package_name];


[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

  [type_definition [type_definition] ...]

  [cursor_body [cursor_body] ...]

  [item_declaration [item_declaration] ...]

  [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]

[BEGIN

  sequence_of_statements]

END [package_name];]

The spec holds public declarations, which are visible to your application. The body contains implementation details and private declarations, which are hidden from your application. Following the declarative part of the package body is the optional initialization part, which typically holds statements that initialize package variables. It is run only once, the first time you reference the package.

A call spec declared in a package spec cannot have the same signature (name and parameter list) as a subprogram in the package body. If you declare all the subprograms in a package spec as call specs, the package body is unnecessary (unless you want to define a cursor or use the initialization part).

The AUTHID clause determines whether all the packaged subprograms execute with the privileges of their definer (the default) or invoker, and whether their unqualified references to schema objects are resolved in the schema of the definer or invoker.

An Example

Consider the Java class DeptManager, which has methods for adding a new department, dropping a department, and changing the location of a department. Notice that method addDept uses a database sequence to get the next department number. The three methods are logically related, so you might want to group their call specs in a PL/SQL package.

import java.sql.*;

import java.io.*;

import oracle.jdbc.*;


public class DeptManager {

  public static void addDept (String deptName, String deptLoc)

  throws SQLException {

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "SELECT deptnos.NEXTVAL FROM dual";

    String sql2 = "INSERT INTO dept VALUES (?, ?, ?)";

    int deptID = 0;

    try {

      PreparedStatement pstmt = conn.prepareStatement(sql);

      ResultSet rset = pstmt.executeQuery();

      while (rset.next()) {deptID = rset.getInt(1);}

      pstmt = conn.prepareStatement(sql2);

      pstmt.setInt(1, deptID);

      pstmt.setString(2, deptName);

      pstmt.setString(3, deptLoc);

      pstmt.executeUpdate(); 

      rset.close();

      pstmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

 

  public static void dropDept (int deptID) throws SQLException {

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "DELETE FROM dept WHERE deptno = ?";

    try { 

      PreparedStatement pstmt = conn.prepareStatement(sql);

      pstmt.setInt(1, deptID);

      pstmt.executeUpdate(); 

      pstmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

 

  public static void changeLoc (int deptID, String newLoc)

  throws SQLException {

    Connection conn =

      DriverManager.getConnection("jdbc:default:connection:");

    String sql = "UPDATE dept SET loc = ? WHERE deptno = ?";

    try { 

      PreparedStatement pstmt = conn.prepareStatement(sql);

      pstmt.setString(1, newLoc);

      pstmt.setInt(2, deptID);

      pstmt.executeUpdate(); 

      pstmt.close();

    } catch (SQLException e) {System.err.println(e.getMessage());}

  }

}

Suppose you want to package methods addDept, dropDept, and changeLoc. First, you create the package spec, as follows:

CREATE OR REPLACE PACKAGE dept_mgmt AS

  PROCEDURE add_dept (dept_name VARCHAR2, dept_loc VARCHAR2);

  PROCEDURE drop_dept (dept_id NUMBER);

  PROCEDURE change_loc (dept_id NUMBER, new_loc VARCHAR2);

END dept_mgmt;

Then, you create the package body by writing call specs for the Java methods:

CREATE OR REPLACE PACKAGE BODY dept_mgmt AS

  PROCEDURE add_dept (dept_name VARCHAR2, dept_loc VARCHAR2)

  AS LANGUAGE JAVA

  NAME 'DeptManager.addDept(java.lang.String, java.lang.String)';

 

  PROCEDURE drop_dept (dept_id NUMBER) 

  AS LANGUAGE JAVA

  NAME 'DeptManager.dropDept(int)';

 

  PROCEDURE change_loc (dept_id NUMBER, new_loc VARCHAR2)

  AS LANGUAGE JAVA

  NAME 'DeptManager.changeLoc(int, java.lang.String)';

END dept_mgmt;

To reference the stored procedures in the package dept_mgmt, you must use dot notation, as the following example shows:

CALL dept_mgmt.add_dept('PUBLICITY', 'DALLAS');

6.5 Writing Object Type Call Specs

In SQL, object-oriented programming is based on object types, which are user-defined composite data types that encapsulate a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are known as attributes. The functions and procedures that characterize the behavior of the object type are known as methods, which can be written in Java.

As with a package, an object type has two parts: a specification (spec) and a body. The spec is the interface to your applications; it declares a data structure (set of attributes) along with the operations (methods) needed to manipulate the data. The body implements the spec by defining PL/SQL subprogram bodies or call specs. (For details, see the PL/SQL User's Guide and Reference.)

If an object type spec declares only attributes or call specs, then the object type body is unnecessary. (You cannot declare attributes in the body.) So, if you implement all your methods in Java, you can place their call specs in the object type spec and omit the body.

In SQL*Plus, you can define SQL object types interactively using this syntax:

CREATE [OR REPLACE] TYPE type_name 

  [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} OBJECT (

  attribute_name datatype[, attribute_name datatype]...

  [{MAP | ORDER} MEMBER {function_spec | call_spec},]

  [{MEMBER | STATIC} {subprogram_spec | call_spec} 

  [, {MEMBER | STATIC} {subprogram_spec | call_spec}]...]

);


[CREATE [OR REPLACE] TYPE BODY type_name {IS | AS}

  { {MAP | ORDER} MEMBER function_body;

   | {MEMBER | STATIC} {subprogram_body | call_spec};} 

  [{MEMBER | STATIC} {subprogram_body | call_spec};]...

END;]

The AUTHID clause determines whether all member methods execute with the current user privileges—which determines invoker's or definer's rights.

instance_expression.method()

object_type_name.method()

CREATE TYPE Department AS OBJECT (

  deptno NUMBER(2),

  dname  VARCHAR2(14),

  loc    VARCHAR2(13)

);

CREATE TYPE Employee AS OBJECT (

  empno    NUMBER(4),

  ename    VARCHAR2(10),

  job      VARCHAR2(9),

  mgr      NUMBER(4),

  hiredate DATE,

  sal      NUMBER(7,2),

  comm     NUMBER(7,2),

  deptno   REF Department

);

CREATE TABLE depts OF Department AS

  SELECT Department(deptno, dname, loc) FROM dept;

CREATE TABLE emps OF Employee AS

  SELECT Employee(e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal,

    e.comm, (SELECT REF(d) FROM depts d WHERE d.deptno = e.deptno))

    FROM emp e;

import java.sql.*;

import java.io.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.oracore.*;

import oracle.jdbc2.*;

import java.math.*;


public class Paymaster {

  public static BigDecimal wages(STRUCT e)

    throws java.sql.SQLException {

    // Get the attributes of the Employee object.

    Object[] attribs = e.getAttributes();

    // Must use numeric indexes into the array of attributes.

    BigDecimal sal = (BigDecimal)(attribs[5]);  // [5] = sal

    BigDecimal comm = (BigDecimal)(attribs[6]); // [6] = comm

    BigDecimal pay = sal;

    if (comm != null) pay = pay.add(comm);

    return pay;

  }

}

CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS

  LANGUAGE JAVA

  NAME 'Paymaster.wages(oracle.sql.STRUCT) return BigDecimal';

import java.sql.*;

import java.io.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.oracore.*;

import oracle.jdbc2.*;

import java.math.*;


public class Paymaster implements SQLData {

  // Implement the attributes and operations for this type.

  private BigDecimal empno;

  private String ename;

  private String job;

  private BigDecimal mgr;

  private Date hiredate;

  private BigDecimal sal;

  private BigDecimal comm;

  private Ref dept;

  

  public static BigDecimal wages(Paymaster e) {

    BigDecimal pay = e.sal;

    if (e.comm != null) pay = pay.add(e.comm);

    return pay;

  }


  public static void raiseSal(Paymaster[] e, BigDecimal amount) {

    e[0].sal =               // IN OUT passes [0]

      e[0].sal.add(amount);  // increase salary by given amount

  }


  // Implement SQLData interface.


  private String sql_type;

  

  public String getSQLTypeName() throws SQLException {

    return sql_type;

  }

 

  public void readSQL(SQLInput stream, String typeName)

    throws SQLException {

    sql_type = typeName;

    empno = stream.readBigDecimal();

    ename = stream.readString();

    job = stream.readString();

    mgr = stream.readBigDecimal();

    hiredate = stream.readDate();

    sal = stream.readBigDecimal();

    comm = stream.readBigDecimal();

    dept = stream.readRef();

  }

 

  public void writeSQL(SQLOutput stream) throws SQLException {

    stream.writeBigDecimal(empno);

    stream.writeString(ename);

    stream.writeString(job);

    stream.writeBigDecimal(mgr);

    stream.writeDate(hiredate);

    stream.writeBigDecimal(sal);

    stream.writeBigDecimal(comm);

    stream.writeRef(dept);

  }

}

CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS

  LANGUAGE JAVA

  NAME 'Paymaster.wages(Paymaster) return BigDecimal';

CREATE OR REPLACE PROCEDURE raise_sal (e IN OUT Employee, r NUMBER)

  AS LANGUAGE JAVA 

  NAME 'Paymaster.raiseSal(Paymaster[], java.math.BigDecimal)';

CREATE TYPE Employee AS OBJECT (

  empno    NUMBER(4),

  ename    VARCHAR2(10),

  job      VARCHAR2(9),

  mgr      NUMBER(4),

  hiredate DATE,

  sal      NUMBER(7,2),

  comm     NUMBER(7,2),

  deptno   REF Department

  MEMBER FUNCTION wages RETURN NUMBER

    AS LANGUAGE JAVA

    NAME 'Paymaster.wages() return java.math.BigDecimal',

  MEMBER PROCEDURE raise_sal (r NUMBER)

    AS LANGUAGE JAVA 

    NAME 'Paymaster.raiseSal(java.math.BigDecimal)'

);

import java.sql.*;

import java.io.*;

import oracle.sql.*;

import oracle.jdbc.*;

import oracle.oracore.*;

import oracle.jdbc2.*;

import java.math.*;


public class Paymaster implements SQLData {

  // Implement the attributes and operations for this type.

  private BigDecimal empno;

  private String ename;

  private String job;

  private BigDecimal mgr;

  private Date hiredate;

  private BigDecimal sal;

  private BigDecimal comm;

  private Ref dept;

  

  public BigDecimal wages() {

    BigDecimal pay = sal;

    if (comm != null) pay = pay.add(comm);

    return pay;

  }


  public void raiseSal(BigDecimal amount) {

    // For SELF/this, even when IN OUT, no array is needed.

    sal = sal.add(amount);

  }


  // Implement SQLData interface.


  String sql_type;

  

  public String getSQLTypeName() throws SQLException { 

    return sql_type; 

  }

 

  public void readSQL(SQLInput stream, String typeName)

    throws SQLException {

    sql_type = typeName;

    empno = stream.readBigDecimal();

    ename = stream.readString();

    job = stream.readString();

    mgr = stream.readBigDecimal();

    hiredate = stream.readDate();

    sal = stream.readBigDecimal();

    comm = stream.readBigDecimal();

    dept = stream.readRef();

  }

 

  public void writeSQL(SQLOutput stream) throws SQLException {

    stream.writeBigDecimal(empno);

    stream.writeString(ename);

    stream.writeString(job);

    stream.writeBigDecimal(mgr);

    stream.writeDate(hiredate);

    stream.writeBigDecimal(sal);

    stream.writeBigDecimal(comm);

    stream.writeRef(dept);

  }

}