Sybase ASE to Oracle Migration

SQLines provides services and open source tools to 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.

We also help convert embedded SQL statements in C/C++ (ODBC, ESQL/C, CTLIB), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl, PHP, Python, Linux shell and other applications.

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

See also Sybase SQL Anywhere to Oracle Migration.

Sybase ASE to Oracle Migration Tools

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from Sybase ASE to Oracle.

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an Sybase ASE database use SQLines Data tool.

Try SQLines Online or download a free Desktop Version.

SQLines Data Tool

SQLines Data is a scalable, high performance data transfer, schema conversion and validation tool for Sybase ASE to Oracle migration.

For more information, see SQLines Data Tool.

Sybase ASE to Oracle Migration Reference

Technical information on migration from Sybase ASE to Oracle.

Identifiers

Converting identifiers:

Sybase ASE Oracle
First character Alphabetical and _ (underscore) Alphabetical
Subsequent characters Alphanumeric, _ , #, @, $, ¥ (yen), and £ Alphanumeric, _ , # and $

Max length:

Sybase ASE Oracle
Tables, columns, indexes, views, procedures, triggers 255 30
Databases 30 8

Data Types

Converting data types:

Sybase ASE Oracle
1 BIGDATETIME Date and time with fraction TIMESTAMP
2 BIGINT 64-bit integer NUMBER(19)
3 BIGTIME Time (Hour, minute, second and fraction) TIMESTAMP
4 BINARY(n) Fixed-length binary string RAW(n)
5 BIT 0 or 1; NULL is not allowed CHAR(1)
6 CHAR(n), CHARACTER(n) Fixed-length string CHAR(n), CHARACTER(n)
7 DATE Date (year, month and day) DATE Includes time part
8 DATETIME Date and time with fraction TIMESTAMP
9 DECIMAL(p,s), DEC(p,s) Fixed-point number NUMBER(p,s)
10 DOUBLE PRECISION Double-precision floating-point number BINARY_DOUBLE
11 FLOAT(p) Floating-point number BINARY_DOUBLE
12 IMAGE Variable-length binary data, ⇐ 2G BLOB
13 INT, INTEGER 32-bit integer NUMBER(10)
14 MONEY 64-bit currency amount NUMBER(15,4)
15 NCHAR(n) Fixed-length national character string NCHAR(n)
16 NUMERIC(p,s) Fixed-point number NUMBER(p,s)
17 NVARCHAR(n) Variable-length national character string NVARCHAR2(n)
18 REAL Single-precision floating-point number BINARY_FLOAT
19 SMALLDATETIME Date and time TIMESTAMP(0)
20 SMALLINT 16-bit integer NUMBER(5)
21 SMALLMONEY 32-bit currency amount NUMBER(6,4)
22 TEXT Variable-length character data, ⇐ 2G CLOB
23 TIME Time (Hour, minute, second and fraction) TIMESTAMP
24 TINYINT 8-bit unsigned integer, 0 to 255 NUMBER(3)
25 UNICHAR(n) Fixed-length Unicode string NCHAR(n)
26 UNITEXT Variable-length Unicode data, ⇐ 1G NCLOB
27 UNIVARCHAR(n) Variable-length Unicode string NVARCHAR2(n)
28 UNSIGNED BIGINT 64-bit unsigned integer NUMBER(20)
29 UNSIGNED INT 32-bit unsigned integer NUMBER(10)
30 UNSIGNED SMALLINT 16-bit unsigned integer NUMBER(5)
31 VARBINARY(n) Variable-length binary string RAW(n)
32 VARCHAR(n) Variable-length string VARCHAR2(n)

Built-in 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
36 DATENAME(datepart, date) Get datepart as a string TO_CHAR(date, 'datepart')
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)

SQL Statements

Converting SQL statements:

Sybase ASE Oracle
CREATE PROC[EDURE] Create a stored procedure CREATE [OR REPLACE] PROCEDURE

Transact-SQL Language

Converting Transact-SQL language:

Sybase ASE Oracle
Optional statement delimiter Adding ;

Transact-SQL Stored Procedures

Conversion of Transact-SQL stored procedures:

Sybase ASE Oracle
CREATE PROC CREATE OR REPLACE PROCEDURE
Procedure body without BEGIN END Adding BEGIN END
Returning result set using SELECT Adding OPEN cur FOR SELECT and OUT SYS_REFCURSOR parameter
GO Replacing with /

SQL Statements

Conversion of SQL statements:

Sybase ASE Oracle
CREATE PROC CREATE OR REPLACE PROCEDURE