Oracle - Reference and Migration

Technical information on Oracle.

Identifiers

Names for database objects such as databases, tables, views, columns, indexes, triggers, procedures etc.

First character Alphabetical
Subsequent characters Alphanumeric, _ (underscore), # (pound sign) and $ (dollar)
Database links Can contain @ (at sign) and . (period)
Identifier quote character " (double quotes)

Maximum length:

Tables, columns, indexes, views, procedures, triggers, constraints 30
Databases 8
Database links 128

Namespace:

Unique within the schema Constraints

Character Sets

Physical Database Design

Database Schema Objects

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

Data

SQL and PL/SQL Language Elements

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

Oracle Microsoft SQL Server
Hexadecimal Constants 0xhhhh
PRAGMA AUTONOMOUS_TRANSACTION Loopback linked server

DDL Options and Clauses

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 Functions

Converting 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 Queries

SQL SELECT statement keywords and clauses:

Oracle SQL Server
Outer Join Operator (+) LEFT OUTER JOIN and RIGHT OUTER JOIN

SQL Statements

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 (Catalog Views)

Data dictionary views:

Oracle Microsoft SQL Server
1 ALL_TABLES, DBA_TABLES, USER_TABLES sys.tables

Error Messagges

Error messages:

Oracle Microsoft SQL Server
1 ORA-00001 Unique constraint violated Msg 2627, Level 14

Transactions

Oracle Call Interface (OCI)

How To - Most Popular Queries and Expressions

How To - Database Administration (DBA) Tasks

Oracle Migration Articles

Articles on various migration related topics.

Data

Security and Access Control