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 - High performance data transfer, schema migration and validation tool. More...
  • SQLines SQL Converter - SQL scripts conversion tool.

We can also help convert embedded SQL statements in C/C++ (ODBC, Pro*C, OCI), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Python, Perl/PHP and other applications.

  • Oracle 12c, 11g, 10g and 9i
  • Microsoft SQL Server 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 built-in functions from Oracle to SQL Server:

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 TRIM(string) Trim leading and trailing spaces LTRIM(RTRIM(string))
9 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 from Oracle to SQL Server:

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

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 () 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 EXECUTE IMMEDIATE sql USING params Execute dynamic SQL EXEC sp_executesql @sql, 'format', @params
6 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 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;

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
IF … ELSIF … ELSE … END IF; IF … ELSE IF … ELSE …
3 LOOP statements END LOOP; Loop statement WHILE 1=1 BEGIN statements END

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