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

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