JanaDotNet: 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

JanaDotNet

Tuesday, 3 March 2015

SQL Server to Oracle Migration Reference