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

## Retrieving and Passing an Object Reference

This section discusses JDBC functionality for retrieving and passing object references.

### Retrieving an Object Reference from a Result Set

To demonstrate how to retrieve object references, the following example first defines an Oracle object type `ADDRESS`, which is then referenced in the `PEOPLE` table:

    create type ADDRESS as object
       (street_name     VARCHAR2(30),
        house_no        NUMBER);

    create table PEOPLE
        (col1 VARCHAR2(30),
         col2 NUMBER,
         col3 REF ADDRESS);

The `ADDRESS` object type has two attributes: a street name and a house number. The `PEOPLE` table has three columns: a column for character data, a column for numeric data, and a column containing a reference to an `ADDRESS` object.

To retrieve an object reference, follow these general steps:

1. Use a standard SQL `SELECT` statement to retrieve the reference from a database table `REF` column.

2. Use `getREF()` to get the address reference from the result set into a `REF` object.

3. Let `Address` be the Java custom class corresponding to the SQL object type `ADDRESS`.

4. Add the correspondence between the Java class `Address` and the SQL type `ADDRESS` to your type map.

5. Use the `getValue()` method to retrieve the contents of the `Address` reference. Cast the output to a Java `Address` object.

Here is the code for these steps (other than adding `Address` to the type map), where `stmt` is a previously defined statement object. The `PEOPLE` database table is defined earlier in this section:

    ResultSet rs = stmt.executeQuery("SELECT col3 FROM PEOPLE");
    while (rs.next())
    {
       REF ref = ((OracleResultSet)rs).getREF(1);
       Address a = (Address)ref.getValue();
    }

As with other SQL types, you could retrieve the reference with the `getObject()` method of your result set. Note that this would require you to cast the output. For example:

    REF ref = (REF)rs.getObject(1);

There are no performance advantages in using `getObject()` instead of `getREF()`; however, using `getREF()` allows you to avoid casting the output.

### Retrieving an Object Reference from a Callable Statement

To retrieve an object reference as an `OUT` parameter in PL/SQL blocks, you must register the bind type for your `OUT` parameter.

1. Cast your callable statement to an `OracleCallableStatement`:

       OracleCallableStatement ocs =
          (OracleCallableStatement)conn.prepareCall("{? = call func()}");

2. Register the `OUT` parameter with this form of the `registerOutParameter()` method:

       ocs.registerOutParameter
                   (int param_index, int sql_type, String sql_type_name);

   Where `param_index` is the parameter index and `sql_type` is the SQL typecode (in this case, `OracleTypes.REF`). The `sql_type_name` is the name of the structured object type that this reference is used for. For example, if the `OUT` parameter is a reference to an `ADDRESS` object (as in "Retrieving and Passing an Object Reference"), then `ADDRESS` is the `sql_type_name` that should be passed in.

3. Execute the call:

       ocs.execute();

### Passing an Object Reference to a Prepared Statement

Pass an object reference to a prepared statement in the same way as you would pass any other SQL type. Use either the `setObject()` method or the `setREF()` method of a prepared statement object.

Continuing the example in "Retrieving and Passing an Object Reference", use a prepared statement to update an address reference based on `ROWID`, as follows:

    PreparedStatement pstmt =
       conn.prepareStatement ("update PEOPLE set ADDR_REF = ? where ROWID = ?");
    ((OraclePreparedStatement)pstmt).setREF (1, addr_ref);
    ((OraclePreparedStatement)pstmt).setROWID (2, rowid);

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