Migrate Oracleto Sql Server 2008

Guide to Migrating from Oracle to SQL Server 2008

SQL Server Technical Article

Writers: Vladimir Kisil (DB Best Technologies), Valery Fomenko (DB Best Technologies), Yuri Rusakov (DB Best Technologies)

Technical Reviewer: Dmitry Balin (DB Best Technologies)

Published: August 2009
Applies to: SQL Server 2008 and SQL Server 2008 R2

Summary: This white paper explores challenges thatarise when you migrate from an Oracle 7.3 database or later to SQL Server 2008. It describes the implementation differences of database objects, SQL dialects, and procedural code between the two platforms. The entire migration process using SQL Server Migration Assistant (SSMA) 2008 for Oracle is explained in depth, with a special focus on converting database objects and PL/SQL code.

Createdby: DB Best Technologies LLC
P.O. Box 7461, Bellevue, WA 98008
Tel.: (408) 202-4567
E-mail: info@dbbest.com
Web: www.dbbest.com

Copyright
This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussedas of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THEINFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the expresswritten permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or otherintellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, logo, person, place or event is intended or should be inferred.

© 2009 Microsoft Corporation. All rights reserved.Microsoft and SQL Server are registered trademarks of Microsoft Corporation in the United States and other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

Contents
Introduction 6
Overview of Oracle-to-SQL Server 2008 Migration 7
Main Migration Steps 7
Conversion of Database Objects 7
Differences in SQL Languages9
PL/SQL Conversion 9
Data Migration Architecture of SSMA for Oracle 11
Implementation in SSMA 11
Solution Layers 11
Client Application 11
Stored Procedures Interface 12
Database Layer 12
Migration Executable 12
Message Handling 13
Validation of the Results 13
Migrating Oracle Data Types 14
Numeric Data Types 15
Character Data Types 15
Date and Time 17
Boolean Type 17Large Object Types 17
XML Type 18
ROWID Types 18
Migrating Oracle Spatial Data 19
Emulating Oracle System Objects 21
Converting Oracle System Views 21
Converting Oracle System Functions 27
Converting Oracle System Packages 37
Converting Nested PL/SQL Subprograms 57
Inline Substitution 57
Emulation by Using Transact-SQL Subprograms 62
Migrating Oracle User-Defined...