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

---