Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## Strongly Typed Collections in SQLJ Executable Statements

As with strongly typed objects and references, Oracle SQLJ supports different 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 syntax choices so that nested tables can be accessed and manipulated either apart from or together with their outer tables. In this section, manipulation of a nested table by itself will be referred to as detail-level manipulation; 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 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".

Notes: In Oracle SQLJ, both `VARRAY` types and nested table types can be retrieved only in their entirety. This is as opposed to Oracle SQL, where nested tables can be selectively queried. Any discussion of custom collection classes applies only to `CustomDatum` implementations.

### Accessing Nested Tables--TABLE syntax and CURSOR syntax

Oracle SQLJ 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 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.

Note: This example uses table alias syntax (`a` for `projects`, `b` for the nested table, and `p` for `employees`) as discussed previously. See the Oracle8i SQL Reference for more information about table aliases.

### 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 (`CustomDatum` implementation) 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.)

      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 example below.

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

    }

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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index