B
Datatype Mapping

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

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

This appendix contains the following tables:

Mapping ANSI Datatypes to Oracle Datatypes Through an ODBC Interface

Table B-1 maps ODBC datatypes into equivalent ANSI and Oracle datatypes. In some cases equivalence to ANSI datatypes 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 datatypes for a typical implementation of ANSI SQL.

Table B-1 Mapping ANSI Datatypes to Oracle Datatypes 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 datatype 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, 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).
`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)`

Mapping ANSI Datatypes to Oracle Datatypes Through an OLE DB Interface

Table B-2 Mapping ANSI Datatypes to Oracle Datatypes 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 Datatype Mapping

Mapping ANSI Datatypes to Oracle Datatypes Through an ODBC Interface

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

Mapping ANSI Datatypes to Oracle Datatypes Through an OLE DB Interface

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

B
Datatype Mapping