This is an old revision of the document!

Oracle to Microsoft SQL Server Migration

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

SQLines Data - Data transfer, schema migration and validation tool.
SQLines SQL Converter - SQL scripts conversion tool.

Databases:

Oracle 12c, 11g, 10g and 9i
Microsoft SQL Server 2017, 2016, 2014, 2012, 2008 and 2005

Migration Reference

SQL Language Elements

Converting SQL and PL/SQL language elements:

	Oracle		SQL Server
1	\|\| Operator	String concatenation	+ Operator and CONCAT function
2	+ and - Operators for date	Datetime arithmetic	+ and - Operators for datetime
3	cursor%NOTFOUND	No row fetched	@@FETCH_STATUS = 0

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

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	VARCHAR(30)
4	TIMESTAMP(p)	Date and time with fraction	DATETIME2(p)
5	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone	DATETIMEOFFSET(p)

Data type mapping for other data types:

	Oracle		SQL Server
1	BFILE	Pointer to binary file, ⇐ 4G	VARCHAR(255)
2	BINARY_FLOAT	32-bit floating-point number	REAL
3	BINARY_DOUBLE	64-bit floating-point number	DOUBLE PRECISION
4	BLOB	Binary large object, ⇐ 4G	VARBINARY(max)
5	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 2000	CHAR(n), CHARACTER(n)
6	CLOB	Character large object, ⇐ 4G	VARCHAR(max)
7	DECIMAL(p,s), DEC(p,s)	Fixed-point number	DECIMAL(p,s), DEC(p,s)
8	DOUBLE PRECISION	Floating-point number	FLOAT
9	FLOAT(p)	Floating-point number	FLOAT
10	INTEGER, INT	38 digits integer	DECIMAL(38)
11	LONG	Character data, ⇐ 2G	VARCHAR(max)
12	LONG RAW	Binary data, ⇐ 2G	VARBINARY(max)
13	NCHAR(n)	Fixed-length UTF-8 string, 1 ⇐ n ⇐ 2000	NCHAR(n)
14	NCHAR VARYING(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000	NVARCHAR(n)
15	NCLOB	Variable-length Unicode string, ⇐ 4G	NVARCHAR(max)
16	NUMBER(p,0), NUMBER(p)	8-bit integer, 1 <= p < 3	TINYINT	(0 to 255)
		16-bit integer, 3 <= p < 5	SMALLINT
		32-bit integer, 5 <= p < 9	INT
		64-bit integer, 9 <= p < 19	BIGINT
		Fixed-point number, 19 <= p <= 38	DECIMAL(p)
17	NUMBER(p,s)	Fixed-point number, s > 0	DECIMAL(p,s)
18	NUMBER, NUMBER(*)	Floating-point number	FLOAT
19	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
20	NVARCHAR2(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000	NVARCHAR(n)
21	RAW(n)	Variable-length binary string, 1 ⇐ n ⇐ 2000	VARBINARY(n)
22	REAL	Floating-point number	FLOAT
23	ROWID	Physical row address	CHAR(18)
24	SMALLINT	38 digits integer	DECIMAL(38)
25	UROWID(n)	Logical row addresses, 1 ⇐ n ⇐ 4000	VARCHAR(n)
26	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 4000	VARCHAR(n)
27	VARCHAR2(n)	Variable-length string, 1 ⇐ n ⇐ 4000	VARCHAR(n)
28	XMLTYPE	XML data	XML

Data Type Attributes:

	Oracle	SQL Server
Character Data Types	BYTE and CHAR column size semantics	Size is always in bytes

Built-in SQL Functions

Converting string functions:

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, occur)		User-defined function
3	TRIM(string)	Trim leading and trailing spaces	LTRIM(RTRIM(string))

Other functions:

	Oracle		SQL Server
1	ADD_MONTHS(datetime, n)	Add n months to datetime	DATEADD(month, n, datetime)
2	DECODE(exp, when, then, ...)	Evaluate conditions	CASE exp WHEN when THEN then ... END
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)
4	MONTHS_BETWEEN(d1, d2)	Get difference in months	User-defined function
5	SYSDATE	Get current date and time	GETDATE()
6	TO_CHAR(datetime, format)	Convert datetime to string	CONVERT(VARCHAR(n), datetime, style)
7	TO_DATE(string, format)	Convert string to datetime	CONVERT(DATETIME, string, style)
8	TRUNC(datetime)	Truncate datetime	Expressions using CONVERT

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

	Oracle	SQL Server
1	ENABLE constraint attribute	Removed

Storage and physical attributes:

	Oracle	SQL Server
1	PCTFREE num	Removed
2	PCTUSED num	Removed
3	INITRANS num	Removed
4	MAXTRANS num	Removed
5	COMPRESS [BASIC] \| COMPRESS num \| NOCOMPRESS	Removed
6	LOGGING \| NOLOGGING	Removed
7	SEGMENT CREATION IMMEDIATE \| DEFERRED	Removed
8	TABLESPACE name	ON name
9	LOB (column) STORE AS BASIC FILE (params)	Removed
10	PARALLEL num \| NOPARALLEL	Removed
11	NOCACHE	Removed
12	NOMONITORING	Removed

STORAGE clause:

	Oracle	SQL Server
1	INITIAL num	Removed
2	NEXT num	Removed
3	MINEXTENTS num	Removed
4	MAXEXTENTS num \| UNLIMITED	Removed
5	PCTINCREASE num	Removed
6	FREELISTS num	Removed
7	FREELIST GROUPS num	Removed
8	BUFFER_POOL DEFAULT \| KEEP \| RECYCLE	Removed
9	FLASH_CACHE DEFAULT \| KEEP \| NONE	Removed
10	CELL_FLASH_CACHE DEFAULT \| KEEP \| NONE	Removed

LOB storage clause:

	Oracle	SQL Server
1	TABLESPACE name	Removed
2	DISABLE \| ENABLE STORAGE IN ROW	Removed
3	CHUNK num	Removed
4	NOCACHE	Removed
5	LOGGING	Removed

SELECT Statement

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

INSERT Statement

Converting SQL INSERT statement:

	Oracle		SQL Server
1	INSERT INTO tab (id, c2, …) VALUES (seq.NEXTVAL, val2, …)	Generate ID	INSERT INTO tab (c2, …) VALUES (val2, …)	Identity column is used
2	INSERT … RETURNING id INTO var	Get generated ID	INSERT … ; SET @var = SCOPE_IDENTITY()

CREATE PROCEDURE Statement

Converting CREATE PROCEDURE statement:

	Oracle	SQL Server
1	CREATE OR REPLACE PROCEDURE	Conditional DROP PROCEDURE and CREATE PROCEDURE
2	param IN \| OUT \| IN OUT datatype DEFAULT default	@param datatype = default [OUT]
3	cur SYS_REFCURSOR OUT	Parameter removed
4	IS \| AS	AS
5	Variable declaration is before BEGIN	Variable declaration is after BEGIN
6	END [proc_name];	END;
7	/	GO

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

CREATE FUNCTION Statement

Converting CREATE FUNCTION statement from Oracle to SQL Server:

	Oracle	SQL Server
1	CREATE OR REPLACE	Conditional DROP FUNCTION
2	No () if the function is without parameters	Empty () is required
3	RETURN	RETURNS
4	IS \| AS	AS
5	/	GO

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

PL/SQL Statements

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

	Oracle		SQL Server
1	variable datatype := value;	Variable declaration	DECLARE @variable datatype = value
2	variable table.column%TYPE;	Inherited data type	@variable datatype
3	variable cursor%ROWTYPE;	Cursor-based record	List of variables
4	variable := value;	Assignment statement	SET @variable = value;
5	SELECT col INTO var FROM	Select a single row	SELECT @var = col FROM

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:

	Oracle		SQL Server
1	SQL%ROWCOUNT	Number of rows affected by last DML statement	@@ROWCOUNT

Flow-of-control statements:

	Oracle		SQL Server
1	EXIT WHEN condition;	Leave a loop	IF condition BREAK;
2	IF condition THEN … END IF;	IF statement	IF condition BEGIN … END
2	IF … ELSIF … ELSE … END IF;	IF statement	IF … ELSE IF … ELSE …
3	LOOP statements END LOOP;	Loop statement	WHILE 1=1 BEGIN statements END

Executing dynamic SQL:

	Oracle		SQL Server
1	EXECUTE IMMEDIATE sql USING params	Execute dynamic SQL	EXECUTE sp_executesql @sql, 'format', @params
2	EXECUTE IMMEDIATE sql INTO var	Execute dynamic SQL	EXECUTE sp_executesql @sql, N'@var INT OUTPUT', @var OUTPUT

Error handling:

	Oracle		SQL Server
1	RAISE_APPLICATION_ERROR (-20000, error_message)	Raise an user error	RAISERROR(error_message, 16, 1)

SQL Statements

Converting Oracle SQL statements to SQL Server:

	Oracle	SQL Server
1	COMMENT ON COLUMN schema.tab.col IS 'text'	execute sp_addextendedproperty 'MS_Description', 'text', 'user', 'schema', 'table', 'tab', 'column', 'col'
2	CREATE PUBLIC SYNONYM	CREATE SYNONYM

Built-in PL/SQL Packages

Converting built-in PL/SQL packages:

	Oracle		SQL Server
1	DBMS_OUTPUT.PUT_LINE(text)	Output a message	PRINT message

SQL*Plus Commands

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

	Oracle		SQL Server
1	PROMPT text	Output a text message	PRINT 'text'
2	REM \| REMARK text	Single line comment	-- text
3	&variable	Substitution variable in a script	$(variable)
4	SHOW ERRORS	Detailed information about the last error	Not supported, removed

Converting C# Applications

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

Converting PowerBuilder Applications

Converting embedded SQL and database access code in PowerBuilder applications:

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference

Oracle to Microsoft SQL Server Migration

Migration Reference

SQL Language Elements

Data Types

Built-in SQL Functions

CREATE TABLE Statement

SELECT Statement

INSERT Statement

CREATE PROCEDURE Statement

CREATE FUNCTION Statement

PL/SQL Statements

SQL Statements

Built-in PL/SQL Packages

SQL*Plus Commands

Converting C# Applications

Converting PowerBuilder Applications