Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
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.
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 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 }
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:
getCursor()
method of the OracleCallableStatement
class to materialize the REF CURSOR as a JDBC ResultSet
object.
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:
CallableStatement
object is created by using the prepareCall()
method of the connection class.
OracleTypes.CURSOR
for a REF CURSOR.
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".
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:
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.
ROWID
datatype with the OracleRowid
class in the oracle.jdbc.driver
package.
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.
BLOB
, CLOB
, and BFILE
.
Table 5-3 summarizes these differences. "OracleTypes Definition" refers to static typecode constants defined in the oracle.jdbc.driver.OracleTypes
class.