Skip Headers
Oracle® Database Heterogeneous Connectivity Administrator's Guide
10g Release 2 (10.2)

Part Number B14232-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

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