Contents

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

Related Documentation

1 Introduction

1.2 Heterogeneous Services Technology

1.3 Oracle Transparent Gateways

2 Configuring the Gateway

2.1 Configuring the Gateway

2.1.1 Task 1: Choose a System Identifier for the Gateway

2.1.2 Task 2: Customize the Initialization Parameter File

2.2 Configuring Oracle Net Services Listener for the Gateway

2.2.1 Task 1: Configure Oracle Net Services TNS Listener for the Gateway

2.2.1.1 Example of Address to Listen On in listener.ora File

2.2.1.2 Example of Gateway to Start in listener.ora File

2.2.2 Task 2: Stop and Start the TNS Listener for the Gateway

2.3 Configuring the Oracle Database Server for Gateway Access

2.3.1 Configuring Oracle Net Services for the Oracle Database Server

2.3.1.1 Configuring tnsnames.ora

2.4 Creating Database Links

2.5 Gateway Password Encryption Tool

2.6 Configuring the Gateway for Multiple Microsoft SQL Server Databases

2.6.1 Multiple Databases Example: Configuring the Gateway

2.6.2 Multiple Databases Example: Configuring Oracle Net Services Listener

2.6.3 Multiple Databases Example: Stopping and Starting the TNS Listener

2.6.4 Multiple Databases Example: Configuring the Oracle Database Server for Gateway Access

2.6.4.1 Configuring Oracle Net Services on the Oracle Database Server for Multiple Gateway Instances

2.6.5 Multiple Databases Example: Accessing Microsoft SQL Server Data

2.7 Performing Configuration Tasks

2.7.1 Configuring for Two-Phase Commit

2.7.1.1 Task 1: Create a Recovery Account and Password

2.7.1.2 Task 2: Create the Transaction Log Table

2.7.2 Specifying an Owner

3 Microsoft SQL Server Gateway Features and Restrictions

3.1 Using the Pass-Through Feature

3.2 Executing Stored Procedures and Functions

3.3 Remote User-defined Function Support

3.3.1 Return Values and Stored Procedures

3.3.2 Result Sets and Stored Procedures

3.3.2.1 OCI Program Fetching from Result Sets in Sequential Mode

3.3.2.2 PL/SQL Program Fetching from Result Sets in Sequential Mode

3.4 Database Compatibility Issues for Microsoft SQL Server

3.4.1 Implicit Transactions (Chained Mode)

3.4.2 Column Definitions

3.4.3 Naming Rules

3.4.3.1 Rules for Naming Objects

3.4.3.2 Case Sensitivity

3.4.4 Data Types

3.4.4.1 Binary Literal Notation

3.4.4.2 Bind Variables With LONG Columns

3.4.4.3 Data Type Conversion

3.4.5.1 Row Selection

3.4.5.2 Empty Strings

3.4.5.3 Empty Bind Variables

3.5 Known Restrictions

3.5.1 Multiple Open Statements

3.5.2 Transactional Integrity

3.5.3 Transaction Capability

3.5.4 COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors

3.5.5 Stored Procedures

3.5.6 Pass-Through Feature

3.5.7 DDL Statements

3.5.8 SQL Syntax

3.5.8.1 WHERE CURRENT OF Clause

3.5.8.2 CONNECT BY Clause

3.5.8.3 Functions in Subqueries

3.5.8.4 Parameters in Subqueries

3.5.8.5 Data Dictionary Table and Views in UPDATE Statement

3.5.8.7 TO_DATE

3.5.8.8 EXPLAIN PLAN Statement

3.5.9 Functions

3.5.10 SQL*Plus COPY Command with Lowercase Table Names

3.5.11 Database Links

3.6 Known Problems

3.6.1 FLOAT Data Type

3.6.2 VARBINARY Data Type

3.6.3 Encrypted Format Login

3.6.4 Date Arithmetic

3.6.5 Microsoft SQL Server IMAGE, TEXT and NTEXT Data Types

3.6.6 String Functions

3.6.7 Schema Names and PL/SQL

3.6.8 Data Dictionary Views and PL/SQL

3.6.9 Stored Procedures

4 Case Studies

4.1 Case Descriptions

4.2 CD-ROM Contents

4.3 Demonstration Files

4.4 Demonstration Requirements

4.5 Creating Demonstration Tables

4.5.1 Demonstration Table Definitions

4.5.2 Demonstration Table Contents

4.6 Case 1: Simple Queries

4.7 Case 2: A More Complex Query

4.8 Case 3: Joining Microsoft SQL Server Tables

4.9 Case 4: Write Capabilities

4.9.1 DELETE Statement

4.9.2 UPDATE Statement

4.9.3 INSERT Statement

4.10 Case 5: Data Dictionary Query

4.11 Case 6: The Pass-Through Feature

4.11.1 UPDATE Statement

4.11.2 SELECT Statement

4.12 Case 7: Executing Stored Procedures

A Data Type Conversion

A.1 Data Type Conversion

B Supported SQL Syntax and Functions

B.1 Supported SQL Statements

B.2 Oracle Functions

B.2.1 Functions Not Supported by Microsoft SQL Server

B.2.2 Functions Supported by Microsoft SQL Server

B.2.2.1 Arithmetic Operators

B.2.2.2 Comparison Operators

B.2.2.3 Pattern Matching

B.2.2.4 Group Functions

B.2.2.5 String Functions

B.2.2.6 Other Functions

B.2.3 Functions Supported by the Gateway

C Data Dictionary

C.1 Data Dictionary Support

C.1.1 Microsoft SQL Server System Tables

C.1.2 Accessing the Gateway Data Dictionary

C.1.3 Direct Queries to Microsoft SQL Server Tables

C.1.4 Supported Views and Tables

C.2 Data Dictionary Mapping

C.2.1 Default Column Values

C.3 Gateway Data Dictionary Descriptions

D Heterogeneous Services Initialization Parameters

D.1 HS Initialization Parameter File Syntax

D.2 HS_FDS_CONNECT_INFO

D.3 HS_FDS_DEFAULT_OWNER

D.4 HS_FDS_PARSER_TOKEN_SIZE

D.5 HS_FDS_PROC_IS_FUNC

D.6 HS_FDS_RECOVERY_ACCOUNT

D.7 HS_FDS_RECOVERY_PWD

D.8 HS_FDS_REPORT_REAL_AS_DOUBLE

D.9 HS_FDS_RESULTSET_SUPPORT

D.10 HS_FDS_TRACE_LEVEL

D.11 HS_FDS_TRANSACTION_LOG

D.12 HS_FDS_TRANSACTION_MODEL

Index