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

## Supported Types for Host Expressions

This section summarizes the types supported by Oracle SQLJ, including information about new support for JDBC 2.0 types, and backwards compatibility for the 8.0.x and 7.3.x Oracle JDBC drivers.

For a complete list of legal Java mappings for each Oracle SQL type, see the reference information in the Oracle8i JDBC Developer's Guide and Reference.

Note: SQLJ (and SQL) perform implicit conversions between SQL and Java types. Although this is generally useful and helpful, it can produce unexpected results. Do not rely on type-checking alone to ensure the correctness of your code.

### Supported Types for Oracle8i

Table 5-1 lists the Java types that you can use in host expressions when employing the Oracle JDBC drivers. This table also documents the correlation between Java types, SQL types whose typecodes are defined in the class `oracle.jdbc.driver.OracleTypes`, and datatypes in the Oracle database.

Note: The `OracleTypes` class simply defines a typecode, which is an integer constant, for each Oracle datatype. For standard JDBC types, the `OracleTypes` value is identical to the standard `java.sql.Types` value.

SQL data output to a Java variable is converted to the corresponding Java type. A Java variable input to SQL is converted to the corresponding Oracle datatype.

Where objects, object references, and arrays are referred to as "JPub-generated", this refers to the Oracle JPublisher utility that can be used in defining Java classes to correspond to Oracle8i objects, object references, and arrays. The JPublisher utility is discussed in "JPublisher and the Creation of Custom Java Classes" and documented in further detail in the Oracle8i JPublisher User's Guide.

##### Table 5-1 Type Mappings for Supported Host Expression Types

Java Type | OracleTypes Definition | Oracle Datatype
--- | --- | ---
STANDARD JDBC 1.x TYPES |  |
boolean | BIT | NUMBER
byte | TINYINT | NUMBER
short | SMALLINT | NUMBER
int | INTEGER | NUMBER
long | BIGINT | NUMBER
float | REAL | NUMBER
double | FLOAT, DOUBLE | NUMBER
java.lang.String | CHAR | CHAR
java.lang.String | VARCHAR | VARCHAR2
java.lang.String | LONGVARCHAR | LONG
byte[] | BINARY | RAW
byte[] | VARBINARY | RAW
byte[] | LONGVARBINARY | LONGRAW
java.sql.Date | DATE | DATE
java.sql.Time | TIME | DATE
java.sql.Timestamp | TIMESTAMP | DATE
java.math.BigDecimal | NUMERIC | NUMBER
java.math.BigDecimal | DECIMAL | NUMBER
STANDARD JDBC 2.0 TYPES |  |
java.sql.Blob | BLOB | BLOB
java.sql.Clob | CLOB | CLOB
java.sql.Struct | STRUCT | STRUCT
java.sql.Ref | REF | REF
java.sql.Array | ARRAY | ARRAY
custom object classes implementing java.sql.SQLData | STRUCT | STRUCT
JAVA WRAPPER CLASSES |  |
java.lang.Boolean | BIT | NUMBER
java.lang.Byte | TINYINT | NUMBER
java.lang.Short | SMALLINT | NUMBER
java.lang.Integer | INTEGER | NUMBER
java.lang.Long | BIGINT | NUMBER
java.lang.Float | REAL | NUMBER
java.lang.Double | FLOAT, DOUBLE | NUMBER
SQLJ STREAM CLASSES |  |
sqlj.runtime.BinaryStream | LONGVARBINARY | LONG RAW
sqlj.runtime.AsciiStream | LONGVARCHAR | LONG
sqlj.runtime.UnicodeStream | LONGVARCHAR | LONG
ORACLE EXTENSIONS |  |
oracle.sql.NUMBER | NUMBER | NUMBER
oracle.sql.CHAR | CHAR | CHAR
oracle.sql.RAW | RAW | RAW
oracle.sql.DATE | DATE | DATE
oracle.sql.ROWID | ROWID | ROWID
oracle.sql.BLOB | BLOB | BLOB
oracle.sql.CLOB | CLOB | CLOB
oracle.sql.BFILE | BFILE | BFILE
oracle.sql.STRUCT | STRUCT | STRUCT
oracle.sql.REF | REF | REF
oracle.sql.ARRAY | ARRAY | ARRAY
custom object classes implementing oracle.sql.CustomDatum | STRUCT | STRUCT
custom reference classes implementing oracle.sql.CustomDatum | REF | REF
custom collection classes implementing oracle.sql.CustomDatum | ARRAY | ARRAY
any other custom Java classes implementing oracle.sql.CustomDatum (to wrap any oracle.sql type) | any | any
QUERY RESULT OBJECTS |  |
java.sql.ResultSet | CURSOR | CURSOR
SQLJ iterator objects | CURSOR | CURSOR

The following points relate to type support for standard SQLJ features:

- In releases 8.1.6 and 8.1.7, Oracle SQLJ requires any class that implements `java.sql.SQLData` to set the `public static _SQL_NAME` field appropriately. This occurs automatically if you use the Oracle JPublisher utility to generate the class.

  Note that this does not follow ISO standard for use of the `SQLData` interface. In future releases, the Oracle SQLJ implementation will adhere to standard.

  See "Requirements for Classes Implementing SQLData".

- JDBC and SQLJ do not support Java `char` and `Character` types. Instead, use the Java `String` type to represent character data.

- Do not confuse the supported `java.sql.Date` type with `java.util.Date`, which is not directly supported. The `java.sql.Date` class is a wrapper for `java.util.Date` that allows JDBC to identify the data as a SQL `DATE` and adds formatting and parsing operations to support JDBC escape syntax for date values.

- Remember that all numeric types in an Oracle database are stored as `NUMBER`. Although you can specify additional precision when you declare a `NUMBER` during table creation (you can declare the total number of places and the number of places to the right of the decimal point), this precision may be lost when retrieving the data through the Oracle JDBC drivers, depending on the Java type that you use to receive the data. (An `oracle.sql.NUMBER` instance would preserve full information.)

- The Java wrapper classes (such as `Integer` and `Float`) are useful in cases where null values may be returned by the SQL statement. Primitive types (such as `int` and `float`) cannot contain null values. See "Null-Handling" for more information.

- For information about SQLJ support for result set and iterator host variables, see "Using Iterators and Result Sets as Host Variables".

- The SQLJ stream classes are required in using streams as host variables. For information, see "Support for Streams".

The following points relate to Oracle extensions, which are covered in "Oracle Type Extensions" and in Chapter 6, "Objects and Collections":

- Oracle SQLJ requires any class that implements `oracle.sql.CustomDatum` to set the `public static _SQL_TYPECODE` parameter according to values defined in the `OracleTypes` class. In some cases an additional parameter must be set as well (such as`_SQL_NAME` for objects and`_SQL_BASETYPE` for object references). This occurs automatically if you use the Oracle JPublisher utility to generate the class.

  See "Oracle Requirements for Classes Implementing CustomDatum".

- The `oracle.sql` classes are wrappers for SQL data for each of the Oracle datatypes. The `ARRAY`, `STRUCT`, `REF`, `BLOB`, and `CLOB` classes correspond to standard JDBC 2.0 interfaces. For background information about these classes and Oracle extensions, see the Oracle8i JDBC Developer's Guide and Reference.

- Custom Java classes can map to Oracle objects (implementing `CustomDatum` or `SQLData`), references (implementing `CustomDatum` only), collections (implementing `CustomDatum` only), or other SQL types (for customized handling, implementing `CustomDatum` only). See "Custom Java Classes".

  You can use the Oracle JPublisher utility to automatically generate custom Java classes. See "JPublisher and the Creation of Custom Java Classes".

- Using any of the Oracle extensions requires an Oracle JDBC driver, Oracle customization during translation, and the Oracle SQLJ runtime when your application runs.

### JDBC 2.0 Type Support

As indicated in Table 5-1 above, Oracle JDBC and SQLJ support JDBC 2.0 types in the standard `java.sql` package.

This section lists JDBC 2.0 supported types and discusses Oracle SQLJ requirements for use of these types.

Important: In a Sun Microsystems JDK environment, JDBC 2.0 types require a JDK 1.2.x version. While Oracle JDBC under JDK 1.1.x supports `oracle.jdbc2` extensions to mimic JDBC 2.0 type functionality, Oracle SQLJ has never supported the `oracle.jdbc2` package.

#### Types Supported

Table 5-2 lists the JDBC 2.0 types supported by Oracle SQLJ. You can use them wherever you can use the corresponding Oracle extensions, summarized in the table.

The Oracle extensions have been available in prior releases and are still available as well. These `oracle.sql.*` classes provide functionality to wrap raw SQL data, and are described in the Oracle8i JDBC Developer's Guide and Reference.

##### Table 5-2 Correlation between Oracle Extensions and JDBC 2.0 Types

JDBC 2.0 Type | Oracle Extension
--- | ---
java.sql.Blob | oracle.sql.BLOB
java.sql.Clob | oracle.sql.CLOB
java.sql.Struct | oracle.sql.STRUCT
java.sql.Ref | oracle.sql.REF
java.sql.Array | oracle.sql.ARRAY
java.sql.SQLData | oracle.sql.CustomDatum (where `_SQL_TYPECODE` = `OracleTypes.STRUCT`)

For more information about support for the types in Table 5-2, see "Support for BLOB, CLOB, and BFILE" and "Support for Weakly Typed Objects, References, and Collections".

The following JDBC 2.0 types are currently not supported in Oracle JDBC or SQLJ:

- `JAVA_OBJECT`--Represents an instance of a Java type in a SQL column.

- `DISTINCT`--A distinct SQL type represented in or retrievable from a basic SQL type (for example, `SHOESIZE` --> `NUMBER`).

#### Oracle SQLJ Requirements

As with the corresponding Oracle extended types, you can use the standard JDBC 2.0 types in the Oracle SQLJ runtime only if you customize your application with the Oracle customizer, which happens by default when you run the SQLJ translator.

This support requirement is not in full compliance with the forthcoming ISO standard. However, if you use the standard `java.sql` types in your application, then the source code will be portable. To use it in an alternative SQLJ runtime environment, you would have to re-translate it with an appropriate SQLJ translator.

### Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types

Oracle JDBC drivers do not support calling arguments or return values of the PL/SQL types `TABLE` (now known as indexed-by tables), `RECORD`, or `BOOLEAN`.

As a workaround, you can create wrapper procedures that process the data as types supported by JDBC. For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as `BOOLEAN`, or, for an output parameter, accepts a `BOOLEAN` argument from the original procedure and passes it as a `CHAR` or `NUMBER` to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as `CHAR` and `NUMBER`). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

Here is an example of a PL/SQL wrapper procedure `MY_PROC` for a stored procedure `PROC` that takes a `BOOLEAN` as input:

    PROCEDURE MY_PROC (n NUMBER) IS
    BEGIN
       IF n=0
          THEN proc(false);
          ELSE proc(true);
       END IF;
    END;

    PROCEDURE PROC (b BOOLEAN) IS
    BEGIN
    ...
    END;

### Backwards Compatibility for Oracle 8.0.x and 7.3.x

Some of the Oracle type extensions supported by the Oracle8i JDBC drivers are either not supported or supported differently by the Oracle 8.0.x and 7.3.x JDBC drivers. Following are the key points:

- The Oracle 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 Oracle 8.0.x and 7.3.x drivers do not support Oracle object and collection types.

- The Oracle 8.0.x and 7.3.x drivers support the Oracle `ROWID` datatype with the `OracleRowid` class in the `oracle.jdbc.driver` package.

- The Oracle 8.0.x drivers support the Oracle `BLOB`, `CLOB`, and `BFILE` datatypes with the `OracleBlob`, `OracleClob`, and `OracleBfile` classes in the `oracle.jdbc.driver` package. These classes do not include LOB and BFILE manipulation methods such as those discussed in "Support for BLOB, CLOB, and BFILE". You must, instead, use the PL/SQL `DBMS_LOB` package, which is discussed in the same section.

- The Oracle 7.3.x drivers do not support `BLOB`, `CLOB`, and `BFILE`.

Table 5-3 summarizes these differences.

##### Table 5-3 Type Support Differences for Oracle 8.0.x and 7.3.x JDBC Drivers

Java Type | OracleTypes Definition | Oracle Datatype
--- | --- | ---
ORACLE EXTENSIONS |  |
oracle.sql.NUMBER | not supported | n/a
oracle.sql.CHAR | not supported | n/a
oracle.sql.RAW | not supported | n/a
oracle.sql.DATE | not supported | n/a
oracle.jdbc.driver.OracleRowid | ROWID | ROWID
oracle.jdbc.driver.OracleBlob | BLOB in 8.0.x not supported in 7.3.x | BLOB in 8.0.x n/a in 7.3.x
oracle.jdbc.driver.OracleClob | CLOB in 8.0.x not supported in 7.3.x | CLOB in 8.0.x n/a in 7.3.x
oracle.jdbc.driver.OracleBfile | BFILE in 8.0.x not supported in 7.3.x | BFILE in 8.0.x n/a in 7.3.x
oracle.sql.STRUCT | not supported | n/a
oracle.sql.REF | not supported | n/a
oracle.sql.ARRAY | not supported | n/a
JPub-generated objects | not supported | n/a
JPub-generated object references | not supported | n/a
JPub-generated arrays | not supported | n/a
client-customized types (customization of any oracle.sql types, including objects, references, and collections) | not supported | n/a

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