Transcription
Oracle Migration WorkbenchReference Guide for Microsoft SQL Server and Sybase Adaptive Server MigrationsRelease 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NTMarch 2002Part Number: A97248-01This reference guide describes how to migrate from Microsoft SQL Server 6.5,Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Sybase Adaptive Server11, and Sybase Adaptive Server 12 to Oracle9i or Oracle8i.
Oracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase Adaptive ServerMigrations, Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT.Part Number: A97248-01Copyright 1998, 2002 Oracle Corporation. All rights reserved.The Programs (which include both the software and documentation) contain proprietary information ofOracle Corporation; they are provided under a license agreement containing restrictions on use anddisclosure and are also protected by copyright, patent and other intellectual and industrial propertylaws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent requiredto obtain interoperability with other independently created software or as specified by law, is prohibited.The information contained in this document is subject to change without notice. If you find any problemsin the documentation, please report them to us in writing. Oracle Corporation does not warrant that thisdocument is error free. Except as may be expressly permitted in your license agreement for thesePrograms, no part of these Programs may be reproduced or transmitted in any form or by any means,electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.If the Programs are delivered to the U.S. Government or anyone licensing or using the programs onbehalf of the U.S. Government, the following notice is applicable:Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercialcomputer software" and use, duplication, and disclosure of the Programs, including documentation,shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computersoftware" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500Oracle Parkway, Redwood City, CA 94065.The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherentlydangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,redundancy, and other measures to ensure the safe use of such applications if the Programs are used forsuch purposes, and Oracle Corporation disclaims liability for any damages caused by such use of thePrograms.Oracle is a registered trademark, and Oracle8, Oracle8i, Oracle9i, SQL*Plus, PL/SQL, and Pro*C aretrademarks or registered trademarks of Oracle Corporation. Other names may be trademarks of theirrespective owners.
ContentsSend Us Your Comments . ixPreface. xiAudience . xiiWhat You Should Already Know. xiiHow this Reference Guide is Organized . xiiUsing This Reference Guide. xiiiDocumentation Accessibility . xiiiAccessibility of Code Examples in Documentation. xiiiRelated Documentation . xiiiConventions. xiv1OverviewIntroduction .Product Description.Features .Glossary .21-11-11-21-3Microsoft SQL Server, Sybase Adaptive Server, and Oracle ComparedSchema Migration.Schema Object Similarities .Schema Object Names.Table Design Considerations .Data Types.2-12-12-32-32-3iii
Entity Integrity Constraints .Referential Integrity Constraints .Unique Key Constraints .Check Constraints.Data Types .Data Types Table .Data Storage Concepts .Data Storage Concepts Table .Data Manipulation Language.Connecting to the Database.SELECT Statement .SELECT with GROUP BY Statement .INSERT Statement .UPDATE Statement .DELETE Statement .Operators .Comparison Operators .Arithmetic Operators .String Operators.Set Operators .Bit Operators .Built-In Functions .Character Functions .Miscellaneous Functions .Date Functions .Mathematical Functions .Locking Concepts and Data Concurrency Issues.Locking.Row-Level Versus Page-Level Locking.Read Consistency.Logical Transaction Handling 2-512-512-542-552-56Triggers and Stored ProceduresIntroduction . 3-1Triggers . 3-1iv
Stored Procedures.Methods Used to Send Data to Clients .Individual SQL Statements .Logical Transaction Handling .Error Handling within the Stored Procedure.Data Types.Local Variable.Server Data Types.Composite Data Types.Schema Objects .Procedure.Function .Package.Package Body .T/SQL Versus PL/SQL Constructs .CREATE PROCEDURE Statement .Parameter Passing .DECLARE Statement .IF Statement.RETURN Statement .RAISERROR Statement .EXECUTE Statement.WHILE Statement.GOTO Statement .@@Rowcount and @@Error Variables .ASSIGNMENT Statement .SELECT Statement .SELECT Statement as Part of the SELECT List .SELECT Statement with GROUP BY Clause .Column Aliases.UPDATE with FROM Statement.DELETE with FROM Statement .Temporary Tables.Result Set (Converted Using a Cursor Variable) .Cursor -533-543-553-563-593-613-623-633-653-673-683-70v
Transaction Handling Statements .T/SQL and PL/SQL Language Elements.Transaction Handling Semantics.Conversion Preparation Recommendations.Exception-Handling and Error-Handling Semantics .Special Global Variables .Operators .Built-in Functions.Sending Data to the Client: Result Sets .Single Result Set.Multiple Result Sets.About Converting a T/SQL Procedure with a Result Set .DDL Constructs within Microsoft SQL Server and Sybase Adaptive Server StoredProcedures 3-844Distributed EnvironmentsDistributed Environments .Accessing Remote Databases in a Distributed Environment .Oracle and Remote Objects .Microsoft SQL Server and Sybase Adaptive Server and Remote Objects .Replication .Application Development Tools .54-14-14-24-24-34-4Migrating Temporary Tables to OracleTemporary Table Usage.Simplify Coding .Simulate Cursors when Processing Data from Multiple Tables .Improve Performance In a Situation Where Multi-Table Joins are Needed.Associate Rows from Multiple Queries in One Result Set (UNION) .Eliminate Re-Querying Data Needed for Joins .Consolidate the Data for Decision Support Data Requirements .Replace Temporary Tables .Emulate Temporary Tables .Implementation as PL/SQL Tables.Implications of Creating Temporary Tables Dynamically 25-15-25-45-45-55-65-75-75-75-75-7
Implications of Creating Permanent Tables .Implementation of Temporary Tables as Permanent Tables .Maintenance of Temporary Tables .Definition of t table catalog .Package Body t table .65-85-85-105-115-11Disconnected Source Model LoadingGenerating Database Metadata Flat Files . 6-1Flat File Generation Scripts . 6-1Running the Scripts. 6-2Indexvii
viii
Send Us Your CommentsOracle Migration Workbench Reference Guide for Microsoft SQL Server and Sybase AdaptiveServer Migrations, Release 9.2.0 for Microsoft Windows 98/2000 and Microsoft Windows NT.Part Number: A97248-01Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of thispublication. Your input is an important part of the information used for revision. Did you find any errors?Is the information clearly presented?Do you need more information? If so, where?Are the examples correct? Do you need more examples?What features did you like most about this manual?If you find any errors or have any other suggestions for improvement, please indicate the chapter,section, and page number (if available). You can send comments to us in the following ways: Electronic mail: gpe techpubs ie@ORACLE.COMTel: 353-1-8031000, Fax: 353-1- 8033321Attn: Oracle Migration WorkbenchPostal service:Oracle Migration Workbench DocumentationOracle CorporationBlock P5East Point Business ParkClontarf, Dublin 3Irelandix
x
PrefaceThe Oracle Migration Workbench Reference Guide for SQL Server and Sybase AdaptiveServer Migrations provides detailed information about migrating a database fromMicrosoft SQL Server 6.5, Microsoft SQL Server 7.0, Microsoft SQL Server 2000,Sybase Adaptive Server 11, and Sybase Adaptive Server 12 to Oracle9i or Oracle8i.It is a useful guide regardless of the conversion tool you are using to perform themigration, but the recommended tool for such migrations is Oracle MigrationWorkbench (Migration Workbench). This reference guide describes severaldifferences between Microsoft SQL Server and Sybase Adaptive Server and Oracleand outlines how those differences are handled by the Migration Workbench duringthe conversion process.This chapter contains the following sections: Audience What You Should Already Know How this Reference Guide is Organized Using This Reference Guide Documentation Accessibility Accessibility of Code Examples in Documentation Related Documentation Conventionsxi
AudienceThis guide is intended for anyone who is involved in converting a Microsoft SQLServer and Sybase Adaptive Server database to Oracle using the MigrationWorkbench.What You Should Already KnowYou should be familiar with relational database concepts and with the operatingsystem environments under which you are running Oracle and Microsoft SQLServer and Sybase Adaptive Server.How this Reference Guide is OrganizedThis reference guide is organized as follows:Chapter 1, "Overview"Introduces the Migration Workbench and outlines features of this tool.Chapter 2, "Microsoft SQL Server, Sybase Adaptive Server, and Oracle Compared"Contains detailed information about the differences between data types, datastorage concepts, schema objects, and the data manipulation language in MicrosoftSQL Server and Sybase Adaptive Server and Oracle.Chapter 3, "Triggers and Stored Procedures"Introduces triggers and stored procedures, and compares T-SQL and PL/SQLlanguage elements and constructs in Microsoft SQL Server and Sybase AdaptiveServer and Oracle.Chapter 4, "Distributed Environments"Describes when and why distributed environments are used, and discussesapplication development tools.Chapter 5, "Migrating Temporary Tables to Oracle"Describes how to emulate temporary tables in Oracle9i and Oracle8i.Chapter 6, "Disconnected Source Model Loading"Describes how to perform a disconnected source model load, using delimited flatfiles containing schema metadata.xii
Using This Reference GuideEvery reader of this reference guide should read Chapter 1, "Overview" as thatchapter provides an introduction to the concept and terminology of the MigrationWorkbench.Documentation AccessibilityOur goal is to make Oracle products, services, and supporting documentationaccessible, with good usability, to the disabled community. To that end, ourdocumentation includes features that make information available to users ofassistive technology. This documentation is available in HTML format, and containsmarkup to facilitate access by the disabled community. Standards will continue toevolve over time, and Oracle Corporation is actively engaged with othermarket-leading technology vendors to address technical obstacles so that ourdocumentation can be accessible to all of our customers. For additional information,visit the Oracle Accessibility Program Web site y of Code Examples in DocumentationJAWS, a Windows screen reader, may not always correctly read the code examplesin this document. The conventions for writing code require that closing bracesshould appear on an otherwise empty line; however, JAWS may not always read aline of text that consists solely of a bracket or brace.Related DocumentationFor more information, see these Oracle Migration Workbench resources: Oracle Migration Workbench Frequently Asked Questions (FAQ) Oracle Migration Workbench Release Notes Oracle Migration Workbench Online HelpTo download free release notes, installation documentation, white papers, or othercollateral, please visit the Oracle Technology Network (OTN). You must registeronline before using OTN; registration is free and you can do it at:http://otn.oracle.com/membership/index.htmxiii
If you already have a user name and password for OTN, then you can go directly tothe Migration Workbench documentation section of the OTN Web site onventionsThis section describes the conventions used in the text and code examples of the thisdocumentation. It describes: Conventions in Text Conventions in Code ExamplesConventions in TextWe use various conventions in text to help you more quickly identify special terms.The following table describes those conventions and provides examples of their use.ConventionMeaningBoldThe C datatypes such as ub4, sword, orBold type indicates GUI options. It alsoindicates terms that are defined in the text OCINumber are valid.or terms that appear in a glossary, or both.When you specify this clause, you create anindex-organized table.ItalicsItalic typeface indicates book titles,Reference Guideemphasis, syntax clauses, or placeholders.Run Uold release.SQL where old releaserefers to the release you installed prior toupgrading.UPPERCASEmonospace(fixed-width font)Uppercase monospace typeface indicateselements supplied by the system. Suchelements include parameters, privileges,datatypes, RMAN keywords, SQLkeywords, SQL*Plus or utility commands,packages and methods, as well assystem-supplied column names, databaseobjects and structures, user names, androles.xivExampleYou can specify this clause only for a NUMBERcolumn.You can back up the database using theBACKUP command.
idth font)Lowercase monospace typeface indicatesexecutables and sample user-suppliedelements. Such elements includecomputer and database names, netservice names, and connect identifiers, aswell as user-supplied database objectsand structures, column names, packagesand classes, user names and roles,program units, and parameter values.Enter sqlplus to open SQL*Plus.The department id, department name,and location id columns are in thehr.departments table.Conventions in Code ExamplesCode examples illustrate SQL, PL/SQL, SQL*Plus, or other command-linestatements. They are displayed in a monospace (fixed-width) font and separatedfrom normal text as shown in this example:SELECT username FROM dba users WHERE username ’MIGRATE’;The following table describes typographic conventions used in code examples andprovides examples of their use.ConventionMeaningExampleSquare Brackets [ ]Indicates that the enclosed arguments areoptional. Do not enter the brackets.DECIMAL (digits [ , precision ])Curly Braces { }Indicates that one of the enclosedarguments is required. Do not enter thebraces.{ENABLE DISABLE}Vertical Line Separates alternative items that may beoptional or required. Do not type thevertical bar.{ENABLE DISABLE}[COMPRESS NOCOMPRESS]Ellipses .Indicates that the preceding item can berepeated. You can enter an arbitraryCREATE TABLE . AS subquery;number of similar items. In codefragments, an ellipsis means that code notrelevant to the discussion has beenSELECT col1, col2, . , coln FROMomitted. Do not type the ellipsisemployees;ItalicsIndicates variables that you must supplyparticular values.CONNECT SYSTEM/system passwordxv
ConventionMeaningExampleUPPERCASEUppercase text indicates case-insensitivefilenames or directory names, commands,command keywords, initializingparameters, data types, table names, orobject names. Enter text exactly asspelled; it need not be in uppercaseSELECT last name, employee id FROMemployees;Lowercase words in example statementsindicate words supplied only for thecontext of the example. For example,lowercase words may indicate the nameof a table, column, or file.SELECT last name, employee id FROMemployees;lowercasexviSELECT * FROM USER TABLES;DROP TABLE hr.employees;sqlplus hr/hr
1OverviewThis chapter introduces the Oracle Migration Workbench (Migration Workbench)under the following headings: Introduction Product Description Features GlossaryIntroductionThe Migration Workbench is a tool that simplifies the process of migrating data andapplications from an Microsoft SQL Server and Sybase Adaptive Serverenvironment to an Oracle9i or Oracle8i destination database. The MigrationWorkbench allows you to quickly and easily migrate an entire application system,that is the database schema including triggers and stored procedures, in anintegrated, visual environment.Microsoft SQL Server is used in this document to refer toboth Microsoft SQL Server 6.5, Microsoft SQL Server 7.0, andMicrosoft SQL Server 2000 unless otherwise stated.Note:Product DescriptionThe Migration Workbench allows you to migrate a Microsoft SQL Server andSybase Adaptive Server database to an Oracle9i or Oracle8i database. The MigrationWorkbench employs an intuitive and informative User Interface and a series ofOverview1-1
Featureswizards to simplify the migration process. To ensure portability, all components ofthe Migration Workbench are written
xi Preface The Oracle Migration Workbench Reference Guide for SQL Server and Sybase Adaptive Server Migrations provides detailed information about migrating a database from Microsoft SQL Server 6.5, Microsoft SQL Server 7.0, Microsoft SQL Server 2000, Sybase Adaptive Server 11, and Sybase Adaptive Server 12 to Oracle9i or Oracle8i. It is a useful guide regardless of the conversion tool you are .
