Oracle Database Gateway For SQL Server User’s Guide

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

vii

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.