Oracle to Microsoft SQL Server (MSSQL) Migration

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

  • SQLines Data - Data transfer, schema migration and validation tool
  • SQLines SQL Converter - SQL Conversion and Assessment tool

Databases:

  • Oracle 21c, 19c, 18c, 12c, 11g, 10g and 9i
  • Microsoft SQL Server 2022, 2019, 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 TIMESTAMP '2023-05-23 10:53:01' Date and time literal { ts '2023-05-23 10:53:01' }
3 + and - Operators for date Datetime arithmetic + and - Operators for datetime
4 cursor%NOTFOUND No row fetched @@FETCH_STATUS = 0

Data Types

Character data types:

Oracle SQL Server
1 CHAR(n) Fixed-length string, 1 <= n <= 2000 CHAR(n) 1 <= n <= 8000
2 CLOB Character large object, up to 4 GB VARCHAR(max)
3 LONG Character data, up to 2 GB VARCHAR(max)
4 VARCHAR2(n) Variable-length string
1 <= n <= 4000, if MAX_STRING_SIZE = STANDARD
1 <= n <= 32767, if MAX_STRING_SIZE = EXTENDED
VARCHAR(n) 1 <= n <= 8000
VARCHAR(max)

Unicode character data types:

Oracle SQL Server
1 NCHAR(n) Fixed-length UTF-8/UTF-16 string, 1 <= n <= 2000 NCHAR(n)
2 NCLOB Variable-length UTF-8/UTF16 string, up to 4 GB NVARCHAR(max)
3 NVARCHAR2(n) Variable-length UTF-8/UTF16 string
1 <= n <= 4000, if MAX_STRING_SIZE = STANDARD
1 <= n <= 32767, if MAX_STRING_SIZE = EXTENDED
NVARCHAR(n) 1 <= n <= 4000
UCS-2 or UTF-16
NVARCHAR(max)

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
4 TIMESTAMP(p) Date and time with fractional seconds DATETIME2(p)
0 <= p <= 9, default is 6 0 <= p <= 7, default is 7
5 TIMESTAMP(p) WITH TIME ZONE Date and time with fraction and time zone DATETIMEOFFSET(p)
0 <= p <= 9, default is 6 0 <= p <= 7, default is 7
6 TIMESTAMP(p) WITH LOCAL TIME ZONE Date and time with fraction and time zone
0 <= p <= 9, default is 6

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)
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)
12 LONG RAW Binary data, ⇐ 2G VARBINARY(max)
14 NCHAR VARYING(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 NVARCHAR(n)
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)
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)
28 XMLTYPE XML data XML

Built-in SQL Functions

Converting string functions:

Oracle SQL Server
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, num) User-defined function
3 LISTAGG(exp, delim)... Aggregate concatenation STRING_AGG(exp, delim)... Since SQL Server 2017
4 TRIM(string) Trim leading and trailing spaces TRIM(string) Since SQL Server 2017
LTRIM(RTRIM(string))

Datetime functions:

Oracle SQL Server
1 ADD_MONTHS(datetime, n) Add n months to datetime DATEADD(month, n, datetime)
2 CURRENT_DATE Get current date and time GETDATE()
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)
EXTRACT(HOUR FROM datetime) Get the hour from datetime DATEPART(HOUR, datetime)
EXTRACT(MINUTE FROM datetime) Get the minute from datetime DATEPART(MINUTE, datetime)
EXTRACT(SECOND FROM datetime) Get the seconds from datetime DATEPART(SECOND, datetime)
4 FROM_TZ(datetime, timezone) Setting timezone datetime AT TIME ZONE timezone
5 MONTHS_BETWEEN(d1, d2) Get difference in months User-defined function
6 SYSDATE Get current date and time GETDATE()
7 TO_CHAR(datetime, format) Convert datetime to string CONVERT(VARCHAR(n), datetime, style)
TO_CHAR(number, format) Convert number to string FORMAT(number, format) Format is different
8 TO_DATE(string, format) Convert string to datetime CONVERT(DATETIME, string, style)
9 TO_TIMESTAMP(string, format) Convert string to timestamp CONVERT(DATETIME, string, style)
10 TRUNC(datetime) Truncate datetime Expressions using CONVERT

Numeric functions:

Oracle SQL Server
1 ROUND(num, s) Round number to s decimal places ROUND(num, s)
ROUND(num) ROUND(num, 0)
2 TO_NUMBER(exp) Convert to number CAST(exp AS FLOAT)
TO_NUMBER(exp, '999.99') CAST(exp AS DECIMAL(p,s))
3 TRUNC(number) Truncate number FLOOR(number)

XML functions:

Oracle SQL Server
1 XMLELEMENT(name, value) Create a XML element SELECT ... FOR XML PATH (name)
2 XMLFOREST(value, value2, ...) Create multiple XML elements SELECT ... FOR XML PATH

Other functions:

Oracle SQL Server
1 BITAND(exp1, exp2) Perform bitwise AND exp1 & exp2
2 DECODE(exp, when, then, ...) Evaluate conditions CASE exp WHEN when THEN then ... END

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

CREATE INDEX Statement

Converting CREATE INDEX statement keywords and clauses:

Oracle SQL Server
1 CREATE INDEX schema.index_name CREATE INDEX index_name Can contain index name only

CREATE SEQUENCE Statement

Converting sequences:

Oracle SQL Server
1 CREATE SEQUENCE seqname CREATE SEQUENCE seqname
2 INCREMENT BY num Positive or negative increment, default is 1 INCREMENT BY num
3 START WITH num Initial value START [WITH] num
4 MAXVALUE num Maximum value is num MAXVALUE num
NOMAXVALUE System limit NO MAXVALUE
5 MINVALUE num Minimum value is num MINVALUE num
NOMINVALUE System limit NO MINVALUE
6 CYCLE Reuse values after reaching the limit CYCLE
NOCYCLE No reuse, this is default NO CYCLE
7 CACHE num Cache num values, default is 20 CACHE num
NOCACHE Values are not preallocated NO CACHE
8 ORDER Guarantee numbers in order of requests Option not supported, commented
NOORDER No guarantee, this is default Option not supported, removed as it is default

By default MAXVALUE is 9999999999999999999999999999 in Oracle, and this value is out of range for type BIGINT in SQL Server, so MAXVALUE is commented in such case.

CREATE VIEW Statement

Converting CREATE VIEW statement keywords and clauses:

Oracle SQL Server
1 CREATE OR REPLACE VIEW name CREATE OR ALTER VIEW name

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
3 CONNECT BY PRIOR Hierarchical queries Recursive Common Table Expressions (CTE)

Rownum pseudo-column conditions:

Oracle SQL Server
1 SELECT … WHERE ROWNUM = 1 Return 1 row only SELECT TOP 1 …
SELECT … WHERE ROWNUM <= n Row limit SELECT TOP n
SELECT … WHERE ROWNUM < n SELECT TOP n - 1

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
IF … ELSIF … ELSE … END IF; 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 sp_executesql @sql, N'@var INT OUTPUT',
@var OUTPUT

PL/SQL Tables:

Error handling:

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

EXCEPTION block:

Oracle SQL Server
1 WHEN NO_DATA_FOUND No rows found IF @@FETCH_STATUS <> 0 BEGIN … END

SQL Statements

Converting Oracle SQL statements to SQL Server:

Oracle SQL Server
1 ALTER TABLE tab ADD col type ALTER TABLE tab ADD col type
2 COMMENT ON COLUMN schema.tab.col IS 'text' execute sp_addextendedproperty 'MS_Description', 'text',
'user', 'schema', 'table', 'tab', 'column', 'col'
3 COMMENT ON TABLE schema.tab IS 'text' execute sp_addextendedproperty 'MS_Description', 'text',
'user', 'schema', 'table', 'tab'
4 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: