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

## Oracle Type Extensions

See other chapters in this book for information about key Oracle type extensions:

- Chapter 7, "Working with LOBs and BFILEs"

- Chapter 8, "Working with Oracle Object Types"

- Chapter 9, "Working with Oracle Object References"

- Chapter 10, "Working with Oracle Collections"

This section covers additional Oracle type extensions and concludes with a discussion of differences between the Oracle8i JDBC drivers and the Oracle 8.0.x and 7.3.x drivers regarding support of Oracle extensions.

Oracle JDBC drivers support the Oracle-specific `BFILE` and `ROWID` datatypes and `REF` `CURSOR` types, which were introduced in Oracle7 and are not part of the standard JDBC specification. This section describes the `ROWID` and `REF CURSOR` type extensions. See Chapter 7 for information about BFILEs.

`ROWID` is supported as a Java string, and `REF` `CURSOR` types are supported as JDBC result sets.

### Oracle ROWID Type

A ROWID is an identification tag unique for each row of an Oracle database table. The ROWID can be thought of as a virtual column, containing the ID for each row.

The `oracle.sql.ROWID` class is supplied as a wrapper for type `ROWID` SQL data.

ROWIDs provide functionality similar to the `getCursorName()` method specified in the `java.sql.ResultSet` interface, and the `setCursorName()` method specified in the `java.sql.Statement` interface.

If you include the ROWID pseudo-column in a query, then you can retrieve the ROWIDs with the result set `getString()` method (passing in either the column index or the column name). You can also bind a ROWID to a `PreparedStatement` parameter with the `setString()` method. This allows in-place updates, as in the example that follows.

Note: The `oracle.sql.ROWID` class replaces `oracle.jdbc.driver.ROWID`, which was used in previous releases of Oracle JDBC.

##### Example: ROWID

The following example shows how to access and manipulate `ROWID` data.

    Statement stmt = conn.createStatement();

    // Query the employee names with "FOR UPDATE" to lock the rows.
    // Select the ROWID to identify the rows to be updated.

    ResultSet rset =
       stmt.executeQuery ("SELECT ename, rowid FROM emp FOR UPDATE");

    // Prepare a statement to update the ENAME column at a given ROWID

    PreparedStatement pstmt =
       conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?");

    // Loop through the results of the query
    while (rset.next ())
    {
        String ename = rset.getString (1);
        oracle.sql.ROWID rowid = rset.getROWID (2);  // Get the ROWID as a String
        pstmt.setString (1, ename.toLowerCase ());
        pstmt.setROWID (2, rowid); // Pass ROWID to the update statement
        pstmt.executeUpdate ();     // Do the update
    }

### Oracle REF CURSOR Type Category

A cursor variable holds the memory location (address) of a query work area, rather than the contents of the area. Declaring a cursor variable creates a pointer. In SQL, a pointer has the datatype `REF` `x` , where `REF` is short for `REFERENCE` and `x` represents the entity being referenced. A REF CURSOR, then, identifies a reference to a cursor variable. Because many cursor variables might exist to point to many work areas, `REF` `CURSOR` can be thought of as a category or "datatype specifier" that identifies many different types of cursor variables.

To create a cursor variable, begin by identifying a type that belongs to the `REF` `CURSOR` category. For example:

    DECLARE TYPE DeptCursorTyp IS REF CURSOR

Then create the cursor variable by declaring it to be of the type `DeptCursorTyp`:

    dept_cv DeptCursorTyp  - - declare cursor variable
    ...

`REF` `CURSOR`, then, is a category of datatypes, rather than a particular datatype.

Stored procedures can return cursor variables of the `REF` `CURSOR` category. This output is equivalent to a database cursor or a JDBC result set. A REF CURSOR essentially encapsulates the results of a query.

In JDBC, ``REF CURSORs are materialized as `ResultSet` objects and can be accessed as follows:

1. Use a JDBC callable statement to call a stored procedure. It must be a callable statement, as opposed to a prepared statement, because there is an output parameter.

2. The stored procedure returns a REF CURSOR.

3. The Java application casts the callable statement to an Oracle callable statement and uses the `getCursor()` method of the `OracleCallableStatement` class to materialize the REF CURSOR as a JDBC `ResultSet` object.

4. The result set is processed as requested.

   Important: Beginning with release 8.1.6, the cursor associated with a REF CURSOR is closed whenever the statement object that produced the REF CURSOR is closed. Unlike in previous releases, the cursor associated with a REF CURSOR is not closed when the result set object in which the REF CURSOR was materialized is closed.

##### Example: Accessing REF CURSOR Data

This example shows how to access REF CURSOR data.

    import oracle.jdbc.driver.*;
    ...
    CallableStatement cstmt;
    ResultSet cursor;

    // Use a PL/SQL block to open the cursor
    cstmt = conn.prepareCall
             ("begin open ? for select ename from emp; end;");

    cstmt.registerOutParameter(1, OracleTypes.CURSOR);
    cstmt.execute();
    cursor = ((OracleCallableStatement)cstmt).getCursor(1);

    // Use the cursor like a normal ResultSet
    while (cursor.next ())
        {System.out.println (cursor.getString(1));}

In the preceding example:

- A `CallableStatement` object is created by using the `prepareCall()` method of the connection class.

- The callable statement implements a PL/SQL procedure that returns a REF CURSOR.

- As always, the output parameter of the callable statement must be registered to define its type. Use the typecode `OracleTypes.CURSOR` for a REF CURSOR.

- The callable statement is executed, returning the REF CURSOR.

- The `CallableStatement` object is cast to an `OracleCallableStatement` object to use the `getCursor()` method, which is an Oracle extension to the standard JDBC API, and returns the REF CURSOR into a `ResultSet` object.

For a full sample application using a REF CURSOR, see "REF CURSORs--RefCursorExample.java".

### Support for Oracle Extensions in 8.0.x and 7.3.x JDBC Drivers

Some of the Oracle type extensions supported by the Oracle8i JDBC drivers are either not supported or are supported differently by the Oracle 8.0.x and 7.3.x JDBC drivers. Following are the key points:

- The 8.0.x and 7.3.x drivers have no `oracle.sql` package, meaning there are no wrapper types such as `oracle.sql.NUMBER` and `oracle.sql.CHAR` that you can use to wrap raw SQL data.

- The 8.0.x and 7.3.x drivers do not support Oracle object and collection types.

- The 8.0.x and 7.3.x drivers support the Oracle `ROWID` datatype with the `OracleRowid` class in the `oracle.jdbc.driver` package.

- The 8.0.x drivers support the Oracle `BLOB`, `CLOB`, and `BFILE` datatypes with the `OracleBlob`, `OracleClob`, and `OracleBfile` classes in the `oracle.jdbc.driver` package. These classes do not include LOB and BFILE manipulation methods--you must instead use the PL/SQL `DBMS_LOB` package.

- The 7.3.x drivers do not support `BLOB`, `CLOB`, and `BFILE`.

Table 6-3 summarizes these differences. "OracleTypes Definition" refers to static typecode constants defined in the `oracle.jdbc.driver.OracleTypes` class.

##### Table 6-3 Support for Oracle Type Extensions, 8.0.x and 7.3.x JDBC Drivers

Oracle Datatype | OracleTypes Definition | Type Extension, 8i Drivers | Type Extension, 8.0.x/7.3.x drivers
--- | --- | --- | ---
NUMBER | OracleTypes.NUMBER | oracle.sql.NUMBER | no type extension for wrapper class
CHAR | OracleTypes.CHAR | oracle.sql.CHAR | no type extension for wrapper class
RAW | OracleTypes.RAW | oracle.sql.RAW | no type extension for wrapper class
DATE | OracleTypes.DATE | oracle.sql.DATE | no type extension for wrapper class
ROWID | OracleTypes.ROWID | oracle.sql.ROWID | oracle.jdbc.driver.OracleRowid
BLOB | OracleTypes.BLOB | oracle.sql.BLOB | oracle.jdbc.driver.OracleBlob in 8.0.x; not supported in 7.3.x
CLOB | OracleTypes.CLOB | oracle.sql.CLOB | oracle.jdbc.driver.OracleClob in 8.0.x; not supported in 7.3.x
BFILE | n/a | oracle.sql.BFILE | oracle.jdbc.driver.OracleBfile in 8.0.x; not supported in 7.3.x
structured object | OracleTypes.STRUCT | oracle.sql.STRUCT or custom class | not supported
object reference | OracleTypes.REF | oracle.sql.REF or custom class | not supported
collection (array) | OracleTypes.ARRAY | oracle.sql.ARRAY or custom class | not supported

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