Oracle to Microsoft SQL Server (MSSQL) Migration

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

• SQLines SQL Converter - SQL Conversion and Assessment tool
• SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

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

• SQL Language Elements
• Data Types
• Built-in SQL Functions
• CREATE TABLE
• CREATE INDEX
• CREATE SEQUENCE
• CREATE VIEW
• SELECT
• INSERT
• UPDATE
• CREATE PROCEDURE
• CREATE FUNCTION
• PL/SQL Statements
• SQL Statements
• Built-in PL/SQL Packages
• SQL*Plus Commands
• C# Applications
• PowerBuilder Applications

Converting SQL and PL/SQL language elements:

	Oracle		SQL Server
1	|| Operator	String concatenation	+ Operator and CONCAT function
2	TIMESTAMP '2023-05-23 10:53:01'	Date and time literal	{ ts '2023-05-23 10:53:01' }
3	+ and - Operators for date	Datetime arithmetic	+ and - Operators for datetime
4	cursor%NOTFOUND	No row fetched	@@FETCH_STATUS = 0

Character data types:

Unicode character data types:

Date and time data types:

	Oracle		SQL Server
1	DATE	Date and time with seconds	DATETIME	DATETIME2(0)
2	INTERVAL YEAR(p) TO MONTH	Date interval	VARCHAR(30)
3	INTERVAL DAY(p) TO SECOND(s)	Day and time interval
4	TIMESTAMP(p)	Date and time with fractional seconds	DATETIME2(p)
		0 <= p <= 9, default is 6	0 <= p <= 7, default is 7
5	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone	DATETIMEOFFSET(p)
		0 <= p <= 9, default is 6	0 <= p <= 7, default is 7
6	TIMESTAMP(p) WITH LOCAL TIME ZONE	Date and time with fraction and time zone
		0 <= p <= 9, default is 6

Data type mapping for other data types:

Converting string functions:

	Oracle		SQL Server
1	CHR(code)	Get character from ASCII code	CHAR(code)
2	INSTR(str, substr, start)	Get position of substring	CHARINDEX(substr, str, start)	Param order
	INSTR(str, substr, start, num)		User-defined function
3	LISTAGG(exp, delim)...	Aggregate concatenation	STRING_AGG(exp, delim)...	Since SQL Server 2017
4	TRIM(string)	Trim leading and trailing spaces	TRIM(string)	Since SQL Server 2017
			LTRIM(RTRIM(string))

Datetime functions:

	Oracle		SQL Server
1	ADD_MONTHS(datetime, n)	Add n months to datetime	DATEADD(month, n, datetime)
2	CURRENT_DATE	Get current date and time	GETDATE()
3	EXTRACT(YEAR FROM datetime)	Get the year from datetime	YEAR(datetime)
	EXTRACT(MONTH FROM datetime)	Get the month from datetime	MONTH(datetime)
	EXTRACT(DAY FROM datetime)	Get the day from datetime	DAY(datetime)
	EXTRACT(HOUR FROM datetime)	Get the hour from datetime	DATEPART(HOUR, datetime)
	EXTRACT(MINUTE FROM datetime)	Get the minute from datetime	DATEPART(MINUTE, datetime)
	EXTRACT(SECOND FROM datetime)	Get the seconds from datetime	DATEPART(SECOND, datetime)
4	FROM_TZ(datetime, timezone)	Setting timezone	datetime AT TIME ZONE timezone
5	MONTHS_BETWEEN(d1, d2)	Get difference in months	User-defined function
6	SYSDATE	Get current date and time	GETDATE()
7	TO_CHAR(datetime, format)	Convert datetime to string	CONVERT(VARCHAR(n), datetime, style)
	TO_CHAR(number, format)	Convert number to string	FORMAT(number, format)	Format is different
8	TO_DATE(string, format)	Convert string to datetime	CONVERT(DATETIME, string, style)
9	TO_TIMESTAMP(string, format)	Convert string to timestamp	CONVERT(DATETIME, string, style)
10	TRUNC(datetime)	Truncate datetime	Expressions using CONVERT

Numeric functions:

XML functions:

Other functions:

	Oracle		SQL Server
1	BITAND(exp1, exp2)	Perform bitwise AND	exp1 & exp2
2	DECODE(exp, when, then, ...)	Evaluate conditions	CASE exp WHEN when THEN then ... END

Converting CREATE TABLE statement keywords and clauses:

Storage and physical attributes:

STORAGE clause:

LOB storage clause:

Converting CREATE INDEX statement keywords and clauses:

	Oracle	SQL Server
1	CREATE INDEX schema.index_name	CREATE INDEX index_name	Can contain index name only

Converting sequences:

	Oracle		SQL Server
1	CREATE SEQUENCE seqname		CREATE SEQUENCE seqname
2	INCREMENT BY num	Positive or negative increment, default is 1	INCREMENT BY num
3	START WITH num	Initial value	START [WITH] num
4	MAXVALUE num	Maximum value is num	MAXVALUE num
	NOMAXVALUE	System limit	NO MAXVALUE
5	MINVALUE num	Minimum value is num	MINVALUE num
	NOMINVALUE	System limit	NO MINVALUE
6	CYCLE	Reuse values after reaching the limit	CYCLE
	NOCYCLE	No reuse, this is default	NO CYCLE
7	CACHE num	Cache num values, default is 20	CACHE num
	NOCACHE	Values are not preallocated	NO CACHE
8	ORDER	Guarantee numbers in order of requests	Option not supported, commented
	NOORDER	No guarantee, this is default	Option not supported, removed as it is default

By default MAXVALUE is 9999999999999999999999999999 in Oracle, and this value is out of range for type BIGINT in SQL Server, so MAXVALUE is commented in such case.

Converting CREATE VIEW statement keywords and clauses:

Converting SQL queries:

	Oracle		SQL Server
1	DUAL table	A single row, single column dummy table	FROM clause can be omitted, DUAL removed
2	FROM (SELECT …)	Optional alias for subquery	FROM (SELECT …) s	Alias required
3	CONNECT BY PRIOR	Hierarchical queries	Recursive Common Table Expressions (CTE)

Rownum pseudo-column conditions:

	Oracle		SQL Server
1	SELECT … WHERE ROWNUM = 1	Return 1 row only	SELECT TOP 1 …
	SELECT … WHERE ROWNUM <= n	Row limit	SELECT TOP n …
	SELECT … WHERE ROWNUM < n		SELECT TOP n - 1 …
	SELECT … WHERE ROWNUM < expr		SELECT TOP (expr) …	Expression has to be enclosed with ()

Converting SQL INSERT statement:

Converting SQL UPDATE statement:

	Oracle		SQL Server
1	UPDATE tab [alias] SET alias.col ...	Alias for table	UPDATE tab SET col ...	Alias cannot be specified

Converting CREATE PROCEDURE statement:

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

Converting CREATE FUNCTION statement from Oracle to SQL Server:

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

Converting Oracle PL/SQL statements and clauses to Transact-SQL:

Cursor declaration and operations:

	Oracle		SQL Server
1	CURSOR cur(params) IS select	Cursor declaration	DECLARE cur CURSOR LOCAL FOR select
2	OPEN cur(params);	Open a declared cursor	OPEN cur;
	OPEN out_cur FOR SELECT	Return a result set	SELECT statement
	OPEN out_cur FOR 'SELECT …'	Dynamic result set	EXECUTE sp_executesql N'SELECT …'
	OPEN out_cur FOR 'SELECT …' + expr [USING v1, …]		SET @sql = 'SELECT …' + expr; EXECUTE sp_executesql @sql [, @v1, …]
	OPEN out_cur FOR variable		EXECUTE (@variable)
3	FETCH cur INTO var, …	Fetch a cursor	FETCH cur INTO @var, …
4	IF cur%NOTFOUND THEN …	No row fetched	IF @@FETCH_STATUS = 0 BEGIN …
5	EXIT WHEN cur%NOTFOUND;	Leave cursor loop	IF @@FETCH_STATUS <> 0 BREAK;
6	CLOSE cur;	Close a cursor	CLOSE cur; DEALLOCATE cur;

Statement attributes:

Flow-of-control statements:

Executing dynamic SQL:

PL/SQL Tables:

• Converting PL/SQL Tables - TYPE name IS TABLE OF

Error handling:

EXCEPTION block:

Converting Oracle SQL statements to SQL Server:

Converting built-in PL/SQL packages:

Converting Oracle SQL*Plus commands to SQLCMD commands in SQL Server:

Converting embedded SQL and database access code in C# applications:

• Overview
• Converting RETURNING Clause in INSERT Statements

Converting embedded SQL and database access code in PowerBuilder applications:

• Overview
• DUAL Table in Embedded SQL
• Empty String and NULL