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.

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL scripts conversion and assessment tool. Try Online

Databases:

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

Migration Reference

SQL Language Elements

Converting SQL and Transact-SQL language elements:

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

Datetime arithmetics:

SQL Server Oracle
1 GETDATE() + n Add n days to a datetime SYSTIMESTAMP + n Result is DATE
(up to seconds)
2 GETDATE() + 0.1 Add 0.1 of 24 hours i.e. 144 minutes SYSTIMESTAMP + 0.1

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)
2 STR(num, length, decimals) Convert decimal number to string TO_CHAR(num, 'FM999.99')

Converting datetime functions:

SQL Server Oracle
1 CONVERT(DATETIME, expr, style) Converts expr to datetime TO_TIMESTAMP(expr, format)
2 DATEADD(part, num, datetime) Add an interval to datetime INTERVAL expression
3 DATENAME(unit, datetime) Extract unit from datetime TO_CHAR(datetime, format)
4 DAY(datetime) Get the day of datetime EXTRACT(DAY FROM datetime)
5 EOMONTH(datetime) Get the last day of the month of datetime LAST_DAY(datetime)
6 GETDATE() Get the current date and time SYSTIMESTAMP
7 MONTH(datetime) Extract month from datetime EXTRACT(MONTH FROM datetime)
8 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:

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
8 col AS exp [PERSISTED] Computed column col AS (exp)

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

ALTER TABLE Statement

Converting ALTER TABLE statement:

SQL Server Oracle
1 ALTER TABLE tab ADD CONSTRAINT cns
DEFAULT exp FOR col
Add default ALTER TABLE tab MODIFY (col DEFAULT exp)
2 ALTER TABLE tab CHECK CONSTRAINT cns Validate constraint ALTER TABLE tab ENABLE CONSTRAINT cns

SELECT Statement

Converting SQL queries:

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
4 SELECT … FROM dbo.func(param) Table valued function SELECT … FROM TABLE (func(param))
5 STUFF((SELECT ... FOR XML PATH... Aggregate concatenation LISTAGG expression

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(*) …)

INSERT Statement

Converting SQL INSERT statement:

SQL Server Oracle
1 INSERT [INTO] table INSERT INTO table INTO keyword is required

CREATE PROCEDURE Statement

Converting stored procedures:

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.

CREATE FUNCTION Statement

Converting user defined functions:

SQL Server Oracle
1 CREATE FUNCTION name CREATE OR REPLACE FUNCTION name
2 RETURN TABLE Table function PIPELINED

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 DECLARE @tab [AS] TABLE (…) Table variable CREATE GLOBAL TEMPORARY TABLE tab (…)
3 SET @var = value Assignment statement var := value;
4 SELECT @var = exp, @var2 = exp2 Assignment statement var := exp; var2 = exp2

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, …
3 CLOSE cur Close cursor CLOSE cur
4 DEALLOCATE cur Deallocate cursor Removed

Stored procedure calls:

SQL Server Oracle
1 EXEC sp_name @param1 = value1,… Execute a procedure sp_name(p_param1 => value1,…)

Error handling:

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

Exception block:

SQL Server Oracle
1 BEGIN TRY … END TRY
BEGIN CATCH … END CATCH
Exception block BEGIN …
EXCEPTION WHEN OTHERS THEN …
END

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
1 PRINT text Send message to the client DBMS_OUTPUT.PUT_LINE(text) Inside a PL/SQL block
PROMPT text In SQL script

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