Microsoft SQL Server (MS SQL) to Oracle Migration

SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server (MSSQL, MS SQL), Azure SQL Database, Azure Synapse to Oracle.

Databases:

  • Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000
  • Oracle 21c, 19c, 18c, 12c, 11g and 10g

SQL Server 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 Microsoft SQL Server to Oracle.

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

SQLines tool is available in Online and Desktop editions:

Try SQLines Online or download a Trial Version.

Migration Reference

SQL Language Elements

Converting SQL and Transact-SQL language elements:

SQL Server Oracle
1 exp & exp2 Bitwise AND operator BITAND(exp, exp2)

Data Types

Converting character data types:

SQL Server Oracle
1 CHAR(n) Fixed-length non-Unicode string, 1 <= n <= 8000 CHAR(n)
2 NCHAR(n) Fixed-length Unicode UCS-2 string, 1 <= n <= 4000 NCHAR(n)
3 NVARCHAR(n) Variable-length Unicode UCS-2 string, 1 <= n <= 4000 NVARCHAR2(n)
NVARCHAR(max) 2 GB NCLOB
4 NTEXT 1 GB, Unicode UCS-2 string NCLOB
5 VARCHAR(n) Variable-length non-Unicode string, 1 <= n <= 8000 VARCHAR2(n)
VARCHAR(max) 2 GB CLOB

Numeric data types:

SQL Server Oracle
1 BIGINT 64-bit integer NUMBER(19)
2 DECIMAL(p, s) DEC(p, s) Fixed-point number NUMBER(p, s)
3 FLOAT(n) Single (n <= 24) and double (n <= 53)
precision floating-point number
NUMBER
4 INTEGER INT 32-bit integer NUMBER(10)
5 NUMERIC(p, s) Fixed-point number NUMBER(p, s)
6 REAL Single precision floating-point number NUMBER
7 SMALLINT 16-bit integer NUMBER(5)
8 TINYINT 0 to 255 NUMBER(3)

Date and time:

SQL Server Oracle
1 DATE Date (year, month and day) DATE Also includes time
2 DATETIME Date and time with milliseconds (accuracy .000, .003, .007 seconds) TIMESTAMP(3)
3 TIME(p) Time, 0 <= p <= 7 (100 nanoseconds accuracy) TIMESTAMP(p)

Other data types:

SQL Server Oracle
1 BIT 0, 1 and NULL NUMBER(1)
2 MONEY Monetary data NUMBER(19, 4)
3 SMALLMONEY Monetary data NUMBER(10, 4)
4 UNIQUEIDENTIFIER GUID with dashes (-) CHAR(36)
5 XML XML data XMLTYPE

Built-in SQL Functions

Converting string functions:

SQL Server Oracle
1 CONVERT(VARCHAR, datetime, style) Convert datetime to string TO_CHAR(datetime, format)

Converting datetime functions:

SQL Server Oracle
1 CONVERT(DATETIME, expr, style) Converts expr to datetime TO_TIMESTAMP(expr, format)
2 DATENAME(unit, datetime) Extract unit from datetime TO_CHAR(datetime, format)
3 DAY(datetime) Get the day of datetime EXTRACT(DAY FROM datetime)
4 GETDATE() Get the current date and time SYSTIMESTAMP
5 MONTH(datetime) Extract month from datetime EXTRACT(MONTH FROM datetime)
6 YEAR(datetime) Extract year from datetime EXTRACT(YEAR FROM datetime)

Converting other SQL functions:

SQL Server Oracle
1 SYSTEM_USER OS user name SYS_CONTEXT('USERENV','OS_USER')

CREATE TABLE Statement

Converting CREATE TABLE statement from SQL Server to Oracle:

SQL Server Oracle
1 IDENTITY(start, increment) Identity column Emulated using a sequence and trigger
2 DEFAULT exp Column default DEFAULT must be specified right after
data type, before NOT NULL etc.
3 CONSTRAINT name DEFAULT value Named DEFAULT DEFAULT value
4 CLUSTERED | NONCLUSTERED Clustered and non-clustered
primary and unique key
Keyword removed
5 col type CONSTRAINT name
PRIMARY KEY(col)
Inline primary key col type CONSTRAINT name
PRIMARY KEY - no column name
in inline constraint
6 PRIMARY KEY(col ASC | DESC, …) Sorting order in constraint PRIMARY KEY(col, …) - No ASC, DESC allowed
7 ROWGUIDCOL Indicates that the column is
GUID, but IDs are not generated
Keyword removed

Primary and unique key index options:

SQL Server Oracle
1 ALLOW_PAGE_LOCKS = ON | OFF Allow to use page locks Removed
2 ALLOW_ROW_LOCKS = ON | OFF Allow to use row locks Removed
3 FILLFACTOR = num Leave free space in leaf index nodes Removed
4 IGNORE_DUP_KEY = ON | OFF Ignore duplicate keys Removed
5 PAD_INDEX = ON | OFF Leave space in intermediate index nodes Removed
6 STATISTICS_NORECOMPUTE = ON | OFF Automatic statistics update Removed

SELECT Statement

Converting SQL queries from SQL Server to Oracle:

SQL Server Oracle
1 SELECT @v = (SELECT c FROM …) Assignment statement SELECT c INTO v FROM …
2 SELECT @v = c, @v2 = c2 FROM … SELECT INTO statement SELECT c, c2 INTO v, v2 FROM …
3 SELECT … FROM Result set from a procedure OPEN out_refcur FOR SELECT … FROM

Limit rows (Oracle 11g/10g):

SQL Server Oracle
1 SELECT TOP n Without sorting SELECT … WHERE rownum <= n
2 SELECT TOP n … ORDER BY With sorting SELECT * (SELECT … ORDER BY) WHERE rownum <= n
3 SELECT TOP n PERCENT … % Without sorting SELECT … WHERE rownum <= n/100 *
(SELECT COUNT(*) …)
4 SELECT TOP n PERCENT … ORDER BY % With sorting SELECT * (SELECT … ORDER BY) rownum <= n/100 *
(SELECT COUNT(*) …)

CREATE PROCEDURE Statement

Converting stored procedures from SQL Server to Oracle:

SQL Server Oracle
1 CREATE PROCEDURE | ALTER PROCEDURE name CREATE OR REPLACE PROCEDURE name
2 @param datatype = default OUT | OUTPUT p_param IN | OUT | IN OUT datatype DEFAULT default
3 Optional () for procedure parameters () required
4 AS IS | AS
5 RETURN int Return the status code RETURN;
6 GO /

For more information, see Conversion of Transact-SQL Statements.

SET Option Statement

Converting SET statement for options from SQL Server to Oracle:

SQL Server Oracle
1 SET ANSI_NULLS ON | OFF Use = <> with NULLs Commented
2 SET ANSI_PADDING ON | OFF Insert trailing blanks to VARCHAR Commented
3 SET NOCOUNT ON | OFF Send messages on affected rows Removed
4 SET QUOTED_IDENTIFIER ON | OFF Quote identifiers with "" Commented

Transact-SQL Statements

Variable declaration and assignment:

SQL Server Oracle
1 DECLARE @var [AS] datatype(len) [= default] Variable declaration var datatype(len) [:= default];
2 SET @var = value Assignment statement var := value;

Flow-of-control statements:

SQL Server Oracle
1 IF condition BEGIN … END IF statement IF condition THEN … END IF
2 IF … ELSE IF … IF ELSE IF statement IF … ELSIF … END IF

Cursors operations and attributes:

SQL Server Oracle
1 @@FETCH_STATUS = 0 Fetch was successful cur%FOUND
2 FETCH NEXT FROM cur INTO var, var2, … Fetch a cursor FETCH cur INTO var, var2, …

Error handling:

SQL Server Oracle
1 IF @@ERROR <> 0 Check for an SQL error EXCEPTION WHEN OTHERS THEN … Exception block

Transaction processing statements:

SQL Server Oracle
1 BEGIN TRANSACTION name Start a nested transaction SAVEPOINT name Set a savepoint
2 COMMIT TRANSACTION name Commit a nested transaction COMMIT Name cannot be specified
3 ROLLBACK TRANSACTION name Rollback a nested transaction ROLLBACK TO name Rollback to a savepoint

Other Transact-SQL statements:

SQL Server Oracle
2 PRINT text Send message to the client DBMS_OUTPUT.PUT_LINE(text)

SQL Statements

Converting SQL statements from SQL Server to Oracle:

SQL Server Oracle
1 ALTER TABLE tab ADD col type ALTER TABLE tab ADD col type
2 CREATE TYPE udt FROM datatype Create a user-defined type CREATE TYPE udt AS OBJECT (udt datatype)
3 USE dbname Change the database ALTER SESSION SET CURRENT_SCHEMA = dbname