Oracle Database Gateway for SQL ServerUser’s Guide,12c Release 1 (12.1)E17935-08April 2017
Oracle Database Gateway for SQL Server User's Guide, 12c Release 1 (12.1)E17935-08Copyright 2002, 2017, Oracle and/or its affiliates. All rights reserved.This software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing iton behalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,any programs installed on the hardware, and/or documentation, delivered to U.S. Government end usersare "commercial computer software" pursuant to the applicable Federal Acquisition Regulation andagency-specific supplemental regulations. As such, use, duplication, disclosure, modification, andadaptation of the programs, including any operating system, integrated software, any programs installed onthe hardware, and/or documentation, shall be subject to license terms and license restrictions applicable tothe programs. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information managementapplications. It is not developed or intended for use in any inherently dangerous applications, includingapplications that may create a risk of personal injury. If you use this software or hardware in dangerousapplications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and othermeasures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damagescaused by use of this software or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarksare used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD,Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of AdvancedMicro Devices. UNIX is a registered trademark of The Open Group.This software or hardware and documentation may provide access to or information about content,products, and services from third parties. Oracle Corporation and its affiliates are not responsible for andexpressly disclaim all warranties of any kind with respect to third-party content, products, and servicesunless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and itsaffiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use ofthird-party content, products, or services, except as set forth in an applicable agreement between you andOracle.
ContentsPreface . ixAudience. ixDocumentation Accessibility . ixRelated Documentation . xConventions . x1IntroductionOverview. 1-1Heterogeneous Services Technology. 1-1Oracle Database Gateways . 1-22SQL Server Gateway Features and RestrictionUsing the Pass-Through Feature . 2-1Executing Stored Procedures and Functions. 2-2CHAR Semantics . 2-2Multi-byte Character Sets Ratio Suppression . 2-2IPv6 Support. 2-3Gateway Session IDLE Timeout . 2-3Remote User-defined Function Support. 2-3Return Values and Stored Procedures . 2-3Result Sets and Stored Procedures . 2-4Database Compatibility Issues for SQL Server. 2-7Implicit Transactions (Chained Mode) . 2-7Column Definitions. 2-7Naming Rules . 2-7Data Types. 2-8Queries. 2-9Locking . 2-10Known Restrictions . 2-10Multiple Open Statements . 2-11Transactional Integrity . 2-11Transaction Capability . 2-11COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors . 2-12Stored Procedures . 2-12Pass-Through Feature. 2-12iii
DDL Statements.SQL Syntax .Functions .SQL*Plus COPY Command with Lowercase Table Names.Database Links.CALLBACK links .Known Problems .Encrypted Format Login .Date Arithmetic .SQL Server IMAGE, TEXT and NTEXT Data Types.String Functions.Schema Names and PL/SQL.Data Dictionary Views and PL/SQL.Stored Procedures .3Case StudiesCase Descriptions .Installation Media Contents.Demonstration Files.Demonstration Requirements.Creating Demonstration Tables .Demonstration Table Definitions.Demonstration Table Contents .Case 1: Simple Queries.Case 2: A More Complex Query .Case 3: Joining SQL Server Tables .Case 4: Write Capabilities.DELETE Statement.UPDATE Statement .INSERT Statement.Case 5: Data Dictionary Query .Case 6: The Pass-Through Feature .UPDATE Statement .SELECT Statement .Case 7: Executing Stored Procedures 3-63-63-63-63-6Data Type ConversionData Type Conversion. A-1BSupported SQL Syntax and FunctionsSupported SQL Statements .DELETE .INSERT .SELECT .UPDATE .Oracle Functions .ivB-1B-1B-1B-1B-2B-2
Functions Not Supported by SQL Server . B-2Functions Supported by SQL Server . B-2Functions Supported by the Gateway. B-4CData DictionaryData Dictionary Support.SQL Server System Tables .Accessing the Gateway Data Dictionary .Direct Queries to SQL Server Tables .Supported Views and Tables.Data Dictionary Mapping.Default Column Values .Gateway Data Dictionary Descriptions .DC-1C-1C-1C-2C-2C-3C-4C-4Initialization ParametersInitialization Parameter File Syntax . D-1Oracle Database Gateway for SQL Server Initialization Parameters . D-2Initialization Parameter Description . D-3HS CALL NAME . D-3HS DB DOMAIN . D-4HS DB INTERNAL NAME . D-4HS DB NAME . D-4HS DESCRIBE CACHE HWM . D-5HS LANGUAGE . D-5HS LONG PIECE TRANSFER SIZE . D-6HS OPEN CURSORS . D-6HS RPC FETCH REBLOCKING . D-7HS RPC FETCH SIZE . D-7HS TIME ZONE . D-7HS TRANSACTION MODEL . D-8IFILE . D-8HS FDS TIMESTAMP MAPPING. D-9HS FDS DATE MAPPING . D-9HS FDS CONNECT INFO . D-9HS FDS PROC IS FUNC. D-10HS FDS RECOVERY ACCOUNT . D-10HS FDS RECOVERY PWD. D-11HS FDS REPORT REAL AS DOUBLE . D-11HS FDS RESULTSET SUPPORT . D-11HS FDS TRACE LEVEL. D-11HS FDS TRANSACTION LOG . D-12HS FDS FETCH ROWS. D-12HS IDLE TIMEOUT . D-12HS NLS LENGTH SEMANTICS. D-12HS KEEP REMOTE COLUMN SIZE . D-13HS FDS REMOTE DB CHARSET . D-13v
HS FDS SUPPORT STATISTICS .HS FDS RSET RETURN ROWCOUNT.HS FDS SQLLEN INTERPRETATION .HS FDS ARRAY EXEC .IndexviD-14D-14D-14D-15
List of –33C–34C–35viiiRestricted DDL Statements. 2-12Data Type Mapping and Restrictions . A-1Oracle Data Dictionary View Names and SQL Server Equivalents . C-3ALL CATALOG . C-4ALL COL COMMENTS . C-5ALL CONS COLUMNS . C-5ALL CONSTRAINTS. C-5ALL IND COLUMNS . C-6ALL INDEXES . C-6ALL OBJECTS. C-7ALL TAB COLUMNS. C-8ALL TAB COMMENTS. C-9ALL TABLES. C-9ALL USERS . C-10ALL VIEWS. C-11DBA CATALOG. C-11DBA COL COMMENTS. C-11DBA OBJECTS . C-11DBA TAB COLUMNS . C-12DBA TAB COMMENTS . C-13DBA TABLES. C-13DICT COLUMNS . C-14DICTIONARY. C-14DUAL. C-15TABLE PRIVILEGES . C-15USER CATALOG . C-15USER COL COMMENTS. C-15USER CONS COLUMNS . C-15USER CONSTRAINTS . C-16USER IND COLUMNS. C-16USER INDEXES. C-17USER OBJECTS . C-18USER TAB COLUMNS . C-18USER TAB COMMENTS . C-19USER TABLES . C-20USER USERS. C-21USER VIEWS . C-21
PrefaceThis manual describes the Oracle Database Gateway for SQL Server, which enablesOracle client applications to access SQL Server data through Structured QueryLanguage (SQL). The gateway, with the Oracle database, creates the appearance thatall data resides on a local Oracle database, even though the data can be widelydistributed.This preface covers the following topics: Audience Documentation Accessibility Related Documentation ConventionsAudienceThis manual is intended for Oracle database administrators who perform thefollowing tasks: Installing and configuring the Oracle Database Gateway for SQL Server Diagnosing gateway errors Using the gateway to access SQL Server dataNote: You should understand the fundamentals of OracleDatabase Gateways and the Microsoft Windows operating systembefore using this guide to install or administer the gateway.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the OracleAccessibility Program website athttp://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers that have purchased support have access to electronic supportthrough My Oracle Support. For information, visithttp://www.oracle.com/pls/topic/lookup?ctx acc&id info or visithttp://www.oracle.com/pls/topic/lookup?ctx acc&id trs if you are hearingimpaired.ix
Related DocumentationFor more information, see the following documents: Oracle Database New Features Guide Oracle Call Interface Programmer's Guide Oracle Database Administrator's Guide Oracle Database Development Guide Oracle Database Concepts Oracle Database Performance Tuning Guide Oracle Database Error Messages Oracle Database Globalization Support Guide Oracle Database Reference Oracle Database SQL Language Reference Oracle Database Net Services Administrator's Guide SQL*Plus User's Guide and Reference Oracle Database Heterogeneous Connectivity User's Guide Oracle Database 2 Day DBA Oracle Database Security GuideMany of the examples in this book use the sample schemas of the seed database, whichis installed by default when you install Oracle. Refer to Oracle Database Sample Schemasfor information on how these schemas were created and how you can use themyourself.Printed documentation is available for sale in the Oracle Store athttps://shop.oracle.com/ConventionsThe following text conventions are used in this document:xConventionMeaningboldfaceBoldface type indicates graphical user interface elements associatedwith an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables forwhich you supply particular values.monospaceMonospace type indicates commands within a paragraph, URLs, codein examples, text that appears on the screen, or text that you enter.
11IntroductionThis chapter introduces the challenge faced by organizations when running severaldifferent database systems. It briefly covers Heterogeneous Services, the technologythat the Oracle Database Gateway for SQL Server is based on.To get a good understanding of generic gateway technology, Heterogeneous Services,and how Oracle Database Gateways fit in the picture, reading Oracle DatabaseHeterogeneous Connectivity User's Guide first is highly recommended.This chapter contains the following sections: Overview Heterogeneous Services Technology Oracle Database GatewaysOverviewHeterogeneous data access is a problem that affects a lot of companies. A lot ofcompanies run several different database systems. Each of these systems stores dataand has a set of applications that run against it. Consolidation of this data in onedatabase system is often hard-in large part because many of the applications that runagainst one database may not have an equivalent that runs against another. Until suchtime as migration to one consolidated database system is made feasible, it is necessaryfor the various heterogeneous database systems to interoperate.Oracle Database Gateways provide the ability to transparently access data residing ina non-Oracle system from an Oracle environment. This transparency eliminates theneed for application developers to customize their applications to access data fromdifferent non-Oracle systems, thus decreasing development efforts and increasing themobility of the application. Applications can be developed using a consistent Oracleinterface for both Oracle and SQL Server.Gateway technology is composed of two parts: a component that has the generictechnology to connect to a non-Oracle system, which is common to all the non-Oraclesystems, called Heterogeneous Services, and a component that is specific to thenon-Oracle system that the gateway connects to. Heterogeneous Services, inconjunction with the Oracle Database Gateway agent, enables transparent access tonon-Oracle systems from an Oracle environment.Heterogeneous Services TechnologyHeterogeneous Services provides the generic technology for connecting to non-Oraclesystems. As
Oracle Database Net Services Administrator's Guide SQL*Plus User's Guide and Reference Oracle Database Heterogeneous Connectivity User's Guide Oracle Database 2 Day DBA Oracle Database Security Guide Many of the examples in this book use the sample schemas of the seed database, which is installed by default when you install Oracle.
Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,
Oracle e-Commerce Gateway, Oracle Business Intelligence System, Oracle Financial Analyzer, Oracle Reports, Oracle Strategic Enterprise Management, Oracle Financials, Oracle Internet Procurement, Oracle Supply Chain, Oracle Call Center, Oracle e-Commerce, Oracle Integration Products & Technologies, Oracle Marketing, Oracle Service,
viii Related Documentation The platform-specific documentation for Oracle Database 10g products includes the following manuals: Oracle Database - Oracle Database Release Notes for Linux Itanium - Oracle Database Installation Guide for Linux Itanium - Oracle Database Quick Installation Guide for Linux Itanium - Oracle Database Oracle Clusterware and Oracle Real Application Clusters
In-database analytical SQL with Oracle Database 12c This section outlines the high level processing concepts behind analytical SQL. Processing concepts behind analytical SQL Oracle’s in-database analytical SQL – first introduced in Oracle Database 8i Release
Advanced Replication Option, Database Server, Enabling the Information Age, Oracle Call Interface, Oracle EDI Gateway, Oracle Enterprise Manager, Oracle Expert, Oracle Expert Option, Oracle Forms, Oracle Parallel Server [or, Oracle7 Parallel Server], Oracle Procedural Gateway, Oracle Replication Services, Oracle Reports, Oracle
Welcome to SQL for Oracle NoSQL Database. This language provides a SQL-like interface to Oracle NoSQL Database. The SQL for Oracle NoSQL Database data model supports flat relational data, hierarchical typed (schema-full) data, and schema-less JSON data. SQL for Oracle NoSQL Database is designed to handle all such data seamlessly without any
Oracle is a registered trademark and Developer/2000, Oracle8, Oracle Application Object Library, Oracle Alert, Oracle Financials, Oracle Quality, Oracle Workflow, Oracle Work in Process, SQL*Forms, SQL*Plus, SQL*AMX, SQL*Report, and SQL*ReportWriter are
C is much more flexible than other high-level programming languages: C is a structured language. C is a relatively small language. C has very loose data typing. C easily supports low-level bit-wise data manipulation. C is sometimes referred to as a “high-level assembly language”. When compared to assembly language .