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 12, 11, 10, 9 and 5
  • Oracle 12 and 11g

Sybase SQL Anywhere to Oracle Migration Tools

Migration Reference

Technical information on migration from Sybase SQL Anywhere to Oracle.

Last Update: Sybase SQL Anywhere 12 and Oracle 11g

Data Types

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

Table Definitions (DDL)

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

Views and SQL Queries

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

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

Built-in SQL Functions

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)

SQL Statements

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

Sybase SQL Anywhere Oracle
CREATE DOMAIN CREATE TYPE

More about SQL statements conversion...