1. User's Guide
  2. Data Type Conversion

A Data Type Conversion

Oracle maps ODBC data types to supported Oracle data types. When the results of a query are returned, Oracle converts the ODBC data types to Oracle data types.

The Oracle Database Gateway for ODBC maps the data types used in ODBC-compliant data sources to supported Oracle data types. When the results of a query are returned, the Oracle database converts the ODBC data types to Oracle data types. For example, the ODBC data type SQL_TYPE_TIMESTAMP is converted to Oracle's DATE data type.

If a table contains a column whose data type is not supported by Oracle Database Gateway for ODBC, the column information is not returned to the Oracle database.

Table A-1 maps ODBC data types into Oracle data types.

Table A-1 Data Type Mapping and Restrictions

ODBC Oracle Criteria If Oracle uses large varchar (32k)

SQL_BIGINT

NUMBER(19,0)

-

SQL_BINARY

RAW

-

SQL_CHAR

CHAR

-

SQL_DECIMAL(p,s)

NUMBER(p,s)

-

SQL_DOUBLE

FLOAT(53)

-

SQL_FLOAT

FLOAT(53)

-

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.

-

SQL_INTERVAL_YEAR

INTERVAL_YEAR_TO_MONTH

-

SQL_INTERVAL_MONTH

INTERVAL_YEAR_TO_MONTH

-

SQL_INTERVAL_YEAR_TO_MONTH

INTERVAL_YEAR_TO_MONTH

-

SQL_INTERVAL_DAY

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_HOUR

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_MINUTE

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_SECOND

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_DAY_TO_HOUR

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_DAY_TO_MINUTE

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_DAY_TO_SECOND

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_HOUR_TO_MINUTE

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_HOUR_TO_SECOND

INTERVAL_DAY_TO_SECOND

-

SQL_INTERVAL_MINUTE_TO_SECOND

INTERVAL_DAY_TO_SECOND

-

SQL_LONGVARBINARY

LONG RAW

-

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.

4000 < N < = 32740

N <= 32767

SQL_NUMERIC(p[,s])

NUMBER(p[,s])

-

SQL_REAL

FLOAT(24)

-

SQL_SMALLINT

NUMBER(5)

-

SQL_TYPE_TIME

CHAR(15)

-

SQL_TINYINT

NUMBER(3)

-

SQL_TYPE_DATE

DATE

-

SQL_TYPE_TIMESTAMP

DATE

-

SQL_VARBINARY

RAW

-

SQL_VARCHAR

VARCHAR2

N < = 4000

N <= 32767

SQL_WCHAR

NCHAR

-

SQL_WVARCHAR

NVARCHAR

-

SQL_WLONGVARCHAR

LONG

if Oracle DB Character Set = Unicode. Otherwise, it is not supported

SQL_BIT

NUMBER(3)

-