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

## 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:

- Package oracle.sql

- Package oracle.jdbc.driver

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

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 Type and Interfaces Implemented if for JDBC 2.0
--- | ---
`oracle.sql.STRUCT` | `STRUCT` (objects) (JDBC 2.0) implements `java.sql.Struct` (`oracle.jdbc2.Struct` under JDK 1.1.x)
`oracle.sql.REF` | `REF` (object references) (JDBC 2.0) implements `java.sql.Ref` (`oracle.jdbc2.Ref` under JDK 1.1.x)``
`oracle.sql.ARRAY` | VARRAY or nested table (collections) (JDBC 2.0) implements `java.sql.Array` (`oracle.jdbc2.Array` under JDK 1.1.x)
`oracle.sql.BLOB` | `BLOB` (binary large objects) (JDBC 2.0) implements `java.sql.Blob` (`oracle.jdbc2.Blob` under JDK 1.1.x)``
`oracle.sql.CLOB` | `CLOB` (character large objects) (JDBC 2.0) implements `java.sql.Clob` (`oracle.jdbc2.Clob` under JDK 1.1.x)``
`oracle.sql.BFILE` | `BFILE` (external files)
`oracle.sql.CHAR` | `CHAR`, `VARCHAR2`
`oracle.sql.DATE` | `DATE`
`oracle.sql.NUMBER` | `NUMBER`
`oracle.sql.RAW` | `RAW`
`oracle.sql.ROWID` | `ROWID` (row identifiers)

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 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"

  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 5, "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 `getAsciStream()`, `getBinaryStream()`, `getUnicodeStream()`, and `getCharacterStream()`. Use `getCursor()` 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. (For information, 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. (For information, see "Creating STRUCT Objects and Descriptors".)

- `oracle.sql.CustomDatum` and `oracle.sql.CustomDatumFactory` interfaces: Used in Java classes implementing the Oracle `CustomDatum` scenario of Oracle object support. (The other possible scenario is the JDBC-standard `SQLData` implementation.) See "Understanding the CustomDatum Interface" for more information on `CustomDatum`.

#### 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, `get`, and `set` 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.

#### 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 9, "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 7, "Working with LOBs and BFILEs".

#### Class oracle.sql.CHAR

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

`CHAR` objects that the driver constructs and returns can be in the database character set, `UTF-8`, or ISO-Latin-1 (`WE8ISO8859P1`). `CHAR` objects that are Oracle8 object attributes are returned in the database character set.

A JDBC application will rarely need to construct `CHAR` objects directly, because the JDBC driver creates `CHAR` objects automatically as character data items are obtained from the database. There may be circumstances, however, where constructing `CHAR` objects directly is more efficient--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.

##### CHAR Objects and Character Sets

The `CHAR` class has special functionality for NLS conversion of character data. A key attribute of the `CHAR` class, and a parameter always passed in when a `CHAR` object is constructed, is the NLS character set used in presenting the character data. Without the character set being known, the bytes of data in the `CHAR` object are meaningless.

The `oracle.sql.CharacterSet` class is instantiated to represent character sets. When you 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 NLS 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 Oracle8i National Language Support Guide.

##### Constructing a CHAR

Follow these general steps to construct a `CHAR` object:

1. Create a `CharacterSet` object by calling the static `CharacterSet.make()` method. This method is a factory for the character set class. The `make()` method takes as input an integer Oracle ID that corresponds to a character set that Oracle supports. For example:

       int oracleId = CharacterSet.JA16SJIS_CHARSET; // this is character set 832
       ...
       CharacterSet mycharset = CharacterSet.make(oracleId);

   Each character set that Oracle supports has a unique predefined Oracle ID.

   Note: If you enter an invalid ID, an exception will not be thrown. Instead, when you try to use the character set, you will receive unpredictable results.

   For more information on character sets and character set IDs, see the Oracle8i National Language Support Guide.

2. Construct a `CHAR` object. Pass to the constructor a string (or the bytes that represent the string) and 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.

   Refer to the `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.

##### CHAR Conversion Methods

The `CHAR` class provides these 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 the character set is not recognized (that is, if you entered an invalid `OracleID`), then `getString()` throws a `SQLException`.

- `toString()`: Identical to `getString()`, but if the character set is not recognized (that is, if you entered an invalid `OracleID`), then `toString()` 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 character set of this `CHAR` object. This default character varies from character set to character set, but is often a question mark.

The server (database) and the client (or application running on the client) can use different character sets. When you use the methods of this 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 the reverse). To convert the data, the drivers use Oracle's National Language Support (NLS). For more information on how the JDBC drivers convert between character sets, see "JDBC and NLS". For more information on NLS, see the Oracle8i National Language Support Guide.

#### 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.

#### 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".

### Package oracle.jdbc.driver

The `oracle.jdbc.driver` package includes classes that add extended features to enable data access in `oracle.sql` format. In addition, these classes provide Oracle-specific extensions to allow access to raw SQL format data by using `oracle.sql.*` objects.

Table 6-2 lists key classes in this package for connections, statements, and result sets.

##### Table 6-2 Connection, Statement, and Result Set Classes

Class | Key Functionality
--- | ---
`OracleDriver` | implements `java.sql.Driver`
`OracleConnection` | 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` | methods to set Oracle performance extensions for individual statement; superclass of `OraclePreparedStatement` and `OracleCallableStatement` (implements `java.sql.Statement`)
`OraclePreparedStatement` | `set` methods to bind `oracle.sql.*` types into a prepared statement (implements `java.sql.PreparedStatement`; extends `OracleStatement`; superclass of `OracleCallableStatement`)
`OracleCallableStatement` | `get` methods to retrieve data in `oracle.sql` format; `set` methods to bind `oracle.sql.*` types into a callable statement (implements `java.sql.CallableStatement`; extends `OraclePreparedStatement`)
`OracleResultSet` | `get` methods to retrieve data in `oracle.sql` format (implements `java.sql.ResultSet`)
`OracleResultSetMetaData` | methods to get meta information about Oracle result sets, such as column names and datatypes (implements `java.sql.ResultSetMetaData`)
`OracleDatabaseMetaData` | 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`)

The `oracle.jdbc.driver` package additionally includes the `OracleTypes` class, which 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 classes of the `oracle.jdbc.driver` package. For more information about using these classes to access Oracle type extensions, see Chapter 5, "Accessing and Manipulating Oracle Data".

#### Class oracle.jdbc.driver.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".

#### Class oracle.jdbc.driver.OracleConnection

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

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

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.driver.OracleConnection` methods are Oracle-defined extensions:

- `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.

#### Class oracle.jdbc.driver.OracleStatement

This class extends standard JDBC statement functionality and is the superclass 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` class 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.driver.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.

#### Class oracle.jdbc.driver.OraclePreparedStatement

This class extends standard JDBC prepared statement functionality, is a subclass of the `OracleStatement` class, and is the superclass of the `OracleCallableStatement` class. Extended functionality consists of `set` 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 `set` 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.

- `setCustomDatum()`: Binds a `CustomDatum` 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.

- `close()`: Closes the current statement.

#### Class oracle.jdbc.driver.OracleCallableStatement

This class extends standard JDBC callable statement functionality and is a subclass of the `OracleStatement` and `OraclePreparedStatement` classes. Extended functionality includes `set` methods for binding structured objects and `oracle.sql`.* objects into prepared statements, and `get` methods for retrieving data into `oracle.sql.*` objects.

Key methods include:

- `getOracleObject()`: This is a generic `get` 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 `set` 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.

- `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.driver.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.

#### Class oracle.jdbc.driver.OracleResultSet

This class extends standard JDBC result set functionality, implementing `get` methods for retrieving data into `oracle.sql.*` objects.

Key methods include:

- `getOracleObject()`: This is a generic `get` 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.

#### Class oracle.jdbc.driver.OracleResultSetMetaData

This class extends standard JDBC result set metadata functionality to retrieve information about Oracle result set objects.

Key methods include the following:

- `getColumnCount()`: Returns the number of columns in an Oracle result set.

- `getColumnName()`: Returns the name of a specified column in an Oracle result set.

- `getColumnType()`: Returns the SQL type of a specified column in an Oracle result set. If the column stores an Oracle object or collection, then this method returns `OracleTypes.STRUCT` or `OracleTypes.ARRAY` respectively.

- `getColumnTypeName()`: Returns the SQL type name of the data stored in the column. If the column stores an array or collection, then this method returns its SQL type name. If the column stores `REF` data, then this method returns the SQL type name of the objects to which the object reference points.

- `getTableName()`: Returns the name of the table from which an Oracle result set column was selected.

#### Class oracle.jdbc.driver.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.driver.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.driver.OracleTypes` identify the SQL types of the output parameters in the `registerOutParameter()` method of the `java.sql.CallableStatement` interface and `oracle.jdbc.driver.OracleCallableStatement` class.

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.driver.OraclePreparedStatement` class.

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

### 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 `CustomDatum` interface, included in the `oracle.sql` package. See "Understanding the SQLData Interface" for more information about `SQLData`, `SQLInput`, and `SQLOutput`.

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