WIXLIB

Oracle Database Gatewayfor Teradata User’s Guide11g Release 1 (11.1)B31050-01May 2007Beta Draft

Oracle Database Gateway for Teradata User’s Guide, 11g Release 1 (11.1)B31050-01Copyright 2002, 2007, Oracle. All rights reserved.Primary Author:Amitai SelaContributing Authors: Laurel Hale , Cynthia Kibbe, Kishan PeyettI, Maitreyee ChalihaContributors: Orit Curiel, Jacco Draaijer, Vira GoorahThe Programs (which include both the software and documentation) contain proprietary information; theyare provided under a license agreement containing restrictions on use and disclosure and are also protectedby copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly,or decompilation of the Programs, except to the extent required to obtain interoperability with otherindependently created software or as specified by law, is prohibited.The information contained in this document is subject to change without notice. If you find any problems inthe documentation, please report them to us in writing. This document is not warranted to be error-free.Except as may be expressly permitted in your license agreement for these Programs, no part of thesePrograms may be reproduced or transmitted in any form or by any means, electronic or mechanical, for anypurpose.If the Programs are delivered to the United States Government or anyone licensing or using the Programson behalf of the United States Government, the following notice is applicable:U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical datadelivered to U.S. Government customers are "commercial computer software" or "commercial technicaldata" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplementalregulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, includingdocumentation and technical data, shall be subject to the licensing restrictions set forth in the applicableOracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19,Commercial Computer Software--Restricted Rights (June 1987). Oracle USA, Inc., 500 Oracle Parkway,Redwood City, CA 94065.The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherentlydangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,redundancy and other measures to ensure the safe use of such applications if the Programs are used for suchpurposes, and we disclaim liability for any damages caused by such use of the Programs.Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respective owners.The Programs may provide links to Web sites and access to content, products, and services from thirdparties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites.You bear all risks associated with the use of such content. If you choose to purchase any products or servicesfrom a third party, the relationship is directly between you and the third party. Oracle is not responsible for:(a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with thethird party, including delivery of products or services and warranty obligations related to purchasedproducts or services. Oracle is not responsible for any loss or damage of any sort that you may incur fromdealing with any third party.Alpha and Beta Draft documentation are considered to be in prerelease status. This documentation isintended for demonstration and preliminary use only. We expect that you may encounter some errors,ranging from typographical errors to data inaccuracies. This documentation is subject to change withoutnotice, and it may not be specific to the hardware on which you are using the software. Please be advisedthat Oracle Corporation does not warrant prerelease documentation and will not be responsible for any loss,costs, or damages incurred due to the use of this documentation.

ContentsPreface . ixAudience.Documentation Accessibility .Related Documents .Conventions .1ixixxxiIntroductionOverview. 1-1Heterogeneous Services Technology. 1-2Oracle Database Gateways . 1-22Teradata Gateway Features and RestrictionsUsing the Pass-Through Feature .Database Compatibility Issues for Teradata.Schema Considerations .Naming Rules .Rules for Naming Objects .Case Sensitivity .Data Types.Binary Literal Notation .Data Type Conversion.Queries.Row Selection .Empty Bind Variables.Locking .Known Restrictions .Transactional Integrity .Transaction Capability .COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors .Pass-Through Feature.Bind Variables for Date Columns .SQL Syntax .WHERE CURRENT OF Clause.CONNECT BY Clause .ROWID .Beta 2-52-52-62-62-62-62-62-6iii

EXPLAIN PLAN Statement.Callback Support.SQL*Plus.Database Links.Stored Procedures .Known Problems .Encrypted Format Login .Teradata LONG VARCHAR Data Type .Schema Names and PL/SQL.Data Dictionary Views and PL/SQL.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 Teradata 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 3-33-43-43-43-43-53-53-53-53-53-53-5Data Type ConversionData Type Conversion. A-1BSupported SQL Syntax and FunctionsSupported SQL Statements .DELETE .INSERT .SELECT .UPDATE .Oracle Functions.Functions Not Supported by Teradata.Functions Supported by Teradata .Arithmetic Operators .Comparison Operators.Group Functions .ivBeta DraftB-1B-1B-1B-1B-2B-2B-2B-2B-2B-2B-3

String Functions . B-3Other Functions. B-3CData DictionaryData Dictionary Support.Teradata System Catalog Tables .Accessing the Gateway Data Dictionary .Direct Queries to Teradata 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 .Oracle Database Gateway for Teradata Initialization Parameters .Initialization Parameter Description.HS DB DOMAIN .HS DB INTERNAL NAME .HS DB NAME .HS DESCRIBE CACHE HWM .HS LANGUAGE .Character Sets .Language .Territory .HS LONG PIECE TRANSFER SIZE .HS OPEN CURSORS .HS RPC FETCH REBLOCKING .HS RPC FETCH SIZE .HS TIME ZONE .HS TRANSACTION MODEL .IFILE .HS FDS CONNECT INFO .HS FDS DEFAULT OWNER .HS FDS RECOVERY ACCOUNT .HS FDS RECOVERY PWD.HS FDS TRACE LEVEL.HS FDS TRANSACTION LOG .HS FDS FETCH ROWS.Beta D-6D-7D-7D-8D-8D-8D-9D-9D-9D-9v

viBeta Draft

List of C–34Data Type Conversions.Oracle Data Dictionary View Names and Teradata Equivalents .ALL CATALOG .ALL COL COMMENTS .ALL CONS COLUMNS .ALL CONSTRAINTS.ALL IND COLUMNS .ALL INDEXES .ALL OBJECTS.ALL TAB COLUMNS.ALL TAB COMMENTS.ALL TABLES .ALL USERS .ALL VIEWS.DBA CATALOG.DBA COL COMMENTS.DBA OBJECTS .DBA TAB COLUMNS .DBA TAB COMMENTS .DBA TABLES.DICT COLUMNS .DICTIONARY .DUAL.USER CATALOG .USER COL COMMENTS.USER CONS COLUMNS .USER CONSTRAINTS .USER IND COLUMNS.USER INDEXES.USER OBJECTS .USER TAB COLUMNS .USER TAB COMMENTS .USER TABLES .USER USERS.USER VIEWS .Beta 4C-15C-15C-16C-17C-17C-18C-19C-19vii

viiiBeta Draft

PrefaceThis manual describes the Oracle Database Gateway for Teradata, which enablesOracle client applications to access Teradata data through Structured Query Language(SQL). The gateway, with the Oracle database, creates the appearance that all dataresides on a local Oracle database, even though the data can be widely distributed.This preface covers the following topics: Audience Documentation Accessibility Related Documents ConventionsAudienceThis manual is intended for Oracle database administrators who perform thefollowing tasks: Installing and configuring the Oracle Database Gateway for Teradata Diagnosing gateway errors Using the gateway to access Teradata dataYou should understand the fundamentals of OracleDatabase Gateways and the UNIX based platforms before usingthis guide to install or administer the gateway.Note:Documentation AccessibilityOur goal is to make Oracle products, services, and supporting documentationaccessible, with good usability, to the disabled community. To that end, ourdocumentation includes features that make information available to users of assistivetechnology. This documentation is available in HTML format, and contains markup tofacilitate access by the disabled community. Accessibility standards will continue toevolve over time, and Oracle is actively engaged with other market-leadingtechnology vendors to address technical obstacles so that our documentation can beaccessible to all of our customers. For more information, visit the Oracle AccessibilityProgram Web site athttp://www.oracle.com/accessibility/Beta Draftix

Accessibility of Code Examples in DocumentationScreen readers may not always correctly read the code examples in this document. Theconventions for writing code require that closing braces should appear on anotherwise empty line; however, some screen readers may not always read a line of textthat consists solely of a bracket or brace.Accessibility of Links to External Web Sites in DocumentationThis documentation may contain links to Web sites of other companies ororganizations that Oracle does not own or control. Oracle neither evaluates nor makesany representations regarding the accessibility of these Web sites.TTY Access to Oracle Support ServicesOracle provides dedicated Text Telephone (TTY) access to Oracle Support Serviceswithin the United States of America 24 hours a day, seven days a week. For TTYsupport, call 800.446.2398.Related DocumentsFor more information, see these Oracle resources: Oracle Database New Features Guide Oracle Call Interface Programmer's Guide Oracle Database Administrator's Guide Oracle Database Advanced Application Developer's 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 Administrator's Guide Oracle Database Security GuideMany of the examples in this book use the sample schemas, which are installed bydefault when you select the Basic Installation option with an Oracle Databaseinstallation. Refer to Oracle Database Sample Schemas for information on how theseschemas were created and how you can use them yourself.Printed documentation is available for sale in the Oracle Store athttp://oraclestore.oracle.com/To download free release notes, installation documentation, white papers, or othercollateral, please visit the Oracle Technology Network (OTN). You must register onlinebefore using OTN; registration is free and can be done athttp://otn.oracle.com/membership/xBeta Draft