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.
Databases:
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 |
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 |
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 | 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 | ||
5 | 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_DATE(string, format) | Convert string to datetime | CONVERT(DATETIME, string, style) | ||
8 | TO_TIMESTAMP(string, format) | Convert string to timestamp | CONVERT(DATETIME, string, style) | ||
9 | 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 |
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 |
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 |
Converting sequences:
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.
Converting CREATE VIEW statement keywords and clauses:
Oracle | SQL Server | |
1 | CREATE OR REPLACE VIEW name … | CREATE OR ALTER VIEW name … |
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:
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() |
Converting SQL UPDATE statement:
Oracle | SQL Server | |||
1 | UPDATE tab [alias] SET alias.col ... | Alias for table | UPDATE tab SET col ... | Alias cannot be specified |
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.
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.
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 |
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 |
Converting built-in PL/SQL packages:
Oracle | SQL Server | ||
1 | DBMS_OUTPUT.PUT_LINE(text) | Output a message | PRINT message |
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 embedded SQL and database access code in C# applications:
Converting embedded SQL and database access code in PowerBuilder applications: