Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » MobiLink - Server Administration » MobiLink Data Mappings Between Remote and Consolidated Databases

IBM DB2 UDB data mapping Next Page

Oracle data mapping


Note

Only supported data types are ncluded here.

SQL Anywhere or UltraLite remote data types mapped to Oracle consolidated data types

The following table identifies how SQL Anywhere or 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

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.

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 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.

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 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.

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.

TEXT

CLOB

Oracle CLOB 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.

UNIQUEIDENTIFIERSTR

CHAR(36)

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

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 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.

UNSIGNED BIGINT

NUMBER(20)

For download, Oracle values must be non-negative.

BIGINT

NUMBER(20)

BIT

NUMBER(1)

For download, Oracle values must be non-negative.

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. In order 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)

UNSIGNED INTEGER

NUMBER(11)

For download, Oracle values must be non-negative.

INTEGER

INT

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. In order 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.

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.

UNSIGNED SMALLINT

NUMBER(5)

For download, Oracle values must be non-negative.

SMALLINT

NUMBER(5)

UNSIGNED TINYINT

NUMBER(3)

For download, Oracle values must be non-negative.

TINYINT

NUMBER(3)

For download, Oracle values must be non-negative.

MONEY

NUMBER(19,4)

SMALLMONEY

NUMBER(10,4)

LONG VARBIT

CLOB

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

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.

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.

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.

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.

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.

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.

IMAGE

BLOB

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

LONG BINARY

BLOB

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

UNIQUEIDENTIFIER

CHAR(36)

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.

1 Only applies to Oracle version 10g or above.

2 Only applies to Oracle version 8i.

Notes

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 dialog of the iAnywhere Solutions Oracle ODBC driver.

Oracle consolidated data types mapped to SQL Anywhere or UltraLite remote data types

The following table identifies how Oracle consolidated data types are mapped to SQL Anywhere or 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 typeSQL Anywhere or UltraLite data typeNotes

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.

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.

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 0 and 38.

LONG

LONG VARCHAR

DATE

TIMESTAMP

The year must be in the range 1-9999.

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.

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.

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 TIME ZONE

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.

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.

RAW

BINARY

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

LONG RAW

LONG BINARY

ROWID

VARCHAR(64)

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

UROWID

VARCHAR(64)

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

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.

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.

CLOB

LONG VARCHAR

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

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.

BLOB

LONG BINARY

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

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.