Datatype Mappings

The Oracle JDBC drivers support the SQL datatypes required by JDBC 1.22. In addition, the Oracle JDBC drivers support the Oracle-specific ROWID datatype and user-defined types of the REF CURSOR category.

For reference, the following table shows the default mappings between JDBC datatypes, native Java datatypes, SQL datatypes, and the corresponding Java datatypes defined by Oracle extensions.

The Standard JDBC Datatypes column lists the datatypes supported by the JDBC 1.22 standard. All of these dataypes are defined in the java.sql.Types class.

The Java Native Datatypes column lists the datatypes defined by the Java language.

The SQL Datatypes column lists the SQL datatypes that exist in the database.

The Oracle Extensions--Java Classes that Represent SQL Datatypes column lists the oracle.sql.* Java types that correspond to each SQL datatype in the database. These are Oracle extensions that let you retrieve all SQL data in the form of a oracle.sql.* Java type. Mapping SQL datatypes into the oracle.sql datatypes lets you store and retrieve data without losing information. Refer to "Classes of the oracle.sql Package" for more information on the oracle.sql.* package.

For a list of all of the Java datatypes to which you can validly map a SQL datatype, see "Valid SQL-JDBC Datatype Mappings".

Table 3-1 Mapping Between JDBC, Java Native, and Oracle Datatypes

Standard JDBC Datatypes	Java Native Datatypes	SQL Datatypes	Oracle Extensions--Java Classes that Represent SQL Datatypes
java.sql.Types.CHAR	java.lang.String	CHAR	oracle.sql.CHAR
java.sql.Types.VARCHAR	java.lang.String	VARCHAR2	oracle.sql.CHAR
java.sql.Types.LONGVARCHAR	java.lang.String	LONG	oracle.sql.CHAR
java.sql.Types.NUMERIC	java.math.BigDecimal	NUMBER	oracle.sql.NUMBER
java.sql.Types.DECIMAL	java.math.BigDecimal	NUMBER	oracle.sql.NUMBER
java.sql.Types.BIT	boolean	NUMBER	oracle.sql.NUMBER
java.sql.Types.TINYINT	byte	NUMBER	oracle.sql.NUMBER
java.sql.Types.SMALLINT	short	NUMBER	oracle.sql.NUMBER
java.sql.Types.INTEGER	int	NUMBER	oracle.sql.NUMBER
java.sql.Types.BIGINT	long	NUMBER	oracle.sql.NUMBER
java.sql.Types.REAL	float	NUMBER	oracle.sql.NUMBER
java.sql.Types.FLOAT	double	NUMBER	oracle.sql.NUMBER
java.sql.Types.DOUBLE	double	NUMBER	oracle.sql.NUMBER
java.sql.Types.BINARY	byte[]	NUMBER	oracle.sql.NUMBER
java.sql.Types.VARBINARY	byte[]	RAW	oracle.sql.RAW
java.sql.Types.LONGVARBINARY	byte[]	LONGRAW	oracle.sql.NUMBER
java.sql.Types.DATE	java.sql.Date	DATE	oracle.sql.DATE
java.sql.Types.TIME	java.sql.Time	DATE	oracle.sql.DATE
java.sql.Types.TIMESTAMP	javal.sql.Timestamp	DATE	oracle.sql.DATE

Oracle JDBC Extension Types

In addition, the following JDBC extensions for SQL datatypes (most of which comply with the JDBC 2.0 standard) are supported. They are not described until Chapter 4, "Oracle Extensions", but are summarized here for reference. Table 3-2 shows their mappings to Oracle datatypes.

The SQL Datatype column lists the SQL datatypes that exist in the database.

The JDBC Extensions for SQL Datatypes column lists the types into which Oracle datatypes should map according to the JDBC 2.0 standard. The class oracle.jdbc.driver.OracleTypes.* includes the definitions of Oracle-specific types that do not exist in the JDBC standard and is a superset of oracle.sql.*.

The Oracle Extensions--Java Classes that Represent SQL Datatypes column lists the oracle.sql.* Java types that correspond to each SQL datatype in the database. These are Oracle extensions that let you retrieve all SQL data in the form of a oracle.sql.* Java type. Refer to "Classes of the oracle.sql Package" for more information on the oracle.sql.* package.

For a list of all of the Java datatypes to which you can validly map a SQL datatype, see "Valid SQL-JDBC Datatype Mappings".

Table 3-2 Mapping Oracle Extension JDBC Types to Oracle Datatypes

SQL Datatype	JDBC Extensions for SQL Datatypes	Oracle Extensions--Java Classes that Represent SQL Datatypes
ROWID	oracle.jdbc.driver.OracleTypes.ROWID	oracle.sql.ROWID
user-defined types of the REF CURSOR category	oracle.jdbc.driver.OracleTypes.CURSOR	java.sql.ResultSet
BLOB	oracle.jdbc.driver.OracleTypes.BLOB	oracle.sql.BLOB
CLOB	oracle.jdbc.driver.OracleTypes.CLOB	oracle.sql.CLOB
BFILE	oracle.jdbc.driver.OracleTypes.BFILE	oracle.sql.BFILE
Object Value	oracle.jdbc.driver.OracleTypes.STRUCT	If there is no entry for the object value in the type map: oracle.sql.STRUCT If there is an entry for the object value in the type map: customized Java class
Object Reference	oracle.jdbc.driver.OracleTypes.REF	class that extends oracle.sql.REF
Collections (varrays and nested tables)	oracle.jdbc.driver.OracleTypes.ARRAY	oracle.sql.ARRAY

See Chapter 4, "Oracle Extensions", for more information on type mappings. In Chapter 4 you can also find more information on:

• packages oracle.sql, oracle.jdbc.driver, and oracle.jdbc2

• type extensions for the Oracle ROWID datatype and user-defined types of the REF CURSOR category

• how to use type maps with object values and collections

---