This is an old revision of the document!

Microsoft SQL Server to Oracle Migration

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

We also help convert embedded SQL statements in C/C++ (ODBC, ESQL/C, DBLIB), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET and Perl/PHP applications.

Microsoft SQL Server 2012, 2008, 2005 and 2000
Oracle 12c, 11g and 10g

SQL Server to Oracle Migration Tools

SQLines tools to help you migrate from Microsoft SQL Server to Oracle:

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:

	Online Edition	Desktop Edition
Conversion Features
DDL - Tables and Indexes
Queries, Views and DML Statements
Stored Procedures, Functions and Triggers
Embedded SQL in Applications
Interfaces
GUI
Command Line	n/a
API	n/a
Platforms
Windows 32-bit	Hosted
Linux x86 32-bit	Hosted
Licensing and Support
Technical Support
Price	Purchase

Try SQLines Online or download a Trial Version.

SQL Server to Oracle Migration Reference

Technical information on migration from Microsoft SQL Server to Oracle.

Last Update: Microsoft SQL Server 2012 and Oracle 12c

Data Types

Data type mapping between SQL Server and Oracle :

Character and binary strings:

	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	VARCHAR(n)	Variable-length non-Unicode string, 1 <= n <= 8000	VARCHAR2(n)
	VARCHAR(max)	2 GB	CLOB

Numbers:

	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 built-in SQL functions:

	SQL Server		Oracle
1	CONVERT(CHAR \| VARCHAR, exp)	Convert to string	TO_CHAR(exp)
2	GETDATE()	Get the current date and time	SYSTIMESTAMP
3	MONTH(datetime)	Extract month from datetime	EXTRACT(MONTH FROM datetime)
4	SYSTEM_USER	OS user name	SYS_CONTEXT('USERENV','OS_USER')
5	YEAR(datetime)	Extract year from datetime	EXTRACT(YEAR FROM datetime)

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

Converting procedural Transact-SQL statements used in stored procedures, functions and triggers from SQL Server to Oracle PL/SQL:

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;

Cursors operations and attributes:

	SQL Server		Oracle
1	@@FETCH_STATUS = 0	Fetch was successful	cur%FOUND

SQL Statements

Converting SQL statements from SQL Server to Oracle:

	SQL Server		Oracle
1	CREATE TYPE udt FROM datatype	Create a user-defined type	CREATE TYPE udt AS OBJECT (udt datatype)
2	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 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