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	DATE² 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	DATE² 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_DOUBLE¹	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_FLOAT¹	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	DATE² 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	DATE² 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	DATE² 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.

¹ Only applies to Oracle Database 10g or later.

² Only applies to Oracle version 8i.

Note

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

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