Mapping to Oracle consolidated data typesThe 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 Database 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 cannot 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 cannot 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 Database 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) |
|
|
ST_GEOMETRY |
SDO_GEOMETRY |
|
|
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. |
| TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | |
|
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 Database 10g or later.
2 Only applies to Oracle version 8i.
The LONG data types are deprecated in Oracle 8, 8i and 9i.
Mapping to SQL Anywhere or UltraLite remote data typesThe 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. |
|
SDO_GEOMETRY |
ST_GEOMETRY |
|
|
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<=6) WITH TIME ZONE |
TIMESTAMP WITH TIME ZONE | |
|
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. |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |