Oracle data mapping

Mapping to Oracle consolidated data types

The following table identifies how SQL Anywhere and UltraLite remote data types are mapped to Oracle consolidated data types. For example, a column of type BIT on the remote database should be type NUMBER on the consolidated database.

SQL Anywhere or UltraLite data type

Oracle data type

Notes

BIGINT

NUMBER(20)

BINARY(n<=2000)

RAW(n)

BINARY(n>2000)

BLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

BIT

NUMBER(1)

CHAR(n<=4000)

VARCHAR2(n byte)

Oracle VARCHAR2 allows you to specify the maximum number of bytes or characters. The maximum length of VARCHAR2 data is 4000 bytes. If you specify the character number, make sure the maximum data length is not over 4000 bytes.

CHAR(n>4000)

CLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

DATE

DATE2 or TIMESTAMP

SQL Anywhere or UltraLite fractional seconds cannot be preserved when using an Oracle DATE data type which has no fractional seconds. To avoid problems, do not use fractional seconds. The year must be in the range 1-9999.

When using the Interactive SQL utility, turn off the Return_date_time_as_string option before executing your SQL statement.

DATETIME

DATE2 or TIMESTAMP

SQL Anywhere or UltraLite fractional seconds cannot be preserved when using an Oracle DATE data type which has no fractional seconds. To avoid problems, do not use fractional seconds. The year must be in the range 1-9999.

When using the Interactive SQL utility, turn off the Return_date_time_as_string option before executing your SQL statement.

DECIMAL(p<=38,s)

NUMBER(p, 0<=s<=38)

In SQL Anywhere DECIMAL, p is between 1 and 127, and s is always less than or equal to p. In Oracle NUMBER, p ranges from 1 to 38, and s ranges from -84 to 127. To synchronize, the Oracle NUMBER scale must be restricted to between 0 and 38.

DECIMAL(p>38,s)

There is no corresponding data type in Oracle.

DOUBLE

DOUBLE PRECISION or BINARY_DOUBLE1

The special values INF, -INF and NAN of Oracle 10g BINARY_FLOAT and BINARY_DOUBLE cannot be synchronized with SQL Anywhere or UltraLite.

FLOAT(p)

FLOAT(p)

IMAGE

BLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

INTEGER

INT

LONG BINARY

BLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

LONG NVARCHAR

NCLOB

Oracle CLOB and NCLOB can hold up to 4G of data. SQL Anywhere LONG VARCHAR and LONG NVARCHAR can only hold up to 2G.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

LONG VARBIT

CLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

LONG VARCHAR

CLOB

Oracle CLOB and NCLOB can hold up to 4G of data. SQL Anywhere LONG VARCHAR and LONG NVARCHAR can only hold up to 2G.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

MONEY

NUMBER(19,4)

NCHAR(c)

NVARCHAR2(c char) or NCLOB

The size of SQL Anywhere NCHAR and Oracle NVARCHAR2 indicates the maximum number of Unicode characters. The data length of Oracle NVARCHAR2 can't be over 4000 bytes. It is difficult to calculate the maximum byte length from character size. In general, if the size is over 1000, map to NCLOB, otherwise map to NVARCHAR2.

NTEXT

NCLOB

Oracle NCLOB can hold up to 4G of data. SQL Anywhere NTEXT (or LONG NVARCHAR) can only hold up to 2G.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

NUMERIC(p<=38,s)

NUMBER(p, 0<=s<=38)

In SQL Anywhere NUMERIC, p is between 1 and 127, and s is always less than or equal to p. In Oracle NUMBER, p ranges from 1 to 38, and s ranges from -84 to 127. To synchronize, the Oracle NUMBER scale must be restricted to between 0 and 38.

NUMERIC(p>38,s)

There is no corresponding data type in Oracle.

NVARCHAR

NVARCHAR2(c char) or NCLOB

The size of SQL Anywhere NCHAR and Oracle NVARCHAR2 indicates the maximum number of Unicode characters. The data length of Oracle NVARCHAR2 can't be over 4000 bytes. It is difficult to calculate the maximum byte length from character size. In general, if the size is over 1000, map to NCLOB, otherwise map to NVARCHAR2.

REAL

REAL or BINARY_FLOAT1

The special values INF, -INF and NAN of Oracle 10g BINARY_FLOAT and BINARY_DOUBLE cannot be synchronized with SQL Anywhere or UltraLite.

SMALLDATETIME

DATE2 or TIMESTAMP

SQL Anywhere or UltraLite fractional seconds cannot be preserved when using an Oracle DATE data type which has no fractional seconds. To avoid problems, do not use fractional seconds. The year must be in the range 1-9999.

SMALLINT

NUMBER(5)

SMALLMONEY

NUMBER(10,4)

TEXT

CLOB

Oracle CLOB can hold up to 4G of data. SQL Anywhere TEXT (or LONG VARCHAR) can only hold up to 2G.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

TIME

DATE2 or TIMESTAMP

SQL Anywhere or UltraLite fractional seconds cannot be preserved when using an Oracle DATE data type which has no fractional seconds. To avoid problems, do not use fractional seconds.

When using the Interactive SQL utility, turn off the Return_date_time_as_string option before executing your SQL statement.

TIMESTAMP

DATE2 or TIMESTAMP

SQL Anywhere or UltraLite fractional seconds cannot be preserved when using an Oracle DATE data type which has no fractional seconds. To avoid problems, do not use fractional seconds. The year must be in the range 1-9999.

When using the Interactive SQL utility, turn off the Return_date_time_as_string option before executing your SQL statement.

TINYINT

NUMBER(3)

For download, Oracle values must be non-negative.

UNSIGNED BIGINT

NUMBER(20)

For download, Oracle values must be non-negative.

UNSIGNED INTEGER

NUMBER(11)

For download, Oracle values must be non-negative.

UNSIGNED SMALLINT

NUMBER(5)

For download, Oracle values must be non-negative.

UNSIGNED TINYINT

NUMBER(3)

For download, Oracle values must be non-negative.

UNIQUEIDENTIFIER

CHAR(36)

UNIQUEIDENTIFIERSTR

CHAR(36)

UNIQUEIDENTIFIERSTR is not recommended to use for Oracle. Use UNIQUEIDENTIFIER instead.

VARBINARY(n<=2000)

RAW(n)

VARBINARY(n>2000)

BLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

VARBIT(n<=4000)

VARCHAR2(n byte)

VARBIT(n>4000)

CLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

VARCHAR(n<=4000)

VARCHAR2(n byte)

Oracle VARCHAR2 allows you to specify the maximum number of bytes or characters. The maximum length of VARCHAR2 data is 4000 bytes. If you specify the character number, make sure the maximum data length is not over 4000 bytes.

VARCHAR(n>4000)

CLOB

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

XML

CLOB

Oracle CLOB and NCLOB can hold up to 4G of data. SQL Anywhere XML can only hold up to 2G.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

1 Only applies to Oracle version 10g or later.

2 Only applies to Oracle version 8i.

Note

The LONG data types are deprecated in Oracle 8, 8i and 9i.

For Oracle LONG data types to synchronize properly, you must check the Oracle Force Retrieval Of Long Columns option in the ODBC Data Source Configuration window of the iAnywhere Solutions Oracle ODBC driver.

Mapping to SQL Anywhere or UltraLite remote data types

The following table identifies how Oracle consolidated data types are mapped to SQL Anywhere and UltraLite remote data types. For example, a column of type LONG on the consolidated database should be type LONG VARCHAR on the remote database.

Oracle data type SQL Anywhere or UltraLite data type Notes

BFILE

LONG BINARY

Download only.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

BINARY_DOUBLE

DOUBLE

The special values INF, -INF and NAN of BINARY_FLOAT cannot be synchronized with SQL Anywhere or UltraLite. The precision of FLOAT and DOUBLE in Oracle is different from SQL Anywhere and UltraLite. The value of the data may change depending on the precision.

BINARY_FLOAT

REAL

The special values INF, -INF and NAN of BINARY_FLOAT cannot be synchronized with SQL Anywhere or UltraLite. The precision of FLOAT and DOUBLE in Oracle is different from SQL Anywhere and UltraLite. The value of the data may change depending on the precision.

BLOB

LONG BINARY

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

CHAR(n byte)

VARCHAR(n)

There is no equivalence between SQL Anywhere CHAR and Oracle CHAR. SQL Anywhere CHAR is equivalent to VARCHAR. You should not use CHAR/NCHAR in a consolidated database column that is synchronized. If you must use non-SQL Anywhere CHAR, run the MobiLink server with the -b option.

SQL Anywhere or UltraLite values can be longer than Oracle values, so make sure values are not too big when uploading.

CLOB

LONG VARCHAR

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

DATE

TIMESTAMP

The year must be in the range 1-9999.

INTERVAL YEAR(year_precision) TO MONTH

There is no corresponding data type in SQL Anywhere or UltraLite.

INTERVAL DAY(day_precision) TO SECOND(p)

There is no corresponding data type in SQL Anywhere or UltraLite.

LONG

LONG VARCHAR

LONG RAW

LONG BINARY

NCHAR(c char)

NVARCHAR(c)

There is no equivalence between SQL Anywhere NCHAR and Oracle NCHAR. SQL Anywhere NCHAR is equivalent to NVARCHAR. You should not use NCHAR in a consolidated database column that is synchronized. If you must use non-SQL Anywhere NCHAR, run the MobiLink server with the -b option.

SQL Anywhere or UltraLite values can be longer than Oracle values, so make sure values are not too big when uploading.

NCLOB

LONG NVARCHAR

Not available in UltraLite.

Oracle values can be longer than SQL Anywhere or UltraLite values, so make sure values are not too big when downloading.

NUMBER(p,s)

NUMBER(p,s)

In SQL Anywhere NUMBER, p is between 1 and 127, and s is always less than or equal to p. In Oracle NUMBER, p ranges from 1 to 38, and s ranges from -84 to 127. To synchronize, the Oracle NUMBER scale must be between 0 and 38.

NVARCHAR2(c char)

NVARCHAR(c)

Not available in UltraLite.

SQL Anywhere or UltraLite values can be longer than Oracle values, so make sure values are not too big when uploading.

RAW

BINARY

SQL Anywhere or UltraLite values can be longer than Oracle values, so make sure values are not too big when uploading.

ROWID

VARCHAR(64)

UROWID and ROWID are read-only and so are unlikely to be synchronized.

TIMESTAMP(p<=6)

TIMESTAMP

When p<6, you may need to ensure SQL Anywhere or UltraLite values have the same precision. Otherwise, conflict detection may fail and/or duplicate rows may result. The year must be in the range 1-9999.

TIMESTAMP(p>6)

There is no corresponding data type in SQL Anywhere or UltraLite.

TIMESTAMP(p) WITH LOCAL TIME ZONE

There is no corresponding data type in SQL Anywhere or UltraLite.

TIMESTAMP(p) WITH TIME ZONE

There is no corresponding data type in SQL Anywhere or UltraLite.

UROWID

VARCHAR(64)

UROWID and ROWID are read-only and so are unlikely to be synchronized.

VARCHAR2(n byte)

VARCHAR(n)

SQL Anywhere or UltraLite values can be longer than Oracle values, so make sure values are not too big when uploading.