Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
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.
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.
The following points relate to type support for basic SQLJ features:
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.)
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.
And the following points relate to Oracle extensions, which are covered in "Oracle Type Extensions" and in Chapter 6, "Objects and Collections":
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.
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;
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:
oracle.sql
package, meaning there are no wrapper types such as oracle.sql.NUMBER
and oracle.sql.CHAR
that you can use to wrap raw SQL data.
ROWID
datatype with the OracleRowid
class in the oracle.jdbc.driver
package.
BLOB
, CLOB
, and BFILE
datatypes with the OracleBlob
, OracleClob
, and OracleBfile
classes in the oracle.jdbc.driver
package. These classes do not include LOB and BFILE manipulation methods 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.
BLOB
, CLOB
, and BFILE
.
Table 5-2 summarizes these differences.