Publishing Stored Procedures

Writing Object Type Call Specs

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

Like 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 and/or call specs. (For details, see the PL/SQL User's Guide and Reference.)

If an object type spec declares only attributes and/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 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.

Declaring Attributes

In an object type spec, all attributes must be declared before any methods. At least one attribute is required (the maximum is 1000). Methods are optional.

Like a Java variable, an attribute is declared with a name and datatype. The name must be unique within the object type but can be reused in other object types. The datatype can be any SQL type except LONG, LONG RAW, NCHAR, NVARCHAR2, NCLOB, ROWID, or UROWID.

You cannot initialize an attribute in its declaration using the assignment operator or DEFAULT clause. Furthermore, you cannot impose the NOT NULL constraint on an attribute. However, objects can be stored in database tables on which you can impose constraints.

Declaring Methods

MEMBER methods accept a built-in parameter named SELF, which is an instance of the object type. Whether declared implicitly or explicitly, it is always the first parameter passed to a MEMBER method. In the method body, SELF denotes the object whose method was invoked. MEMBER methods are invoked on instances, as follows:

instance_expression.method()

However, STATIC methods, which cannot accept or reference SELF, are invoked on the object type, not its instances, as follows:

object_type_name.method()

If you want to call a non-static Java method, you specify the keyword MEMBER in its call spec. Likewise, if you want to call a static Java method, you specify the keyword STATIC in its call spec.

Map and Order Methods

The values of a SQL scalar datatype such as CHAR have a predefined order, which allows them to be compared. However, instances of an object type have no predefined order. To put them in order, SQL calls a user-defined map method.

SQL uses the ordering to evaluate Boolean expressions such as x > y and to make comparisons implied by the DISTINCT, GROUP BY, and ORDER BY clauses. A map method returns the relative position of an object in the ordering of all such objects. An object type can contain only one map method, which must be a parameterless function with one of the following return types: DATE, NUMBER, or VARCHAR2.

Alternatively, you can supply SQL with an order method, which compares two objects. Every order method takes just two parameters: the built-in parameter SELF and another object of the same type. If o1 and o2 are objects, a comparison such as o1 > o2 calls the order method automatically. The method returns a negative number, zero, or a positive number signifying that SELF is respectively less than, equal to, or greater than the other parameter. An object type can contain only one order method, which must be a function that returns a numeric result.

You can declare a map method or an order method but not both. If you declare either method, you can compare objects in SQL and PL/SQL. However, if you declare neither method, you can compare objects only in SQL and solely for equality or inequality. (Two objects of the same type are equal if the values of their corresponding attributes are equal.)

Constructor Methods

Every object type has a constructor method (constructor for short), which is a system-defined function with the same name as the object type. The constructor initializes and returns an instance of that object type.

Oracle generates a default constructor for every object type. The formal parameters of the constructor match the attributes of the object type. That is, the parameters and attributes are declared in the same order and have the same names and datatypes. SQL never calls a constructor implicitly, so you must call it explicitly. Constructor calls are allowed wherever function calls are allowed.

Note:
To invoke a Java constructor from SQL, you must wrap calls to it in a static method and declare the corresponding call spec as a STATIC member of the object type.

Examples

In this section, each example builds on the previous one. To begin, you create two SQL object types to represent departments and employees. First, you write the spec for object type Department as shown below. The body is unnecessary because the spec declares only attributes.

CREATE TYPE Department AS OBJECT (
  deptno NUMBER(2),
  dname  VARCHAR2(14),
  loc    VARCHAR2(13)
);

Then, you create object type Employee as shown below. Its last attribute, deptno, stores a handle, called a ref, to objects of type Department. A ref indicates the location of an object in an object table, which is a database table that stores instances of an object type. The ref does not point to a specific instance copy in memory. To declare a ref, you specify the datatype REF and the object type that the ref targets.

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
);

Next, as shown below, you create SQL object tables to hold objects of type Department and Employee. First, you create object table depts, which will hold objects of type Department. You populate the object table by selecting data from the relational table dept and passing it to a constructor, which is a system-defined function with the same name as the object type. You use the constructor to initialize and return an instance of that object type.

CREATE TABLE depts OF Department AS
  SELECT Department(deptno, dname, loc) FROM dept;

Then, as shown below, you create the object table emps, which will hold objects of type Employee. The last column in object table emps, which corresponds to the last attribute of object type Employee, holds references to objects of type Department. To fetch the references into that column, you use the operator REF, which takes as its argument a table alias associated with a row in an object table.

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;

Selecting a ref returns a handle to an object; it does not materialize the object itself. To do that, you can use methods in class oracle.sql.REF, which supports Oracle object references. This class, which is a subclass of oracle.sql.Datum, extends the standard JDBC interface oracle.jdbc2.Ref. For more information, see the Oracle8i JDBC Developer's Guide and Reference.

Using Class oracle.sql.STRUCT

To continue, you write a Java stored procedure, as shown below. The class Paymaster has one method, which computes an employee's wages. The method getAttributes() defined in class oracle.sql.STRUCT uses the default JDBC mappings for the attribute types. So, for example, NUMBER maps to BigDecimal.

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
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;
  }
}

Because the method wages returns a value, you write a function call spec for it, as follows:

CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS
  LANGUAGE JAVA
  NAME 'Paymaster.wages(oracle.sql.STRUCT) return BigDecimal';

This is a top-level call spec because it is not defined inside a package or object type.

Implementing the SQLData Interface

To make access to object attribute values more natural, you can create a Java class for the object that implements the SQLData interface. For details, see the Oracle8i JDBC Developer's Guide and Reference. If you choose to create a Java class that implements SQLData, you must provide the methods readSQL() and writeSQL() as defined by the SQLData interface. The JDBC driver calls method readSQL() to read a stream of database values and populate an instance of your Java class. In the following example, you revise class Paymaster, adding a second method named raiseSal():

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
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);
  }
}

You must revise the call spec for method wages, as follows, because its parameter has changed from oralce.sql.STRUCT to Paymaster:

CREATE OR REPLACE FUNCTION wages (e Employee) RETURN NUMBER AS
  LANGUAGE JAVA
  NAME 'Paymaster.wages(Paymaster) return BigDecimal';

Because the new method raiseSal is void, you write a procedure call spec for it, as follows:

CREATE OR REPLACE PROCEDURE raise_sal (e IN OUT Employee, r NUMBER)
  AS LANGUAGE JAVA 
  NAME 'Paymaster.raiseSal(Paymaster[], java.math.BigDecimal)';

Again, this is a top-level call spec.

Implementing Object Type Methods

Later, you decide to drop the top-level call specs wages and raise_sal and redeclare them as methods of object type Employee, as shown below. In an object type spec, all methods must be declared after the attributes. The object type body is unnecessary because the spec declares only attributes and call specs.

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)'
);

Then, you revise class Paymaster accordingly, as shown below. You need not pass an array to method raiseSal because the SQL parameter SELF corresponds directly to the Java parameter this--even when SELF is declared as IN OUT (the default for procedures).

import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
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);
  }
}