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 tables in this appendix show how Oracle maps ODBC data types to supported Oracle data types when it is retrieving data from a non-Oracle system.

This appendix contains the following table:

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

-