Skip Headers

Oracle® Database Heterogeneous Connectivity Administrator's Guide
10g Release 1 (10.1)

Part Number B10764-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
Feedback

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

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