6
Overview of Oracle Extensions

Oracle's extensions to the JDBC standard include Java packages and interfaces that let you access and manipulate Oracle datatypes and use Oracle performance extensions. Compared to standard JDBC, the extensions offer you greater flexibility in how you can manipulate the data. This chapter presents an overview of the packages and classes included in Oracle's extensions to standard JDBC. It also describes some of the key support features of the extensions.

This chapter includes these topics:

Introduction to Oracle Extensions
Support Features of the Oracle Extensions
Oracle JDBC Packages and Classes
Oracle Character Datatypes Support
Additional Oracle Type Extensions

Note:
This chapter focuses on type extensions, as opposed to performance extensions, which are discussed in detail in Chapter 12, "Performance Extensions".

Introduction to Oracle Extensions

Oracle provides two implementations of its JDBC drivers--one that supports Sun Microsystems JDK versions 1.2.x through 1.4 and complies with the Sun JDBC 2.0 standard, and one that supports JDK 1.1.x and complies with the Sun JDBC 1.22 standard.

Beyond standard features, Oracle JDBC drivers provide Oracle-specific type extensions and performance extensions.

Note:

The JDBC OCI, Thin, and server-side internal drivers support the same functionality and all Oracle extensions.

Both implementations include the following Java packages:

oracle.sql (classes to support all Oracle type extensions)
oracle.jdbc (interfaces to support database access and updates in Oracle type formats)

In addition to these packages, the implementation for JDK 1.1.x includes the following Java package. This package supports some JDBC 2.0 and JDBC 3.0 features by providing interfaces that mimic the new interfaces in the standard java.sql package:

oracle.jdbc2 (interfaces equivalent to standard JDBC 2.0 interfaces)

(For example, oracle.jdbc2.Struct mimics java.sql.Struct, which exists in JDK 1.2.)

"Oracle JDBC Packages and Classes" further describes the preceding packages and their classes.

Support Features of the Oracle Extensions

The Oracle extensions to JDBC include a number of features that enhance your ability to work with Oracle databases. Among these are support for Oracle datatypes, Oracle objects, and specific schema naming.

Support for Oracle Datatypes

A key feature of the Oracle JDBC extensions is the type support in the oracle.sql package. This package includes classes that map to all the Oracle SQL datatypes, acting as wrappers for raw SQL data. This functionality provides two significant advantages in manipulating SQL data:

Accessing data directly in SQL format is more efficient than first converting it to Java format.
Performing mathematical manipulations of the data directly in SQL format avoids the loss of precision that occurs in converting between SQL and Java formats.

Once manipulations are complete and it is time to output the information, each of the oracle.sql.* type support classes has all the necessary methods to convert data to appropriate Java formats. For a more detailed description of these general issues, see "Package oracle.sql".

See the following for more information on specific oracle.sql.* datatype classes:

"Oracle Character Datatypes Support" for information on oracle.sql.* character datatypes which includes the SQL CHAR and SQL NCHAR datatypes
"Additional Oracle Type Extensions" for information on the oracle.sql.* datatype classes for ROWIDs and REF CURSOR types
Chapter 8, "Working with LOBs and BFILEs" for information on oracle.sql.* datatype support for BLOBs, CLOBs, and BFILEs
Chapter 9, "Working with Oracle Object Types" for information on oracle.sql.* datatype support for composite data structures (Oracle objects) in the database
Chapter 10, "Working with Oracle Object References" for information on oracle.sql.* datatype support for object references
Chapter 11, "Working with Oracle Collections" for information on oracle.sql.* datatype support for collections (VARRAYs and nested tables)

Support for Oracle Objects

Oracle JDBC supports the use of structured objects in the database, where an object datatype is a user-defined type with nested attributes. For example, a user application could define an Employee object type, where each Employee object has a firstname attribute (a character string), a lastname attribute (another character string), and an employeenumber attribute (integer).

Oracle's JDBC implementation supports Oracle object datatypes. When you work with Oracle object datatypes in a Java application, you must consider the following:

how to map between Oracle object datatypes and Java classes
how to store Oracle object attributes in corresponding Java objects (they can be stored in standard Java types or in oracle.sql.* types)
how to convert attribute data between SQL and Java formats
how to access data

Oracle objects can be mapped either to the weak java.sql.Struct or oracle.sql.STRUCT types or to strongly typed customized classes. These strong types are referred to as custom Java classes, which must implement either the standard java.sql.SQLData interface or the Oracle extension oracle.sql.ORAData interface. (Chapter 9, "Working with Oracle Object Types" provides more detail regarding these interfaces.) Each interface specifies methods to convert data between SQL and Java.

Note:

The ORAData interface has replaced the CustomDatum interface. While the latter interface is deprecated for Oracle9i, it is still supported for backward compatibility.

To create custom Java classes to correspond to your Oracle objects, Oracle recommends that you use the Oracle9i JPublisher utility to create the classes. To do this, you must define attributes according to how you want to store the data. JPublisher performs this task seamlessly with command-line options and can generate either SQLData or ORAData implementations.

For SQLData implementations, a type map defines the correspondence between Oracle object datatypes and Java classes. Type maps are objects of a special Java class that specify which Java class corresponds to each Oracle object datatype. Oracle JDBC uses these type maps to determine which Java class to instantiate and populate when it retrieves Oracle object data from a result set.

Note:

Oracle recommends using the ORAData interface, instead of the SQLData interface, in situations where portability is not a concern. ORAData works more easily and flexibly in conjunction with other features of the Oracle Java platform offerings.

JPublisher automatically defines getXXX() methods of the custom Java classes, which retrieve data into your Java application. For more information on the JPublisher utility, see the Oracle9i JPublisher User's Guide.

Chapter 9, "Working with Oracle Object Types" describes Oracle JDBC support for Oracle objects.

Support for Schema Naming

Oracle JDBC classes have the ability to accept and return fully qualified schema names. A fully qualified schema name has this syntax:

{[schema_name].}[sql_type_name]

Where schema_name is the name of the schema and sql_type_name is the SQL type name of the object. Notice that schema_name and sql_type_name are separated by a dot (".").

To specify an object type in JDBC, you use its fully qualified name (that is, a schema name and SQL type name). It is not necessary to enter a schema name if the type name is in current naming space (that is, the current schema). Schema naming follows these rules:

Both the schema name and the type name may or may not be quoted. However, if the SQL type name has a dot in it, such as CORPORATE.EMPLOYEE, the type name must be quoted.
The JDBC driver looks for the first unquoted dot in the object's name and uses the string before the dot as the schema name and the string following the dot as the type name. If no dot is found, the JDBC driver takes the current schema as default. That is, you can specify only the type name (without indicating a schema) instead of specifying the fully qualified name if the object type name belongs to the current schema. This also explains why you must quote the type name if the type name has a dot in it.

For example, assume that user Scott creates a type called person.address and then wants to use it in his session. Scott might want to skip the schema name and pass in person.address to the JDBC driver. In this case, if person.address is not quoted, then the dot will be detected, and the JDBC driver will mistakenly interpret person as the schema name and address as the type name.
JDBC passes the object type name string to the database unchanged. That is, the JDBC driver will not change the character case even if it is quoted.

For example, if ScOtT.PersonType is passed to the JDBC driver as an object type name, the JDBC driver will pass the string to the database unchanged. As another example, if there is white space between characters in the type name string, then the JDBC driver will not remove the white space.

OCI Extensions

See Chapter 17, "JDBC OCI Extensions" for the following OCI driver-specific information:

Oracle JDBC Packages and Classes

This section describes the Java packages that support the Oracle JDBC extensions and the key classes that are included in these packages:

You can refer to the Oracle JDBC Javadoc for more information about all the classes mentioned in this section.

Package oracle.sql

The oracle.sql package supports direct access to data in SQL format. This package consists primarily of classes that provide Java mappings to SQL datatypes.

Essentially, the classes act as Java wrappers for the raw SQL data. Because data in an oracle.sql.* object remains in SQL format, no information is lost. For SQL primitive types, these classes simply wrap the SQL data. For SQL structured types (objects and arrays), they provide additional information such as conversion methods and details of structure.

Each of the oracle.sql.* datatype classes extends oracle.sql.Datum, a superclass that encapsulates functionality common to all the datatypes. Some of the classes are for JDBC 2.0-compliant datatypes. These classes, as Table 6-1 indicates, implement standard JDBC 2.0 interfaces in the java.sql package (oracle.jdbc2 for JDK 1.1.x), as well as extending the oracle.sql.Datum class.

Classes of the oracle.sql Package

Table 6-1 lists the oracle.sql datatype classes and their corresponding Oracle SQL types.

Table 6-1 Oracle Datatype Classes

Java Class	Oracle SQL Types and Interfaces Implemented
`oracle.sql.STRUCT`	`STRUCT` (objects) implements `java.sql.Struct` (`oracle.jdbc2.Struct` under JDK 1.1.x)
`oracle.sql.REF`	`REF` (object references) implements `java.sql.Ref` (`oracle.jdbc2.Ref` under JDK 1.1.x)
`oracle.sql.ARRAY`	`VARRAY` or nested table (collections) implements `java.sql.Array` (`oracle.jdbc2.Array` under JDK 1.1.x)
`oracle.sql.BLOB`	`BLOB` (binary large objects) implements java.sql.Blob (`oracle.jdbc2.Blob` under JDK 1.1.x)
`oracle.sql.CLOB`	SQL `CLOB` (character large objects) and globalization support `NCLOB` datatypes both implement `java.sql.Clob` (`oracle.jdbc2.Clob` under JDK 1.1.x)
`oracle.sql.BFILE`	`BFILE` (external files)
`oracle.sql.CHAR`	`CHAR, NCHAR, VARCHAR2, NVARCHAR2`
`oracle.sql.DATE`	`DATE`
`oracle.sql.TIMESTAMP`	`TIMESTAMP`
`oracle.sql.TIMESTAMPTZ`	`TIMESTAMPTZ` (Timestamp with Time Zone)
`oracle.sql.TIMESTAMPLTZ`	`TIMESTAMPLTZ` (Timestamp with Local Time Zone)
`oracle.sql.NUMBER`	`NUMBER`
`oracle.sql.RAW`	`RAW`
`oracle.sql.ROWID`	`ROWID` (row identifiers)
`oracle.sql.OPAQUE`	`OPAQUE`

You can find more detailed information about each of these classes later in this chapter. Additional details about use of the Oracle extended types (STRUCT, REF, ARRAY, BLOB, CLOB, BFILE, and ROWID) are described in the following locations:

"Oracle Character Datatypes Support"
"Additional Oracle Type Extensions"
Chapter 8, "Working with LOBs and BFILEs"
Chapter 9, "Working with Oracle Object Types"
Chapter 10, "Working with Oracle Object References"

Chapter 11, "Working with Oracle Collections"

Notes:

For information about retrieving data from a result set or callable statement object into oracle.sql.* types, as opposed to Java types, see Chapter 7, "Accessing and Manipulating Oracle Data".
The LONG and LONG RAW SQL types and REF CURSOR type category have no oracle.sql.* classes. Use standard JDBC functionality for these types. For example, retrieve LONG or LONG RAW data as input streams using the standard JDBC result set and callable statement methods getBinaryStream() and getCharacterStream(). Use the getCursor() method for REF CURSOR types.

In addition to the datatype classes, the oracle.sql package includes the following support classes and interfaces, primarily for use with objects and collections:

oracle.sql.ArrayDescriptor class: Used in constructing oracle.sql.ARRAY objects; describes the SQL type of the array. (See "Creating ARRAY Objects and Descriptors".)
oracle.sql.StructDescriptor class: Used in constructing oracle.sql.STRUCT objects, which you can use as a default mapping to Oracle objects in the database. (See "Creating STRUCT Objects and Descriptors".)
oracle.sql.ORAData and oracle.sql.ORADataFactory interfaces: Used in Java classes implementing the Oracle ORAData scenario of Oracle object support. (The other possible scenario is the JDBC-standard SQLData implementation.) See "Understanding the ORAData Interface" for more information on ORAData.
oracle.sql.OpaqueDescriptor class: Used to obtain the meta data for an instance of the oracle.sql.OPAQUE class.

General oracle.sql.* Datatype Support

Each of the Oracle datatype classes provides, among other things, the following:

one or more constructors, typically with a constructor that uses raw bytes as input and a constructor that takes a Java type as input
data storage as Java byte arrays for SQL data
a getBytes() method, which returns the SQL data as a byte array (in the raw format in which JDBC received the data from the database)
a toJdbc() method that converts the data into an object of a corresponding Java class as defined in the JDBC specification

The JDBC driver does not convert Oracle-specific datatypes that are not part of the JDBC specification, such as ROWID; the driver returns the object in the corresponding oracle.sql.* format. For example, it returns an Oracle ROWID as an oracle.sql.ROWID.
appropriate xxxValue() methods to convert SQL data to Java typed--for example: stringValue(), intValue(), booleanValue(), dateValue(), bigDecimalValue()
additional conversion, getXXX() and setXXX() methods as appropriate for the functionality of the datatype (such as methods in the LOB classes that get the data as a stream, and methods in the REF class that get and set object data through the object reference)

Refer to the Oracle JDBC Javadoc for additional information about these classes. See "Class oracle.sql.CHAR" to learn how the oracle.sql.CHAR class supports character data.

Overview of Class oracle.sql.STRUCT

For any given Oracle object type, it is usually desirable to define a custom mapping between SQL and Java. (If you use a SQLData custom Java class, the mapping must be defined in a type map.)

If you choose not to define a mapping, however, then data from the object type will be materialized in Java in an instance of the oracle.sql.STRUCT class.

The STRUCT class implements the standard JDBC 2.0 java.sql.Struct interface (oracle.jdbc2.Struct under JDK 1.1.x) and extends the oracle.sql.Datum class.

In the database, Oracle stores the raw bytes of object data in a linearized form. A STRUCT object is a wrapper for the raw bytes of an Oracle object. It contains the SQL type name of the Oracle object and a "values" array of oracle.sql.Datum objects that hold the attribute values in SQL format.

You can materialize a STRUCT's attributes as oracle.sql.Datum[] objects if you use the getOracleAttributes() method, or as java.lang.Object[] objects if you use the getAttributes() method. Materializing the attributes as oracle.sql.* objects gives you all the advantages of the oracle.sql.* format:

Materializing oracle.sql.STRUCT data in oracle.sql.* format completely preserves data by maintaining it in SQL format. No translation is performed. This is useful if you want to access data but not necessarily display it.

It allows complete flexibility in how your Java application unpacks data.

Notes:

Elements of the values array, although of the generic Datum type, actually contain data associated with the relevant oracle.sql.* type appropriate for the given attribute. You can cast the element to the appropriate oracle.sql.* type as desired. For example, a CHAR data attribute within the STRUCT is materialized as oracle.sql.Datum. To use it as CHAR data, you must cast it to the oracle.sql.CHAR type.
Nested objects in the values array of a STRUCT object are materialized by the JDBC driver as instances of STRUCT.

In some cases, you might want to manually create a STRUCT object and pass it to a prepared statement or callable statement. To do this, you must also create a StructDescriptor object.

For more information about working with Oracle objects using the oracle.sql.STRUCT and StructDescriptor classes, see "Using the Default STRUCT Class for Oracle Objects".

Overview of Class oracle.sql.REF

The oracle.sql.REF class is the generic class that supports Oracle object references. This class, as with all oracle.sql.* datatype classes, is a subclass of the oracle.sql.Datum class. It implements the standard JDBC 2.0 java.sql.Ref interface (oracle.jdbc2.Ref under JDK 1.1.x).

The REF class has methods to retrieve and pass object references. Be aware, however, that selecting an object reference retrieves only a pointer to an object. This does not materialize the object itself. But the REF class also includes methods to retrieve and pass the object data.

You cannot create REF objects in your JDBC application--you can only retrieve existing REF objects from the database.

For more information about working with Oracle object references using the oracle.sql.REF class, see Chapter 10, "Working with Oracle Object References".

Overview of Class oracle.sql.ARRAY

The oracle.sql.ARRAY class supports Oracle collections--either VARRAYs or nested tables. If you select either a VARRAY or nested table from the database, then the JDBC driver materializes it as an object of the ARRAY class; the structure of the data is equivalent in either case. The oracle.sql.ARRAY class extends oracle.sql.Datum and implements the standard JDBC 2.0 java.sql.Array interface (oracle.jdbc2.Array under JDK 1.1.x).

You can use the setARRAY() method of the OraclePreparedStatement or OracleCallableStatement class to pass an array as an input parameter to a prepared statement. Similarly, you might want to manually create an ARRAY object to pass it to a prepared statement or callable statement, perhaps to insert into the database. This involves the use of ArrayDescriptor objects.

For more information about working with Oracle collections using the oracle.sql.ARRAY and ArrayDescriptor classes, see "Overview of Collection (Array) Functionality".

Overview of Classes oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE

BLOBs and CLOBs (referred to collectively as "LOBs"), and BFILEs (for external files) are for data items that are too large to store directly in a database table. Instead, the database table stores a locator that points to the location of the actual data.

The oracle.sql package supports these datatypes in several ways:

BLOBs point to large unstructured binary data items and are supported by the oracle.sql.BLOB class.
CLOBs point to large fixed-width character data items (that is, characters that require a fixed number of bytes per character) and are supported by the oracle.sql.CLOB class.
BFILEs point to the content of external files (operating system files) and are supported by the oracle.sql.BFILE class.

You can select a BLOB, CLOB, or BFILE locator from the database using a standard SELECT statement, but bear in mind that you are receiving only the locator, not the data itself. Additional steps are necessary to retrieve the data.

For information about how to access and manipulate locators and data for LOBs and BFILEs, see Chapter 8, "Working with LOBs and BFILEs".

Classes oracle.sql.DATE, oracle.sql.NUMBER, and oracle.sql.RAW

These classes map to primitive SQL datatypes, which are a part of standard JDBC, and supply conversions to and from the corresponding JDBC Java types. For more information, see the Javadoc.

Classes oracle.sql.TIMESTAMP, oracle.sql.TIMESTAMPTZ, and oracle.sql.TIMESTAMPLTZ

The Oracle9i JDBC drivers support the following date/time datatypes:

Timestamp (TS)
Timestamp with Time Zone (TSTZ)
TIMESTAMP with Local Time Zone (TSLTZ)

Oracle9i JDBC drivers allow conversions among DATE and date/time datatypes. For example, you can access a TIMESTAMPTZ column as a DATE value.

Oracle9i JDBC drivers support the most popular time zone names used in the industry as well as most of the time zone names defined in the JDK from Sun Microsystems. Time zones are specified by using the java.util.Calendar class.

Note:

Do not use TimeZone.getTimeZone() to create timezone objects; the Oracle timezone datatypes support more time zone names than does the JDK.

The following code shows how the TimeZone and Calendar objects are created for US_PACIFIC, which is a time zone name not defined in the JDK:

TimeZone tz = TimeZone.getDefault();
tz.setID("US_PACIFIC");
GregorianCalendar gcal = new GregorianCalendar(tz);

The following Java classes represent the SQL date/time types:

oracle.sql.TIMESTAMP
oracle.sql.TIMESTAMPTZ
oracle.sql.TIMESTAMPLTZ

Use the following methods from the oracle.jdbc.OraclePreparedStatement interface to set a date/time:

setTIMESTAMP(int paramIdx,TIMESTAMP x)
setTIMESTAMPTZ(int paramIdx,TIMESTAMPTZ x)
setTIMESTAMPLTZ(int paramIdx,TIMESTAMPLTZ x)

Use the following methods from the oracle.jdbc.OracleCallableStatement interface to get a date/time:

TIMESTAMP getTIMESTAMP (int paramIdx)
TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)
TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)

Use the following methods from the oracle.jdbc.OracleResultSet interface to get a date/time:

TIMESTAMP getTIMESTAMP(int paramIdx)
TIMESTAMP getTIMESTAMP(java.lang.String colName)
TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)
TIMESTAMPTZ getTIMESTAMPTZ(java.lang.String colName)
TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)
TIMESTAMPLTZ getTIMESTAMPLTZ(java.lang.String colName)
TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)

Use the following methods from the oracle.jdbc.OracleResultSet interface to update a date/time:

updateTIMESTAMP(int paramIdx)
updateTIMESTAMPTZ(int paramIdx)
updateTIMESTAMPLTZ(int paramIdx)

Before accessing TIMESTAMPLTZ data, call the OracleConnection.setSessionTime() method to set the session time zone. When this method is called, the JDBC driver sets the session time zone of the connection and saves the session time zone so that any TIMESTAMPLTZ data accessed through JDBC can be adjusted using the session time zone.

Overview of Class oracle.sql.ROWID

This class supports Oracle ROWIDs, which are unique identifiers for rows in database tables. You can select a ROWID as you would select any column of data from the table. Note, however, that you cannot manually update ROWIDs--the Oracle database updates them automatically as appropriate.

The oracle.sql.ROWID class does not implement any noteworthy functionality beyond what is in the oracle.sql.Datum superclass. However, ROWID does provide a stringValue() method that overrides the stringValue() method in the oracle.sql.Datum class and returns the hexadecimal representation of the ROWID bytes.

For information about accessing ROWID data, see "Oracle ROWID Type".

Class oracle.sql.OPAQUE

The oracle.sql.OPAQUE class gives you the name and characteristics of the OPAQUE type and any attributes. OPAQUE types provide access only to the uninterrupted bytes of the instance.

Note:

For Oracle9i 9.0.1, there is minimal support for OPAQUE types.

The following are the methods of the oracle.sql.OPAQUE class:

getBytesValue(): Returns a byte array that represents the value of the OPAQUE object, in the format used in the database.
public boolean isConvertibleTo(Class jClass): Determines if a Datum object can be converted to a particular class, where Class is any class and jClass is the class to convert. true is returned if conversion to jClass is permitted and false if conversion to jClass is not permitted.
getDescriptor(): Returns the OpaqueDescriptor object that contains the type information.
getJavaSqlConnection(): Returns the connection associated with the receiver. Because methods that use the oracle.jdbc.driver package are deprecated, the getConnection() method has been deprecated in favor of the getJavaSqlConnection() method.
getSQLTypeName(): Implements the java.sql.Struct interface function and retrieves the SQL type name of the SQL structured type that this Struct object represents. This method returns the fully-qualified type name of the SQL structured type which this STRUCT object represents.
getValue(): Returns a Java object that represents the value (raw bytes).
toJdbc(): Returns the JDBC representation of the Datum object.

Package oracle.jdbc

The interfaces of the oracle.jdbc package provide Oracle-specific extensions to allow access to raw SQL format data by using oracle.sql.* objects.

Note:

The interfaces of the oracle.jdbc package replace the deprecated classes of the oracle.jdbc.driver package found in previous releases. (See "Package oracle.jdbc" for more information.)

For the oracle.jdbc package, Table 6-2 lists key interfaces and classes used for connections, statements, and result sets.

Table 6-2 Key Interfaces and Classes of the oracle.jdbc Package

Name	Interface or Class	Key Functionality
`OracleDriver`	Class	implements java.sql.Driver
`OracleConnection`	Interface	methods to return Oracle statement objects; methods to set Oracle performance extensions for any statement executed in the current connection (implements java.sql.Connection)
`OracleStatement`	Interface	methods to set Oracle performance extensions for individual statement; superclass of OraclePreparedStatement and OracleCallableStatement (implements java.sql.Statement)
`OraclePreparedStatement`	Interface	setXXX() methods to bind oracle.sql.* types into a prepared statement (implements java.sql.PreparedStatement; extends OracleStatement; superclass of OracleCallableStatement)
`OracleCallableStatement`	Interface	getXXX() methods to retrieve data in oracle.sql format; setXXX() methods to bind oracle.sql.* types into a callable statement (implements java.sql.CallableStatement; extends OraclePreparedStatement)
`OracleResultSet`	Interface	getXXX() methods to retrieve data in oracle.sql format (implements java.sql.ResultSet)
`OracleResultSetMetaData`	Interface	methods to get meta information about Oracle result sets, such as column names and datatypes (implements java.sql.ResultSetMetaData)
`OracleDatabaseMetaData`	Class	methods to get meta information about the database, such as database product name/version, table information, and default transaction isolation level (implements java.sql.DatabaseMetaData)
OracleTypes	Class	defines integer constants used to identify SQL types. For standard types, it uses the same values as the standard java.sql.Types class. In addition, it adds constants for Oracle extended types.

The remainder of this section describes the interfaces and classes of the oracle.jdbc package. For more information about using these interfaces and classes to access Oracle type extensions, see Chapter 7, "Accessing and Manipulating Oracle Data".

Class oracle.jdbc.OracleDriver

Use this class to register the Oracle JDBC drivers for use by your application. You can input a new instance of this class to the static registerDriver() method of the java.sql.DriverManager class so that your application can access and use the Oracle drivers. The registerDriver() method takes as input a "driver" class, that is, a class that implements the java.sql.Driver interface, as is the case with OracleDriver.

Once you register the Oracle JDBC drivers, you can create your connection using the DriverManager class. For more information on registering drivers and writing a connection string, see "First Steps in JDBC".

Interface oracle.jdbc.OracleConnection

This interface extends standard JDBC connection functionality to create and return Oracle statement objects, set flags and options for Oracle performance extensions, support type maps for Oracle objects, and support client identifiers.

"Additional Oracle Performance Extensions" describes the performance extensions, including row prefetching, update batching, and metadata TABLE_REMARKS reporting.

Client Identifiers

In a connection pooling environment, the client identifier can be used to identify which light-weight user is currently using the database session. A client identifier can also be used to share the Globally Accessed Application Context between different database sessions. The client identifier set in a database session is audited when database auditing is turned on.

Note:

See the Oracle9i Application Developer's Guide - Fundamentals for a full discussion of Globally Accessed Contexts.

Key methods include:

createStatement(): Allocates a new OracleStatement object.
prepareStatement(): Allocates a new OraclePreparedStatement object.
prepareCall(): Allocates a new OracleCallableStatement object.
getTypeMap(): Retrieves the type map for this connection (for use in mapping Oracle object types to Java classes).
setTypeMap(): Initializes or updates the type map for this connection (for use in mapping Oracle object types to Java classes).
getTransactionIsolation(): Gets this connection's current isolation mode.
setTransactionIsolation(): Changes the transaction isolation level using one of the TRANSACTION_* values.

These oracle.jdbc.OracleConnection methods are Oracle-defined extensions:

setClientIdentifier(): Sets the client identifier for this connection.
clearClientIdentifier(): Clears the client identifier for this connection.
getDefaultExecuteBatch(): Retrieves the default update-batching value for this connection.
setDefaultExecuteBatch(): Sets the default update-batching value for this connection.
getDefaultRowPrefetch(): Retrieves the default row-prefetch value for this connection.
setDefaultRowPrefetch(): Sets the default row-prefetch value for this connection.
getRemarksReporting(): Returns true if TABLE_REMARKS reporting is enabled.
setRemarksReporting(): Enables or disables TABLE_REMARKS reporting.

Interface oracle.jdbc.OracleStatement

This interface extends standard JDBC statement functionality and is the superinterface of the OraclePreparedStatement and OracleCallableStatement classes. Extended functionality includes support for setting flags and options for Oracle performance extensions on a statement-by-statement basis, as opposed to the OracleConnection interface that sets these on a connection-wide basis.

"Additional Oracle Performance Extensions" describes the performance extensions, including row prefetching and column type definitions.

Key methods include:

executeQuery(): Executes a database query and returns an OracleResultSet object.
getResultSet(): Retrieves an OracleResultSet object.
close(): Closes the current statement.

These oracle.jdbc.OracleStatement methods are Oracle-defined extensions:

defineColumnType(): Defines the type you will use to retrieve data from a particular database table column.
getRowPrefetch(): Retrieves the row-prefetch value for this statement.
setRowPrefetch(): Sets the row-prefetch value for this statement.

Interface oracle.jdbc.OraclePreparedStatement

This interface extends the OracleStatement interface and extends standard JDBC prepared statement functionality. Also, the oracle.jdbc.OraclePreparedStatement interface is extended by the OracleCallableStatement interface. Extended functionality consists of setXXX() methods for binding oracle.sql.* types and objects into prepared statements, and methods to support Oracle performance extensions on a statement-by-statement basis.

"Additional Oracle Performance Extensions" describes the performance extensions, including database update batching.

Key methods include:

getExecuteBatch(): Retrieves the update-batching value for this statement.
setExecuteBatch(): Sets the update-batching value for this statement.
setOracleObject(): This is a generic setXXX() method for binding oracle.sql.* data into a prepared statement as an oracle.sql.Datum object.
setXXX(): These methods, such as setBLOB(), are for binding specific oracle.sql.* types into prepared statements.
setORAData(): Binds an ORAData object (for use in mapping Oracle object types to Java) into a prepared statement.
setNull(): Sets the value of the object specified by its SQL type name to NULL. For setNull(param_index, type_code, sql_type_name), if type_code is REF, ARRAY, or STRUCT, then sql_type_name is the fully qualified name (schema.sql_type_name) of the SQL type.
setFormOfUse(): Sets which form of use this method is going to use. There are two constants that specify the form of use: FORM_CHAR and FORM_NCHAR, where FORM_CHAR is the default. If the form of use is set to FORM_NCHAR, the JDBC driver will represent the provided data in the national character set of the server. The following code show how the FORM_NCHAR is used:
```
pstmt.setFormOfUse 
 (parameter index,
 oracle.jdbc.OraclePreparedStatement.FORM_NCHAR)
```
close(): Closes the current statement.

Interface oracle.jdbc.OracleCallableStatement

This interface extends the OraclePreparedStatement interface (which extends the OracleStatement interface) and incorporates standard JDBC callable statement functionality.

Key methods include:

getOracleObject(): This is a generic getXXX() method for retrieving data into an oracle.sql.Datum object, which can be cast to the specific oracle.sql.* type as necessary.
getXXX(): These methods, such as getCLOB(), are for retrieving data into specific oracle.sql.* objects.
setOracleObject(): This is a generic setXXX() method for binding oracle.sql.* data into a callable statement as an oracle.sql.Datum object.
setXXX(): These methods, such as setBLOB(), are inherited from OraclePreparedStatement for binding specific oracle.sql.* objects into callable statements.
setNull(): Sets the value of the object specified by its SQL type name to NULL. For setNull(param_index, type_code, sql_type_name), if type_code is REF, ARRAY, or STRUCT, then sql_type_name is the fully qualified (schema.type) name of the SQL type.
setFormOfUse(): Sets which form of use this method is going to use. There are two constants that specify the form of use: FORM_CHAR and FORM_NCHAR, where FORM_CHAR is the default. If the form of use is set to FORM_NCHAR, the JDBC driver will represent the provided data in the national character set of the server. The following code show how the FORM_NCHAR is used:
```
pstmt.setFormOfUse 
 (parameter index,
 oracle.jdbc.OraclePreparedStatement.FORM_NCHAR)
```
registerOutParameter(): Registers the SQL typecode of the statement's output parameter. JDBC requires this for any callable statement with an OUT parameter. It takes an integer parameter index (the position of the output variable in the statement, relative to the other parameters) and an integer SQL type (the type constant defined in oracle.jdbc.OracleTypes).

This is an overloaded method. One version of this method is for named types only--when the SQL typecode is OracleTypes.REF, STRUCT, or ARRAY. In this case, in addition to a parameter index and SQL type, the method also takes a String SQL type name (the name of the Oracle user-defined type in the database, such as EMPLOYEE).
close(): Closes the current result set, if any, and the current statement.

Interface oracle.jdbc.OracleResultSet

This interface extends standard JDBC result set functionality, implementing getXXX() methods for retrieving data into oracle.sql.* objects.

Key methods include:

getOracleObject(): This is a generic getXXX() method for retrieving data into an oracle.sql.Datum object. It can be cast to the specific oracle.sql.* type as necessary.
getXXX(): These methods, such as getCLOB(), are for retrieving data into oracle.sql.* objects.

Interface oracle.jdbc.OracleResultSetMetaData

This interface extends standard JDBC result set metadata functionality to retrieve information about Oracle result set objects. See "Using Result Set Meta Data Extensions" for information on the functionality of the OracleResultSetMetadata interface.

Class oracle.jdbc.OracleTypes

The OracleTypes class defines constants that JDBC uses to identify SQL types. Each variable in this class has a constant integer value. The oracle.jdbc.OracleTypes class duplicates the typecode definitions of the standard Java java.sql.Types class and contains these additional typecodes for Oracle extensions:

OracleTypes.BFILE
OracleTypes.ROWID
OracleTypes.CURSOR (for REF CURSOR types)

As in java.sql.Types, all the variable names are in all-caps.

JDBC uses the SQL types identified by the elements of the OracleTypes class in two main areas: registering output parameters, and in the setNull() method of the PreparedStatement class.

OracleTypes and Registering Output Parameters

The typecodes in java.sql.Types or oracle.jdbc.OracleTypes identify the SQL types of the output parameters in the registerOutParameter() method of the java.sql.CallableStatement interface and oracle.jdbc.OracleCallableStatement interface.

These are the forms that registerOutputParameter() can take for CallableStatement and OracleCallableStatement (assume a standard callable statement object cs):

cs.registerOutParameter(int index, int sqlType);

cs.registerOutParameter(int index, int sqlType, String sql_name);

cs.registerOutParameter(int index, int sqlType, int scale);

In these signatures, index represents the parameter index, sqlType is the typecode for the SQL datatype, sql_name is the name given to the datatype (for user-defined types, when sqlType is a STRUCT, REF, or ARRAY typecode), and scale represents the number of digits to the right of the decimal point (when sqlType is a NUMERIC or DECIMAL typecode).

Note:

The second signature is standard under JDBC 2.0 in a JDK 1.2.x environment, but is an Oracle extension under JDK 1.1.x.

The following example uses a CallableStatement to call a procedure named charout, which returns a CHAR datatype. Note the use of the OracleTypes.CHAR typecode in the registerOutParameter() method (although java.sql.Types.CHAR could have been used as well).

CallableStatement cs = conn.prepareCall ("BEGIN charout (?); END;");
cs.registerOutParameter (1, OracleTypes.CHAR);
cs.execute ();
System.out.println ("Out argument is: " + cs.getString (1));

The next example uses a CallableStatement to call structout, which returns a STRUCT datatype. The form of registerOutParameter() requires you to specify the typecode (Types.STRUCT or OracleTypes.STRUCT), as well as the SQL name (EMPLOYEE).

The example assumes that no type mapping has been declared for the EMPLOYEE type, so it is retrieved into a STRUCT datatype. To retrieve the value of EMPLOYEE as an oracle.sql.STRUCT object, the statement object cs is cast to an OracleCallableStatement and the Oracle extension getSTRUCT() method is invoked.

CallableStatement cs = conn.prepareCall ("BEGIN structout (?); END;");
cs.registerOutParameter (1, OracleTypes.STRUCT, "EMPLOYEE");
cs.execute ();

// get the value into a STRUCT because it 
// is assumed that no type map has been defined
STRUCT emp = ((OracleCallableStatement)cs).getSTRUCT (1);

OracleTypes and the setNull() Method

The typecodes in Types and OracleTypes identify the SQL type of the data item, which the setNull() method sets to NULL. The setNull() method can be found in the java.sql.PreparedStatement interface and the oracle.jdbc.OraclePreparedStatement interface.

These are the forms that setNull() can take for PreparedStatement and OraclePreparedStatement objects (assume a standard prepared statement object ps):

ps.setNull(int index, int sqlType);

ps.setNull(int index, int sqlType, String sql_name);

In these signatures, index represents the parameter index, sqlType is the typecode for the SQL datatype, and sql_name is the name given to the datatype (for user-defined types, when sqlType is a STRUCT, REF, or ARRAY typecode). If you enter an invalid sqlType, a Parameter Type Conflict exception is thrown.

Note:

The second signature is standard under JDBC 2.0 in a JDK 1.2.x environment, but is an Oracle extension under JDK 1.1.x.

The following example uses a PreparedStatement to insert a NULL numeric value into the database. Note the use of OracleTypes.NUMERIC to identify the numeric object set to NULL (although Types.NUMERIC could have been used as well).

PreparedStatement pstmt =
    conn.prepareStatement ("INSERT INTO num_table VALUES (?)");

pstmt.setNull (1, OracleTypes.NUMERIC);
pstmt.execute ();

In this example, the prepared statement inserts a NULL STRUCT object of type EMPLOYEE into the database.

PreparedStatement pstmt = conn.prepareStatement 
                               ("INSERT INTO employee_table VALUES (?)");

pstmt.setNull (1, OracleTypes.STRUCT, "EMPLOYEE");
pstmt.execute ();

Oracle Interfaces for Oracle-specific Features

The oracle.jdbc interfaces introduced in Oracle9i are recommended alternatives to the classes by the same name in the oracle.jdbc.driver package in older releases. These interfaces essentially duplicate the functionality in the oracle.jdbc.driver package.

The following example shows how the oracle.jdbc package is used to cast pstmt as an Oracle type:

java.sql.PreparedStatement pstmt
  = conn.prepareStatement(...);

((oracle.jdbc.OraclePreparedStatement) pstmt)
  .setExecuteBatch(10);   // Oracle-specific method

Method getJavaSqlConnection()

The getJavaSqlConnection() method of the oracle.sql.* classes returns java.sql.Connection while the getConnection() method returns oracle.jdbc.driver.OracleConnection. Because the methods that use the oracle.jdbc.driver package are deprecated, the getConnection() method is also deprecated in favor of the getJavaSqlConnection() method.

For the following Oracle datatype classes, the getJavaSqlConnection() method was added:

oracle.sql.ARRAY
oracle.sql.BFILE
oracle.sql.BLOB
oracle.sql.CLOB
oracle.sql.OPAQUE
oracle.sql.REF
oracle.sql.STRUCT

The following shows the getJavaSqlConnection() and the getConnection() methods in the Array class:

public class ARRAY
{
  // New API
  //
  java.sql.Connection getJavaSqlConnection()
    throws SQLException;

  // Deprecated API.
  //
  oracle.jdbc.driver.OracleConnection
    getConnection() throws SQLException;

  ...
}

Package oracle.jdbc2 (for JDK 1.1.x only)

The oracle.jdbc2 package is an Oracle implementation for use with JDK 1.1.x, containing classes and interfaces that mimic a subset of standard JDBC 2.0 classes and interfaces (which exist in the JDK 1.2 version of the standard java.sql package).

The following interfaces are implemented by oracle.sql.* type classes for JDBC 2.0-compliant Oracle type extensions under JDK 1.1.x.

oracle.jdbc2.Array is implemented by oracle.sql.ARRAY
oracle.jdbc2.Struct is implemented by oracle.sql.STRUCT
oracle.jdbc2.Ref is implemented by oracle.sql.REF
oracle.jdbc2.Clob is implemented by oracle.sql.CLOB
oracle.jdbc2.Blob is implemented by oracle.sql.BLOB

In addition, the oracle.jdbc2 package includes the following interfaces for users employing the JDBC-standard SQLData interface to create Java classes that map to Oracle objects. Again, these interfaces mimic java.sql interfaces available with JDK 1.2:

oracle.jdbc2.SQLData is implemented by classes that map to Oracle objects; users must provide this implementation
oracle.jdbc2.SQLInput is implemented by classes that read object data; Oracle provides a SQLInput class that the JDBC drivers use
oracle.jdbc2.SQLOutput is implemented by classes that write object data; Oracle provides a SQLOutput class that the JDBC drivers use

The SQLData interface is one of the two facilities you can use to support Oracle objects in Java. (The other choice is the Oracle ORAData interface, included in the oracle.sql package.) See "Understanding the SQLData Interface" for more information about SQLData, SQLInput, and SQLOutput.

Oracle Character Datatypes Support

Oracle character datatypes include the SQL CHAR and SQL NCHAR datatypes. The following sections describe how these datatypes can be accessed using the Oracle JDBC drivers.

SQL CHAR Datatypes

The SQL CHAR datatypes include CHAR, VARCHAR2, and CLOB. These datatypes allow you to store character data in the database character set encoding scheme. The character set of the database is established when you create the database.

SQL NCHAR Datatypes

SQL NCHAR datatypes were created for Globalization Support (formerly NLS). SQL NCHAR datatypes include NCHAR, NVARCHAR2, and NCLOB. These datatypes allow you to store unicode data in the database NCHAR character set encoding. The NCHAR character set, which never changes, is established when you create the database. See the Oracle9i Database Globalization Support Guide for information on SQL NCHAR datatypes.

Note:

Because the UnicodeStream class is deprecated in favor of the CharacterStream class, the setUnicodeStream() and getUnicodeStream() methods are not supported for NCHAR datatype access. Use the setCharacterStream() method and the getCharacterStream() method if you want to use stream access.

The usage of SQL NCHAR datatypes is similar to that of the SQL CHAR (CHAR, VARCHAR2, and CLOB) datatypes. JDBC uses the same classes and methods to access SQL NCHAR datatypes that are used for the corresponding SQL CHAR datatypes. Therefore, there are no separate, corresponding classes defined in the oracle.sql package for SQL NCHAR datatypes. Likewise, there is no separate, corresponding constant defined in the oracle.jdbc.OracleTypes class for SQL NCHAR datatypes. The only difference in usage between the two datatypes occur in a data bind situation: a JDBC program must call the setFormOfUse() method to specify if the data is bound for a SQL NCHAR datatype.

Note:

For Oracle9i 9.0.1, the setFormOfUse() method must be called before the registerOutParameter() method is called in order to avoid unpredictable results.

The following code shows how to access SQL NCHAR data:

// 
// Table TEST has the following columns: 
// - NUMBER 
// - NVARCHAR2 
// - NCHAR 
// 
oracle.jdbc.OraclePreparedStatement pstmt = 
  (oracle.jdbc.OraclePreparedStatement) 
conn.prepareStatement("insert into TEST values(?, ?, ?)");

// 
// oracle.jdbc.OraclePreparedStatement.FORM_NCHAR should be used for all NCHAR, 
// NVARCHAR2 and NCLOB data types.
//
pstmt.setFormOfUse(2, Const.NCHAR);
pstmt.setFormOfUse(3, Const.NCHAR);

pstmt.setInt(1, 1);                    // NUMBER column
pstmt.setString(2, myUnicodeString1);  // NVARCHAR2 column
pstmt.setString(3, myUnicodeString2);  // NCHAR column
pstmt.execute();

Class oracle.sql.CHAR

The CHAR class is used by Oracle JDBC in handling and converting character data. The JDBC driver constructs and populates oracle.sql.CHAR objects once character data has been read from the database.

Note:

The oracle.sql.CHAR class is used for both SQL CHAR and SQL NCHAR datatypes.

The CHAR objects constructed and returned by the JDBC driver can be in the database character set, UTF-8, or ISO-Latin-1 (WE8ISO8859P1). The CHAR objects that are Oracle object attributes are returned in the database character set.

JDBC application code rarely needs to construct CHAR objects directly, since the JDBC driver automatically creates CHAR objects as character data are obtained from the database. There may be circumstances, however, where constructing CHAR objects directly in application code is useful--for example, to repeatedly pass the same character data to one or more prepared statements without the overhead of converting from Java strings each time.

oracle.sql.CHAR Objects and Character Sets

The CHAR class provides Globalization Support functionality to convert character data. This class has two key attributes: (1) Globalization Support character set and (2) the character data. The Globalization Support character set defines the encoding of the character data. It is a parameter that is always passed when a CHAR object is constructed. Without the Globalization Support character set being know, the bytes of data in the CHAR object are meaningless.

The oracle.sql.CharacterSet class is instantiated to represent character sets. To construct a CHAR object, you must provide character set information to the CHAR object by way of an instance of the CharacterSet class. Each instance of this class represents one of the Globalization Support character sets that Oracle supports. A CharacterSet instance encapsulates methods and attributes of the character set, mainly involving functionality to convert to or from other character sets. You can find a complete list of the character sets that Oracle supports in the Oracle9i Database Globalization Support Guide.

Constructing an oracle.sql.CHAR Object

Follow these general steps to construct a CHAR object:

Create a CharacterSet object by calling the static CharacterSet.make() method.

This method is a factory for the character set instance. The make() method takes an integer as input, which corresponds to a character set ID that Oracle supports. For example:
```
int oracleId = CharacterSet.JA16SJIS_CHARSET; // this is character set ID,
                                              // 832
...
CharacterSet mycharset = CharacterSet.make(oracleId);
```
Each character set that Oracle supports has a unique, predefined Oracle ID.

For more information on character sets and character set IDs, see the Oracle9i Database Globalization Support Guide.

Construct a CHAR object.

Pass a string (or the bytes that represent the string) to the constructor along with the CharacterSet object that indicates how to interpret the bytes based on the character set. For example:

String mystring = "teststring";
...
CHAR mychar = new CHAR(teststring, mycharset);

The CHAR class has multiple constructors--they can take a string, a byte array, or an object as input along with the CharacterSet object. In the case of a string, the string is converted to the character set indicated by the CharacterSet object before being placed into the CHAR object.

See the oracle.sql.CHAR class Javadoc for more information.

Notes:

The CharacterSet object cannot be null.
The CharacterSet class is an abstract class, therefore it has no constructor. The only way to create instances is to use the make() method.
The server recognizes the special value CharacterSet.DEFAULT_CHARSET as the database character set. For the client, this value is not meaningful.
Oracle does not intend or recommend that users extend the CharacterSet class.

oracle.sql.CHAR Conversion Methods

The CHAR class provides the following methods for translating character data to strings:

getString(): Converts the sequence of characters represented by the CHAR object to a string, returning a Java String object. If you enter an invalid OracleID, then the character set will not be recognized and the getString() method throws a SQLException.
toString(): Identical to the getString() method. But if you enter an invalid OracleID, then the character set will not be recognized and the toString() method returns a hexadecimal representation of the CHAR data and does not throw a SQLException.
getStringWithReplacement(): Identical to getString(), except a default replacement character replaces characters that have no unicode representation in the CHAR object character set. This default character varies from character set to character set, but is often a question mark ("?").

The server (a database) and the client, or application running on the client, can use different character sets. When you use the methods of the CHAR class to transfer data between the server and the client, the JDBC drivers must convert the data from the server character set to the client character set or vice versa. To convert the data, the drivers use Globalization Support. For more information on how the JDBC drivers convert between character sets, see "JDBC and Globalization Support".

Additional 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 current Oracle 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 8 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:

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.
The stored procedure returns a REF CURSOR.
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.

The result set is processed as requested.

Important:

The cursor associated with a REF CURSOR is closed whenever the statement object that produced the REF CURSOR is closed.

Unlike in past 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.*;
...
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.

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

Some of the Oracle type extensions supported by the current Oracle JDBC drivers are either not supported or are supported differently by the Oracle 8.0.x and 7.3.x JDBC drivers. The 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 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 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.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, Current 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
OPAQUE	OracleTypes.OPAQUE	oracle.sql.OPAQUE	not supported

6 Overview of Oracle Extensions

Introduction to Oracle Extensions

Support Features of the Oracle Extensions

Support for Oracle Datatypes

Support for Oracle Objects

Support for Schema Naming

OCI Extensions

Oracle JDBC Packages and Classes

Package oracle.sql

Classes of the oracle.sql Package

Table 6-1 Oracle Datatype Classes

General oracle.sql.* Datatype Support

Overview of Class oracle.sql.STRUCT

Overview of Class oracle.sql.REF

Overview of Class oracle.sql.ARRAY

Overview of Classes oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE

Classes oracle.sql.DATE, oracle.sql.NUMBER, and oracle.sql.RAW

Classes oracle.sql.TIMESTAMP, oracle.sql.TIMESTAMPTZ, and oracle.sql.TIMESTAMPLTZ

Overview of Class oracle.sql.ROWID

Class oracle.sql.OPAQUE

Package oracle.jdbc

Table 6-2 Key Interfaces and Classes of the oracle.jdbc Package

Class oracle.jdbc.OracleDriver

Interface oracle.jdbc.OracleConnection

Client Identifiers

Interface oracle.jdbc.OracleStatement

Interface oracle.jdbc.OraclePreparedStatement

Interface oracle.jdbc.OracleCallableStatement

Interface oracle.jdbc.OracleResultSet

Interface oracle.jdbc.OracleResultSetMetaData

Class oracle.jdbc.OracleTypes

OracleTypes and Registering Output Parameters

OracleTypes and the setNull() Method

Oracle Interfaces for Oracle-specific Features

Method getJavaSqlConnection()

Package oracle.jdbc2 (for JDK 1.1.x only)

Oracle Character Datatypes Support

SQL CHAR Datatypes

SQL NCHAR Datatypes

Class oracle.sql.CHAR

oracle.sql.CHAR Objects and Character Sets

Constructing an oracle.sql.CHAR Object

oracle.sql.CHAR Conversion Methods

Additional Oracle Type Extensions

Oracle ROWID Type

Example: ROWID

Oracle REF CURSOR Type Category

Example: Accessing REF CURSOR Data

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

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

6
Overview of Oracle Extensions