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
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 |
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 |
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) |
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 |