Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83724-01





Go to previous page Go to beginning of chapter Go to next page

Oracle Type Extensions

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

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.


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 ( ()) 
    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:


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.


    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);
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

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

In the preceding example:

For a full sample application using a REF CURSOR, see "REF".

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:

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




no type extension for wrapper class  




no type extension for wrapper class  




no type extension for wrapper class  




no type extension for wrapper class  








oracle.jdbc.driver.OracleBlob in 8.0.x;
not supported in 7.3.x  




oracle.jdbc.driver.OracleClob in 8.0.x;
not supported in 7.3.x  




oracle.jdbc.driver.OracleBfile in 8.0.x;
not supported in 7.3.x  

structured object  


oracle.sql.STRUCT or custom class  

not supported  

object reference  


oracle.sql.REF or custom class  

not supported  

collection (array)  


oracle.sql.ARRAY or custom class  

not supported  

Go to previous page
Go to beginning of chapter
Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.