Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

What You Should Already Know

How the Oracle Migration Workbench for MS SQL Server and Sybase Adaptive Server Reference Guide is Organized

How to Use This Reference Guide

Conventions Used in This Reference Guide

1 Overview

Introduction

Product Description

Features

Terminology

2 Databases

Schema Migration

Schema Object Similarities

Schema Object Names

Table Design Considerations

Data Types

Entity Integrity Constraints

Referential Integrity Constraints

Unique Key Constraints

Check Constraints

Data Types

Data Types Table

Data Storage Concepts

Data Storage Concepts Table

Schema Objects

Alias

Database

Database Link

Data and Hash Cluster

Defaults

Index

Privilege

Profile

Role

Rule

Sequence

Snapshot

Synonym

Tables

Tablespace

User

View

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

3 Triggers and Stored Procedures

Introduction

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

4 Distributed Environments

Distributed Environments

Accessing Remote Databases in a Distributed Environment

Oracle and Remote Objects

MS SQL Server and Sybase and Remote Objects

Replication

Application Development Tools

5 Migrating Temporary Tables to Oracle

Temporary 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

Index