Transcription
Oracle Migration Workbench for MS SQL Server and SybaseAdaptive Server Reference GuideRelease 1.2.5.0.0 for Windows NT and Windows 95/98December 1999Part No. Z26179-01This reference guide describes how to migrate from MS SQL Server 6.5, MS SQLServer 7.0, and Sybase Adaptive Server 11 to Oracle8 or Oracle8i.
Oracle Migration Workbench Reference for MS SQL Server and Sybase Adaptive Server, Release 1.2.5.0.0Part No. Z26179-01Release 1.2.5.0.0Copyright 1998, 1999. Oracle Corporation. All rights reserved.The Programs (which include both the software and documentation) contain proprietary information of OracleCorporation; they are provided under a license agreement containing restrictions on use and disclosure and are alsoprotected by copyright, patent and other intellectual and industrial property laws. Reverse engineering, disassemblyor decompilation of the Programs is prohibited.The information contained in this document is subject to change without notice. If you find any problems in thedocumentation, please report them to us in writing. Oracle Corporation does not warrant that this document is errorfree. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programsmay be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, withoutthe express written permission of Oracle Corporation.If the Programs are delivered to the US Government or anyone licensing or using the Programs on behalf of the USGovernment, the following notice is applicable:Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are 'commercial computersoftware' and use, duplication and disclosure of the Programs including documentation, shall be subject to thelicensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject tothe Federal Acquisition Regulations are 'restricted computer software' and use, duplication and disclosure of thePrograms shall be subject to the restrictions in FAR 52.227-19, Commercial Computer Software - Restricted Rights(June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerousapplications. It shall be licensee's responsibility to take all appropriate fail-safe, back up, redundancy and othermeasures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracledisclaims liability for any damages caused by such use of the Programs.Alpha and Beta Draft documentation are considered to be in prerelease status. This documentation is intended fordemonstration and preliminary use only. We expect that you may encounter some errors, ranging fromtypographical errors to data inaccuracies. This documentation is subject to change without notice, and it may not bespecific to the hardware on which you are using the software. Please be advised that Oracle Corporation does notwarrant prerelease documentation and will not be responsible for any loss, costs, or damages incurred due to the useof this documentation.Oracle is registered trademark and Oracle8, Oracle8i, Oracle Migration Workbench, SQL*Plus, SQL*Loader,SQL*Module, Net8, PL/SQL, Pro*C, and Oracle Objects are trademarks or registered trademarks of OracleCorporation. All other products or company names are used for identification purposes only, and may betrademarks of their respective owners.
ContentsSend Us Your Comments . ixPreface. xiAudience.What You Should Already Know .How the Oracle Migration Workbench for MS SQL Server and Sybase Adaptive ServerReference Guide is Organized xiHow to Use This Reference Guide .Conventions Used in This Reference Guide .xixixiixii1 OverviewIntroduction .Product Description.Features .Terminology .1-11-11-21-22 DatabasesSchema Migration.Schema Object Similarities .Schema Object Names.Table Design Considerations .Data Types.Entity Integrity Constraints .Referential Integrity Constraints.2-12-12-32-32-32-62-6iii
Unique Key Constraints . 2-7Check Constraints. 2-7Data Types . 2-8Data Types Table . 2-8Data Storage Concepts . 2-13Data Storage Concepts Table . 2-14Schema Objects . 2-18Alias . 2-19Database . 2-21Database Link . 2-31Data and Hash Cluster. 2-33Defaults . 2-39Index . 2-40Privilege . 2-46Profile. 2-51Role . 2-55Rule . 2-60Sequence. 2-62Snapshot . 2-65Synonym . 2-66Tables. 2-69Tablespace. 2-80User . 2-84View . 2-88Data Manipulation Language. 2-93Connecting to the Database. 2-94SELECT Statement. 2-95SELECT with GROUP BY Statement . 2-101INSERT Statement . 2-102UPDATE Statement . 2-103DELETE Statement . 2-105Operators . 2-106Comparison Operators . 2-106Arithmetic Operators . 2-110String Operators. 2-110iv
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 202-1212-122Triggers and Stored ProceduresIntroduction .Triggers .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 3-173-183-253-293-333-373-393-403-413-42v
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 Handling.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 MS SQL Server and Sybase Stored Procedures.4Distributed EnvironmentsDistributed Environments .Accessing Remote Databases in a Distributed Environment .Oracle and Remote Objects .MS SQL Server and Sybase and Remote Objects -803-803-803-823-844-14-14-24-2
Replication . 4-3Application Development Tools. 4-45Migrating 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 .Implications of Creating Permanent Tables .Implementation of Temporary Tables as Permanent Tables .Maintenance of Temporary Tables .Definition of temp table catalog .Package Body temp table 11Indexvii
viii
Send Us Your CommentsOracle Migration Workbench for MS SQL Server and Sybase Adaptive Server Reference Guide,Release 1.2.5.0.0 for Windows NT and Windows 95/98Part No. Z26179-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:Email - mwbinfo@ie.oracle.comFAX - 353-1-803-1899 Postal service:Documentation ManagerMigration Technology GroupOracle CorporationEastpoint Business ParkDublin 3IrelandIf you would like a reply, please give your name, address, and telephone number below. ix
x
PrefaceThe Oracle Migration Workbench for MS SQL Server and Sybase Adaptive Server Reference Guide providesdetailed information about migrating a database from MS SQL Server 6.5, MS SQL Server 7.0, andSybase Adaptive Server 11 to Oracle8 or Oracle8i. It is a useful guide regardless of the conversion toolyou are using to perform the migration, but the recommended tool for such migrations is OracleMigration Workbench (Migration Workbench). This reference guide describes several differencesbetween MS SQL Server, Sybase Adaptive Server, and Oracle and outlines how those differences arehandled by the Migration Workbench during the conversion process.AudienceThis guide is intended for anyone who is involved in converting an MS SQL Server or Sybase Adaptive Server database to Oracle using the Migration Workbench.What You Should Already KnowYou should be familiar with relational database concepts and with the operating system environments under which you are running Oracle and MS SQL Server or Sybase Adaptive Server.How the Oracle Migration Workbench for MS SQL Server and SybaseAdaptive Server 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, "Databases"xi
Contains detailed information about the differences between data types, data storage concepts,schema objects, and the data manipulation language in MS SQL Server, Sybase Adaptive Server, andOracle.Chapter 3, "Triggers and Stored Procedures"Introduces triggers and stored procedures, and compares T-SQL and PL/SQL language elements andconstructs in MS SQL Server, Sybase Adaptive Server, and Oracle.Chapter 4, "Distributed Environments"Describes when and why distributed environments are used, and discusses application developmenttools.Chapter 5, "Migrating Temporary Tables to Oracle"Describes how to emulate temporary tables in Oracle8.How to Use This Reference GuideEvery reader of this reference guide should read Chapter 1, "Overview" as that chapter provides anintroduction to the concept and terminology of the Migration Workbench.Conventions Used in This Reference GuideThe following typographic conventions are used in this reference guide:xiiConventionDescriptionUPPERCASEUppercase text indicates case-insensitive filenames or directory names, commands, command keywords, initializingparameters, data types, table names, or object names. Enter textexactly as spelled; it need not be in uppercase.[UPPERCASE]Key names are represented by uppercase letters enclosed inbrackets, as square in [RETURN].Italicized CharactersItalicized Italic type in text indicates the complete names ofdocuments, emphasizes a single word or short phrase,indicates variables, or indicates the first instance of animportant word or phrase.
ConventionDescriptionUPPERCASEUppercase text indicates case-insensitive filenames or directory names, commands, command keywords, initializingparameters, data types, table names, or object names. Enter textexactly as spelled; it need not be in uppercase.Code ExamplesMonospace text distinguishes examples of commands andstatements from the rest of the text. Monospace text must beentered exactly as shown. Example statements may includepunctuation, such as commas or quotation marks. Allpunctuation in example statements is required. All examplestatements terminate with a semicolon (;). Depending on theapplication, a semicolon or other terminator may or may not berequired to end a statement.UPPERCASE in CodeExamplesUppercase words in example statements indicate the keywordswithin Oracle SQL. When you issue statements, however,keywords are not case sensitive.lowercase in CodeExamplesLowercase words in example statements indicate wordssupplied only for the context of the example. For example,lowercase words may indicate the name of a table, column, orfile.BoldBoldface type in text indicates emphasis with stress, a termdefined in the text or the glossary or in both locations, orcase-sensitive filenames or directory names. Right-facing angle brackets appear in navigation paths toindicate movement from one Web page to another.{}Curly braces indicate that one of the enclosed arguments isrequired. Do not enter the braces themselves.[]Square brackets indicate that the enclosed arguments areoptional. Do not enter the brackets themselves. A vertical bar separates alternative items that may be optionalor required. Do not type the vertical bar.Ellipses indicate that the preceding item can be repeated. Youcan enter an arbitrary number of similaritems. In codefragments, an ellipsis means that code not relevant to thediscussion has been omitted. Do not type the ellipsis.SQL*Plus PromptsThe SQL*Plus prompt, SQL , appears in SQL statement andSQL*Plus command examples. Enter your response at theprompt. Do not enter the text of the prompt, SQL , in yourresponse.xiii
xivConventionDescriptionUPPERCASEUppercase text indicates case-insensitive filenames or directory names, commands, command keywords, initializingparameters, data types, table names, or object names. Enter textexactly as spelled; it need not be in uppercase.MS-DOS PromptsThe MS-DOS prompt, , appears in MS-DOS commandexamples. Enter your response at the prompt. Do not enter theprompt in your response.Storage MeasurementsStorage measurements use these abbreviations:K, for kilobyte which equals 1024 bytesM, for megabyte which equals 1 048 576 bytesG, for gigabyte which equals 1 073 741 824 bytes
1OverviewThis chapter introduces the Oracle Migration Workbench (Migration Workbench)under the following headings:nIntroductionnProduct DescriptionnFeaturesnTerminologyIntroductionThe Migration Workbench is a tool that simplifies the process of migrating data andapplications from an MS SQL Server 6.5, MS SQL Server 7.0, or Sybase AdaptiveServer 11 environment to Oracle8 or Oracle8i. The Migration Workbench allowsyou to quickly and easily migrate an entire application system, that is the databaseschema including triggers and stored procedures, in an integrated, visualenvironment.Note: MS SQL Server is used in this document to refer to both MS SQLServer 6.5 and MS SQL Server 7.0 unless otherwise stated.Product DescriptionThe Migration Workbench allows you to migrate an MS SQL Server or SybaseAdaptive Server database to an Oracle8 or Oracle8i database. The MigrationWorkbench employs an intuitive and informative User Interface and a series ofwizards to simplify the migration process. To ensure portability, all components ofthe Migration Workbench are written in Java.Overview 1-1
FeaturesThe Migration Workbench uses a repository to store migration information. Thisallows you to query the initial state of the application before migration. By initiallyloading the migratable components of the application system into a repository, youcan work independently of the production application.Furthermore, the Migration Workbench saves useful dependency information aboutthe components being converted. For example, the Migration Workbench keeps arecord of all the tables ac
between MS SQL Server, Sybase Adaptive Server, and Oracle and outlines how those differences are handled by the Migration Workbench during the conversion process. Audience This guide is intended for anyone who is involved in converting an MS SQL Server or Sybase Adap-tive Server database to Oracle using the Migration Workbench.
