B
Data Type Mapping

Oracle9i maps the ANSI data types through ODBC and OLE DB interfaces to supported Oracle data types. When the results of a query are returned, Oracle9i converts the ODBC or OLE DB data types to Oracle data types.

The tables in this appendix show how Oracle maps ANSI data types through ODBC and OLE DB interfaces to supported Oracle data types when it is retrieving data from a non-Oracle system.

This appendix contains the following tables

Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

Table B-1 Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

ANSI	ODBC	Oracle
`NUMERIC(19,0)`	`SQL_BIGINT`	`NUMBER(19,0)`
`N/A`	`SQL_BINARY`	`RAW`
`CHAR`	`SQL_CHAR`	`CHAR`
`DATE`	`SQL_DATE`	`DATE`
`DECIMAL(p,s)`	`SQL_DECIMAL(p,s)`	`NUMBER(p,s)`
`DOUBLE PRECISION`	`SQL_DOUBLE`	`FLOAT(49)`
`FLOAT`	`SQL_FLOAT`	`FLOAT(49)`
`INTEGER`	`SQL_INTEGER`	`NUMBER(10)`^{Foot 1}
`N/A`	`SQL_LONGVARBINARY`	`LONG RAW`
`N/A`	`SQL_LONGVARCHAR`	`LONG`^{Foot 2}
`REAL`	`SQL_REAL`	`FLOAT(23)`
`SMALLINT`	`SQL_SMALLINT`	`NUMBER(5)`
`TIME`	`SQL_TIME`	`DATE`
`TIMESTAMP`	`SQL_TIMESTAMP`	`DATE`
`NUMERIC(3,0)`	`SQL_TINYINT`	`NUMBER(3)`
`VARCHAR`	`SQL_VARCHAR`	`VARCHAR`

¹ It's possible under some circumstance for the INTEGER ANSI data type to map to Precision 38, but it usually maps to Precision 10.
² If an ANSI SQL implementation defines a large value for the maximum length of VARCHAR data, then it is possible that ANSI VARCHAR will map to SQL_LONGVARCHAR and Oracle LONG. The same is true for OLE DB DBTYPE_STRING (long attribute).

Note:

This table maps ODBC data types into equivalent ANSI and Oracle data types. In some cases equivalence to ANSI data types is not guaranteed to be exact because the ANSI SQL standard delegates definition of numeric precision and maximum length of character data to individual implementations. This table reflects a probable mapping between ANSI and ODBC data types for a typical implementation of ANSI SQL.

Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

Table B-2 Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

ANSI	OLE DB	Oracle
`NUMERIC(3,0)`	`DBTYPE_UI1`	`NUMBER(3)`
`NUMERIC(3,0)`	`DBTYPE_I1`	`NUMBER(3)`
`SMALLINT`	`DBTYPE_UI2`	`NUMBER(5)`
`SMALLINT`	`DBTYPE_I2`	`NUMBER(5)`
`NUMERIC(3,0)`	`DBTYPE_BOOL`	`NUMBER(5)`
`INTEGER`	`DBTYPE_UI4`	`NUMBER(10)`
`INTEGER`	`DBTYPE_I4`	`NUMBER(10)`
`NUMERIC(19,0)`	`DBTYPE_UI8`	`NUMBER(19,0)`
`NUMERIC(19,0)`	`DBTYPE_I8`	`NUMBER(19,0)`
`NUMERIC(p,s)`	`DBTYPE_NUMERIC(p,s)`	`NUMBER(p,s)`
`FLOAT`	`DBTYPE_R4`	`FLOAT(23)`
`DOUBLE PRECISION`	`DBTYPE_R8`	`FLOAT(49)`
`N/A`	`DBTYPE_DECIMAL`	`FLOAT(49)`
`VARCHAR`	`DBTYPE_STR`	`VARCHAR2`
`VARCHAR`	`DBTYPE_WSTR`	`VARCHAR2`
`NUMERIC(19,0)`	`DBTYPE_CY`	`NUMBER(19,0)`
`DATE`	`DBTYPE_DBDATE`	`DATE`
`TIME`	`DBTYPE_DBTIME`	`DATE`
`TIMESTAMP`	`DBTYPE_TIMESTAMP`	`DATE`
`N/A`	`DBTYPE_BYTES`	`RAW`
`N/A`	`DBTYPE_BYTES (long attribute)`	`LONG RAW`
`N/A`	`DBTYPE_STRING (long attribute)`	`LONG`

B Data Type Mapping

Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

Table B-1 Mapping ANSI Data Types to Oracle Data Types Through an ODBC Interface

Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

Table B-2 Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

B
Data Type Mapping