Oracle Migration Reference

• Identifiers
• Built-in Functions

Names for database objects, columns, variables etc.

Maximum length:

Namespaces:

Table constraints names

Must be unique within the schema

Migrating database schema objects:

Oracle	Microsoft SQL Server
Tables	Different data types, storage clauses
Materialized Views
Sequences	Sequences are available since SQL Server 2012, syntax differences
Function-Based Indexes	Indexes on Computed Columns and Collation for Case-Insensitive Search
Triggers	PL/SQL to Transact-SQL conversion, significant redesign

Converting Oracle SQL and PL/SQL language elements to SQL Server.

Converting data definition (DDL) options and clauses from Oracle to SQL Server.

Oracle		Microsoft SQL Server
DEFAULT	DEFAULT	Add DEFAULT to existing table

Built-in SQL functions:

Oracle		SQL Server
CAST	Convert one built-in data type into another
EMPTY_BLOB	Create an empty BLOB value	0x Constant (Empty binary string)
EMPTY_CLOB	Create an empty CLOB or NCLOB value	'' (Empty string)
EXTRACT for Datetime	Extract day, month, year etc from datetime
INITCAP	Capitalize the first letter of each word	An user-defined function
INSTR	Find position of substring in string	CHARINDEX	First occurrence only, different parameter order
LAST_DAY	Get last date of month	EOMONTH	Since SQL Server 2012
LENGTH	Get string length in characters	LEN	CHAR handled differently, excludes trailing spaces
LPAD	Left-pad string to the specified length	Expression using REPLICATE, RIGHT and LEFT
MOD	Get the remainder of division of one number by another	% Operator
NVL	Replace NULL with expression	ISNULL
SUBSTR	Return a substring from string	SUBSTRING	Negative start position is not allowed, length must be specified
TRANSLATE	One-to-one single-character substitution	Expressions using REPLACE or user-defined function
TRIM	Trim leading or trailing characters	LTRIM and RTRIM
UNISTR	Convert Unicode code points to characters	Expressions using NCHAR

SQL SELECT statement keywords and clauses:

SQL statements:

	Oracle	Microsoft SQL Server
1	COMMENT ON COLUMN	EXECUTE sp_addextendedproperty
2	COMMENT ON TABLE	EXECUTE sp_addextendedproperty
3	CREATE SEQUENCE	CREATE SEQUENCE	Available since SQL Server 2012
4	CREATE TABLE	CREATE TABLE
5	CREATE TRIGGER	CREATE TRIGGER

Data dictionary views:

Error messages:

• Character Sets and Length Semantics
• Date and Timestamp Formats

Data migration issues from Oracle to SQL Server:

• Trailing Spaces in Primary Key Columns
• Case Sensitivity Issues in Primary Keys
• Inserting Empty String to a Foreign Key Column
• Inserting Empty String to a Numeric Column

• Oracle Schema to SQL Server Database Mapping (Oracle Tablespaces and SQL Server Filegroups)

• Implicit COMMIT after DDL Statement in Oracle (No COMMIT After DDL in SQL Server)

• Fetch Data in Array

• Get Number of Days Left in Month

• Convert Object Types and Collections to JSON

• Import Oracle Dump (.dmp) File
• Show Content of Oracle Dump (.dmp) File
• Load LOB Data using Oracle SQL*Loader

Articles on various migration related topics.

Data

• DATE Data with Year less than 1753

Security and Access Control

• Application Context and Fine-Grained Access Control