6
Objects, Collections, and OPAQUE Types

This chapter discusses how Oracle SQLJ supports user-defined SQL types--namely objects (and related object references) and collections (variable arrays and nested tables). This includes discussion of the Oracle JPublisher utility, which you can use to generate Java classes corresponding to user-defined SQL types.

There is also a small section at the end regarding Oracle OPAQUE types. These can be similar in functionality to object types, but with a different kind of implementation. Data is represented as an opaque payload of bytes rather than in structured object format.

The following topics are discussed:

Oracle Objects and Collections

This section provides some background conceptual information about Oracle9i objects and collections.

For additional conceptual and reference information about Oracle objects, references, and collections, refer to the Oracle9i SQL Reference and the Oracle9i Application Developer's Guide - Fundamentals.

For information about how to declare objects and collections, see "User-Defined Types".

Introduction to Objects and Collections

Oracle9i and Oracle SQLJ support user-defined SQL object types (composite data structures), related SQL object reference types, and user-defined SQL collection types. Oracle objects and collections are composite data structures consisting of individual data elements.

Oracle SQLJ supports either strongly typed or weakly typed Java representations of object types, reference types, and collection types to use in iterators or host expressions. Strongly typed representations use a custom Java class that maps to a particular object type, reference type, or collection type and must implement either the JDBC 2.0 standard java.sql.SQLData interface (for object types only) or the Oracle oracle.sql.ORAData interface. Either paradigm is supported by the Oracle9i JPublisher utility, which you can use to automatically generate custom Java classes. Weakly typed representations use the class oracle.sql.STRUCT (for objects), oracle.sql.REF (for object references), or oracle.sql.ARRAY (for collections). Or, alternatively, you can use standard java.sql.Struct, Ref, or Array objects in a weakly typed scenario.

The term "strongly typed" is used where a particular Java type is associated with a particular SQL named (user-defined) type. For example, if there is a PERSON type with a corresponding Person Java class.

The term "weakly typed" is used where a Java type is used in a generic way and can map to multiple SQL named types. The Java class (or interface) has no special information particular to any SQL type. This is the case for the oracle.sql.STRUCT, REF, and ARRAY types and the java.sql.Struct, Ref, and Array types.

Note that using Oracle extensions in your code requires the following:

Use one of the Oracle JDBC drivers.
Use default Oracle-specific code generation or, for ISO code generation, customize the profiles appropriately. (The default customizer, oracle.sqlj.runtime.util.OraCustomizer, is recommended.)

For Oracle-specific generated code, produced through the default -codegen=oracle translator setting, no profiles are produced so customization is not applicable. Oracle JDBC APIs are called directly through the generated Java code.
Use the Oracle SQLJ runtime when your application runs.

The Oracle SQLJ runtime and an Oracle JDBC driver are required whenever you use the Oracle customizer, even if you do not actually use Oracle extensions in your code.

For Oracle-specific semantics-checking, you must use an appropriate checker. The default checker, oracle.sqlj.checker.OracleChecker, acts as a front end and will run the appropriate checker based on your environment. This will be one of the Oracle-specific checkers if you are using an Oracle JDBC driver.

Oracle-specific types for Oracle objects and collections are included in the oracle.sql package.

For information about translator options relating to semantics-checking, see "Connection Options" and "Semantics-Checking and Offline-Parsing Options".

Custom Java Class Usage Notes

This chapter primarily discusses the use of custom Java classes with user-defined types; however, classes implementing ORAData can be used for other Oracle SQL types as well. A class implementing ORAData can be employed to perform any kind of desired processing or conversion in the course of transferring data between SQL and Java. See "Additional Uses for ORAData Implementations".
The SQLData interface is intended only for custom object classes. The ORAData interface can be used for any custom Java class.

Terminology Notes

User-defined SQL object types and user-defined SQL collection types are referred to as user-defined types (UDTs).
Custom Java classes for objects, references, and collections are referred to as custom object classes, custom reference classes, and custom collection classes, respectively.

For general information about Oracle object features and functionality, see the Oracle9i Application Developer's Guide - Object-Relational Features.

Oracle Object Fundamentals

Oracle objects (SQL objects) are composite data structures that group related data items, such as facts about each employee, into a single data unit. An object type is functionally similar to a Java class--you can populate and use any number of individual objects of a given object type, just as you can instantiate and use individual objects of a Java type.

For example, you can define an object type EMPLOYEE that has the attributes name (type CHAR), address (type CHAR), phonenumber (type CHAR), and employeenumber (type NUMBER).

Oracle objects can also have methods--stored procedures associated with the object type. These methods can be either static methods or instance methods and can be implemented either in PL/SQL or in Java. Their signatures can include any number of input, output, or input-output parameters. All this depends on how they are initially defined.

Oracle Collection Fundamentals

There are two categories of Oracle collections (SQL collections):

variable-length arrays (VARRAY types)
nested tables (TABLE types)

Both categories are one-dimensional, although the elements can be complex object types. VARRAY types are used for one-dimensional arrays; nested table types are used for single-column tables within an outer table. A variable of any VARRAY type can be referred to as a VARRAY; a variable of any nested table type can be referred to as a nested table.

A VARRAY, as with any array, is an ordered set of data elements, with each element having an index and all elements being of the same datatype. The size of a VARRAY refers to the maximum number of elements. Oracle VARRAYs, as indicated by their name, are of variable size, but the maximum size of any particular VARRAY type must be specified when the VARRAY type is declared.

A nested table is an unordered set of elements. Nested table elements within a table can themselves be queried in SQL. A nested table, as with any table, is not created with any particular number of rows--this is determined dynamically.

Notes:

The elements in a VARRAY or the rows in a nested table can be of a user-defined object type, and VARRAY and nested table types can be used for attributes in a user-defined object type. Oracle9i supports nesting of collection types. The elements of a VARRAY or rows of a nested table can be of another VARRAY or nested table type, or these elements can be of a user-defined object type that has VARRAY or nested table attributes.

Object and Collection Datatypes

User-specified object and collection definitions in Oracle9i function as SQL datatype definitions. You can then use these datatypes, as with any other datatype, in defining table columns, SQL object attributes, and stored procedure or function parameters. In addition, once you have defined an object type, the related object reference type can be used as any other SQL reference type.

Once you have defined EMPLOYEE as an Oracle object, as described in "Oracle Object Fundamentals", it becomes an Oracle datatype, and you can have a table column of type EMPLOYEE just as you can have a table column of type NUMBER. Each row in an EMPLOYEE column contains a complete EMPLOYEE object. You can also have a column type of REF EMPLOYEE, consisting of references to EMPLOYEE objects.

Similarly, you can define a variable-length array MYVARR as VARRAY(10) of NUMBER and a nested table NTBL of CHAR(20). The MYVARR and NTBL collection types become Oracle datatypes, and you can have table columns of either type. Each row of a MYVARR column consists of an array of up to ten numbers; each row of an NTBL column consists of 20 characters.

Custom Java Classes

The purpose of custom Java classes is to provide a way to convert data between SQL and Java and make the data accessible, particularly in supporting objects and collections or if you want to perform custom data conversions.

It is generally advisable to provide custom Java classes for all user-defined types (objects and collections) that you use in a SQLJ application. The Oracle JDBC driver will use instances of these classes in converting data, which is more convenient and less error-prone than using the weakly typed oracle.sql.STRUCT, REF, and ARRAY classes.

Custom Java classes are first-class types that you can use to read from and write to user-defined SQL types transparently.

To be used in SQLJ iterators or host expressions, a custom Java class must implement either the oracle.sql.ORAData (and ORADataFactory) interface or the standard java.sql.SQLData interface. This section provides an overview of these interfaces and custom Java class functionality, covering the following topics:

Custom Java Class Interface Specifications

This section discusses specifications of the ORAData and ORADataFactory interfaces and the standard SQLData interface.

Oracle9i includes a set of new APIs for Oracle-specific custom Java class functionality for user-defined types--oracle.sql.ORAData and oracle.sql.ORADataFactory.

The oracle.sql.CustomDatum and oracle.sql.CustomDatumFactory interfaces used previously for this functionality are deprecated in Oracle9i, but still supported for backward compatibility. You must use the CustomDatum interfaces if you are working with an Oracle8i JDBC driver.

ORAData and ORADataFactory Specifications

Oracle provides the interface oracle.sql.ORAData and the related interface oracle.sql.ORADataFactory to use in mapping and converting Oracle object types, reference types, and collection types to custom Java classes.

Data is sent or retrieved in the form of an oracle.sql.Datum object, with the underlying data being in the format of the appropriate oracle.sql.Datum subclass--oracle.sql.STRUCT, for example. This data is still in its SQL format; the oracle.sql.Datum object is just a wrapper. (For information about classes in the oracle.sql package that support Oracle type extensions, see the Oracle9i JDBC Developer's Guide and Reference.)

The ORAData interface specifies a toDatum() method for data conversion from Java format to SQL format. This method takes as input your connection object and converts data to the appropriate oracle.sql.* representation. The connection object is necessary so that the JDBC driver can perform appropriate type checking and type conversions at runtime. Here is the ORAData and toDatum() specification:

interface oracle.sql.ORAData
{
   oracle.sql.Datum toDatum(java.sql.Connection c) throws SQLException;
}

The ORADataFactory interface specifies a create() method that constructs instances of your custom Java class, converting from SQL format to Java format. This method takes as input a Datum object containing the data, and a typecode, such as OracleTypes.RAW, indicating the SQL type of the underlying data. It returns an object of your custom Java class, which implements the ORAData interface. This object receives its data from the Datum object that was input. Here is the ORADataFactory and create() specification:

interface oracle.sql.ORADataFactory
{
   oracle.sql.ORAData create(oracle.sql.Datum d, int sqlType) 
                      throws SQLException;
}

To complete the relationship between the ORAData and ORADataFactory interfaces, you must implement a static getORADataFactory() method in any custom Java class that implements the ORAData interface. This method returns an object that implements the ORADataFactory interface and that, therefore, can be used to create instances of your custom Java class. This returned object can itself be an instance of your custom Java class, and its create() method is used by the Oracle JDBC driver to produce further instances of your custom Java class, as necessary.

Note:

JPublisher output implements the ORAData interface and its toDatum() method and the ORADataFactory interface and its create() method in a single custom Java class; however, toDatum() and create() are specified in different interfaces to allow the option of implementing them in separate classes. You can have one custom Java class that implements ORAData, its toDatum() method, and the getORADataFactory() method, and have a separate factory class that implements ORADataFactory and its create() method. For purposes of discussion here, however, the assumption is that both interfaces are implemented in a single class.

For information about Oracle SQLJ requirements of a class that implements ORAData, see "Oracle Requirements for Classes Implementing ORAData".

For more information about the ORAData and ORADataFactory interfaces, the oracle.sql classes, and the OracleTypes class, see the Oracle9i JDBC Developer's Guide and Reference.

If you use JPublisher, specifying -usertypes=oracle will result in JPublisher generating custom Java classes that implement the ORAData and ORADataFactory interfaces and the getORADataFactory() method. Or, for backward compatibility, you have the option of using the JPublisher -compatible option in conjunction with -usertypes=oracle to use the CustomDatum and CustomDatumFactory interfaces instead. See the Oracle9i JPublisher User's Guide for more information.

ORAData Versus CustomDatum Interfaces

As a result of the oracle.jdbc interfaces being introduced in Oracle9i as replacements for the oracle.jdbc.driver classes, the oracle.sql.CustomDatum and oracle.sql.CustomDatumFactory interfaces, formerly used to access customized objects, have been deprecated in favor of new interfaces--oracle.sql.ORAData and oracle.sql.ORADataFactory. Like the CustomDatum interfaces, these can be used as an Oracle-specific alternative to the standard SQLData interface. The CustomDatum interfaces are still supported for backward compatibility.

CustomDatum and CustomDatumFactory have the following definitions:

public interface CustomDatum
{
  oracle.sql.Datum toDatum(
    oracle.jdbc.driver.OracleConnection conn
  ) throws SQLException;

public interface CustomDatumFactory
{
  oracle.sql.CustomDatum create(
    oracle.sql.Datum d, int sqlType
    ) throws SQLException;
}

The connection conn and typecode sqlType are used as described for ORAData and ORADataFactory in "ORAData and ORADataFactory Specifications". Note, however, that CustomDatum uses the Oracle-specific OracleConnection type instead of the standard Connection type.

SQLData Specification

Standard JDBC 2.0 supplies the interface java.sql.SQLData to use in mapping and converting structured object types to Java classes. This interface is intended for mapping structured object types only, not object references, collections/arrays, or other SQL types.

The SQLData interface is a JDBC 2.0 standard, specifying a readSQL() method to read data into a Java object, and a writeSQL() method to write to the database from a Java object.

For information about functionality that is required of a class that implements SQLData, see "Requirements for Classes Implementing SQLData".

For additional information about standard SQLData functionality, refer to the Sun Microsystems JDBC 2.0 or higher API specification.

If you use JPublisher, specifying -usertypes=jdbc will result in JPublisher generating custom Java classes that implement the SQLData interface.

Custom Java Class Support for Object Methods

Methods of Oracle objects can be invoked from custom Java class wrappers. Whether the underlying stored procedure is written in PL/SQL or is written in Java and published to SQL is invisible to the user.

A Java wrapper method used to invoke a server method requires a connection to communicate with the server. The connection object can be provided as an explicit parameter or can be associated in some other way (as an attribute of your custom Java class, for example).

If the connection object used by the wrapper method is a non-static attribute, then the wrapper method must be an instance method of the custom Java class in order to have access to the connection. Custom Java classes generated by JPublisher use this technique.

There are also issues regarding output and input-output parameters in methods of Oracle objects. If a stored procedure (SQL object method) modifies the internal state of one of its arguments, then the actual argument passed to the stored procedure is modified. In Java this is not possible. When a JDBC output parameter is returned from a stored procedure call, it must be stored in a newly created object. The original object identity is lost.

One way to return an output or input-output parameter to the caller is to pass the parameter as an element of an array. If the parameter is input-output, the wrapper method takes the array element as input; after processing, the wrapper assigns the output to the array element. Custom Java classes generated by JPublisher use this technique--each output or input-output parameter is passed in a one-element array.

When you use JPublisher, it implements wrapper methods by default. This is true for generated classes implementing either the SQLData interface or the ORAData interface. To disable this feature, set the JPublisher -methods flag to false. See the Oracle9i JPublisher User's Guide for more information.

Note:

If you are implementing a custom Java class yourself, there are various ways that you can implement wrapper methods. Data processing in the server can be done either through the SQL object method directly, or by forwarding the object value from the client to the server and then executing the method there. To see how JPublisher implements wrapper methods, and whether this may meet your needs, see "JPublisher Implementation of Wrapper Methods".

Custom Java Class Requirements

Custom Java classes must satisfy certain requirements to be recognized by the Oracle SQLJ translator as valid host variable types, and to allow type-checking by the translator.

This section discusses Oracle-specific requirements of custom Java classes so they can support this functionality. Requirements for both ORAData implementations and SQLData implementations are covered.

Note:

Custom Java classes for user-defined types are often referred to in this manual as "wrapper classes".

Oracle Requirements for Classes Implementing ORAData

Oracle requirements for ORAData implementations are primarily the same for any kind of custom Java class but vary slightly depending on whether the class is for mapping to objects, object references, collections, or some other SQL type.

These requirements are as follows:

The class implements the oracle.sql.ORAData interface.
The class implements a method getORADataFactory() that returns an oracle.sql.ORADataFactory object, as follows:
```
public static oracle.sql.ORADataFactory getORADataFactory();
```
If using the deprecated CustomDatum interface, the class implements the method getFactory() that returns an oracle.sql.CustomDatumFactory object as follows:
```
public static oracle.sql.CustomDatumFactory getFactory();
```
The class has a constant, _SQL_TYPECODE (string), initialized to the oracle.jdbc.OracleTypes typecode of the Datum subclass instance that toDatum() returns.
- For custom object classes:
```
public static final int _SQL_TYPECODE = OracleTypes.STRUCT;
```
- For custom reference classes:
```
public static final int _SQL_TYPECODE = OracleTypes.REF;
```
- For custom collection classes:
```
public static final int _SQL_TYPECODE = OracleTypes.ARRAY;
```
For other uses, some other typecode might be appropriate. For example, for using a custom Java class to serialize and deserialize Java objects into or out of RAW fields, a _SQL_TYPECODE of OracleTypes.RAW is used. See "Serialized Java Objects".

(The OracleTypes class simply defines a typecode, which is an integer constant, for each Oracle datatype. For standard SQL types, the OracleTypes entry is identical to the entry in the standard java.sql.Types type definitions class.)
For custom Java classes with _SQL_TYPECODE of STRUCT, REF, or ARRAY (in other words, for custom Java classes that represent objects, object references, or collections), the class has a constant that indicates the relevant user-defined type name.
- Custom object classes and custom collection classes must have a constant, _SQL_NAME (string), initialized to the SQL name you declared for the user-defined type, as follows:
```
public static final String _SQL_NAME = UDT name;
```
  Custom object class example for a user-defined PERSON object:
```
public static final String _SQL_NAME = "PERSON";
```
  or (to specify the schema, if that is appropriate):
```
public static final String _SQL_NAME = "SCOTT.PERSON";
```
  Custom collection class example for a collection of PERSON objects, which you have declared as PERSON_ARRAY:
```
public static final String _SQL_NAME = "PERSON_ARRAY";
```
- Custom reference classes must have a constant, _SQL_BASETYPE (string), initialized to the SQL name you declared for the user-defined type being referenced, as follows:
```
public static final String _SQL_BASETYPE = UDT name;
```
  Custom reference class example for PERSON references:
```
public static final String _SQL_BASETYPE = "PERSON";
```
  For other ORAData uses, specifying a UDT name is not applicable.

Usage Notes

A collection type name reflects the collection type, not the base type. For example, if you have declared a VARRAY or nested table type PERSON_ARRAY for PERSON objects, then the name of the collection type that you specify for the _SQL_NAME entry is PERSON_ARRAY, not PERSON.
When specifying the SQL type in a _SQL_NAME field, if the SQL type was declared in a case-sensitive way (in quotes), then you must specify the SQL name exactly as it was declared, such as CaseSensitive or SCOTT.CaseSensitive. (Note that this differs from usage in a JPublisher input file, where the case-sensitive name must also appear in quotes.) If you did not declare the SQL type in a case-sensitive way (no quotes), then you must specify the SQL name in all uppercase, such as ADDRESS or SCOTT.ADDRESS.

JPublisher automatically generates the value of this field appropriately, according to case-sensitivity and the JPublisher -omit_schema_names setting if applicable.

Requirements for Classes Implementing SQLData

The ISO SQLJ standard outlines requirements for type map definitions for classes implementing the SQLData interface.

Alternatively, SQLData wrapper classes can identify associated SQL object types through public static final fields. This non-standard functionality was introduced in Oracle SQLJ release 8.1.6 and continues to be supported.

Be aware of the following important points:

Whether you use a type map or use alternative (non-standard) public static final fields to specify mappings, you must be consistent in your approach. Either use a type map that specifies all relevant mappings so that you do not require public static final fields, or do not use a type map at all and specify all mappings through public static final fields.
SQLData, unlike ORAData, is for mapping structured object types only. It is not for object references, collections/arrays, or any other SQL types. If you are not using ORAData, then your only choices for mapping object references and collections are the weak types java.sql.Ref and java.sql.Array, respectively, or oracle.sql.REF and oracle.sql.ARRAY.
SQLData implementations require a JDK 1.2.x or higher environment. Although Oracle JDBC supports JDBC 2.0 extensions under JDK 1.1.x through the oracle.jdbc2 package, Oracle SQLJ does not.
When specifying the mapping from a SQL type to a Java type (described below), if the SQL type was declared in a case-sensitive way (in quotes), then you must specify the SQL name exactly as it was declared, such as CaseSensitive or SCOTT.CaseSensitive. (Note that this differs from usage in a JPublisher input file, where the case-sensitive name must also appear in quotes.) If you did not declare the SQL type in a case-sensitive way (no quotes), then you must specify the SQL name in all uppercase, such as ADDRESS or SCOTT.ADDRESS.

Mapping Specified in Type Map Resource

First, consider the mapping representation according to the ISO SQLJ standard. Assume that Address, pack.Person, and pack.Manager.InnerPM (where InnerPM is an inner class of Manager) are three wrapper classes that implement java.sql.SQLData.

You must employ these classes only in statements that use explicit connection context instances of a declared connection context type. Assume, for example, that this type is called SDContext. Example:
```
Address               a =...;
pack.Person           p =...;
pack.Manager.InnerPM pm =...;
SDContext ctx = new SDContext(url,user,pwd,false);
#sql [ctx] { ... :a ... :p ... :pm ... };
```
The connection context type must have been declared using the with attribute typeMap that specifies an associated class implementing a java.util.PropertyResourceBundle. In the preceding example, SDContext might have been declared as follows:
```
#sql public static context SDContext with (typeMap="SDMap");
```

The type map resource must provide the mapping from SQL object types to corresponding Java classes that implement the java.sql.SQLData interface. This mapping is specified with entries of the following form:

class.<java_class_name>=STRUCT <sql_type_name>

The keyword STRUCT can also be omitted. In the example, the resource file SDMap.properties might contain the following entries:

class.Address=STRUCT SCOTT.ADDRESS
class.pack.Person=PERSON
class.pack.Manager$InnerPM=STRUCT PRODUCT_MANAGER

Although "." separates package and class name, you must use the character "$" to separate an inner class name.

Important:

If you used default Oracle-specific code generation in this example, then any iterator that is used for a statement whose context type is SDContext must also have been declared with the same associated type map, SDMap, such as in the following example:

#sql public static iterator SDIter with (typeMap="SDMap");
...
SDContext sdctx = ...
SDIter sditer;
#sql [sdctx] sditer = { SELECT ...};

This is to ensure that proper code is generated for the iterator class.

This mechanism of specifying mappings in a type map resource is more complicated than the non-standard alternative (discussed next). Furthermore, it is not possible to associate a type map resource with the default connection context. The advantage is that all the mapping information is placed in a single location--the type map resource.This means that the type mapping in an already compiled application can be easily adjusted at a later time, for example to accommodate new SQL types and Java wrappers in an expanding SQL-Java type hierarchy.

Be aware of the following:

You must employ the SQLJ runtime12 or runtime12ee library to use this feature. Type maps are represented as java.util.Map objects. These are exposed in the SQLJ runtime API and, therefore, cannot be supported by the JDK 1.1 or generic runtime libraries.
You must use the Oracle SQLJ runtime and Oracle-specific code generation or profile customization if your SQLData wrapper classes occur as OUT or INOUT parameters in SQLJ statements. This is because the SQL type of such parameters is required for registerOutParameter() by the Oracle JDBC driver. Furthermore, for OUT parameter type registration, the SQL type is "frozen in" by the type map in effect during translation.
The SQLJ type map is independent of any JDBC type map you may be using on the underlying connection. Thus, you must be careful if you are mixing SQLJ and JDBC code that both use SQLData wrappers. However, you can easily extract the type map in effect on a given SQLJ connection context:
```
ctx.getTypeMap();
```

Mapping Specified in Static Field of Wrapper Class

Alternatively, a class that implements SQLData can satisfy the following non-standard requirement.

The Java class declares the public static final String-valued field _SQL_NAME. This field defines the name of the SQL type that is being wrapped by the Java class.

In the example, the Address class would have the following field declaration:
```
public static final String _SQL_NAME="SCOTT.ADDRESS";
```
The following declaration would be in pack.Person:
```
public static final String _SQL_NAME="PERSON";
```
And the class pack.Manager.InnerPM would have the following:
```
public static final String _SQL_NAME="PRODUCT_MANAGER";
```

Note that JPublisher always generates SQLData wrapper classes with the _SQL_NAME field. However, this field is ignored in SQLJ statements that reference a type map.

Notes:

If a class that implements the _SQL_NAME field is used in a SQLJ statement with an explicit connection context type and associated type map, then that type map is used, and the _SQL_NAME field is ignored, thereby simplifying migration of existing SQLJ programs to the new ISO SQLJ standard.
The static SQL-Java type correspondence specified in the _SQL_NAME field is independent from any JDBC type map you may be using on the underlying connection. Thus, you must be careful if you are mixing SQLJ and JDBC code that both use SQLData wrappers.

Compiling Custom Java Classes

You can include any .java files for your custom Java classes (whether ORAData or SQLData implementations) on the SQLJ command line together with the .sqlj file(s) for your application. However, this is not necessary if the SQLJ -checksource flag is set to true (the default) and your classpath includes the directory where the custom Java source is located. (This discussion assumes you are creating .java files for your custom objects and collections, not .sqlj files. Any .sqlj files must be included in the SQLJ command line.)

For example, if ObjectDemo.sqlj uses Oracle object types ADDRESS and PERSON and you have produced custom Java classes for these objects, then you can run SQLJ as follows.

If -checksource=true (default) and the classpath includes the custom Java source location:
```
% sqlj ObjectDemo.sqlj
```

If -checksource=false (this is a single wraparound line):

% sqlj ObjectDemo.sqlj Address.java AddressRef.java Person.java 
PersonRef.java

You also have the choice of using your Java compiler to compile custom .java source files directly. If you do this, you must do it prior to translating .sqlj files.

Running the SQLJ translator is discussed in Chapter 8, "Translator Command Line and Options". For more information about the -checksource flag, see "Source Check for Type Resolution (-checksource)".

Note:

Because ORAData implementations rely on Oracle-specific features, SQLJ will report numerous portability warnings if you do not use the translator portability setting -warn=noportable (the default). For information about the -warn flag, see "Translator Warnings (-warn)".

Reading and Writing Custom Data

Through the use of custom Java class instances, Oracle SQLJ and JDBC allow you to read and write user-defined types as though they are built-in types. Exactly how this is accomplished is transparent to the user.

For the mechanics of how data is read and written, for both ORAData implementations and SQLData implementations, see the Oracle9i JDBC Developer's Guide and Reference.

Additional Uses for ORAData Implementations

To this point, discussion of custom Java classes has been for use as one of the following:

wrappers for SQL objects--custom object classes, for use with oracle.sql.STRUCT instances
wrappers for SQL references--custom reference classes, for use with oracle.sql.REF instances
wrappers for SQL collections--custom collection classes, for use with oracle.sql.ARRAY instances

It might be useful, however, to provide custom Java classes to wrap other oracle.sql.* types as well, for customized conversions or processing. You can accomplish this with classes that implement ORAData (but not SQLData), as in the following examples:

Perform encryption and decryption or validation of data.
Perform logging of values that have been read or are being written.
Parse character columns (such as character fields containing URL information) into smaller components.
Map character strings into numeric constants.
Map data into more desirable Java formats (such as mapping a DATE field to java.util.Date format).
Customize data representation (for example, data in a table column is in feet, but you want it represented in meters after it is selected).
Serialize and deserialize Java objects--into or out of RAW fields, for example

This last use is further discussed in "Serialized Java Objects".

The rest of this section provides an example of a class (BetterDate) that implements ORAData and can be used instead of java.sql.Date to represent dates.

Note:

This sort of functionality is not possible through the SQLData interface, as SQLData implementations can wrap only structured object types.

General Use of ORAData--BetterDate.java

This example shows a class that implements the ORAData interface to provide a customized representation of Java dates.

Note:

This is not a complete application--there is no main() method.

import java.util.Date;
import oracle.sql.ORAData;
import oracle.sql.DATE;
import oracle.sql.ORADataFactory;
import oracle.jdbc.OracleTypes;

// a Date class customized for user's preferences:
//      - months are numbers 1..12, not 0..11
//      - years are referred to via four-digit numbers, not two.

public class BetterDate extends java.util.Date
             implements ORAData, ORADataFactory {
  public static final int _SQL_TYPECODE = OracleTypes.DATE;
  
  String[]monthNames={"JAN", "FEB", "MAR", "APR", "MAY", "JUN",
                      "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"};
  String[]toDigit={"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"};

  static final BetterDate _BetterDateFactory = new BetterDate();

  public static ORADataFactory getORADataFactory() { return _BetterDateFactory;}

  // the current time...
  public BetterDate() {
    super();
  }

  public oracle.sql.Datum toDatum(java.sql.Connection conn) {
    return new DATE(toSQLDate());
  }

  public oracle.sql.ORAData create(oracle.sql.Datum dat, int intx) {
    if (dat==null) return null;
    DATE DAT = ((DATE)dat);
    java.sql.Date jsd = DAT.dateValue();
    return new BetterDate(jsd);
  }
   
  public java.sql.Date toSQLDate() {
    java.sql.Date retval;
    retval = new java.sql.Date(this.getYear()-1900, this.getMonth()-1,
             this.getDate());
    return retval;
  }
  public BetterDate(java.sql.Date d) {
    this(d.getYear()+1900, d.getMonth()+1, d.getDate());
  }
  private static int [] deconstructString(String s) {
    int [] retval = new int[3];
    int y,m,d; char temp; int offset;
    StringBuffer sb = new StringBuffer(s);
    temp=sb.charAt(1);
    // figure the day of month
    if (temp < '0' || temp > '9') {
      m = sb.charAt(0)-'0';
      offset=2;
    } else {
      m = (sb.charAt(0)-'0')*10 + (temp-'0');
      offset=3;
    }

    // figure the month
    temp = sb.charAt(offset+1);
    if (temp < '0' || temp > '9') {
      d = sb.charAt(offset)-'0';
      offset+=2;
    } else {
      d = (sb.charAt(offset)-'0')*10 + (temp-'0');
      offset+=3;
    }
    // figure the year, which is either in the format "yy" or "yyyy"
    // (the former assumes the current century)
    if (sb.length() <= (offset+2)) {
      y = (((new BetterDate()).getYear())/100)*100 +
          (sb.charAt(offset)- '0') * 10 +
          (sb.charAt(offset+1)- '0');
    } else {
      y = (sb.charAt(offset)- '0') * 1000 +
          (sb.charAt(offset+1)- '0') * 100 +
          (sb.charAt(offset+2)- '0') * 10 +
          (sb.charAt(offset+3)- '0');
    }
    retval[0]=y;
    retval[1]=m;
    retval[2]=d;
//    System.out.println("Constructing date from string as: "+d+"/"+m+"/"+y);
    return retval;
  }
  private BetterDate(int [] stuff) {
    this(stuff[0], stuff[1], stuff[2]);
  }
  // takes a string in the format: "mm-dd-yyyy" or "mm/dd/yyyy" or
  // "mm-dd-yy" or "mm/dd/yy" (which assumes the current century)
  public BetterDate(String s) {
    this(BetterDate.deconstructString(s));
  }

  // years are as '1990', months from 1..12 (unlike java.util.Date!), date
  // as '1' to '31' 
  public BetterDate(int year, int months, int date) {
    super(year-1900,months-1,date);
  }
  // returns "Date: dd-mon-yyyy"
  public String toString() { 
    int yr = getYear();
    return getDate()+"-"+monthNames[getMonth()-1]+"-"+
      toDigit[(yr/1000)%10] + 
      toDigit[(yr/100)%10] + 
      toDigit[(yr/10)%10] + 
      toDigit[yr%10];
//    return "Date: " + getDate() + "-"+getMonth()+"-"+(getYear()%100);
  }
  public BetterDate addDays(int i) {
    if (i==0) return this;
    return new BetterDate(getYear(), getMonth(), getDate()+i);
  }
  public BetterDate addMonths(int i) {
    if (i==0) return this;
    int yr=getYear();
    int mon=getMonth()+i;
    int dat=getDate();
    while(mon<1) { 
      --yr;mon+=12;
    }
    return new BetterDate(yr, mon,dat);
  }
  // returns year as in 1996, 2007
  public int getYear() {
    return super.getYear()+1900;
  }
  // returns month as 1..12
  public int getMonth() {
    return super.getMonth()+1;
  }
  public boolean equals(BetterDate sd) {
    return (sd.getDate() == this.getDate() &&
            sd.getMonth() == this.getMonth() &&
            sd.getYear() == this.getYear());
  }
  // subtract the two dates; return the answer in whole years
  // uses the average length of a year, which is 365 days plus
  // a leap year every 4, except 100, except 400 years =
  // = 365 97/400 = 365.2425 days = 31,556,952 seconds
  public double minusInYears(BetterDate sd) {
    // the year (as defined above) in milliseconds
    long yearInMillis = 31556952L;
    long diff = myUTC()-sd.myUTC();
    return (((double)diff/(double)yearInMillis)/1000.0);
  }
  public long myUTC() {
    return Date.UTC(getYear()-1900, getMonth()-1, getDate(),0,0,0);
  }
  
  // returns <0 if this is earlier than sd
  // returns = if this == sd
  // else returns >0
  public int compare(BetterDate sd) {
    if (getYear()!=sd.getYear()) {return getYear()-sd.getYear();}
    if (getMonth()!=sd.getMonth()) {return getMonth()-sd.getMonth();}
    return getDate()-sd.getDate();
  }
}

User-Defined Types

This section contains examples of creating and using user-defined object types and collection types in Oracle9i. For more information about any of the SQL commands used here, refer to the Oracle9i SQL Reference.

Creating Object Types

Oracle SQL commands to create object types are of the following form:

CREATE TYPE typename AS OBJECT
( 
  attrname1    datatype1,
  attrname2    datatype2,
  ...         ...
  attrnameN    datatypeN
);

Where typename is the desired name of your object type, attrname1 through attrnameN are the desired attribute names, and datatype1 through datatypeN are the attribute datatypes.

The remainder of this section provides an example of creating user-defined object types in Oracle9i.

The following items are created using the SQL script below:

two object types, PERSON and ADDRESS
a typed table for PERSON objects
an EMPLOYEES table that includes an ADDRESS column and two columns of PERSON references

Here is the script:

/*** Using user-defined types (UDTs) in SQLJ ***/
/
/*** Create ADDRESS UDT ***/
CREATE TYPE ADDRESS AS OBJECT
( 
  street        VARCHAR(60),
  city          VARCHAR(30),
  state         CHAR(2),
  zip_code      CHAR(5)
)
/
/*** Create PERSON UDT containing an embedded ADDRESS UDT ***/
CREATE TYPE PERSON AS OBJECT
( 
  name    VARCHAR(30),
  ssn     NUMBER,
  addr    ADDRESS
)
/
/*** Create a typed table for PERSON objects ***/
CREATE TABLE persons OF PERSON
/
/*** Create a relational table with two columns that are REFs 
     to PERSON objects, as well as a column which is an Address ADT. ***/
CREATE TABLE  employees
( 
  empnumber            INTEGER PRIMARY KEY,
  person_data     REF  PERSON,
  manager         REF  PERSON,
  office_addr          ADDRESS,
  salary               NUMBER
)
/*** Insert some data--2 objects into the persons typed table ***/
INSERT INTO persons VALUES (
            PERSON('Wolfgang Amadeus Mozart', 123456,
               ADDRESS('Am Berg 100', 'Salzburg', 'AT','10424')))
/
INSERT INTO persons VALUES (
            PERSON('Ludwig van Beethoven', 234567,
               ADDRESS('Rheinallee', 'Bonn', 'DE', '69234')))
/
/** Put a row in the employees table **/
INSERT INTO employees (empnumber, office_addr, salary) VALUES (
            1001,
            ADDRESS('500 Oracle Parkway', 'Redwood Shores', 'CA', '94065'),
            50000)
/
/** Set the manager and PERSON REFs for the employee **/
UPDATE employees 
   SET manager =  
       (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart')
/
UPDATE employees 
   SET person_data =  
       (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')

Note:

Use of a table alias, such as p above, is a recommended general practice in Oracle SQL, especially in accessing tables with user-defined types. It is required syntax in some cases where object attributes are accessed. Even when not required, it helps in avoiding ambiguities. See the Oracle9i SQL Reference for more information about table aliases.

Creating Collection Types

There are two categories of collections

variable-length arrays (VARRAYs)
nested tables

Oracle SQL commands to create VARRAY types are of the following form:

CREATE TYPE typename IS VARRAY(n) OF datatype;

The typename designation is the desired name of your VARRAY type, n is the desired maximum number of elements in the array, and datatype is the datatype of the array elements. For example:

CREATE TYPE myvarr IS VARRAY(10) OF INTEGER;

Oracle SQL commands to create nested table types are of the following form:

CREATE TYPE typename AS TABLE OF datatype;

The typename designation is the desired name of your nested table type, and datatype is the datatype of the table elements. This can be a user-defined type as well as a standard datatype. A nested table is limited to one column, although that one column type can be a complex object with multiple attributes. The nested table, as with any database table, can have any number of rows. For example:

CREATE TYPE person_array AS TABLE OF person;

This command creates a nested table where each row consists of a PERSON object.

The rest of this section provides an example of creating a user-defined collection type (as well as object types) in Oracle9i.

The following items are created and populated using the SQL script below:

two object types, PARTICIPANT_T and MODULE_T
a collection type, MODULETBL_T, which is a nested table of MODULE_T objects
a PROJECTS table that includes a column of PARTICIPANT_T references and a column of MODULETBL_T nested tables
a collection type PHONE_ARRAY, which is a VARRAY of VARCHAR2(30)
PERSON and ADDRESS objects (repeating the same definitions used earlier in "Creating Object Types")
an EMPLOYEES table, which includes a PHONE_ARRAY column

Here is the script:

Rem This is a SQL*Plus script used to create schema to demonstrate collection 
Rem manipulation in SQLJ 

CREATE TYPE PARTICIPANT_T AS OBJECT (
  empno   NUMBER(4),
  ename   VARCHAR2(20),
  job     VARCHAR2(12),
  mgr     NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  deptno   NUMBER(2)) 
/
show errors 
CREATE TYPE MODULE_T  AS OBJECT (
  module_id  NUMBER(4),
  module_name VARCHAR2(20), 
  module_owner REF PARTICIPANT_T, 
  module_start_date DATE, 
  module_duration NUMBER )
/
show errors 
create TYPE MODULETBL_T AS TABLE OF MODULE_T;
/
show errors 
CREATE TABLE projects (
  id NUMBER(4),
  name VARCHAR(30),
  owner REF PARTICIPANT_T,
  start_date DATE,
  duration NUMBER(3),
  modules  MODULETBL_T  ) NESTED TABLE modules STORE AS modules_tab;

show errors 
CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30)
/

/*** Create ADDRESS UDT ***/
CREATE TYPE ADDRESS AS OBJECT
( 
  street        VARCHAR(60),
  city          VARCHAR(30),
  state         CHAR(2),
  zip_code      CHAR(5)
)
/
/*** Create PERSON UDT containing an embedded ADDRESS UDT ***/
CREATE TYPE PERSON AS OBJECT
( 
  name    VARCHAR(30),
  ssn     NUMBER,
  addr    ADDRESS
)
/
CREATE TABLE  employees
( empnumber            INTEGER PRIMARY KEY,
  person_data     REF  person,
  manager         REF  person,
  office_addr          address,
  salary               NUMBER,
  phone_nums           phone_array
)
/

JPublisher and the Creation of Custom Java Classes

Oracle offers flexibility in how users can customize the mapping of Oracle object types, reference types, and collection types to Java classes in a strongly typed paradigm. Developers have the following choices in creating these custom Java classes:

using Oracle JPublisher to automatically generate custom Java classes and using those classes directly without modification
using JPublisher to automatically generate custom Java classes and corresponding subclasses, which can subsequently be user-modified for any desired functionality
manually coding custom Java classes without using JPublisher, if the classes meet the requirements stated in "Custom Java Class Requirements"

Although you have the option of manually coding your custom Java classes, it is advisable to instead use JPublisher-generated classes directly or modify JPublisher-generated subclasses.

JPublisher can implement either the Oracle oracle.sql.ORAData interface or the standard java.sql.SQLData interface when it generates a custom object class. If you choose the ORAData implementation, then JPublisher will also generate a custom reference class. For compatibility with older JDBC versions, JPublisher can also generate classes that implement the deprecated oracle.sql.CustomDatum interface.

The SQLData interface is not intended for custom reference or custom collection classes. If you want your code to be portable, you have no choice but to use standard weakly typed java.sql.Ref objects to map to references, and java.sql.Array objects to map to collections.

This manual provides only minimal information and detail regarding the JPublisher utility. See the Oracle9i JPublisher User's Guide for more information.

For detailed discussion of the ORAData and SQLData interfaces and relative advantages of the ORAData interface, see the Oracle9i JDBC Developer's Guide and Reference.

What JPublisher Produces

When you use JPublisher to generate custom Java classes, you can use either an ORAData implementation (for custom object classes, custom reference classes, or custom collection classes) or a SQLData implementation (for custom object classes only). An ORAData implementation will also implement the ORADataFactory interface, for creating instances of the custom Java class.

This is controlled by how you set the JPublisher -usertypes option. A setting of -usertypes=oracle specifies an ORAData implementation; a setting of -usertypes=jdbc specifies a SQLData implementation.

ORAData Implementation

When you run JPublisher for a user-defined object type and use the ORAData implementation for your custom object class (through the default -usertypes=oracle setting), JPublisher automatically creates the following:

a custom object class, typically in a .sqlj source file, to act as a type definition to correspond to your Oracle object type

This class includes getter and setter methods for each attribute. The method names are of the form getFoo() and setFoo() for attribute foo.

In addition, JPublisher by default will generate wrapper methods in your class that invoke the associated Oracle object methods executing in the server. This can be disabled, however, by setting -methods=false. In this case, JPublisher produces no wrapper methods and generates .java files instead of .sqlj files for custom objects. The -methods option is described later in this section.
a related custom reference class for object references to your Oracle object type

This class includes a getValue() method that returns an instance of your custom object class, and a setValue() method that updates an object value in the database, taking as input an instance of the custom object class.

A strongly typed reference class is always generated, regardless of whether the SQL object type uses references.

Advantages of using strongly typed instead of weakly typed references are described in"Strongly Typed Object References for ORAData Implementations".
custom classes for any object or collection attributes of the top-level object

This is necessary so that attributes can be materialized in Java whenever an instance of the top-level class is materialized.

When you run JPublisher for a user-defined collection type, choosing the ORAData implementation, JPublisher automatically creates the following:

a custom collection class to act as a type definition to correspond to your Oracle collection type

This class includes overloaded getArray() and setArray() methods to retrieve or update a collection as a whole, a getElement() method and setElement() method to retrieve or update individual elements of a collection, and additional utility methods.
a custom object class for the elements, if the elements of the collection are objects

This is necessary so that object elements can be materialized in Java whenever an instance of the collection is materialized.

JPublisher-generated custom Java classes in any of these categories implement the ORAData interface, the ORADataFactory interface, and the getORADataFactory() method.

Notes:

If you specify the ORAData implementation, the generated classes will use Oracle-specific features and therefore will not be portable.
JPublisher still supports implementation of the CustomDatum interface, replaced by ORAData and deprecated in Oracle9i, through the -compatible option. This is described in "Choose the Implementation for Generated Classes".

Strongly Typed Object References for ORAData Implementations

For Oracle ORAData implementations, JPublisher always generates strongly typed object reference classes as opposed to using the weakly typed oracle.sql.REF class. This is to provide greater type safety and to mirror the behavior in SQL, where object references are strongly typed. The strongly typed classes (with names such as PersonRef for references to PERSON objects) are essentially wrappers for the REF class.

In these strongly typed REF wrappers, there is a getValue() method that produces an instance of the SQL object that is referenced, in the form of an instance of the corresponding Java class. (Or, in the case of inheritance, perhaps as an instance of a subclass of the corresponding Java class.) For example, if there is a PERSON SQL object type, with a corresponding Person Java class, there will also be a PersonRef Java class. The getValue() method of the PersonRef class would return a Person instance containing the data for a PERSON object in the database.

Whenever a SQL object type has an attribute that is an object reference, the Java class corresponding to the object type would have an attribute that is an instance of a Java class corresponding to the appropriate reference type. For example, if there is a PERSON object with a MANAGER REF attribute, then the corresponding Person Java class will have a ManagerRef attribute.

SQLData Implementation

When you run JPublisher for a user-defined object type and choose the SQLData implementation for your custom object class (through the -usertypes=jdbc setting), JPublisher will produce a custom object class to act as a type definition to correspond to your Oracle object type. This class will include the following:

getter and setter methods for each attribute
implementations of the standard SQLData interface readSQL() and writeSQL() methods
wrapper methods that invoke the Oracle object methods executing in the server (unless you specify -methods=false when you run JPublisher)

Because the SQLData interface is intended only for objects, however, and not for references or collections, JPublisher will not generate a custom reference class for references to the Oracle object type. You will have to use standard weakly typed java.sql.Ref instances, or perhaps oracle.sql.REF instances if you do not require portability. Note that REF instances, like custom reference class instances, have Oracle extension methods getValue() and setValue() to read or write instances of the referenced object. Standard Ref instances do not have this functionality.

Similarly, because you cannot use a SQLData implementation for a custom collection class, you must use standard weakly typed java.sql.Array instances, or perhaps oracle.sql.ARRAY instances if you do not require portability. Array and ARRAY instances, like custom collection class instances, have getArray() functionality to read the collection as a whole or in part, but do not have the element-level access and writability offered by the custom collection class getElement() and setElement() methods.

Note:

The SQLData interface is defined in the JDBC specification to be portable. However, if you want the SQLData implementation produced by JPublisher to be portable, you must avoid using any Oracle-specific features and Oracle type mapping (which uses the Oracle-specific oracle.sql.* classes).

Generating Custom Java Classes

This section discusses key JPublisher command-line functionality for specifying the user-defined types that you want to map to Java and for specifying object class names, collection class names, attribute type mappings, and wrapper methods. These key points can be summarized as follows:

Specify the implementation to use (ORAData or SQLData), through the JPublisher -usertypes option.
Specify user-defined types to map to Java. You can specify the custom object and custom collection class names for JPublisher to use, or you can accept the default names. Use the JPublisher -sql, -user, and -case options, as appropriate.
Optionally specify attribute type mappings through the JPublisher -XXXtypes options: -numbertypes, -builtintypes, and -lobtypes.
Choose whether or not JPublisher will create wrapper methods, in particular for Oracle object methods. Use the JPublisher -methods flag, which is enabled by default.

Note:
Throughout the remainder of this section, we simplify discussion of custom reference classes or custom collection classes by referring only to ORAData implementations.

Choose the Implementation for Generated Classes

Before running JPublisher, consider whether you want the generated classes to implement the Oracle ORAData interface or the standard SQLData interface. Using SQLData will likely make your code more portable, but using ORAData offers a number of advantages, including no need for type maps.

The preceding section, "What JPublisher Produces", discusses some of the implementation details for each scenario.

Remember the following:

You must use ORAData implementations for custom collection classes. The SQLData interface does not support collections (arrays).
Strongly typed reference classes are always generated for ORAData custom object class implementations, but not for SQLData custom object class implementations. The SQLData interface does not support strongly typed object references--use the weak java.sql.Ref type or oracle.sql.REF type instead.

For detailed discussion of the ORAData and SQLData interfaces and relative advantages of the ORAData interface, see the Oracle9i JDBC Developer's Guide and Reference.

Use the JPublisher -usertypes option to specify which interface you want your classes to implement. A setting of -usertypes=oracle (the default) specifies the ORAData interface, while a setting of -usertypes=jdbc specifies the SQLData interface.

Note:

If you have a requirement to implement the CustomDatum interface, which is replaced by ORAData and deprecated in Oracle9i, you can do so with a JPublisher -compatible setting of customdatum. This, combined with a -usertypes=oracle setting, results in generated classes implementing the CustomDatum interface. The default is -compatible=oradata.

The setting -compatible=8i or -compatible=both8i also directs JPublisher to use CustomDatum, as well as resulting in code generation that is backward compatible to Oracle8i versions of JPublisher. See the Oracle9i JPublisher User's Guide for more information.

The following JPublisher command-line examples will result in implementation of ORAData, CustomDatum, and SQLData, respectively (assume % is a system prompt).

% jpub -usertypes=oracle ... <other option settings>

% jpub -usertypes=oracle -compatible=customdatum ... <other option settings>

% jpub -usertypes=jdbc ... <other option settings>

JPublisher will ignore a -compatible=customdatum or -compatible=oradata setting if -usertypes=jdbc.

Specify User-Defined Types to Map to Java

In using JPublisher to create custom Java classes, use the -sql option to specify the user-defined SQL types that you want to map to Java. You can either specify the custom object class names and custom collection class names, or you can accept the defaults.

The default names of your top-level custom classes--the classes that will correspond to the user-defined type names you specify to the -sql option--are identical to the user-defined type names as you enter them on the JPublisher command line. Because SQL names in the database are case-insensitive by default, you can capitalize them to ensure that your class names are capitalized according to Java convention. For example, if you want to generate a custom class for employee objects, you can run JPublisher as follows:

% jpub -sql=Employee ...

The default names of other classes, such as for home_address objects that are attributes of employee objects, are determined by the JPublisher -case option. If you do not set the -case option, it is set to mixed. This means that the default for the custom class name is to capitalize the initial character of the corresponding user-defined type name and the initial character of every word unit thereafter. JPublisher interprets underscores (_), dollar signs ($), and any characters that are illegal in Java identifiers as word-unit separators; these characters are discarded in the process.

For example, for Oracle object type home_address, JPublisher would create class HomeAddress in a HomeAddress.sqlj or .java source file.

Important:

Only non-case-sensitive SQL names are supported on the JPublisher command line. If a user-defined type was defined in a case-sensitive way (in quotes) in SQL, then you must specify the name in the JPublisher INPUT file instead of on the command line, and in quotes. See "Using JPublisher INPUT Files".

Note:

For backward compatibility to previous versions of JPublisher, the -types option is still accepted as an alternative to -sql.

On the JPublisher command line, use the following syntax for the -sql option (you can specify multiple actions in a single option setting).

-sql=udt1<:mapclass1><,udt2<:mapclass2>>,...,<udtN<:mapclassN>> ...

And use the -user option to specify the database schema. Following is an example:

% jpub -sql=Myobj,mycoll:MyCollClass -user=scott/tiger

(There can be no space before or after the comma.)

For the Oracle object MYOBJ, this command will name it as you typed it, creating source Myobj.sqlj to define a Myobj class. For the Oracle collection MYCOLL, this command will create source MyCollClass.java to define a MyCollClass class.

You can optionally specify schema names in the -sql option--for example, the scott schema:

% jpub -sql=scott.Myobj,scott.mycoll:MyCollClass -user=scott/tiger

You cannot specify custom reference class names; JPublisher automatically derives them by adding "Ref" to custom object class names (relevant to ORAData implementations only). For example, if JPublisher produces Java source Myobj.sqlj to define custom object class Myobj, then it will also produce Java source MyobjRef.java to define a MyobjRef custom reference class.

Note:

When specifying the schema, such as scott in the above example, this is not incorporated into the custom Java class name.

To create custom Java classes for the object and collection types defined in "User-Defined Types", you can run JPublisher as follows:

%jpub -user=scott/tiger -sql=Address,Person,Phone_array,Participant_t,
Module_t,Moduletbl_t

or, to explicitly specify custom object class and custom collection class names:

%jpub -user=scott/tiger -sql=Address,Person,phone_array:PhoneArray,
participant_t:ParticipantT,module_t:ModuleT,moduletbl_t:ModuletblT

(Each of the preceding two examples is a single wraparound command line.)

The second example will produce Java source files Address.sqlj, AddressRef.java, Person.sqlj, PersonRef.java, PhoneArray.java, ParticipantT.sqlj, ParticipantTRef.java, ModuleT.sqlj, ModuleTRef.java, and ModuletblT.java. Examples of some of these source files are provided in "JPublisher Custom Java Class Examples".

So that it knows how to populate the custom Java classes, JPublisher connects to the specified schema (here, scott/tiger) to determine attributes of your specified object types or elements of your specified collection types.

Note:

As of Oracle9i release 2, as an alternative to specifying multiple mappings in a single -sql setting, you can use multiple -sql options in the same command line. The effect of multiple -sql options is cumulative.

If you want to change how JPublisher uses character case in default names for the methods and attributes that it generates, including lower-level custom Java class names for attributes that are objects or collections, you can accomplish this using the -case option. There are four possible settings:

-case=mixed (default)--The following will be uppercase: the first character of every word unit of a class name, every word unit of an attribute name, and every word unit after the first word unit of a method name. All other characters are in lowercase. JPublisher interprets underscores (_), dollar signs ($), and any characters that are illegal in Java identifiers as word-unit separators; these characters are discarded in the process.
-case=same--Character case is unchanged from its representation in the database. Underscores and dollar signs are retained; illegal characters are discarded.
-case=upper--Lowercase letters are converted to uppercase. Underscores and dollar signs are retained; illegal characters are discarded.

-case=lower--Uppercase letters are converted to lowercase. Underscores and dollar signs are retained; illegal characters are discarded.

Note:

If you run JPublisher without specifying the user-defined types to map to Java, it will process all user-defined types in the schema. Generated class names, for both your top-level custom classes and any other classes for object attributes or collection elements, will be based on the setting of the -case option.

Specify Type Mappings

JPublisher offers several choices for how to map user-defined types and their attribute and element types between SQL and Java. The rest of this section lists categories of SQL types and the mapping options available for each category.

(See "Supported Types for Host Expressions" for general information about how Oracle datatypes map to Java types.)

For more information about JPublisher features or options, see the Oracle9i JPublisher User's Guide.

Categories of SQL Types

JPublisher categorizes SQL types into the following groups, with corresponding JPublisher options as noted:

numeric types--anything stored as SQL type NUMBER

Use the JPublisher -numbertypes option to specify type-mapping for numeric types.
LOB types--SQL types BLOB and CLOB

Use the JPublisher -lobtypes option to specify type-mapping for LOB types.
built-in types--anything stored as a SQL type not covered by the preceding categories, for example: CHAR, VARCHAR2, LONG, and RAW

Use the JPublisher -builtintypes option to specify type-mapping for built-in types.

Type-Mapping Modes

JPublisher defines the following type-mapping modes:

JDBC mapping (setting jdbc)--Uses standard default mappings between SQL types and Java native types. This setting is valid for the -numbertypes, -lobtypes, and -builtintypes options.
Oracle mapping (setting oracle)--Uses corresponding oracle.sql types to map to SQL types. This setting is valid for the -numbertypes, -lobtypes, and -builtintypes options.
object-JDBC mapping (setting objectjdbc)--This is an extension of JDBC mapping. Where relevant, object-JDBC mapping uses numeric object types from the standard java.lang package (such as java.lang.Integer, Float, and Double) instead of primitive Java types (such as int, float, and double). The java.lang types are nullable; the primitive types are not. This setting is valid for the -numbertypes option only.
BigDecimal mapping (setting bigdecimal)--Uses java.math.BigDecimal to map to all numeric attributes; appropriate if you are dealing with large numbers but do not want to map to the oracle.sql.NUMBER type. This setting is valid for the -numbertypes option only.

Note:
Using BigDecimal mapping can significantly degrade performance.

The next section discusses type mapping options that you can use for object attributes and collection elements.

Mapping Attribute or Element Types to Java

If you do not specify mappings for the attribute types of a SQL object type or the element types of a SQL collection type, then JPublisher uses the following defaults:

For numeric types, object-JDBC mapping is the default mapping.
For LOB types, Oracle mapping is the default mapping.
For built-in type types, JDBC mapping is the default mapping.

If you want alternate mappings, use the -numbertypes, -lobtypes, and -builtintypes options as necessary, depending on the attribute types you have and the mappings you desire.

If an attribute type is itself a SQL object type, it will be mapped according to the -usertypes setting.

Important:

Be especially aware that if you specify a SQLData implementation for the custom object class and want the code to be portable, you must use portable mappings for the attribute types. The defaults for numeric types and built-in types are portable, but for LOB types you must specify -lobtypes=jdbc.

Summary of SQL Type Categories and Mapping Settings

Table 6-1 summarizes JPublisher categories for SQL types, the mapping settings relevant for each category, and the default settings.

Table 6-1 JPublisher SQL Type Categories, Supported Settings, and Defaults

SQL Type Category	JPublisher Mapping Option	Mapping Settings	Default
UDT types	-usertypes	oracle, jdbc	oracle
numeric types	-numbertypes	oracle, jdbc, objectjdbc, bigdecimal	objectjdbc
LOB types	-lobtypes	oracle, jdbc	oracle
built-in types	-builtintypes	oracle, jdbc	jdbc

Note:

The JPublisher -mapping option used in previous releases is deprecated but still supported. For information about how JPublisher converts -mapping option settings to settings for the new mapping options, see the Oracle9i JPublisher User's Guide.

Generate Wrapper Methods

In creating custom object classes to map Oracle objects to Java, the -methods option instructs JPublisher whether to include Java wrappers for Oracle object methods (member functions). The default -methods=true setting generates wrappers, and also results in JPublisher generating a .sqlj file instead of a .java file for a custom object class (unless the underlying SQL object actually has no methods).

Wrapper methods generated by JPublisher are always instance methods, even when the original object methods are static. See "Custom Java Class Support for Object Methods" for more information.

The following example shows how to set the -methods option:

% jpub -sql=Myobj,mycoll:MyCollClass -user=scott/tiger -methods=true

This will use default naming--the Java method names will be derived in the same fashion as custom Java class names (as described in "Specify User-Defined Types to Map to Java"), except that the initial character will be lowercase. For example, by default an object method name of CALC_SAL results in a Java wrapper method of calcSal().

Alternatively, you can specify desired Java method names, but this requires use of a JPublisher INPUT file and is discussed in "Creating Custom Java Classes and Specifying Member Names".

Note:

The -methods option has additional uses as well, such as for generating wrapper classes for packages, or wrapper methods for package methods. This is beyond the scope of this manual--see the Oracle9i JPublisher User's Guide for information.

Regarding Overloaded Methods

If you run JPublisher for an Oracle object that has an overloaded method where multiple signatures have the same corresponding Java signature, then JPublisher will generate a uniquely named method for each signature. It accomplishes this by appending _n to function names, where n is a number. This is to ensure that no two methods in the generated custom Java class have the same name and signature. Consider, for example, the SQL functions defined in creating a MY_TYPE object type:

CREATE OR REPLACE TYPE my_type AS OBJECT
(
   ...

   MEMBER FUNCTION myfunc(x INTEGER)
      RETURN my_return IS
      BEGIN
         ...
      END;

   MEMBER FUNCTION myfunc(y SMALLINT)
      RETURN my_return IS
      BEGIN
         ...
      END;
   ...
);

6 Objects, Collections, and OPAQUE Types

Oracle Objects and Collections

Introduction to Objects and Collections

Custom Java Class Usage Notes

Terminology Notes

Oracle Object Fundamentals

Oracle Collection Fundamentals

Object and Collection Datatypes

Custom Java Classes

Custom Java Class Interface Specifications

ORAData and ORADataFactory Specifications

ORAData Versus CustomDatum Interfaces

SQLData Specification

Custom Java Class Support for Object Methods

Custom Java Class Requirements

Oracle Requirements for Classes Implementing ORAData

Usage Notes

Requirements for Classes Implementing SQLData

Mapping Specified in Type Map Resource

Mapping Specified in Static Field of Wrapper Class

Compiling Custom Java Classes

Reading and Writing Custom Data

Additional Uses for ORAData Implementations

General Use of ORAData--BetterDate.java

User-Defined Types

Creating Object Types

Creating Collection Types

JPublisher and the Creation of Custom Java Classes

What JPublisher Produces

ORAData Implementation

Strongly Typed Object References for ORAData Implementations

SQLData Implementation

Generating Custom Java Classes

Choose the Implementation for Generated Classes

Specify User-Defined Types to Map to Java

Specify Type Mappings

Categories of SQL Types

Type-Mapping Modes

Mapping Attribute or Element Types to Java

Summary of SQL Type Categories and Mapping Settings

Table 6-1 JPublisher SQL Type Categories, Supported Settings, and Defaults

Generate Wrapper Methods

Regarding Overloaded Methods

6
Objects, Collections, and OPAQUE Types