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 used to perform any kind of desired processing or conversion in the course of transferring data between SQL and Java.

  See Also:

  "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.

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

Oracle Database 12c Release 2 (12.2) includes a set of APIs for Oracle-specific custom Java class functionality for user-defined types: oracle.sql.ORAData and oracle.sql.ORADataFactory.

ORAData and ORADataFactory Specifications

Oracle provides the oracle.sql.ORAData interface and the related oracle.sql.ORADataFactory interface 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, such as oracle.sql.STRUCT. This data is still in its SQL format. The oracle.sql.Datum object is just a wrapper.

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 run time. The ORAData and toDatum() specification is as follows:

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 type code, 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. The ORADataFactory and create() specification is as follows:

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 Oracle JDBC driver to produce further instances of your custom Java class, as necessary.

SQLData Specification

Standard JDBC 2.0 supplies the java.sql.SQLData interface 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 or 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 additional information about standard SQLData functionality, refer to the Sun Microsystems JDBC 2.0 or later API specification.

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

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 the getORADataFactory() method that returns an oracle.sql.ORADataFactory object. The method signature is as follows:

  public static oracle.sql.ORADataFactory getORADataFactory();
  

• The class has a String constant, _SQL_TYPECODE, initialized to the oracle.jdbc.OracleTypes type code of the Datum subclass instance that toDatum() returns. The type code is:

  • 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 type code 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.

  Note:

  The OracleTypes class simply defines a type code, which is an integer constant, for each Oracle data type. For standard SQL types, the OracleTypes entry is identical to the entry in the standard java.sql.Types type definitions class.

  See Also:

  "Serialized Java Objects"

• For custom Java classes with _SQL_TYPECODE of STRUCT, REF, or ARRAY, that is, for custom Java classes that represent objects, object references, or collections, the class has a constant that indicates the relevant user-defined type name. This is as follows:

  • Custom object classes and custom collection classes must have a String constant, _SQL_NAME, initialized to the SQL name you declared for the user-defined type, as follows:

    public static final String _SQL_NAME = UDT name;
    

    For example, the custom object class for a user-defined PERSON object will have the constant:

    public static final String _SQL_NAME = "PERSON";
    

    The same can be specified along with the schema, if appropriate, as follows:

    public static final String _SQL_NAME = "HR.PERSON";
    

    The custom collection class for a collection of PERSON objects, which you have declared as PERSON_ARRAY, will have the constant:

    public static final String _SQL_NAME = "PERSON_ARRAY";
    

  • Custom reference classes must have a String constant, _SQL_BASETYPE, initialized to the SQL name you declared for the user-defined type being referenced, as follows:

    public static final String _SQL_BASETYPE = UDT name;
    

    The custom reference class for PERSON references will have the constant:

    public static final String _SQL_BASETYPE = "PERSON";
    

    For other ORAData uses, specifying a UDT name is not applicable.

Keep in mind the following 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 HR.CaseSensitive.

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 the public static final fields.

Be aware of the following important points:

• Whether you use a type map or use alternative (nonstandard) 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 the public static final fields, or do not use a type map at all and specify all mappings through the public static final fields.

• SQLData, unlike ORAData, is for mapping structured object types only. It is not for object references, collections or 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 java.sql.Ref and java.sql.Array types, respectively, or oracle.sql.REF and oracle.sql.ARRAY.

• SQLData implementations require a Java Development Kit (JDK) 1.4.x or 1.5.x environment.

• When specifying the mapping from a SQL type to a Java type, if the SQL type was declared in a case-sensitive way, then you must specify the SQL name exactly as it was declared, such as CaseSensitive or HR.CaseSensitive.

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.

Then, you need to consider the following:

• You must use these classes only in statements that use explicit connection context instances of a declared connection context type. For example, assuming that this type is called SDContext:

  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 java.util.PropertyResourceBundle. In the preceding example, SDContext may be 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 STRUCT keyword can also be omitted. In the example, the SDMap.properties resource file may contain the following entries:

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

  Although the period (.) separates package and class name, you must use the dollar sign ($) to separate an inner class name.

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

This mechanism of specifying mappings in a type map resource is more complicated than the nonstandard alternative. Also, 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 run-time API and, therefore, cannot be supported by the generic run-time library.

• You must use Oracle SQLJ run time 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 Oracle JDBC driver. Also, 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 when you are mixing SQLJ and JDBC code if 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

A class that implements SQLData can satisfy the following nonstandard requirement:

• The Java class declares the String constant _SQL_NAME, which 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="HR.ADDRESS";
  

  The following declaration would be in pack.Person:

  public static final String _SQL_NAME="PERSON";
  

  And the pack.Manager.InnerPM class would have the following:

  public static final String _SQL_NAME="PRODUCT_MANAGER";
  

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 files for your application. However, this is not necessary if the SQLJ -checksource flag is set to true, which is the default, and your classpath includes the directory where the custom Java source is located.

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

• If -checksource=true 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, then you must do it prior to translating .sqlj files.

Through the use of custom Java class instances, the Oracle SQLJ and JDBC implementations 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, refer to the Oracle Database JDBC Developer's Guide.

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, for example, into or out of RAW fields.

General Use of ORAData: BetterDate.java

This example shows a class that implements the ORAData interface to provide a customized representation of Java dates and can be used instead of java.sql.Date.

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 through 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 in the preceding text) 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();
  }
}

This example uses a custom Java class and a custom reference class as iterator column types. Presume the following definition of the ADDRESS Oracle object type:

CREATE TYPE ADDRESS AS OBJECT
(  street VARCHAR(40),
   zip NUMBER );

And the following definition of the EMPADDRS table, which includes an ADDRESS column and an ADDRESS reference column:

CREATE TABLE empaddrs
(  name VARCHAR(60),
   home ADDRESS,
   loc REF ADDRESS );

Once you create a custom Java class, Address, and custom reference class, AddressRef, corresponding to the ADDRESS Oracle object type, you can use Address and AddressRef in a named iterator as follows:

#sql iterator EmpIter (String name, Address home, AddressRef loc);

...
EmpIter ecur;
#sql ecur = { SELECT name, home, loc FROM empaddrs };
while (ecur.next()) {
   Address homeAddr = ecur.home();
   // Print out the home address.
   System.out.println ("Name: " + ecur.name() + "\n" +
                       "Home address: " + homeAddr.getStreet() + "   " +
                       homeAddr.getZip());
   // Now update the loc address zip code through the address reference.
   AddressRef homeRef = ecur.loc();
   Address location = homeRef.getValue();
   location.setZip(new BigDecimal(98765));
   homeRef.setValue(location);
   }
...

The ecur.home() method call extracts an Address object from the home column of the iterator and assigns it to the homeAddr local variable (for efficiency). The attributes of that object can then be accessed using standard Java dot syntax:

homeAddr.getStreet()

Use the getValue() and setValue() methods to manipulate the location address (in this case its zip code).

This example declares and sets an input host variable of the Address Java type to update an ADDRESS object in a column of the employees table. Both before and after the update, the address is selected into an output host variable of the Address type and printed for verification.

...
// Updating an object 

static void updateObject() 
{

   Address addr;
   Address new_addr;
   int empnum = 1001;

   try {
      #sql {
         SELECT office_addr
         INTO :addr
         FROM employees
         WHERE empnumber = :empnum };
      System.out.println("Current office address of employee 1001:");

      printAddressDetails(addr);

      /* Now update the street of address */

      String street ="100 Oracle Parkway";
      addr.setStreet(street);

      /* Put updated object back into the database */

      try {
         #sql {
            UPDATE employees
            SET office_addr = :addr
            WHERE empnumber = :empnum };
         System.out.println
            ("Updated employee 1001 to new address at Oracle Parkway.");

         /* Select new address to verify update */
      
         try {
            #sql {
               SELECT office_addr
               INTO :new_addr
               FROM employees
               WHERE empnumber = :empnum };
      
            System.out.println("New office address of employee 1001:");
            printAddressDetails(new_addr);

         } catch (SQLException exn) {
         System.out.println("Verification SELECT failed with "+exn); }
      
      } catch (SQLException exn) {
      System.out.println("UPDATE failed with "+exn); }

   } catch (SQLException exn) {
   System.out.println("SELECT failed with "+exn); }
}
...

Note the use of the setStreet() accessor method of the Address object.

This example uses the printAddressDetails() utility. The source code for this method is as follows:

static void printAddressDetails(Address a) throws SQLException
{

  if (a == null)  {
    System.out.println("No Address available.");
    return;
   }

   String street = ((a.getStreet()==null) ? "NULL street" : a.getStreet()) ;
   String city = (a.getCity()==null) ? "NULL city" : a.getCity();
   String state = (a.getState()==null) ? "NULL state" : a.getState();
   String zip_code = (a.getZipCode()==null) ? "NULL zip" : a.getZipCode();

   System.out.println("Street: '" + street + "'");
   System.out.println("City:   '" + city   + "'");
   System.out.println("State:  '" + state  + "'");
   System.out.println("Zip:    '" + zip_code + "'" );
}

This example declares and sets input host variables corresponding to attributes of PERSON and nested ADDRESS objects, then uses these values to insert a new PERSON object into the persons table in the database.

...
// Inserting an object

static void insertObject() 
{
   String new_name   = "NEW PERSON";
   int    new_ssn    = 987654;
   String new_street = "NEW STREET";
   String new_city   = "NEW CITY";
   String new_state  = "NS";
   String new_zip    = "NZIP";
  /*
   * Insert a new PERSON object into the persons table
   */
   try {
      #sql {
         INSERT INTO persons
         VALUES (PERSON(:new_name, :new_ssn,
         ADDRESS(:new_street, :new_city, :new_state, :new_zip))) };

      System.out.println("Inserted PERSON object NEW PERSON."); 

   } catch (SQLException exn) { System.out.println("INSERT failed with "+exn); }
}
...

This example selects a PERSON reference from the persons table and uses it to update a PERSON reference in the employees table. It uses simple input host variables to check attribute value criteria. The newly updated reference is then used in selecting the PERSON object to which it refers, so that information can be output to the user to verify the change.

...
// Updating a REF to an object

static void updateRef()
{
   int empnum = 1001;
   String new_manager = "NEW PERSON";

   System.out.println("Updating manager REF.");
   try {
      #sql {
         UPDATE employees
         SET manager = 
            (SELECT REF(p) FROM persons p WHERE p.name = :new_manager)
         WHERE empnumber = :empnum };

      System.out.println("Updated manager of employee 1001. Selecting back");

   } catch (SQLException exn) {
   System.out.println("UPDATE REF failed with "+exn); }

   /* Select manager back to verify the update */
   Person manager;

   try { 
      #sql {
         SELECT deref(manager)
         INTO :manager
         FROM employees e
         WHERE empnumber = :empnum };

      System.out.println("Current manager of "+empnum+":");
      printPersonDetails(manager);

   } catch (SQLException exn) {
   System.out.println("SELECT REF failed with "+exn); }

}
...

The Oracle SQLJ implementation supports the use of nested iterators to access data in nested tables. Use the CURSOR keyword in the outer SELECT statement to encapsulate the inner SELECT statement. This is shown in "Selecting Data from a Nested Table Using a Nested Iterator".

Oracle also supports use of the TABLE keyword to manipulate the individual rows of a nested table. This keyword informs Oracle that the column value returned by a subquery is a nested table, as opposed to a scalar value. You must prefix the TABLE keyword to a subquery that returns a single column value or an expression that yields a nested table.

The following example shows the use of the TABLE syntax:

UPDATE TABLE(SELECT a.modules FROM projects a WHERE a.id=555) b
       SET module_owner= 
       (SELECT ref(p) FROM employees p WHERE p.ename= 'Smith') 
       WHERE b.module_name = 'Zebra';

When you see TABLE used as it is here, realize that it is referring to a single nested table that has been selected from a column of an outer table.

This example shows an operation that manipulates the master level (outer table) and detail level (nested tables) simultaneously and explicitly. This inserts a row in the projects table, where each row includes a nested table of the MODULETBL_T type, which contains rows of MODULE_T objects.

First, the scalar values are set (id, name, start_date, duration), then the nested table values are set. This involves an extra level of abstraction, because the nested table elements are objects with multiple attributes. In setting the nested table values, each attribute value must be set for each MODULE_T object in the nested table. Finally, the owner values, initially set to null, are set in a separate statement.

// Insert Nested table details along with master details 

  public static void insertProject2(int id)  throws Exception 
  {
    System.out.println("Inserting Project with Nested Table details..");
    try {
      #sql { INSERT INTO Projects(id,name,owner,start_date,duration, modules) 
             VALUES ( 600, 'Ruby', null, '10-MAY-98',  300, 
             moduletbl_t(module_t(6001, 'Setup ', null, '01-JAN-98', 100),
                        module_t(6002, 'BenchMark', null, '05-FEB-98',20) ,
                        module_t(6003, 'Purchase', null, '15-MAR-98', 50),
                        module_t(6004, 'Install', null, '15-MAR-98',44),
                        module_t(6005, 'Launch', null,'12-MAY-98',34))) };
    } catch ( Exception e) {
      System.out.println("Error:insertProject2");
      e.printStackTrace();
    }

    // Assign project owner to this project 

    try {
      #sql { UPDATE Projects pr
          SET owner=(SELECT ref(pa) FROM participants pa WHERE pa.empno = 7698)
         WHERE pr.id=600 };
    } catch ( Exception e) {
      System.out.println("Error:insertProject2:update");
      e.printStackTrace();
    }
  }

This example presents an operation that works directly at the detail level of the nested table.

  static ModuletblT mymodules=null;
  ...

  public static void getModules2(int projId)
  throws Exception 
  {
    System.out.println("Display modules for project " + projId );

    try {
      #sql {SELECT modules INTO :mymodules 
                           FROM projects  WHERE id=:projId };
      showArray(mymodules);
    } catch(Exception e) {
      System.out.println("Error:getModules2");
      e.printStackTrace();
    }
  }

  public static void showArray(ModuletblT a) 
  {
    try {
      if ( a == null )
        System.out.println( "The array is null" );
      else {
        System.out.println( "printing ModuleTable array object of size "
                             +a.length());
        ModuleT[] modules = a.getArray();

        for (int i=0;i<modules.length; i++) {
          ModuleT module = modules[i];
          System.out.println("module "+module.getModuleId()+
                ", "+module.getModuleName()+
                ", "+module.getModuleStartDate()+
                ", "+module.getModuleDuration());
        }
      }
    }
    catch( Exception e ) {
      System.out.println("Show Array");
      e.printStackTrace();
    }
  }

This example uses TABLE syntax to work at the detail level to access and update nested table elements directly, based on master-level criteria.

The assignModule() method selects a nested table of MODULE_T objects from the MODULES column of the PROJECTS table, then updates MODULE_NAME for a particular row of the nested table. Similarly, the deleteUnownedModules() method selects a nested table of MODULE_T objects, then deletes any unowned modules in the nested table, where MODULE_OWNER is null.

These methods use table alias syntax, as discussed previously. In this case, m is used for the nested table, and p is used for the participants table.

  /* assignModule 
     Illustrates accessing the nested table using the TABLE construct 
     and updating the nested table row 
  */
  public static void assignModule(int projId, String moduleName, 
                                  String modOwner) throws Exception 
  {
    System.out.println("Update:Assign '"+moduleName+"' to '"+ modOwner+"'");

    try {
      #sql {UPDATE TABLE(SELECT modules FROM projects WHERE id=:projId) m
            SET m.module_owner=
           (SELECT ref(p) FROM participants p WHERE p.ename= :modOwner) 
            WHERE m.module_name = :moduleName };
    } catch(Exception e) {
      System.out.println("Error:insertModules");
      e.printStackTrace();
    }
  }

  /* deleteUnownedModules 
  // Demonstrates deletion of the Nested table element 
  */

  public static void deleteUnownedModules(int projId)
  throws Exception 
  {
    System.out.println("Deleting Unowned Modules for Project " + projId);
    try {
      #sql { DELETE TABLE(SELECT modules FROM projects WHERE id=:projId) m
             WHERE m.module_owner IS NULL };
    } catch(Exception e) {
      System.out.println("Error:deleteUnownedModules");
      e.printStackTrace();
    }
  }

SQLJ supports the use of nested iterators as a way of accessing nested tables. This requires CURSOR syntax, as used in the following example. The code defines a named iterator class, ModuleIter, then uses that class as the type for a modules column in another named iterator class, ProjIter. Inside a populated ProjIter instance, each modules item is a nested table rendered as a nested iterator.

The CURSOR syntax is part of the nested SELECT statement that populates the nested iterators. Once the data has been selected, it is output to the user through the iterator accessor methods.

This example uses required table alias syntax, as discussed previously. In this case, a for the projects table and b for the nested table.

...

//  The Nested Table is accessed using the ModuleIter 
//  The ModuleIter is defined as Named Iterator 

#sql public static iterator ModuleIter(int moduleId , 
                                       String moduleName , 
                                       String moduleOwner);

// Get the Project Details using the ProjIter defined as 
// Named Iterator. Notice the use of ModuleIter:

#sql public static iterator ProjIter(int id, 
                                     String name, 
                                     String owner, 
                                     Date start_date, 
                                     ModuleIter modules);

...

public static void listAllProjects() throws SQLException
{
  System.out.println("Listing projects...");

   // Instantiate and initialize the iterators 

   ProjIter projs = null;
   ModuleIter  mods = null;
   #sql projs = {SELECT a.id, 
                        a.name, 
                        initcap(a.owner.ename) as "owner", 
                        a.start_date,
                        CURSOR (
                        SELECT b.module_id AS "moduleId",
                               b.module_name AS "moduleName",
                                 initcap(b.module_owner.ename) AS "moduleOwner"
                        FROM TABLE(a.modules) b) AS "modules"  
                 FROM projects a };
  
  // Display Project Details
  
  while (projs.next()) {
    System.out.println( "\n'" + projs.name() + "' Project Id:" 
                + projs.id() + " is owned by " +"'"+ projs.owner() +"'"
                + " start on "  
                + projs.start_date());
              
    // Notice the modules from the ProjIter are assigned to the module
    // iterator variable 

    mods = projs.modules();
    System.out.println ("Modules in this Project are : ");

    // Display Module details 

    while(mods.next()) { 
      System.out.println ("  "+ mods.moduleId() + " '"+ 
                                mods.moduleName() + "' owner is '" +
                                mods.moduleOwner()+"'" );
    }                    // end of modules 
    mods.close();
  }                      // end of projects 
  projs.close();
}

This section provides an example of selecting a VARRAY into a host expression. Presume the following SQL definitions:

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

And presume that you created a PhoneArray custom collection class to map from the PHONE_ARRAY SQL type.

The following method selects a row from this table, placing the data into a host variable of the PhoneArray type:

private static void selectVarray() throws SQLException
{
  PhoneArray ph;
  #sql {select phone_nums into :ph from employees where empnumber=2001};
  System.out.println(
    "there are "+ph.length()+" phone numbers in the PhoneArray.  They are:");

  String [] pharr = ph.getArray();
  for (int i=0;i<pharr.length;++i) 
    System.out.println(pharr[i]);
}

This section provides an example of inserting data from a host expression into a VARRAY, using the same SQL definitions and custom collection class (PhoneArray) as in the previous section.

The following methods populate a PhoneArray instance and use it as a host variable, inserting its data into a VARRAY in the database:

// creates a varray object of PhoneArray and inserts it into a new row
private static void insertVarray() throws SQLException
{
  PhoneArray phForInsert = consUpPhoneArray();
  // clean up from previous demo runs
  #sql {delete from employees where empnumber=2001};
  // insert the PhoneArray object
  #sql {insert into employees (empnumber, phone_nums)
        values(2001, :phForInsert)};
}

private static PhoneArray consUpPhoneArray()
{
  String [] strarr = new String[3];
  strarr[0] = "(510) 555.1111";
  strarr[1] = "(617) 555.2222";
  strarr[2] = "(650) 555.3333";
  return new PhoneArray(strarr);
}

Defining a Type Map for Serializable Classes

Consider an example where SAddress, pack.SPerson, and pack.Manager.InnerSPM, where InnerSPM is an inner class of Manager, are serializable Java classes. In other words, these classes implement the java.io.Serializable interface.

You must use the classes only in statements that use explicit connection context instances of a declared connection context type, such as SerContext in the following example:

SAddress               a =...;
pack.SPerson           p =...;
pack.Manager.InnerSPM pm =...;
SerContext ctx = new SerContext(url,user,pwd,false);
#sql [ctx] { ... :a ... :OUT p ... :INOUT pm ... };

The following is required:

• The connection context type must have been declared using the typeMap attribute of a with clause to specify an associated class implementing java.util.PropertyResourceBundle. In the example, SerContext may be declared as follows.

  #sql public static context SerContext with (typeMap="SerMap");
  

• The type map resource must provide nonstandard mappings from RAW or BLOB columns to the serializable Java classes. This mapping is specified with entries of the following form, depending on whether the Java class is mapped to a RAW or a BLOB column:

  oracle-class.java_class_name=JAVA_OBJECT RAW
  oracle-class.java_class_name=JAVA_OBJECT BLOB
  

  The keyword oracle-class marks this as an Oracle-specific extension. In the example, the SerMap.properties resource file may contain the following entries:

  oracle-class.SAddress=JAVA_OBJECT RAW
  oracle-class.pack.SPerson=JAVA_OBJECT BLOB
  oracle-class.packManager$InnerSPM=JAVA_OBJECT RAW
  

  Although the period (.) separates package and class names, you must use the dollar sign ($) to separate an inner class name.

Note that this Oracle-specific extension can be placed in the same type map resource as standard SQLData type map entries.

Using Fields to Determine Mapping for Serializable Classes

As an alternative to using a type map for a serializable class, you can use static fields in the serializable class to determine type mapping. You can add either of the following fields to a class that implements the java.io.Serializable interface, such as the SAddress and SPerson classes from the preceding example:

public final static int _SQL_TYPECODE = oracle.jdbc.OracleTypes.RAW;

public final static int _SQL_TYPECODE = oracle.jdbc.OracleTypes.BLOB;

Limitations on Serializing Java Objects

You should be aware of the effect of serialization. If two objects, A and B, share the same object, C, then upon serialization and subsequent deserialization of A and B, each will point to its own clone of the object C. Sharing is broken.

In addition, note that for a given Java class, you can declare only one kind of serialization: either into RAW or into BLOB. The SQLJ translator can check only that the actual usage conforms to either RAW or BLOB.

RAW columns are limited in size. You might experience run-time errors if the actual size of the serialized Java object exceeds the size of the column.

Column size is much less restrictive for BLOB columns. Writing a serialized Java object to a BLOB column is supported by Oracle JDBC Oracle Call Interface (OCI) driver and Oracle JDBC Thin driver. Retrieving a serialized object from a BLOB column is supported by all Oracle JDBC drivers since Oracle9i.

Finally, treating serialized Java objects this way is an Oracle-specific extension and requires Oracle SQLJ run time as well as either the default Oracle-specific code generation (-codegen=oracle during translation) or, for ISO standard code generation (-codegen=iso), Oracle-specific profile customization.

10iProd: Note that future versions of Oracle might support SQL types that directly encapsulate Java serialized objects. These are described as JAVA_OBJECT SQL types in JDBC 2.0. At that point, you can replace each of the BLOB and RAW designations by the names of their corresponding JAVA_OBJECT SQL types, and you can drop the oracle- prefix on the entries.

"Additional Uses for ORAData Implementations" includes examples of situations where you might want to define a custom Java class that maps to some oracle.sql.* type other than oracle.sql.STRUCT, oracle.sql.REF, or oracle.sql.ARRAY.

An example of such a situation is if you want to serialize and deserialize Java objects into and out of RAW fields, with a custom Java class that maps to the oracle.sql.RAW type. This could apply equally to BLOB fields, with a custom Java class that maps to the oracle.sql.BLOB type.

This section presents an example of such an application, creating a class, SerializableDatum, that implements the ORAData interface and follows the general form of custom Java classes. The example starts with a step-by-step approach to the development of SerializableDatum, followed by the complete sample code.

SerializableDatum as Host Variable

The following uses a SerializableDatum instance as a host variable:

...
SerializableDatum pinfo = new SerializableDatum();
pinfo.setData (
   new Object[] {"Some objects", new Integer(51), new Double(1234.27) } );
String pname = "MILLER";
#sql { INSERT INTO persondata VALUES(:pname, :pinfo) };
...

SerializableDatum in Iterator Column

Following is an example of using SerializableDatum as a named iterator column:

#sql iterator PersonIter (SerializableDatum info, String name);

...
PersonIter pcur;
#sql pcur = { SELECT * FROM persondata WHERE info IS NOT NULL };
while (pcur.next())
{
   System.out.println("Name:" + pcur.name() + " Info:" + pcur.info());
}
pcur.close();
...

In using Oracle objects, references, or collections in a SQLJ application, you have the option of using generic and weakly typed java.sql or oracle.sql instances instead of the strongly typed custom object, reference, and collection classes that implement the ORAData interface or the strongly typed custom object classes that implement the SQLData interface. Note that if you use SQLData implementations for your custom object classes, then you will have no choice but to use weakly typed custom reference instances.

The following weak types can be used for iterator columns or host expressions in the Oracle SQLJ implementation:

• java.sql.Struct or oracle.sql.STRUCT for objects

• java.sql.Ref or oracle.sql.REF for object references

• java.sql.Array or oracle.sql.ARRAY for collections

In host expressions, they are supported as follows:

• As input host expressions

• As output host expressions in an INTO-list

Using these weak types is not generally recommended, however, as you would lose all the advantages of the strongly typed paradigm that SQLJ offers.

Each attribute in a STRUCT object or each element in an ARRAY object is stored in an oracle.sql.Datum object, with the underlying data being in the form of the appropriate oracle.sql.* subtype of Datum, such as oracle.sql.NUMBER or oracle.sql.CHAR. Attributes in a STRUCT object are nameless. Because of the generic nature of the STRUCT and ARRAY classes, SQLJ cannot perform type checking where objects or collections are written to or read from instances of these classes.

It is generally recommended that you use custom Java classes for objects, references, and collections.

A weakly typed object (Struct or STRUCT instance), reference (Ref or REF instance), or collection (Array or ARRAY instance) cannot be used in host expressions in the following circumstances:

• IN parameter if null

• OUT or INOUT parameter in a stored procedure or function call

• OUT parameter in a stored function result expression

They cannot be used in these ways, because there is no way to know the underlying SQL type name, such as Person, which is required by Oracle JDBC driver to materialize an instance of a user-defined type in Java.