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	Result is DATE (up to seconds)

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

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference