Working with Oracle Object References

This section has these subsections:

• Retrieving an Object Reference

• Passing an Object Reference to a Callable Statement

• Accessing and Updating Object Values through an Object Reference

• Passing an Object Reference to a Prepared Statement

You can define an Oracle object reference to an object stored in an object table. In contrast, you cannot define an object reference for an object value that is stored in a table column.

In SQL, object references (REFs) are strongly typed. For example, a reference to an EMPLOYEE object would be defined as an EMPLOYEE REF, not just a REF.

When you select an object reference in Oracle JDBC, it is materialized as an instance of the oracle.sql.REF class and is not strongly typed. So, if you select an EMPLOYEE REF, an oracle.sql.REF object is returned. To find out what kind of REF it really is, use the object's getBaseTypeName() method. This method returns the object's SQL type, which in this case would be EMPLOYEE.

An object reference is a primitive SQL type. The steps to access and manipulate object references are similar to the steps you employ for any other primitive SQL type.

Note: You cannot have a reference to an array, even though arrays, like objects, are structured types.

JDBC provides support for REFs as any of the following:

• columns in a SELECT list

• IN or OUT bind variables

• attributes in an Oracle8 object

• elements in a collection (array) type object

If you use JPublisher to generate custom Java classes, then it also generates reference classes. These reference classes are extensions of oracle.sql.REF and, unlike the oracle.sql.REF class, are strongly typed. For example, if you define an Oracle object EMPLOYEE, then JPublisher generates an Employee class and an EmployeeRef class.

Retrieving an Object Reference

To demonstrate how to retrieve REFs, the following example first defines an Oracle object type ADDRESS:

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 three steps, 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"); 
rs.next();
REF ref = 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 is no advantage or disadvantage in using getObject() instead of getREF().

Passing an Object Reference to a Callable Statement

To retrieve an object reference as an OUT parameter in PL/SQL blocks, do the following to 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 type code (in this case, OracleTypes.REF). The sql_type_name is the name of the STRUCT to which this object reference points. For example, if the OUT parameter is a REF to an ADDRESS object (as in the previous section), then ADDRESS is the sql_type_name that should be passed in.

3. Execute the call:

   ocs.execute()
   

Accessing and Updating Object Values through an Object Reference

You could then create a Java Address object and update a database ADDRESS object through the reference as follows (omitting whatever would be required for the constructor of the Address class). This example assumes that you have already retrieved a valid REF object:

Address addr = new Address(...);
ref.setValue(addr);

Here, the setValue() method updates the database ADDRESS object.

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 preceding example, 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 = ?"); 
pstmt.setREF (1, addr_ref);
pstmt.setROWID (2, rowid);

---