Strongly Typed Collections in SQLJ Executable Statements

As is the case with strongly typed objects and references, Oracle SQLJ supports various scenarios for reading and writing data through strongly typed collections, using either iterators or host expressions.

From the perspective of a SQLJ developer, both categories of collections--VARRAY and nested table--are treated essentially the same, but there are some differences in implementation and performance.

Oracle SQLJ, and Oracle SQL in general, support various syntax that allows nested tables to be accessed and manipulated either apart from their outer tables or together with their outer tables. In this section, manipulation of a nested table by itself will be referred to as detail-level manipulation, while manipulation of a nested table together with its outer table will be referred to as master-level manipulation.

Most of this section, after a brief discussion of some syntax, focuses on examples of manipulating nested tables, given that their use is somewhat more complicated than that of VARRAYs.

Refer back to the Oracle collection type MODULETBL_T and related tables and object types that were defined in "Creating Collection Types".

For complete nested table sample applications, including one that incorporates the sample code below, see "Oracle Nested Tables--NestedDemo1.sqlj and NestedDemo2.sqlj".

Following the nested table discussion are some brief VARRAY examples. There are also complete VARRAY sample applications, including one that incorporate this code, in "Oracle VARRAYs--VarrayDemo1.sqlj and VarrayDemo2.sqlj".

Accessing Nested Tables--TABLE syntax and CURSOR syntax

Oracle SQLJ supports the use of nested iterators to access the data in nested tables. This involves use of the CURSOR keyword, used 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 SQLJ 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 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.

Inserting a Row that Includes a Nested 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 type MODULETBL_T, 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();
    }
  }

Selecting a Nested Table into a Host Expression

This example presents an operation that works directly at the detail level of the nested table. Recall that ModuletblT is a JPublisher-generated custom collection class for MODULETBL_T nested tables, ModuleT is a JPublisher-generated custom object class for MODULE_T objects, and MODULETBL_T nested tables contain MODULE_T objects.

A nested table of MODULE_T objects is selected from the modules column of the projects table into a ModuletblT host variable.

Following that, the ModuletblT variable (containing the nested table) is passed to a method that accesses its elements through its getArray() method, writing the data to a ModuleT[] array. (All custom collection classes generated by JPublisher include a getArray() method.) Then each element is copied from the ModuleT[] array into a ModuleT object, and individual attributes are retrieved through accessor methods (getModuleName(), for example) and then printed. (All JPublisher-generated custom object classes include such accessor methods.)

Presume the following declaration:

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

Manipulating a Nested Table Using TABLE Syntax

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 for the nested table, and p for the participants table. See the Oracle8i SQL Reference for more information about table aliases.

  /* 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();
    }
  }

Selecting Data from a Nested Table Using a Nested Iterator

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. See the Oracle8i SQL Reference for more information about table aliases.

...

//  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 below:

#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 below 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();
}

Selecting a VARRAY into a Host Expression

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

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

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 JPublisher is used to create a custom collection class PhoneArray to map from the PHONE_ARRAY VARRAY type in the database.

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

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

}

Inserting a Row that Includes a VARRAY

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

---