Sybase ASE to Oracle Migration

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

• SQLines Data - Schema, data migration and validation tool
• SQLines SQL Converter - SQL scripts conversion tool

Databases:

• Sybase Adaptive Server Enterprise 16.x, 15.x, 12.x and 11.x
• Oracle 21c, 19c, 18c, 12c and 11g

See also Sybase SQL Anywhere to Oracle Migration.

• Identifiers
• Data Types
• Built-in SQL Functions

Converting identifiers:

Max length:

Other identifier issues:

Character data types:

Numeric data types:

	Sybase ASE		Oracle
1	BIGINT	64-bit integer	NUMBER(19)
2	DECIMAL(p,s), DEC(p,s)	Fixed-point number	NUMBER(p,s)
3	DOUBLE PRECISION	Double-precision floating-point number	BINARY_DOUBLE
4	FLOAT(p)	Floating-point number	BINARY_DOUBLE
5	INT, INTEGER	32-bit integer	NUMBER(10)
6	MONEY	64-bit currency amount	NUMBER(15,4)
7	NUMERIC(p,s)	Fixed-point number	NUMBER(p,s)
8	REAL	Single-precision floating-point number	BINARY_FLOAT
9	SMALLINT	16-bit integer	NUMBER(5)
10	SMALLMONEY	32-bit currency amount	NUMBER(6,4)
11	TINYINT	8-bit unsigned integer, 0 to 255	NUMBER(3)
12	UNSIGNED BIGINT	64-bit unsigned integer	NUMBER(20)
13	UNSIGNED INT	32-bit unsigned integer	NUMBER(10)
14	UNSIGNED SMALLINT	16-bit unsigned integer	NUMBER(5)

Datetime data types:

	Sybase ASE		Oracle
1	BIGDATETIME	Date and time with fraction	TIMESTAMP
2	BIGTIME	Time (hour, minute, second and microseconds fraction)	TIMESTAMP
3	DATE	Date (year, month and day)	DATE	Includes time part
4	DATETIME	Date and time with fraction	TIMESTAMP
5	SMALLDATETIME	Date and time (minute accuracy)	TIMESTAMP(0)
6	TIME	Time (hour, minute, second and milliseconds fraction)	TIMESTAMP

Binary data types:

	Sybase ASE		Oracle
1	BINARY(n)	Fixed-length binary data, n ⇐ 255, padded with 0x00	RAW(n)	n ⇐ 2000, variable-length
2	IMAGE	Variable-length binary data, ⇐ 2G	BLOB
3	VARBINARY(n)	Variable-length binary data, n ⇐ 255	RAW(n)	n ⇐ 2000

Other data types:

Converting string functions:

Converting datetime functions:

Converting built-in SQL functions:

	Sybase ASE		Oracle
1	ABS(num)	Get the absolute value	ABS(num)
2	ACOS(num)	Get the arc cosine	ACOS(num)
3	ASCII(str)	Get ASCII code of left-most char	ASCII(str)
4	ASEHOSTNAME()	Get the workstation name	SYS_CONTEXT('USERENV', 'SERVER_HOST')
5	ASIN(num)	Get the arc sine	ASIN(num)
6	ATAN(num)	Get the arc tangent	ATAN(num)
7	ATN2(x, y)	Get arctangent of x and y	ATAN2(x, y)
8	BIGINTTOHEX(exp)	Convert bigint to 8-byte hex	TO_CHAR(ABS(exp), 'XXXX')
9	BINTOSTR(exp)	Convert hexadecimal to string
10	CEILING(num)	Get the smallest following integer	CEIL(num)
11	CHAR(num)	Get character from ASCII code	CHR(num)
12	CHAR_LENGTH(string)	Get length of string in characters	LENGTH(string)
13	CHARINDEX(substr, str)	Get position of substr	INSTR(str, substr)
14	COALESCE(exp1, exp2, …)	Return first non-NULL expression	COALESCE(exp1, exp2, …)
15	COL_LENGTH('table', 'col')	Get length of column	User-defined function
16	COL_NAME(tbl_id, col_id)	Get name of column	User-defined function
17	COMPARE(str, str2)	Compare two strings	User-defined function
18	CONVERT(CHAR, exp)	Convert to string	TO_CHAR(exp)
19	CONVERT(VARCHAR, exp)
20	CONVERT(IMAGE, exp)	Convert to binary data	TO_BLOB(exp)
21	CONVERT(TEXT, exp)	Convert to character data	TO_CLOB(exp)
22	COS(num)	Get the cosine	COS(num)
23	COT(num)	Get the cotangent	1 / TAN(num)
24	CURRENT_BIGDATETIME()	Get the current date and time	CURRENT_TIMESTAMP
25	CURRENT_BIGTIME()	Get the current time with fraction	CURRENT_TIMESTAMP
26	CURRENT_DATE()	Get the current date	TRUNC(SYSDATE)
27	CURRENT_TIME()	Get the current time	SYSTIMESTAMP
28	DATALENGTH(exp)	Get length of exp in bytes	LENGTHB(exp)
29	DATEADD(unit, num, date)	Add an interval to datetime	INTERVAL expression
30	DATEDIFF(MM, start, end)	Get MONTHS difference	MONTHS_BETWEEN(end, start)
31	DATEDIFF(YY, start, end)	Get YEARS difference	MONTHS_BETWEEN(end, start) / 12
32	DATEDIFF(DAY, start, end)	Get DAYS difference	end - start
33	DATEDIFF(HH, start, end)	Get HOURS difference	(end - start) * 24
34	DATEDIFF(MI, start, end)	Get MINUTES difference	(end - start) * 1440
35	DATEDIFF(SS, start, end)	Get SECONDS difference	(end - start) * 86400
37	DATEPART(datepart, date)	Get datepart as an integer	TO_NUMBER(TO_CHAR(date, 'datepart'))
38	DAY(datetime)	Extract day from datetime	EXTRACT(DAY FROM datetime)
39	DB_ID()	Get ID number of current database	SYS_CONTEXT('USERENV', 'CURRENT_SCHEMAID')
40	DB_INSTANCEID()	Get ID number of instance	SYS_CONTEXT('USERENV', 'INSTANCE')
41	DB_NAME()	Get the name of database	SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
42	DEGREES(num)	Convert radians to degrees	(num) * 180/3.1415926535
43	EXP(n)	Raise e to the nth power	EXP(n)
44	FLOOR(num)	Get the largest preceding integer	FLOOR(num)
45	GETDATE()	Get the current date and time	SYSTIMESTAMP
46	GETUTCDATE()	Get the current UTC datetime	SYS_EXTRACT_UTC(SYSTIMESTAMP)
47	HEXTOBIGINT(exp)	Convert hexadecimal to bigint	TO_NUMBER(exp, 'XXXX')
48	HEXTOINT(exp)	Convert hexadecimal to integer	TO_NUMBER(exp, 'XXXX')
49	HOST_ID()	Get the workstation ID
50	HOST_NAME()	Get the workstation name	SYS_CONTEXT('USERENV', 'SERVER_HOST')
51	INDEX_COL(obj, id, key)	Get indexed column name
52	INDEX_COLORDER	Get column order	User-defined function
53	INDEX_NAME	Get index name	User-defined function
54	INSTANCE_ID()	Get ID number of instance	SYS_CONTEXT('USERENV', 'INSTANCE')
55	INSTANCE_NAME()	Get name of instance	SYS_CONTEXT('USERENV', 'INSTANCE_NAME')
56	INTTOHEX(integer)	Convert integer to hexadecimal	TO_CHAR(TO_CHAR(integer), 'XXXXXXXX')
57	ISDATE(string)	Check for a valid datetime	User-defined function
58	ISNULL(exp, replace)	Replace NULL	NVL(exp, replace)
59	ISNUMERIC(string)	Check for a valid numeric	User-defined function
60	IS_SINGLEUSERMODE()	Check for a single-user mode	User-defined function
61	LEFT(string, n)	Get n leftmost characters	SUBSTR(string, 1, n)
62	LEN(string)	Get length of string in characters	LENGTH(string)
63	LOG(numeric)	Get natural logarithm	LN(numeric)
64	LOG10(numeric)	Get base 10 logarithm	LOG(10, numeric)
65	LOWER(string)	Lowercase string	LOWER(string)
66	LTRIM(string)	Remove leading spaces	LTRIM(string)
67	MONTH(date)	Extract month from date	EXTRACT(MONTH FROM date)
68	NEWID([flag])	Generate GUIDs	SYS_GUID()
69	NEXT_IDENTITY('table')	Get next identity value of table
70	NULLIF(exp1, exp2)	Return NULL if exp1 = exp2	NULLIF(exp1, exp2)
71	OBJECT_ID(name)	Get database object ID	User-defined function
72	OBJECT_NAME(id [,db_id])	Get database object name	User-defined function
73	OBJECT_OWNER_ID(id)	Get database object’s owner ID	User-defined function
74	PARTITION_ID(table, ptn)	Get partition ID
75	PARTITION_NAME(indid, id)	Get partition name	User-defined function
76	PARTITION_OBJECT_ID(id)	Get object ID for specified ptn ID
77	PASSWORD_RANDOM(len)	Get pseudorandom password	DBMS_RANDOM.STRING('P', len)
78	PATINDEX(pattern, string)	Get starting position of pattern	REGEXP_INSTR(string, pattern)
79	PI()	Get number pi	3.1415926535897931
80	POWER(value, n)	Raise value to the nth power	POWER(value, n)
81	RADIANS(numeric)	Convert degrees to radians	(numeric) * 3.1415926535/180
82	RAND([integer])	Get random float value in (0, 1)	DBMS_RANDOM.VALUE
83	RAND2()	Var. RAND for each returned row	DBMS_RANDOM.VALUE
84	REPLICATE(string, n)	Repeat string n times	RPAD(string, LENGTH(string) * n, string)
85	RESERVE_IDENTITY(tbl, n)	Repeat string n times
86	REVERSE(string)	Get reverse string	REVERSE(string)
87	RIGHT(string, n)	Get n rightmost characters	SUBSTR(string, -n)
88	ROUND(num, integer)	Get rounded value	ROUND(num, integer)
89	RTRIM(string)	Remove trailing spaces	RTRIM(string)
90	SIGN(exp)	Get sign of exp	SIGN(exp)
91	SIN(num)	Get sine	SIN(num)
92	SOUNDEX(string)	Get 4-character sound code	SOUNDEX(string)
93	SPACE(integer)	Get string of spaces	RPAD(' ', integer)
94	SPID_INSTANCE_ID(spid)	Get instance ID by spid
95	SQUARE(exp)	Get square	POWER(exp, 2)
96	SQRT(num)	Get square root	SQRT(num)
97	STR_REPLACE(s, sub, r)	Replace substring	REPLACE(s, sub, r)
98	STRTOBIN(exp)	Convert string to hexadecimal
99	STUFF(exp, start, len, rep)	Replace characters in string	User-defined function
100	SUBSTRING(exp, pos, len)	Get a substring of exp	SUBSTR(exp, pos, len)
101	SUSER_ID()	Get the server user’s ID	UID
102	SUSER_NAME()	Get the server user’s name	SYS_CONTEXT('USERENV', 'OS_USER')
103	TAN(num)	Get tangent	TAN(num)
104	TEXTPTR(exp)	Get pointer that conform to LOB
105	TEXTVALID(exp, pointer)	Check validity of a pointer
106	TO_UNICHAR(int)	Convert int to Unicode character	TO_NCHAR(int)
107	TSEQUAL(value1, value2)	Compare two timestamp values	value1 = value2
108	UHIGHSURR(exp, start)	Check for higher part of surrogate pair
109	ULOWSURR(exp, start)	Check for lower part of surrogate pair
110	UPPER(string)	Uppercase string	UPPER(string)
111	USCALAR(exp)	Get Unicode code of exp
112	USER	Get the current user	USER
113	USER_ID()	Get ID of the current user	UID
114	USER_NAME()	Get name of the current user	SYS_CONTEXT('USERENV', 'OS_USER')
115	XMLEXTRACT(xpath, exp)	Extract XML data	EXTRACT(exp, xpath)
116	XMLPARSE(exp)	Parse XML document
117	XMLREPRESENTATION(exp)	Check for XML content
118	XMLVALIDATE(exp)	Get XML with information	XMLISVALID(exp)
119	YEAR(date)	Extract year from date	EXTRACT(YEAR FROM date)

Converting SQL statements:

Converting Transact-SQL language:

Conversion of Transact-SQL stored procedures:

Conversion of SQL statements: