B Data Type Mapping for Generic Connectivity

Oracle maps the ANSI data types through ODBC and OLE DB interfaces to supported Oracle data types. When the results of a query are returned, Oracle 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
Mapping ANSI Data Types to Oracle Data Types Through an OLE DB Interface

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

Table B-1 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.

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)` Note: It is possible under some circumstance for the `INTEGER` ANSI data type to map to `Precision 38`, but it usually maps to `Precision 10`.
`N/A`	`SQL_LONGVARBINARY`	`LONG RAW`
`N/A`	`SQL_LONGVARCHAR`	`LONG` Note: If an ANSI SQL implementation defines a large value for the maximum length of `VARCHAR` data, 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).
`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`
`BOOLEAN`	`SQL_BIT`	`RAW(1)`

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

Table B-2 shows the ANSI data types to Oracle data types mappings using 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`