Supported Types for Host Expressions

This section summarizes the types that are supported by Oracle SQLJ, including information about backwards compatibility for the 8.0.5 and 7.3.4 Oracle JDBC drivers.

Supported Types for Oracle8i

Table 5-1 lists the Java types that you can use in host expressions when employing the 8.1.5 Oracle JDBC drivers. This table also documents the correlation between Java types, SQL types whose typecodes are defined in the oracle.jdbc.driver.OracleTypes class, and datatypes in the Oracle database. (The OracleTypes class simply defines a typecode, which is an integer constant, for each Oracle datatype. For standard SQL types, the OracleTypes entry is the same as the entry in the standard java.sql.Types type definitions class.)

SQL data that is output to a Java variable is converted to the corresponding Java type. A Java variable that is 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 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
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
JPub-generated objects	STRUCT	STRUCT
JPub-generated object references	REF	REF
JPub-generated arrays	ARRAY	ARRAY
client-customized types (customization of any oracle.sql types, including objects, references, and collections)	depends on what is being customized	depends on what is being customized
QUERY RESULT OBJECTS
java.sql.ResultSet	CURSOR	CURSOR
SQLJ iterator objects	CURSOR	CURSOR

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

• Remember that all numeric types in an Oracle database are stored as NUMBER. While 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 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".

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

• 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 objects and references refer to Oracle8i object support, which allows strongly typed custom objects in the database. The Oracle JPublisher utility creates Java classes to correspond to your custom objects and can also create Java classes corresponding to custom collections (variable arrays or nested tables).

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 handle 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.5 and 7.3.4

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

• The 8.0.5 and 7.3.4 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.5 and 7.3.4 drivers do not support Oracle object and collection types.

• The 8.0.5 and 7.3.4 drivers support the Oracle ROWID datatype with the OracleRowid class in the oracle.jdbc.driver package.

• The 8.0.5 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 7.3.4 drivers do not support BLOB, CLOB, and BFILE.

Table 5-2 summarizes these differences.

Table 5-2 Type Support Differences for 8.0.5 and 7.3.4 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.5 not supported in 7.3.4	BLOB in 8.0.5 n/a in 7.3.4
oracle.jdbc.driver.OracleClob	CLOB in 8.0.5 not supported in 7.3.4	CLOB in 8.0.5 n/a in 7.3.4
oracle.jdbc.driver.OracleBfile	BFILE in 8.0.5 not supported in 7.3.4	BFILE in 8.0.5 n/a in 7.3.4
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

---