Sybase SQL Anywhere to Oracle Migration Tools and Services

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Sybase SQL Anywhere, Sybase Adaptive Server Anywhere (ASA) to Oracle.

• Sybase SQL Anywhere 17, 12, 11, 10, 9 and 5
• Oracle 19c, 12c and 11g

• SQLines - Database and SQL Conversion Tool
• SQLines Data - Data Transfer, Schema Migration and Validation Tool
• Online SQL Conversion Tool

Technical information on migration from Sybase SQL Anywhere to Oracle.

• Data Types

Last Update: Sybase SQL Anywhere 12 and Oracle 11g

Converting data types:

	Sybase SQL Anywhere		Oracle
1	BIGINT	64-bit integer	NUMBER(19)
2	BINARY(n)	Variable-length binary string, 1 ⇐ n ⇐ 32767	RAW(n)
3	BINARY VARYING(n)	Variable-length binary string, 1 ⇐ n ⇐ 32767	RAW(n)
4	BIT	0 or 1 value; NULL is not allowed	CHAR(1)
5	BIT VARYING(n)	Variable-length bit array, 1 ⇐ n ⇐ 32767	RAW(n/8)
6	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 32767	CHAR(n), CHARACTER(n)
7	DATE	Date (year, month and day)	DATE	Includes time part
8	DATETIME	Date and time with fraction	TIMESTAMP
9	DATETIMEOFFSET	Date and time with fraction and time zone	TIMESTAMP WITH TIME ZONE
10	DECIMAL(p,s), DEC(p,s)	Fixed point number	NUMBER(p,s)
11	DOUBLE [PRECISION]	Double-precision floating-point number	BINARY_DOUBLE
12	FLOAT(p)	Floating-point number	BINARY_DOUBLE
13	IMAGE	Binary data, ⇐ 2G	BLOB
14	INTEGER, INT	32-bit integer	NUMBER(10)
15	LONG BINARY	Binary data, ⇐ 2G	BLOB
16	LONG BIT VARYING	Bit array data, ⇐ 2G	BLOB
17	LONG NVARCHAR	UTF-8 character data, ⇐ 2G	NCLOB
18	LONG VARBIT	Bit array data, ⇐ 2G	BLOB
19	LONG VARCHAR	Character data, ⇐ 2G	CLOB
20	MONEY	Monetary data	NUMBER(19,4)
21	NCHAR(n)	Fixed-length UTF-8 string, 1 ⇐ n ⇐ 32767	NCHAR(n)
22	NTEXT	UTF-8 character data, ⇐ 2G	NCLOB
23	NUMERIC(p,s)	Fixed point number	NUMBER(p,s)
24	NVARCHAR(n)	Variable-length UTF-8 string, 1 ⇐ n ⇐ 32767	NVARCHAR2(n)
25	REAL	Single-precision floating-point number	BINARY_FLOAT
26	SMALLDATETIME	Date and time with fraction	TIMESTAMP
27	SMALLINT	16-bit integer	NUMBER(5)
28	SMALLMONEY	Monetary data, ⇐ million currency units	NUMBER(10,4)
29	TEXT	Character data, ⇐ 2G	CLOB
30	TIME	Time (hour, minute, second and fraction)	TIMESTAMP
31	TIMESTAMP	Date and time with fraction	TIMESTAMP
32	TIMESTAMP WITH TIME ZONE	Date and time with fraction and time zone	TIMESTAMP WITH TIME ZONE
33	TINYINT	8-bit unsigned integer, 0 to 255	NUMBER(3)
34	UNIQUEIDENTIFIER	16-byte GUID (UUID) data	CHAR(16)
35	UNIQUEIDENTIFIERSTR	GUID (UUID) data in string format	CHAR(36)
36	UNSIGNED BIGINT	64-bit unsigned integer	NUMBER(20)
37	UNSIGNED INT	32-bit unsigned integer	NUMBER(10)
38	UNSIGNED SMALLINT	16-bit unsigned integer	NUMBER(5)
39	UNSIGNED TINYINT	8-bit unsigned integer	NUMBER(3)
40	VARBINARY(n)	Variable-length binary string, 1 ⇐ n ⇐ 32767	RAW(n)
41	VARBIT(n)	Variable-length bit array, 1 ⇐ n ⇐ 32767	RAW(n/8)
42	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 32767	VARCHAR2(n)
43	XML	XML data	XMLTYPE

Besides data types, there are various other issues in table definitions requiring conversion from Sybase SQL Anywhere to Oracle syntax and equivalents:

• User-Defined Types (UDT)
• DEFAULT Values
• AUTOINCREMENT and IDENTITY Properties
• PRIMARY and FOREIGN KEY Constraints

There are various clauses in views and standalone SQL queries that require conversion from Sybase SQL Anywhere to Oracle:

• KEY JOIN
• Built-in Functions
• User-defined functions (UDF)

You have to convert built-in SQL functions statements in SQL queries, scripts, stored procedures, functions, triggers and applications:

	Sybase SQL Anywhere		Oracle
1	ABS(num)	Get the absolute value	ABS(num)
2	ACOS(num)	Get the arc cosine	ACOS(num)
3	ARGN(position, exp, exp2, …)	Get selected argument from list	CASE expression
4	ASCII(str)	Get ASCII code of left-most char	ASCII(str)
5	ASIN(num)	Get the arc sine	ASIN(num)
6	ATAN(num)	Get the arc tangent	ATAN(num)
7	ATAN2(x, y)	Get the arc tangent of x and y	ATAN2(x, y)
8	BASE64_DECODE(base64_str)	MIME base64 decoding	UTL_ENCODE.BASE64_DECODE
9	BASE64_ENCODE(str)	MIME base64 encoding	UTL_ENCODE.BASE64_ENCODE
10	BIT_LENGTH(bit_str)	Get length of bit_str in bits	LENGTH(bit_str) * 8
11	BIT_SUBSTR(bit_str, start, len)	Get a substring of bit_str	SUBSTR(bit_str, start, len)
12	BYTE_LENGTH(string)	Get length of string in bytes	LENGTHB(string)
13	BYTE_SUBSTR(string, start, len)	Get a substring of string in bytes	SUBSTRB(string, start, len)
14	CEILING(num)	Get the smallest following integer	CEIL(num)
	CEIL(num)
15	CHAR(num)	Get character from ASCII code	CHR(num)
16	CHAR_LENGTH(string)	Get length of string in characters	LENGTH(string)
17	CHARINDEX(substring, string)	Get position of substring	INSTR(string, substring)
18	COALESCE(exp1, exp2, …)	Return first non-NULL expression	COALESCE(exp1, exp2, …)
19	COMPARE(string, string2)	Compare two character strings	CASE expression
20	CONVERT(CHAR[(len)], exp)	Convert to string	TO_CHAR(exp)
21	CONVERT(VARCHAR[(len)], exp)
22	CONVERT(INTEGER, exp)	Convert to integer	TRUNC(exp)
23	COS(num)	Get the cosine	COS(num)
24	COT(num)	Get the cotangent	1 / TAN(num)
25	CSCONVERT(string, charset)	Convert string between character sets	CONVERT(string, charset)
26	DATALENGTH(exp)	Get length of exp in bytes	LENGTHB(exp)
27	DATE(datetime)	Extract date from datetime	TRUNC(datetime)
28	DATEADD(unit, num, datetime)	Add an interval to datetime	INTERVAL expression
29	DATEDIFF(MONTH, start, end)	Get MONTHS difference	MONTHS_BETWEEN(end, start)
30	DATEDIFF(YEAR, start, end)	Get YEARS difference	MONTHS_BETWEEN(end, start) / 12
31	DATEDIFF(DAY, start, end)	Get DAYS difference	end - start
32	DATEDIFF(HOUR, start, end)	Get HOURS difference	(end - start) * 24
33	DATEDIFF(MINUTE, start, end)	Get MINUTES difference	(end - start) * 1440
34	DATEDIFF(SECOND, start, end)	Get SECONDS difference	(end - start) * 86400
35	DATEFORMAT(datetime, format)	Convert datetime to string	TO_CHAR(datetime, format)
36	DATENAME(datepart, date)	Get the name of datepart as a string	TO_CHAR(date, 'datepart')
37	DATEPART(part, date)	Get datepart as an integer	TO_NUMBER(TO_CHAR(date, 'part'))
38	DATETIME(exp)	Convert exp to TIMESTAMP	TO_TIMESTAMP(exp)
39	DAY(datetime)	Extract day from datetime	EXTRACT(DAY FROM datetime)
40	DAYNAME(datetime)	Get the name of the weekday	TO_CHAR(datetime, 'Day')
41	DAYS(date)	Get the number of days	TRUNC(date - DATE '0000-03-02')
42	DB_ID(database_name)	Get ID of current database	SYS_CONTEXT
43	DB_NAME(database_id)	Get the name of current database	SYS_CONTEXT
44	DEGREES(num)	Convert radians to degrees	(num) * 180/3.1415926535
45	DOW(date)	Get the day of the week as integer	TO_NUMBER(TO_CHAR(date, 'D'))
46	ERRORMSG(exp)	Get the error message	SQLERRM(exp)
47	EXP(n)	Raise e to the nth power	EXP(n)
48	EXPRTYPE(exp, integer)	Get the data type of exp	User-defined function
49	FLOOR(num)	Get the largest preceding integer	FLOOR(num)
50	GET_BIT(bit_array, position)	Get specified bit in a bit_array	User-defined function
51	GETDATE()	Get the current date and time	SYSTIMESTAMP
52	GREATER(exp, exp2)	Get the greater of two expressions	GREATEST(exp, exp2)
53	HASH(string[, algorithm])	Get the specified value in hashed form	DBMS_CRYPTO.HASH
54	HEXTOINT(hexadecimal)	Convert hexadecimal to integer	TO_NUMBER(hexadecimal, 'XXXX')
55	HOUR(datetime)	Extract hour from datetime	EXTRACT(HOUR FROM datetime)
56	HTML_DECODE(string)	Decode special HTML characters	User-defined function
57	HTML_ENCODE(string)	Encode special HTML characters	HTF.ESCAPE_SC(string)
58	HTTP_DECODE(string)	URL decoding	User-defined function
59	HTTP_ENCODE(string)	URL encoding	User-defined function
60	IDENTITY(exp)	Get the sequential number of a row	ROWNUM
61	IFNULL(exp, exp2)	If exp is NULL, returns exp2 otherwise NULL	NVL2(exp, NULL, exp2)
	IFNULL(exp, exp2, exp3)	If exp is NULL, returns exp2 otherwise exp3	NVL2(exp, exp3, exp2)
62	INSERTSTR(position, str, str2)	Insert str2 into str in specified position	SUBSTR and concatenation
63	INTTOHEX(integer)	Convert integer to hexadecimal	TO_CHAR(TO_CHAR(integer), 'XXXX')
64	ISDATE(string)	Check for a valid datetime	User-defined function
65	ISNULL(exp, exp2, …)	Return first non-NULL expression	COALESCE(exp, exp2, …)
66	ISNUMERIC(string)	Check for a valid numeric	User-defined function
67	LCASE(string)	Lowercase string	LOWER(string)
68	LEFT(string, n)	Get n leftmost characters	SUBSTR(string, 1, n)
69	LENGTH(string), LEN(string)	Get length of string in chars	LENGTH(string)
70	LESSER(exp, exp2)	Get the lesser of two expressions	LEAST(exp, exp2)
71	LOCATE(str, substring, start)	Get position of substring	INSTR(str, substring, start)
72	LOG(numeric)	Get natural logarithm	LN(numeric)
73	LOG10(numeric)	Get base 10 logarithm	LOG(10, numeric)
74	LOWER(string)	Lowercase string	LOWER(string)
75	LTRIM(string)	Remove leading spaces	LTRIM(string)
76	MINUTE(datetime)	Extract minute from datetime	EXTRACT(MINUTE FROM datetime)
77	MOD(dividend, divisor)	Get remainder	MOD(dividend, divisor)
78	MONTH(date)	Extract month from date	EXTRACT(MONTH FROM date)
79	MONTHNAME(date)	Get the name of the month	TO_CHAR(date, 'Month')
80	NCHAR(integer)	Convert Unicode code point to char	NCHR(integer)
81	NEWID()	Generate GUIDs	SYS_GUID()
82	NOW([*])	Get the current date and time	SYSTIMESTAMP
83	NULLIF(exp1, exp2)	Return NULL if exp1 = exp2	NULLIF(exp1, exp2)
84	NUMBER([*])	Get the sequential number of a row	ROWNUM
85	PATINDEX(pattern, string)	Get starting position of pattern	REGEXP_INSTR(string, pattern)
86	PI([*])	Get number pi	3.1415926535897931
87	POWER(value, n)	Raise value to the nth power	POWER(value, n)
88	QUARTER(date)	Get the quarter of the year	TO_NUMBER(TO_CHAR(date, 'Q'))
89	RADIANS(numeric)	Convert degrees to radians	(numeric) * 3.1415926535/180
90	RAND([integer])	Get random float value in (0, 1)	DBMS_RANDOM.VALUE
91	REGEXP_SUBSTR(str, pattern)	Get a substring of str using regexp	REGEXP_SUBSTR(str, pattern)
92	REMAINDER(dividend, divisor)	Get remainder	MOD(dividend, divisor)
93	REPEAT(string, n)	Repeat string n times	RPAD(string, LENGTH(string) * n, string)
94	REPLACE(str, search, replace)	Replace search-string	REPLACE(str, search, replace)
95	REPLICATE(string, n)	Repeat string n times	RPAD(string, LENGTH(string) * n, string)
96	REVERSE(string)	Get reverse string	REVERSE(string)
97	RIGHT(string, n)	Get n rightmost characters	SUBSTR(string, -n)
98	ROUND(num, integer)	Get rounded value	ROUND(num, integer)
99	RTRIM(string)	Remove trailing spaces	RTRIM(string)
100	SECOND(datetime)	Extract second from datetime	TRUNC(EXTRACT(SECOND FROM datetime))
101	SIGN(exp)	Get sign of exp	SIGN(exp)
102	SIN(num)	Get sine	SIN(num)
103	SOUNDEX(string)	Get 4-character sound code	SOUNDEX(string)
104	SPACE(integer)	Get string of spaces	RPAD(' ', integer)
105	SQRT(num)	Get square root	SQRT(num)
106	STR(numeric)	Get string equivalent of a number	TO_CHAR(numeric)
107	STRING(str, str2, ...)	Concatenates strings	(str || str2 || …)
108	STUFF(exp, start, len, rep)	Replace characters in string	User-defined function
109	SUBSTRING(string, pos, len)	Get a substring of string	SUBSTR(string, pos, len)
110	SUBSTR(string, pos, len)
111	SUSER_ID()	Get the server user’s ID	UID
112	SUSER_NAME()	Get the server user’s name	SYS_CONTEXT('USERENV', 'OS_USER')
113	SWITCHOFFSET(tmz, offset)	Change time zone offset
114	TAN(num)	Get tangent	TAN(num)
115	TEXTPTR(exp)	Get pointer that conform to LOB
116	TO_CHAR(exp)	Convert to string	TO_CHAR(exp)
117	TO_NCHAR(exp)	Convert to NCHAR	TO_NCHAR(exp)
118	TODATETIMEOFFSET(tms, tz)	Convert to DATETIMEOFFSET	FROM_TZ(tms, tz)
119	TODAY([*])	Get the current date	TRUNC(SYSDATE)
120	TRIM(string)	Remove leading and trailing spaces	TRIM(string)
121	TRUNCNUM(numeric, integer)	Truncate numeric	TRUNC(numeric, integer)
122	TRUNCATE(numeric, integer)
123	TSEQUAL(value1, value2)	Compare two timestamp values	value1 = value2
124	UCASE(string)	Uppercase string	UPPER(string)
125	UNICODE(string)	Get Unicode code of the 1st character
126	UNISTR(string)	Convert Unicode code points to chars	UNISTR(string)
127	UPPER(string)	Uppercase string	UPPER(string)
128	USER_ID()	Get ID of the current user	UID
129	USER_NAME()	Get name of the current user	SYS_CONTEXT('USERENV', 'OS_USER')
130	XMLCONCAT(exp, exp2, …)	Concatenate XML expressions	XMLCONCAT(exp, exp2, …)
131	XMLELEMENT(NAME exp)	Get an XQuery element node	XMLELEMENT(NAME exp)
132	XMLFOREST(exp, exp2, …)	Get a forest of XML expressions	XMLFOREST(exp, exp2, …)
133	XMLGEN(construnctor, exp, …)	Get XML based on constructor
134	YEAR(date)	Extract year from date	EXTRACT(YEAR FROM date)

You also need to convert SQL statements in SQL scripts, stored procedures, functions, triggers and applications:

More about SQL statements conversion...