oracle rdb - s-and-b.ru · pdf file... oracle forms, oracle7, rdb7, oracle rdb, oracle sql ......

Oracle Rdb

Guide to SQL*Net® for Rdb7

Release 1.0.2

December 1997

Part No. A59211-01

Guide to SQL*Net for Rdb7

Part No. A59211-01

Release 1.0.2

Copyright © 1997, Oracle Corporation. All rights reserved.

This Program contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright, patent and other intellectual property law. Reverse engineering of the software is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this document is error free.

If this Program is delivered to a U.S. Government Agency of the Department of Defense, then it is deliv-ered with Restricted Rights and the following legend is applicable:

Restricted Rights Legend Programs delivered subject to the DOD FAR Supplement are 'commercial computer software' and use, duplication and disclosure of the Programs shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are 'restricted computer software' and use, duplication and disclo-sure of the Programs shall be subject to the restrictions in FAR 52..227-14, Rights in Data -- General, including Alternate III (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.

The programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be licensee's responsibility to take all appropriate fail-safe, back up, redundancy and other measures to ensure the safe use of such applications if the programs are used for such purposes, and Oracle disclaims liability for any damages caused by such use of the programs.

Oracle, SQL*Net, and SQL*Plus are registered trademarks of Oracle Corporation, Redwood City, California.

Developer/2000, Oracle Call Interface, Oracle Forms, Oracle7, Rdb7, Oracle Rdb, Oracle SQL/Services, and Enabling the Information Age are trademarks of Oracle Corporation, Redwood City, California.

All other products or company names are used for identification purposes only, and may be trademarks of their respective owners.

iii

Contents

Send Us Your Comments ................................................................................................................... ix

Preface............................................................................................................................................................ xi

1 Introduction to SQL*Net for Rdb7

1.1 What Is SQL*Net for Rdb7? ................................................................................................. 1-11.1.1 Oracle Call Interface ...................................................................................................... 1-21.1.2 Server-Side Solution....................................................................................................... 1-21.1.3 Common Application Development ........................................................................... 1-31.2 Oracle SQL/Services Universal Services Supported ....................................................... 1-5

2 SQL*Net for Rdb7 Processing

2.1 SQL*Net for Rdb7 Functions ............................................................................................... 2-12.1.1 OCI Message Mapping.................................................................................................. 2-12.1.2 Cursor Management ...................................................................................................... 2-22.1.3 Data Types....................................................................................................................... 2-22.1.4 Data Definition Language............................................................................................. 2-22.1.5 SQL Cursor Semantics ................................................................................................... 2-22.1.6 Oracle SQL ALTER SESSION Statement .................................................................... 2-22.1.7 Data Formatting ............................................................................................................. 2-22.1.8 Statement Parsing........................................................................................................... 2-32.1.9 Data Type Descriptions ................................................................................................. 2-42.1.10 Oracle7 Data Dictionary................................................................................................ 2-52.1.11 Multischema Emulation ................................................................................................ 2-6

iv

2.2 Initializing Your Server Environment ................................................................................ 2-6

3 Setup Procedures

3.1 Installation and Setup Overview......................................................................................... 3-13.2 Step 1: Software Installation................................................................................................. 3-33.2.1 Standard and Multiversion Kits ................................................................................... 3-43.2.2 Using VMSINSTAL........................................................................................................ 3-43.2.3 Changes to the Installation Procedure ........................................................................ 3-53.2.4 Installation Verification ................................................................................................. 3-73.2.5 Problem Reporting ......................................................................................................... 3-73.3 Step 2: Preparing Your Database......................................................................................... 3-73.3.1 Defining Oracle7 Functions and the Emulated Oracle7 Data Dictionary .............. 3-73.3.2 Handling Errors .............................................................................................................. 3-93.4 Step 3: Configuring Oracle SQL/Services ....................................................................... 3-103.4.1 Defining the OCI Service............................................................................................. 3-113.4.2 Defining the OCI Dispatcher ...................................................................................... 3-123.5 Step 4: Configuring SQL*Net............................................................................................. 3-133.5.1 Preserving Existing SQL*Net Definitions ................................................................. 3-133.5.2 Step 4a: Creating the SQL*Net Configuration Definitions..................................... 3-153.5.3 Step 4b: Distributing SQL*Net Configuration Files ................................................ 3-193.6 Step 5: Starting the OCI Dispatcher and OCI Service .................................................... 3-223.7 Connecting Using SQL*Net for Rdb7............................................................................... 3-233.8 Defining Character Sets ...................................................................................................... 3-243.8.1 Defining Character Sets on Server Systems.............................................................. 3-253.8.2 Defining Character Sets on Client Systems .............................................................. 3-263.8.3 Rules and Recommendations ..................................................................................... 3-263.8.4 Error Messages.............................................................................................................. 3-263.9 SQL*Net Errors and Trace Output.................................................................................... 3-273.10 Error Messages Returned to OCI Client Applications ................................................... 3-273.10.1 Logon Error ................................................................................................................... 3-273.10.2 Database Setup Error ................................................................................................... 3-273.10.3 SQL Initialization File Error........................................................................................ 3-273.10.4 Errors When Attaching to an Rdb7 Database or When Oracle SQL/Services

Database Service Is Not Available 3-283.10.5 Errors When Oracle SQL/Services Server or OCI Dispatcher Is Not Available. 3-28

v

3.10.6 Error When SQL*Net Service Name Is Not Defined .............................................. 3-293.11 Inspecting Oracle SQL/Services Log Files for Errors .................................................... 3-303.12 Calling Rdb7 Stored Procedures in OCI Programs ........................................................ 3-313.13 Sample Setup Procedures................................................................................................... 3-323.14 Referencing an Rdb7 Database as a Database Link........................................................ 3-343.14.1 CREATE DATABASE LINK Example ...................................................................... 3-353.14.2 Database Link Restrictions.......................................................................................... 3-363.15 Removing SQL*Net for Rdb7 from Your Database........................................................ 3-37

4 SQL ALTER SESSION Statement

A Tables and Relations

A.1 Oracle Metadata Views......................................................................................................... A-1A.2 Current Session Information................................................................................................ A-4A.3 Handling 31-Character Object Names ............................................................................... A-5A.4 Defining Data Types Recognized by Rdb7........................................................................ A-6

B Common Installation Script

B.1 Using a Common Installation Script .................................................................................. B-1

Index

vi

Examples

2–1 Inserting an Oracle7 Date Literal into an ANSI Date Column ....................................... 2-32–2 Replacing an SQL Statement Containing the Keyword VALUE.................................... 2-42–3 Using a Pound Character (#) in an Oracle SQL Statement .............................................. 2-43–1 Invoking VMSINSTAL ......................................................................................................... 3-43–2 Listener Definition............................................................................................................... 3-223–3 Invoking the PLUS32 Application .................................................................................... 3-233–4 Specifying the Western European Character Set ............................................................ 3-253–5 Specifying the French Language and Territory .............................................................. 3-253–6 PL/SQL Block in an OCI Program.................................................................................... 3-313–7 TNSNAMES.ORA File ........................................................................................................ 3-353–8 Database Link Definition.................................................................................................... 3-353–9 Valid Oracle SQL UPDATE Statement............................................................................. 3-363–10 Invalid Oracle SQL UPDATE Statement.......................................................................... 3-363–11 Using PL/SQL When DML Does Not Work ................................................................... 3-37B–1 Common SQL*Plus Installation Script ............................................................................... B-1

vii

Figures

1–1 Client/Server Processing ..................................................................................................... 1-23–1 SQL*Net for Rdb7 Installation, Database Preparation, and Configuration.................. 3-2

viii

Tables

1–1 SQL*Net for Rdb7 Processing Features...................................................................... 1-33–1 Setting Up to Use SQL*Net for Rdb7 ......................................................................... 3-13–2 Oracle SQL/Services Standard and Multiversion Save Sets ...................................... 3-43–3 Supported Character Sets ........................................................................................ 3-24A–1 Installed SQL*Net for Rdb7 Relations........................................................................ A-2A–2 ORA_SESSION Table................................................................................................. A-5A–3 Oracle7 and Rdb7 Data Types ................................................................................... A-6

ix

Send Us Your Comments

Guide to SQL*Net for Rdb7, Release 1.0.2

Part No. A59211-01

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision.

■ Did you find any errors?■ Is the information clearly presented?■ Do you need more information? If so, where?■ Are the examples correct? Do you need more examples?■ What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the chapter, section, and page number (if available). You can send comments to us in the following ways:

■ electronic mail - [email protected]■ FAX - 603-897-3334. Attn: Oracle Rdb Documentation■ postal service:

Oracle Corporation Oracle Rdb SQL*Net for Rdb DocumentationOne Oracle DriveNashua, NH 03062-2698USA

If you would like a reply, please give your name, address, and telephone number below.

xi

Preface

This guide helps you set up and use SQL*Net for Rdb7 software to configure and develop useful connections between SQL*Net clients and Oracle Rdb7 databases.

Intended AudienceThis guide is intended for database administrators and other database users inter-ested in accessing an Rdb7 database using Oracle SQL statements.

This guide assumes that you are familiar with the Rdb7 and Oracle7 database prod-ucts, and with the operating system environment under which the database is run-ning. Also, readers should be familiar with Oracle Rdb SQL statements and Oracle SQL statements.

StructureThis guide contains four chapters, two appendixes, and an index.

Chapter 1 Provides an overview of how SQL*Net for Rdb7 software works.

Chapter 2 Explains in detail the features and benefits of SQL*Net for Rdb7.

Chapter 3 Describes how to prepare to run SQL*Net for Rdb7 software on OpenVMS systems.

Chapter 4 Describes how to use the Oracle SQL ALTER SESSION statement.

xii

Related DocumentsThe following list includes the names of the documents to which this guide refers:

■ For information about the Rdb7 product and its SQL statements, refer to the fol-lowing:

– Oracle Rdb Release Notes

– Oracle Rdb SQL Reference Manual

■ For information about installing SQL*Net for Rdb7 and configuring Oracle SQL/Services, refer to the following:

– Oracle SQL/Services Release Notes

– Oracle SQL/Services Installation Guide

– Oracle SQL/Services Server Configuration Guide

■ For an overview of SQL*Net, refer to Understanding SQL*Net.

■ For information about the Oracle7 SQL statements and functions, refer to the following:

– Oracle7 Server SQL Language Reference Manual

– Oracle7 PL/SQL User’s Guide and Reference

– Oracle7 SQL*Plus User’s Guide and Reference

■ For more information about the differences between Oracle7 SQL and the Oracle_Level1 dialect of Rdb7 SQL statements, refer to Oracle Rdb: A Compari-son of SQL Dialects for Oracle and Oracle Rdb included with the SQL*Net for Rdb7 software kit.

■ For information about using the Oracle Call Interface (OCI) to build applications that access data in Rdb7 databases, refer to the Programmer’s Guide to the Oracle Call Interface.

Appendix A Defines the tables, views, and other database objects that comprise the emulated Oracle7 data dictionary that the SQL*Net for Rdb7 product stores in your Rdb7 database. This appendix also lists the file name, target location, and purpose of all files installed with SQL*Net for Rdb7 software.

Appendix B Explains how to use a common installation script to install the Rdb7 and Oracle7 servers.

xiii

■ For information about building Oracle Developer/2000 applications that access data in Rdb7 databases, refer to the following:

– The supplemental documentation addendum to Chapter 14 of the Oracle Developer/2000 Forms Advanced Techniques Manual

– Appendix D of the Oracle Developer/2000 Installation and User’s Guide (Release 1.3)

ConventionsOpenVMS refers to both the OpenVMS Alpha and the OpenVMS VAX operating systems.

Rdb7 refers to Oracle Rdb for OpenVMS Release 7.0 software.

Oracle7 refers to Oracle Release 7.0 software.

The Oracle Rdb SQL interface is referred to as Rdb7 SQL. The dialect of Rdb7 SQL used is Oracle Level1.

The Oracle7 SQL server is referred to as Oracle SQL.

Oracle Rdb SQL*Net for Rdb7 is referred to as SQL*Net for Rdb7.

Oracle Developer/2000 is referred to as Developer/2000.

In examples, an implied carriage return occurs at the end of each line, unless other-wise noted. You must press the Return key at the end of a line of input.

The following conventions are also used in this guide:

Convention Meaning

. . .

Vertical ellipsis points in an example mean that information not directly related to the example has been omitted.

. . . Horizontal ellipsis points in statements or commands mean that parts of the statement or command not directly related to the exam-ple have been omitted.

boldface text Boldface type in text indicates a term defined in the text.

xiv

italic text Italic text emphasizes important information, information supplied by the user, titles of manuals, and variables. Variables include infor-mation that varies in system messages (internal error number), in command lines (/PRODUCER=name), and in command parameters in text (where device-name is the current disk name).

< > Angle brackets enclose user-supplied names.

[ ] Brackets enclose optional clauses from which you can choose one or none.

{nn} nn, enclosed in brackets, indicates an optional product-version num-ber for a file name in a multiversion environment. If you omit the version number, you are using the standard version environment.

$ The dollar sign represents the DIGITAL Command Language prompt in OpenVMS systems.

Convention Meaning

Introduction to SQL*Net for Rdb7 1-1

1Introduction to SQL*Net for Rdb7

SQL*Net for Rdb7 provides an environment in which you can run existing SQL*Net applications to access data in Rdb7 databases. The SQL*Net applications can use the Oracle Call Interface (OCI) or software such as the PL/SQL interface or Developer/2000 to access and manage data in an Rdb7 database.

1.1 What Is SQL*Net for Rdb7?SQL*Net for Rdb7 connects Oracle SQL*Net clients to Rdb7 servers. The unique advantage offered by SQL*Net for Rdb7 is the ability to use Oracle SQL semantics to access data in Rdb7 databases.

SQL*Net for Rdb7:

■ Identifies itself to the client applications as an Oracle database server

■ Emulates many of the Oracle SQL semantics

■ Uses SQL*Net for network communications

SQL*Net for Rdb7 broadens the range of your client applications by letting you build a single source code stream that runs against either an Rdb7 or Oracle7 data-base instance.

For example, you can substitute the Rdb7 server for the Oracle7 Server when your application requires functions supplied by an Rdb7 database. If you use only the Rdb7 server, SQL*Net for Rdb7 provides many of the capabilities of the OCI archi-tecture to your Rdb7 applications.

Figure 1–1 shows the client/server relationships in a SQL*Net for Rdb7 environ-ment.

What Is SQL*Net for Rdb7?

1-2 Guide to SQL*Net for Rdb7

Figure 1–1 Client/Server Processing

1.1.1 Oracle Call InterfaceThe unique capabilities of SQL*Net for Rdb7 are made possible through the Oracle Call Interface (OCI), a key, open technology that is supported on more platforms than either ODBC or the Oracle SQL/Services application programming interface (API).

OCI applications and tools run in the SQL*Net client/server environment, enabling diverse combinations of server and client hardware and operating system environ-ments. Because the OCI architecture separates the client user interface from the server implementation, it is possible to add new and different user interfaces to existing servers, and change the server implementation without any effect on the user interface.

1.1.2 Server-Side SolutionSQL*Net for Rdb7 capitalizes on the flexibility of OCI by connecting your SQL*Net client applications directly to an Rdb7 server.

Because SQL*Net for Rdb7 is designed as a server-side solution, it is as easy and cost-effective to use with a diverse set of client platforms as it is to use the Oracle7 Server with a diverse set of client platforms. The client applications can be running on any operating system platform on which Oracle supports SQL*Net.

Rdb DatabaseOracle Database

OCI Client

Oracle RdbServer

NU−3650A−RA

SQL*Net SQL*Net

OracleServer SQL*Net for Rdb



The application programming interface (API) software that you use on client sys-tems is distinctly separate from your SQL*Net for Rdb7 server-side implementa-tion. Any API software that you use to code client applications, including OCI or any of the Pro* compilers, must be separately purchased and installed for each cli-ent system.

To build new OCI applications, you must install the particular OCI software needed to develop and build new OCI applications. Your existing OCI applications will run without the need to purchase, install, configure, or manage additional cli-ent software to use SQL*Net for Rdb7.

SQL*Net for Rdb7 appears as an Oracle7 Server to the client, and the client inter-acts with SQL*Net for Rdb7 in the same way it interacts with the Oracle7 Server. The client typically queries the Oracle7 data dictionary to obtain metadata informa-tion about the target database, and performs a number of other OCI calls to query and manipulate the data in the database.

1.1.3 Common Application DevelopmentSQL*Net for Rdb7 was built to help SQL programmers create software that can run against both the Rdb7 server and the Oracle7 Server.

To help you to run SQL*Net client applications and tools against an Rdb7 server, SQL*Net for Rdb7 augments the features of Rdb7 SQL with the processing features described in Table 1–1.

Note: The metadata for the Rdb7 SQL dialect is very different from that of the Oracle7 Server. The Oracle7 data dictionary you create using the sup-plied Rdb7 SQL script allows SQL*Net for Rdb7 to emulate most aspects of the Oracle7 data dictionary that are important to client software.

Table 1–1 SQL*Net for Rdb7 Processing Features

Function Description

Cursor management Manages OCI cursors for each statement, then ties the cursors to Rdb7 SQL statements.

OCI message mapping Maps OCI calls to Rdb7 dynamic SQL calls.

Oracle data types Describes and converts Rdb7 data types as Oracle7 data types.



All these features allow for common application development between the Oracle7 Server and the Rdb7 server (using SQL*Net for Rdb7). Chapter 2 describes SQL*Net for Rdb7 processing in more detail.

Data formatting Performs Oracle7 style formatting in which the Oracle7 Server formats data for the client or receives formatted data from the client. The formatted information is passed to the server by the Oracle SQL ALTER SESSION statement.

Statement changes Reprocesses a failed SQL statement after performing the neces-sary modifications to make the statement comply with Rdb7 syntax. This processing is done for a limited number of syntax differences between Oracle7 and Rdb7. This reprocessing usu-ally allows the SQL statement to succeed.

Statement type Obtains the type of SQL statement being parsed from Rdb7 SQL and returns it to the client.

Data definition language (DDL)

Provides some DDL and SQL cursor semantics that provide behavior similar to what you get from an Oracle7 Server. For example, before and after each DDL request, a COMMIT state-ment is issued.

Data dictionary Provides a collection of views and stored procedures that emu-late the Oracle7 data dictionary to provide the style of meta-data tables typical to Oracle7.

Multischema emulation Emulates a multischema environment that is similar to what you get with Oracle multischema databases (all Oracle data-bases are multischema databases, while most Rdb7 databases are not). The table name cannot be used in more than one schema, but the data dictionary provides a multischema appearance.

Note: There are a number of differences between Oracle SQL and the Ora-cle Level1 dialect of Rdb7 SQL statements that you should know about when programming applications to run on both Oracle7 and Rdb7. Refer to Oracle Rdb: A Comparison of SQL Dialects for Oracle and Oracle Rdb for more information.

Table 1–1 SQL*Net for Rdb7 Processing Features(Cont.)

Function Description

Oracle SQL/Services Universal Services Supported


1.2 Oracle SQL/Services Universal Services SupportedSQL*Net for Rdb7 supports Oracle SQL/Services universal services. A universal service allows an Oracle7 client application to attach to any number of Rdb7 data-bases. Prior to this release, SQL*Net for Rdb7 supported only the Oracle SQL/Ser-vices database services that allow client applications to preattach to only one database.

Universal service support allows one or a small number of Oracle SQL/Services universal services to support a large number of infrequently used databases. With one or a small number of universal services serving any number of databases, it is reasonable to prestart the executor processes. With universal service support, client applications no longer need to wait for Rdb7 executor processes to start up.

To specify the database or databases to which clients attach in a universal service, clients must define the SQL*Net service for each database or set of databases. Each SQL*Net service can map to the same Oracle SQL/Services universal service.

With the Oracle Network Manager Release 3.1.0.5.1, an Rdb7 database name can be supplied in the connect string for an Rdb7 service. The RDB_DATABASE field in the connect string is used to specify the database or databases to which a universal service should attach.

If the Oracle SQL/Services service to which the SQL*Net connect strings map is a universal service, clients can specify the Rdb7 database name as one of the follow-ing:

■ A full Rdb7 SQL ATTACH statement

■ An @file_spec

The file you specify can contain SQL statements that tailor the SQL environ-ment for a client connection. Also, the use of a file specification is a way for the the client application to execute multiple ATTACH statements. The first ATTACH statement cannot contain the ALIAS clause, as the first ATTACH statement must set up the default alias RDB$DBHANDLE.

SQL*Net for Rdb7 uses the following syntax conventions when executing this file:

– Leading and trailing space on a line is ignored.

– Comments start with two consecutive hyphens (- -). The comment lines must start at the beginning of a line and continue to the next new line.

– Each SQL statement must be able to be dynamically prepared, executed, and released by the SQL EXECUTE IMMEDIATE statement.

Oracle SQL/Services Universal Services Supported


– SQL statements cannot span multiple lines.

– A trailing semicolon (;) at the end of the SQL statement is ignored to allow SQL files to be invoked and verified using interactive SQL.

■ A simple file specification

An ATTACH statement is built around this file specification. The following example shows a TNSNAMES.ORA file with the RDB_DATABASE field speci-fied in the connect string:

RARE_DB = (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=SQS_NODE) (PORT=1234) ) ) (CONNECT_DATA=(SERVICE=OCI_UNIV) (RDB_DATABASE=disk:[dev]db.rdb) )

SQL*Net for Rdb7 provides limited universal service support for Oracle Rdb7 Dis-tributed Technology databases. The limitations for these databases or any data source not prepared1 for SQL*Net for Rdb7 are as follows:

■ The default alias must be prepared for SQL*Net for Rdb7 and cannot be an Ora-cle Rdb7 Distributed Technology data source.

■ Oracle7 metadata tables, such as the ALL_OBJECTS view, are not supported in an Oracle Rdb7 Distributed Technology data source.

■ Use of any of the Oracle7 functions that we provide as external or stored func-tions in Rdb7 are not supported.

■ Use of any of the SQL*Net for Rdb7 NLS functions such as TO_DATE, TO_CHAR, or TO_NUMBER are not supported.

■ Use of Oracle7 formatted date literals is not supported.

■ Use of Oracle7 ROWID literals is not supported.

1 An unprepared Rdb7 database is one for which you have not run the RDB_NATCONN_PREPARE.SQL script.

SQL*Net for Rdb7 Processing 2-1

2SQL*Net for Rdb7 Processing

Within Rdb7 SQL, the Oracle Level1 dialect and the Oracle7 functions were built specifically with SQL*Net for Rdb7 in mind. The Rdb7 SQL features help SQL pro-grammers create client applications that can run against both the Rdb7 server and the Oracle7 Server.

This chapter provides more information about the following aspects of SQL*Net for Rdb7 processing:

■ Section 2.1 describes how SQL*Net for Rdb7 augments Rdb7 SQL with many processing features to allow common application development between the Oracle7 Server and the Rdb7 server.

■ Section 2.2 describes the SQL initialization file that sets specific session parame-ters to initialize the Oracle SQL/Services execution environment.

2.1 SQL*Net for Rdb7 FunctionsThe following sections provide a more detailed description of these SQL*Net for Rdb7 processing features.

2.1.1 OCI Message MappingWith OCI, you can open and fetch a number of rows with a single call. To emulate this capability, SQL*Net for Rdb7 implicitly performs a number of steps to achieve the same result:

1. Opens the cursor

2. Fetches the specified number of rows

3. Responds with the data

SQL*Net for Rdb7 Functions


The order in which a cursor is opened and described is different when using OCI compared to using Rdb7 dynamic SQL statements. SQL*Net for Rdb7 hides the dif-ferences by manipulating the message order and presenting the OCI message order to the client.

2.1.2 Cursor ManagementOCI uses a cursor for every statement. Therefore, SQL*Net for Rdb7 manages a vir-tual OCI cursor for each statement. SQL*Net for Rdb7 then ties these virtual OCI cursors to Rdb7 dynamic SQL statement IDs for most statements or to Rdb7 dynamic SQL cursors for SELECT statements.

2.1.3 Data TypesSQL*Net for Rdb7 fetches data in machine native data types, and then converts the data to Oracle7 portable data types prior to sending it to the client.

Conversely, SQL*Net for Rdb7 receives data in portable data types, and then con-verts and passes them to Rdb7 dynamic SQL as machine native data types.

2.1.4 Data Definition LanguageBefore and after each DDL request, SQL*Net for Rdb7 mimics Oracle7 behavior by issuing a COMMIT statement.

2.1.5 SQL Cursor SemanticsOn session startup, SQL*Net for Rdb7 uses the SQL SET HOLD CURSORS ‘ALL’ statement to make all cursors into HOLD CURSORS by default. The benefit of this action is that cursors stay open across transactions.

2.1.6 Oracle SQL ALTER SESSION StatementThe ALTER SESSION statement, as documented by the Oracle7 Server SQL Lan-guage Reference Manual, is processed by SQL*Net for Rdb7 to provide a variety of information to SQL*Net for Rdb7. SQL*Net for Rdb7 supports only the ALTER SES-SION statement syntax described in Chapter 4.

2.1.7 Data FormattingThe Oracle7 Server formats data for the client and receives formatted data from the client. The formatted information is passed to the server using the ALTER SES-SION statement. This enables three important Oracle7 features:



■ The correct processing of the TO_DATE, TO_CHAR, and TO_NUMBER func-tions.

■ The correct handling of date literals in the format specified by the ALTER SES-SION statement.

■ The correct formatting of date and numeric data when fetched as text accord-ing to the information specified in the ALTER SESSION statement.

See Section 2.1.6 for more information about the SQL ALTER SESSION statement.

2.1.8 Statement ParsingRdb7 SQL provides a new Oracle Level1 dialect. However, even with the new dia-lect, there are a number of Oracle7 constructs that Rdb7 SQL does not accept. If Rdb7 SQL rejects a statement for specific reasons, such as a date conversion error, SQL*Net for Rdb7 examines the statement and replaces the Oracle7 format date lit-eral with an equivalent statement that Rdb7 SQL accepts.

The following examples demonstrate statement parsing.

Example 2–1 Inserting an Oracle7 Date Literal into an ANSI Date Column

This example attempts to insert an Oracle7 date literal into an ANSI date column:

INSERT INTO ATABLE VALUES (‘3-AUG-46’);

Because this statement is rejected by Rdb7 SQL, SQL*Net for Rdb7 replaces it with the following:

INSERT INTO ATABLE VALUES (CAST (TO_DATE(‘3-AUG-46’) AS DATE ANSI));

The TO_DATE function supplied by SQL*Net for Rdb7 works similarly to the Oracle7 TO_DATE function for formatting strings into dates. Because the TO_DATE function supplied by SQL*Net for Rdb7 returns a DATE VMS date, you must use the CAST function to match the DATE ANSI format.

Note: Oracle Corporation recommends the use of DATE VMS dates whenever possible. DATE VMS most closely resembles the Oracle7 DATE data type.



Example 2–2 Replacing an SQL Statement Containing the Keyword VALUE

This example shows SQL*Net for Rdb7 replacing an SQL statement containing the keyword VALUE:

SELECT VALUE FROM ATABLE;

Rdb7 SQL rejects this keyword when it is used in a SELECT list and returns a spe-cific error. Unfortunately, an important table containing National Language Sup-port (NLS) settings has this column name. Therefore, SQL*Net for Rdb7 reconciles this error by retrying the statement after substituting “VALUE” in place of the word VALUE. This replacement enables the request to succeed.

Example 2–3 Using a Pound Character (#) in an Oracle SQL Statement

This example shows how the Rdb7 pound character (#) is handled by SQL*Net for Rdb7. When used in Oracle7 databases, the pound character (#) is allowed in col-umn and table names. The following example shows the use of a pound character (#) in an Oracle SQL statement:

SELECT “MY#COL” FROM “MY#TABLE”;

Using ANSI quoting allows the # character to pass through the compiler and into the database, as requested.

To see how SQL*Net for Rdb7 modifies SQL statements, you can turn on logging with the ALTER SESSION LOG BRIEF statement (described in Chapter 4).

2.1.9 Data Type DescriptionsSQL*Net for Rdb7 describes all the Rdb7 data types in terms of Oracle7 data types:

■ Rdb7 date and time data types are described as DATE.

■ All CHAR data types greater than 255 bytes and VARCHAR data types greater than 2000 bytes are described as LONG.

■ The LIST OF BYTE VARYING column is described as LONG.

Note: Parsing does not occur unless the statement fails. For example, if you provide a valid OpenVMS date literal, it will be processed without the assistance of SQL*Net for Rdb7.



■ All the numeric data types are described as Oracle numbers:

– An INTEGER is described as a NUMBER(10,0).

– A BIGINT(2) is described as a NUMBER(19,2).

Data type precision is described as one more place of precision than can actually be represented. This is because Rdb7 uses native binary data types whose range does not map directly to a decimal range. Oracle numbers use decimal representation. So, when SQL*Net for Rdb7 describes an Rdb7 TINYINT column as NUMBER(3,0), the column cannot hold the number 999, but it can hold the number 111.

2.1.10 Oracle7 Data DictionaryWhen you prepare an Rdb7 database for SQL*Net for Rdb7, you install a number of different database objects used to help SQL*Net for Rdb7 emulate the Oracle7 data dictionary that can be used by OCI clients. These objects include:

■ The Oracle metadata tables (or data dictionary) provided as views over the Rdb7 metadata tables. See Appendix A for complete details.

■ Four domains that SQL*Net for Rdb7 adds as a convenience for programmers migrating Oracle applications. These four domains are:

NUMBER AS DOUBLE PRECISIONRDB1LONG AS 64KRDB2LONG AS 32KRDB4LONG AS 16K

■ TO_DATE (DATA, FORMAT, NLS_parameters)—Data is a string literal and uses either the provided format string or the default format string to convert the string to a DATE VMS data type. With Rdb7, you cannot combine DATE VMS and ANSI date-time data types without using a CAST function. In the Oracle Level1 dialect the DATE VMS data type can be used with mathematical operators, so use it whenever possible.

■ TO_NUMBER (DATA, FORMAT, NLS_parameters)—Data is a string literal and uses either the provided format string or the default format string to convert the string to a DOUBLE PRECISION data type. The TO_NUMBER function is restricted by the DOUBLE PRECISION data type, so integers with a precision 16 or greater cannot be represented precisely.

■ TO_CHAR (DATA, FORMAT, NLS_parameters)—Data is a number or date lit-eral and creates a formatted character string. When using TO_CHAR with an unscaled integer of precision 18, a format string must be provided. Use a for-

Initializing Your Server Environment


mat string when using TO_CHAR with unscaled BIGINT data. TO_CHAR assumes an 18-digit number is a date if no format string is provided.

■ USERENV—Given one input string, the function USERENV supplies details about the current session as a VARCHAR data type. Only the ‘TERMINAL’ and ‘LANGUAGE’ input values return any meaningful information. Input strings ‘LABEL’, ‘SESSIONID’, and ‘ENTRYID’ return valid fixed values.

Refer to the Oracle7 documentation for more information about the referenced func-tions, domains, and data types.

2.1.11 Multischema EmulationBecause most Rdb7 databases are not multischema databases and because all Oracle7 databases are multischema, SQL*Net for Rdb7 provides a form of multi-schema emulation.

SQL*Net for Rdb7 provides the ALTER SESSION SCHEMA EMULATION state-ment to emulate multischema databases. This statement uses the Oracle7 data dic-tionary and hooks into the SQL compiler to provide a type of multischema emulation. See Chapter 4 for additional information about this statement.

2.2 Initializing Your Server EnvironmentSQL*Net for Rdb7 databases are served by Oracle SQL/Services. To initialize the execution environment, Oracle SQL/Services allows you to specify an SQL initial-ization file for the service. The initialization file executes SQL statements that set specific session parameters (for example, locking defaults or character set defaults).

You can execute most initialization statements:

■ Directly in your Oracle SQL/Services initialization file defined for the service

■ Indirectly using the ORA_INIT stored procedure

The advantage to using the ORA_INIT stored procedure is that it allows you to con-ditionally enable data definition language (DDL) statements such as the ALTER SESSION statement in an IF block. Although Rdb7 SQL does not allow DDL state-ments in a compound statement such as an IF block, the ORA_INIT stored proce-dure allows you to store DDL and other statements for subsequent execution by SQL*Net for Rdb7. Initialization statements that you stipulate with ORA_INIT are executed in the order you specify them after your SQL initialization file defined for the service has completed.

For example, you might want to enable full server logging with connections from the SQL*Plus client application. Even though you can query the ORA_SESSION



table to determine the client program name, you cannot form a query in your SQL initialization file defined for the service that conditionally enables full server log-ging depending upon the client program name. This is because the ALTER SES-SION LOG FULL statement is a DDL statement, and cannot occur in a compound statement such as an IF block. However, you can use the ORA_INIT stored proce-dure in your SQL initialization file to achieve the same effect, as follows:

1. Define a stored procedure, SQLPLUS_LOG, that you can use to determine if the client program is a SQL*Plus application.

The stored procedure might be similar to the following:

create module SQLPLUS_LOG_MODULE language sql procedure SQLPLUS_LOG; begin declare :A integer; select count(*) into :A from ORA_SESSION where (INFO_TYPE = ’PROGRAM’ and INFO containing ’SQLPLUS’); if :A > 0 then call ORA_INIT (’ALTER SESSION LOG FULL’); end if; end;end module;

2. Call the SQLPLUS_LOG procedure from your service SQL initialization file, as follows:

call SQLPLUS_LOG();

3. If the client program is a SQL*Plus application, call the ORA_INIT procedure to store an ALTER SESSION LOG FULL statement for subsequent execution by SQL*Net for Rdb7.

See Section A.2 for more information about the ORA_SESSION table.

Note: Even though querying the client program name or client terminal name can be a convenient and powerful method of tailoring the server environment to the client, Oracle Corporation does not recommend that you use this method for security purposes. SQL*Net for Rdb7 cannot guar-antee that the client application has accurately reported the client program name or client terminal name.

Setup Procedures 3-1

3Setup Procedures

This chapter describes the steps you must perform to set up and use SQL*Net for Rdb7 on OpenVMS systems.

3.1 Installation and Setup OverviewYou install SQL*Net for Rdb7 as a part of the Oracle SQL/Services installation pro-cedure. During installation, the procedure also installs the Oracle SQL/Services ver-sion of the SQL*Net network transport, so that OCI clients can communicate with SQL*Net for Rdb7. After installation, you use the Oracle SQL/Services Manager graphical user interface (GUI) to set up the dispatcher and services with the OCI message protocol necessary for SQL*Net for Rdb7 communications.

Table 3–1 lists the tasks you must perform to install and set up your server and cli-ent systems to use SQL*Net for Rdb7. Figure 3–1 illustrates steps 1 through 5.

Table 3–1 Setting Up to Use SQL*Net for Rdb7

Step Task Reference

1 Install the Oracle SQL/Services 7.1 kit. The installation procedure automatically installs the SQL*Net for Rdb7 option on your Rdb7 server system.

Section 3.2

2 Prepare your Rdb7 database for use with SQL*Net for Rdb7. Section 3.3

3 Configure Oracle SQL/Services to work with SQL*Net for Rdb7 and your network.

Section 3.4

4 Configure your SQL*Net network to recognize your Oracle SQL/Services service.

Section 3.5

5 Start the Oracle SQL/Services OCI dispatcher (and the Oracle SQL/Services OCI service if you did not start it in step 3).

Section 3.6

Installation and Setup Overview


Figure 3–1 SQL*Net for Rdb7 Installation, Database Preparation, and ConfigurationAction Result

NU−3665A−RA

Install

Prepare

Configure

Configure

Distribute

SQL*Net for Rdb

SQL*Net Network

rdb_natconn_prepare.sql orrdb_natconn_prepare71.sql

OCI Dispatcher

OCI Service

Service Name Definition

Listener Definition

Rdb7 Server System

SQL/Services

Rdb7 Databaseusing Rdb7 SQLRdb7 environment Oracle7

Oracle Data Dictionary

Oracle SQL/ServicesManager GUI orSQLSRV_MANAGEcommand line

*.ORA Client Files

*.ORA Server Files

SQL*Net Network

Manager GUI

SQL*Net definition files

StartOracle SQL/Services

and OCI service

Oracle Network

Oracle SQL/ServicesManager GUI or

*.ORA Client Files

*.ORA Server Files

OCI Client System

SQL*Net for Rdb

OCI Dispatcher

OCI Service

Rdb7 Server System

SQL/Services

Rdb7 Database

Oracle7Oracle Data Dictionary

SQL*Net Network

*.ORA Client Files

*.ORA Server Files

1.

2.

3.

4a.

4b.

5.

SQL*Net definition files

OCI dispatcherand OCI service

OCI dispatcher SQLSRV_MANAGEcommand line

SQL*Net for Rdb VMSINSTAL kit

Step 1: Software Installation


In Figure 3–1:

■ Step 1 installs SQL*Net for Rdb7 and sets up a sample database.

■ Step 2 prepares the Oracle Rdb database environment to be served by SQL*Net for Rdb7.

■ Step 3 sets up the Oracle SQL/Services OCI service and OCI dispatcher for the Oracle Rdb database.

■ Step 4a configures your SQL*Net network to recognize the OCI service.

■ Step 4b generates a listener entry for the OCI dispatcher.

■ Step 5 starts the OCI service and OCI dispatcher.

See Appendix B for an example of a common installation script for the Rdb7 and Oracle7 servers.

3.2 Step 1: Software InstallationYou must install Oracle SQL/Services before you can set up SQL*Net for Rdb7 (described in Section 3.3). SQL*Net for Rdb7 is installed automatically as a part of the Oracle SQL/Services installation procedure. You do not need to perform any special steps to install SQL*Net for Rdb7 software.

Note: The installation procedure cannot complete the configura-tion of the SQL*Net network for the sample database because of the possible variations in your existing SQL*Net configuration. You must perform steps 4a and 4b to complete the SQL*Net con-figuration.

Note: Sections 3.2.1 through 3.2.5 provide an abbreviated description of the Oracle SQL/Services (Release 7.1) installation procedure.

Complete installation instructions are provided in the Oracle SQL/Services Installation Guide (Release 7.0). A copy of this manual is included in your SQL*Net for Rdb7 software kit. Also, see the Oracle Rdb: SQL*Net for Rdb Release Notes for additional installation information.



3.2.1 Standard and Multiversion KitsOracle SQL/Services provides a multiversion kit and a standard kit:

■ The standard kit installs the single-version variant of Oracle SQL/Services.

If you install the single-version kit, the installation procedure replaces any pre-vious standard version of Oracle SQL/Services with Release 7.1.

■ The multiversion kit enables you to run multiple versions of Oracle SQL/Services on a single system.

Refer to the Oracle SQL/Services Installation Guide before you perform a multi-version installation. The documentation provides special information to help you determine correct values for all items in a multiversion installation.

Both the standard and multiversion kits are available for the OpenVMS VAX and OpenVMS Alpha operating systems. Table 3–2 shows the save-set names of the Oracle SQL/Services kits that you can install.

3.2.2 Using VMSINSTALYou must install Oracle SQL/Services on each server node or cluster that will be accessed by clients using OCI and PL/SQL in the client applications. SQL*Net for Rdb7 is installed automatically as a part of the Oracle SQL/Services (Release 7.1) software kit.

The following example displays the command to invoke VMSINSTAL to install the multiversion variant of Oracle SQL/Services for Rdb7 for OpenVMS Alpha from the software CD-ROM. Example 3–1 includes the command line containing the multiversion kit name (obtained from Table 3–2), the device name (DKA400), the OPTIONS parameter, and includes the system response to the command.

Example 3–1 Invoking VMSINSTAL

$ @SYS$UPDATE:vmsinstal sqlsrvam071 DKA400: OPTIONS N OpenVMS ALPHA Software Product Installation Procedure V7.1It is 24-SEP-1996 at 14:42.Enter a question mark (?) at any time for help.

Table 3–2 Oracle SQL/Services Standard and Multiversion Save Sets

Operating System Standard (Single-Version) Kit Multiversion Kit

OpenVMS Alpha SQLSRVBSA071 SQLSRVBMVA071

OpenVMS VAX SQLSRVBS071 SQLSRVBMV071



Refer to the Oracle SQL/Services Installation Guide for complete installation instruc-tions and more examples, including a standard kit installation example.

3.2.3 Changes to the Installation ProcedureThis section describes changes to the Oracle SQL/Services installation procedure for the SQL*Net for Rdb7 product. Note the following differences between the Ora-cle SQL/Services Release 7.0 and 7.1 installation procedures:

■ New save set

The Oracle SQL/Services Release 7.1 kit includes Save Set B which contains the SQL*Net for Rdb7 files and images. (The Oracle SQL/Services Release 7.0 kit consisted of a single save set.)

■ Existing configuration files are now preserved

Prior to Release 7.1, when you installed a new Oracle SQL/Services kit, the existing Oracle SQL/Services configuration files were deleted. For example, if you installed the standard version Oracle SQL/Services kit, the new kit deleted the existing standard configuration file, and then created a default Oracle SQL/Services configuration in its place. Similarly, the installation of a multiver-sion kit would delete the existing multiversion configuration file. However, if you have an existing Oracle SQL/Services Release 7.0 multiversion configura-tion and you install a Release 7.1 kit, the existing Release 7.0 configuration files are not deleted.

Beginning with Release 7.1, a new Oracle SQL/Services installation no longer deletes existing configurations. If you install the standard kit or multiversion kit and a configuration file already exists for the installation, the existing config-uration file is preserved. In this case, installing a new kit does not result in the creation of a default configuration. Thus, the following questions are not dis-played as a part of the installation procedure:

What version of SQL should the GENERIC service specify?Do you want to use the default Oracle SQL/Services network ports [NO]

■ New question asks for the root directory of your Oracle installation

The Oracle SQL/Services Release 7.1 installation procedure asks for the root directory of your Oracle installation, if any. Provide the location by taking the actions described in the following table:



The Oracle SQL/Services dispatcher looks for the SQL*Net configuration informa-tion and message files in the appropriate directory (as described in the previous table). See Section 3.5.3 for information about distributing the configuration files after installation.

■ SQL*Net question is removed

For Oracle SQL/Services Release 7.0, the installation procedure asked if you wanted to install the Oracle SQL/Services version of SQL*Net. Using SQL*Net was optional because it was one of several networking options available to Oracle SQL/Services sites running 7.0. However, sites running Release 7.1 require SQL*Net for OCI clients to communicate with SQL*Net for Rdb7. There-fore, the installation no longer asks this question and installs the Oracle SQL/Services version of SQL*Net automatically.

■ The Oracle SQL/Services IVP is not run

The Oracle SQL/Services Installation Verification Procedure (IVP) is not run as part of an Oracle SQL/Services Release 7.1 installation.

■ Default configuration includes the SQL*Net for Rdb7 components

If you have an existing Oracle Server installation on this system

Enter the root directory of your Oracle installation.

If you have not installed Oracle Server on this system

Enter a carriage return.

The Oracle SQL/Services installation procedure creates the following directory:

■ For a standard kit installation, the directory is:

SYS$COMMON:[SQLSRV.SQLNET.NETWORK.ADMIN]

■ For a multiversion kit installation, the directory is:

SYS$COMMON:[SQLSRV71.SQLNET.NETWORK.ADMIN]

Note: Installing the Oracle SQL/Services version of SQL*Net does not affect your existing SQL*Net installation, if any. Only Oracle SQL/Services clients and servers use the SQL*Net image provided by this installation.

Step 2: Preparing Your Database


The Oracle SQL/Services installation procedure creates the following SQL*Net for Rdb7 samples:

– Database

– OCI service

– OCI dispatcher

3.2.4 Installation Verification There is no installation verification procedure for SQL*Net for Rdb7.

3.2.5 Problem ReportingIf an error occurs while you are using SQL*Net for Rdb7 and you believe that the error is caused by a problem with an Oracle product, contact your Oracle support representative for technical assistance.

When you experience a reproducible problem, it is important to provide as much detailed information as possible. Use the SQL ALTER SESSION LOG FULL state-ment to collect detailed information about the current SQL*Net for Rdb7 session. By providing the logged information with your problem report, you supply impor-tant data that can help solve the problem. See Chapter 4 for more information about using the SQL ALTER SESSION LOG statement.

3.3 Step 2: Preparing Your DatabaseAlthough you need to install SQL*Net for Rdb7 software only once on each server system, you must prepare each Rdb7 database environment by defining the Oracle7 functions and the emulated Oracle7 data dictionary that you want to serve with SQL*Net for Rdb7. The following subsections provide more information about defining these environments.

3.3.1 Defining Oracle7 Functions and the Emulated Oracle7 Data DictionaryIf you have installed the Oracle7 functions previously, you can skip to Step 3 docu-mented in Section 3.4. There is no need to reinstall the functions.

To install the Oracle7 functions, perform the following steps:

1. Enter the following command at the DCL prompt as follows:

■ For an Rdb7 single-version installation, enter:

$ @SYS$LIBRARY:RDB$SETVER STANDARD



Using the keyword STANDARD (or just the letter S) specifies that you want to run a standard version of SQL in an Rdb7 single-version environ-ment.

■ For a multiversion Rdb7 environment, enter the following command at the DCL prompt and specify the appropriate Rdb7 environment:

$ @SYS$LIBRARY:RDB$SETVER nn

For nn in the command line, substitute the Rdb7 release number (for exam-ple, @SYS$LIBRARY:RDB$SETVER 70).

2. Using the Rdb7 interactive SQL utility, SQL$.EXE or SQL$70.EXE, attach to the database.

3. Install the Oracle7 functions by running the SQL_FUNCTIONS.SQL script. For example:

■ For an Rdb7 single-version installation, enter:

SQL> @SYS$LIBRARY:SQL_FUNCTIONS

■ For an Rdb7 multiversion installation, enter:

SQL> @SYS$LIBRARY:SQL_FUNCTIONS70

4. Using the Rdb7 interactive SQL utility, SQL$.EXE or SQL$70.EXE, run the RDB_NATCONN_PREPARE.SQL interactive script (which was copied with the SQL*Net for Rdb7 software during the installation procedure) to create the emulated Oracle7 data dictionary. For example:

Note: You must specify “70” on the command line. This release of SQL*Net for Rdb7 requires that you use Oracle Rdb Release 7.0 with Ora-cle SQL/Services Release 7.1.

Note: For multiversion installations, you must include the Rdb7 release number when you run the SQL_FUNCTIONS.SQL script. Make sure you include the Rdb7 release number, not the Oracle SQL/Services release number.



■ For an Oracle SQL/Services single-version installation, enter:

SQL> @SYS$LIBRARY:RDB_NATCONN_PREPARE.SQL

■ For an Oracle SQL/Services multiversion installation, enter:

SQL> @SYS$LIBRARY:RDB_NATCONN_PREPARE71.SQL

If you encounter errors running the script, enter a ROLLBACK statement and investigate the cause of the errors. If you do not encounter any errors, enter a COMMIT statement at the SQL prompt:

SQL> COMMIT;

3.3.2 Handling ErrorsYou might encounter errors while running the scripts if you have already installed the script to run Developer/2000 using ODBC. (Developer/2000 instructs you to run the RDBVUBLD.SQL script.) Because, the SQL*Net for Rdb7 script provides a superset of the dictionary tables that the Developer/2000 script provides, you must remove the Developer/2000 tables before you can successfully run the SQL*Net for Rdb7 script.

To remove the tables created by RDBVUBLD.SQL, execute the RDB_NATCONN_DROP_RDBVUBLD.SQL script, then run RDB_NATCONN_PREPARE.SQL script, as follows:

■ For an Oracle SQL/Services single-version installation, enter:

SQL> @SYS$LIBRARY:RDB_NATCONN_DROP_RDBVUBLD.SQLSQL> @SYS$LIBRARY:RDB_NATCONN_PREPARE.SQL

■ For an Oracle SQL/Services multiversion installation, enter:

SQL> @SYS$LIBRARY:RDB_NATCONN_DROP_RDBVUBLD71.SQLSQL> @SYS$LIBRARY:RDB_NATCONN_PREPARE71.SQL

Note: For multiversion installations, you must include the Oracle SQL/Services release number when you run the RDB_NATCONN_PRE-PARE.SQL script. Make sure you include the Oracle SQL/Services release number, not the Rdb7 release number.

Step 3: Configuring Oracle SQL/Services


If you are having trouble running the RDB_NATCONN_PREPARE.SQL script, there may be a name conflict. You need to evaluate why you have the conflicting table names and change the names by moving the data and deleting the table.

If you have a name conflict with the NUMBER domain, you do not have to change it. SQL*Net for Rdb7 provides the NUMBER AS DOUBLE PRECISION domain to make using Oracle DDL scripts easier. However, if your definition differs, do not use that definition.

3.4 Step 3: Configuring Oracle SQL/ServicesSQL*Net for Rdb7 databases are served through Oracle SQL/Services. This section describes how to create an Oracle SQL/Services dispatcher, and an Oracle SQL/Services service for each SQL*Net for Rdb7 database.

The items to be configured are:

■ An OCI service describes how OCI clients access the database.

■ An OCI dispatcher uses either a TCP/IP or DECnet network protocol for com-munications with OCI clients. SQL*Net for Rdb7 requires that the network transport use the OCI message protocol.

You can use either of the following methods to create and manage an OCI service, dispatcher, and other configuration data:

■ The Oracle SQL/Services Manager graphical user interface (GUI) utility.

This GUI is installed with Oracle DBAPack for Rdb7.

The GUI is available on all Microsoft platforms to help you manage an Oracle SQL/Services server from any Windows client.

■ The SQLSRV_MANAGE client utility.

This utility provides a command line interface to help you manage an Oracle SQL/Services server from an OpenVMS or Digital UNIX system.

Note: For multiversion installations, you must include the Oracle SQL/Services release number when you run the RDB_NATCONN_DROP_RDBVUBLD.SQL script and the RDB_NATCONN_PREPARE.SQL script. Make sure you include the Oracle SQL/Services release number, not the Rdb7 release number.



3.4.1 Defining the OCI ServiceThe following instructions take you step-by-step through the process of configur-ing the OCI service using the Oracle SQL/Services Manager GUI.

1. In the Oracle SQL/Services Manager, connect to your server system and click on Services in the navigation tree on the left side of the window. (Note that the image name is SQSMG.EXE.) Then, click on either the Create Service toolbar button, or click on the Actions pull-down menu and select the Create Service option.

If the following instructions have omitted some attributes that you see on the tab pages within the Create Service window, you should supply values accord-ing to the needs of your environment.

2. On the General tab page:

■ Specify a service name.

This is the name that you use for the OCI service when defining your SQL*Net for Rdb7 database in the SQL*Net configuration. This is described in Section 3.5.2.

■ Select OCI protocol to override the default SQLSRV protocol.

■ Specify the Service Owner as the OpenVMS user name of the owner of your SQL*Net for Rdb7 database.

■ Accept the default STANDARD for SQL_Version if your Rdb7 installation is running the standard version. For multiversion Rdb7 installations, spec-ify the Rdb7 release number for SQL_Version.

3. On the Reuse Scope/Executors tab page:

■ Accept the default SESSION for Reuse Scope.

■ Enter an attach statement for your SQL*Net for Rdb7 database.

■ Do not specify a Default Multischema Schema. (SQL*Net for Rdb7 does not support Rdb7 multischema databases at this time.)

Note: As you perform the following steps, it might be helpful to refer to the Oracle SQL/Services Server Configuration Guide for more information about this GUI. Also, to see a working example of how to accomplish these tasks, see the sample setup procedure described in Section 3.13.



4. On the Authorization tab page:

■ Accept the default Connect Username for Database Access Authorization.

5. When you are done configuring the OCI service:

■ Click on OK. When the input box asks if you want to start the service, select YES or NO. However, the service will not be available until you com-plete all steps.

3.4.2 Defining the OCI DispatcherTo enable an Oracle SQL/Services OCI service to which Oracle clients can connect, you need an OCI dispatcher that listens for messages using a SQL*Net transport and the OCI message protocol. The OCI_DISP dispatcher created during the Oracle SQL/Services installation (described in Section 3.13) is such a dispatcher and can serve all your OCI dispatcher needs for SQL*Net for Rdb7. However, you can cre-ate your own OCI dispatcher to satisfy any unusual requirements in your environ-ment.

To create your own dispatcher, click on either the Create Dispatcher toolbar button, or click on the Actions pull-down menu and select Create Dispatcher. Proceed through the steps in the following list.

If the following instructions have omitted some attributes that you see on the tab pages within the Create Dispatcher window, you should supply values according to the needs of your environment.


■ Enter a unique dispatcher name.

■ Override the default and set AutoStart to Off.

Note: The following attribute values are not supported for SQL*Net for Rdb7 databases:

■ TRANSACTION for Reuse Scope

■ Service Owner for Database Access Authorization

■ Grant Use

All users have access to OCI services. However, the database still is protected because access to the database must be through the connect (client) user name.

Step 4: Configuring SQL*Net


2. On the Network Ports tab page:

■ Select SQL*Net for the Transport.

■ Specify your SQL*Net listener name for Name/ID field.

This is the name you give to your SQL*Net listener object described in Section 3.5.2.

The Oracle listener uses the name LISTENER. If the Oracle7 listener is run-ning on your system, you must choose a different listener name. Each SQL*Net listener object must have a unique name and must receive connec-tion requests on different network ports or object names.

The OCI_DISP dispatcher created by the SQL*Net for Rdb7 installation uses OCI_LISTENER for its listener name. Therefore, you must choose a different listener name (and different network ports or object names) if your dispatcher is to run at the same time as the supplied OCI_DISP dis-patcher.

■ Select OCI as the Protocol.

■ Click on the Add button.

■ When you are done configuring the OCI dispatcher, click on OK. When the input box asks if you want to start the dispatcher, click on NO. The dis-patcher cannot start correctly until you have completed all steps.

3.5 Step 4: Configuring SQL*NetAn Oracle SQL/Services dispatcher that uses SQL*Net as its network transport requires SQL*Net definitions that you can create with the Oracle Network Manager.

The Oracle Network Manager is a graphical user interface (GUI) tool that runs on Microsoft platforms. You use this GUI to create and maintain SQL*Net configura-tions. Refer to the Oracle Network Manager documentation for complete details on how to create SQL*Net configuration objects.

The following sections step you through the process of configuring SQL*Net for Rdb7 using the Oracle Network Manager GUI.

3.5.1 Preserving Existing SQL*Net DefinitionsYou must take care to preserve your existing SQL*Net definitions (if any) when you create the new SQL*Net definitions required by SQL*Net for Rdb7.



Using the Oracle Network Manager, open your network definition (stored in either a file with the .NET file extension or in a database) prior to configuring SQL*Net. In this way, your updated network definition and the client and server definition files generated from it contain both your existing definitions and the newly created defi-nitions. If you do not have existing SQL*Net definitions, you can create a new net-work definition with the Oracle Network Manager.

The following network definition files provide a useful starting point and informa-tion if you are defining a SQL*Net configuration from scratch:

■ SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRV]OCIDNET.NET (standard installation) or SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRVnn]OCIDNET.NET (where nn represents the release number for a multiversion installation)

This file defines a SQL*Net configuration for the sample database using the DECnet network transport.

■ SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRV]OCITCPIP.NET (standard installation orSYS$COMMON:[SYSHLP.EXAMPLES.SQLSRVnn]OCITCPIP.NET (where nn represents the release number for a multiversion installation)

This file defines a SQL*Net configuration for the sample database using the TCP/IP transport.

The installation procedure (described in Section 3.2) automatically installs these sample network definition files for use with the Oracle Network Manager. How-ever, you cannot use these network definition files to modify an existing SQL*Net configuration. Section 3.13 provides more information about these network defini-tions.



3.5.2 Step 4a: Creating the SQL*Net Configuration DefinitionsBefore defining a SQL*Net configuration, you should collect information about the network. For example, you should gather the underlying transport protocol type (TCP/IP or DECnet), port numbers or object numbers, node or host names, Oracle SQL/Services service names, and SQL*Net listener names used by Oracle SQL/Services dispatchers.

When you define a SQL*Net configuration, the following SQL*Net definitions are required by Oracle SQL/Services and SQL*Net for Rdb7:

■ Community

■ Node

■ Listener

■ Rdb7 database

To begin defining the SQL*Net configuration, start the Oracle Network Manager and click on the File pull-down menu and either open an existing network defini-tion or create a new network definition.

Once you are in the Oracle Network Manager, follow the instructions in each of the following subheadings to create the SQL*Net definitions. If you create a new net-work definition, Oracle Network Manager provides an option that walks you through your network definitions.

If the discussions in the following subheadings omit information that you see on a tab page, you should supply values according to the needs of your environment.

Note: If both Oracle7 and Oracle SQL/Services co-exist on a sys-tem, Oracle Corporation recommends that you add SQL*Net objects used by Oracle SQL/Services (such as listeners for Oracle SQL/Services dispatchers and Rdb7 databases) to an exist-ing SQL*Net configuration for Oracle7 through the Oracle Net-work Manager.

The OCIDNET.NET and OCITCPIP.NET files are intended only for reference purposes and for systems with Oracle SQL/Services. The Oracle Network Manager performs a validation check to ensure that each network resource is used by only one listener.



CommunityOracle SQL/Services and SQL*Net for Rdb7 have no special requirements concern-ing community definitions. Nonetheless, you must define at least one community for each transport protocol that you intend to use. Thus, if you plan to use both TCP/IP and DECnet, you must define, at least, two communities: one for TCP/IP and one for DECnet.

To create the community, either click on the Community button in the toolbar or click on the Create pull-down menu and select the Community option to bring up the Community property sheet. Then, on the General tab page:

■ Specify a meaningful name for the community.

■ Select a domain or accept the default world domain.

■ Select either TCP/IP or DECnet protocol.

NodeYou must provide a SQL*Net node definition for the Rdb7 server system where SQL*Net for Rdb7 runs. Either click on the Node button in the toolbar or click on the Create pull-down menu and select the Node option. Then, perform the follow-ing steps:


■ Enter the node name.

■ Specify the same domain that you used for the community.

■ Specify the Type as VMS Server.

2. On the Community tab page:

■ Click on the community that you defined.

■ Click on the Add button.

ListenerYou must define each SQL*Net listener that you referenced in your Oracle SQL/Services configuration in Step 3 (see Section 3.4.2). Either click on the Listener button in the toolbar or click on the Create pull-down menu and select the Listener option. Then, perform the following steps:




■ Specify Name using the same listener name that you used on the Network Ports tab when configuring your OCI dispatcher in Step 3 (see Section 3.4.2).

The Oracle listener uses the name LISTENER. If the Oracle7 listener is run-ning on your system, you must choose a different listener name. Each SQL*Net listener object must have a unique name and must receive connec-tion requests on different network ports or object names.

The OCI_DISP dispatcher created by the SQL*Net for Rdb7 installation uses OCI_LISTENER for its listener name. Therefore, you must choose a different listener name (and different network ports or object names) if your dispatcher is to run at the same time as the supplied OCI_DISP dis-patcher.

While typically the listener name is LISTENER, the important thing is that your Oracle SQL/Services dispatcher listener name and the SQL*Net lis-tener name match.

■ Select the Rdb7 server node where you want to run SQL*Net for Rdb7.

2. On the Address tab page:

■ Click on the Create button to bring up the Service Address property sheet.

■ Select the Community and Host that you defined.

■ For DECnet network protocols, fill in the Object. For TCP/IP network pro-tocols, fill in the Port.

If you chose to have the Oracle Network Manager walk you through the steps to create a new network definition, you will see an Attention box alerting you about the omission of a database. Click on OK because you can ignore this message. Whether or not you chose the walk through, you create the Rdb7 database defini-tion as described in the next subheading.

Rdb7 DatabaseYou need to define the Rdb7 database that is to be served by SQL*Net for Rdb7. Either click on the Rdb button in the toolbar or click on the Create pull-down menu and select the Rdb Database option. Then, perform the following steps:

Note: Do not use the Databases, Gateways, or SNMP tabs.




■ Specify the Name value.

This is the name OCI client applications use to connect to the SQL*Net for Rdb7 database.

■ Specify the Service value.

This is the name you specified for your OCI service in Step 3 (see Section 3.4.1).

■ Specify the Node on which SQL*Net for Rdb7 will run.

2. On the Listeners tab page:

■ Click on the Listener that you defined for this node and database.

■ Click on Add.

After Creating the DefinitionsOnce you have created the preceding definitions, either click on the Generate but-ton on the toolbar or click on the File pull-down menu and select the Generate option to generate the client and server definition files. If the Oracle Network Man-ager detects any problems, you must correct them before you go on to the next step.

When generating the client and server definition files, specify an output drive and directory that is convenient for network file transfer to both the client and server systems. The Oracle Network Manager creates a number of subdirectories under the directory that you specify, one for each node and each community that you have defined. Each subdirectory name has the domain name (for example, WORLD) appended to the end of the node or community name, separated by an underscore. If the result exceeds 8 characters, the subdirectory name is truncated.

Note: If you are running a release of the Oracle Network Manager prior to Release 3.1.0.5.1, you will not find an Rdb7 database object. You can cre-ate an Oracle database object instead and specify the OCI service name in the service identifier (SID) parameter.

When you create an Oracle database object for an OpenVMS server, you must enter a Program in the OpenVMS Specific Details box on the General tab page. However, the Oracle SQL/Services dispatcher ignores this field. Because you must enter a value, Oracle suggests you enter the text ”THIS_FIELD_IS_IGNORED” in the Program field.



3.5.3 Step 4b: Distributing SQL*Net Configuration FilesThe following subheadings describe how to distribute SQL*Net configuration files to the server and client systems.

Server systemThe procedure for distributing SQL*Net configuration files varies depending on whether or not Oracle7 is installed on the server system.

The following table describes the procedure for the server system:

1 If you need to change the location of the Oracle directory tree, you can edit this file with the new location. Note that the SQLSRV_SQLNET.DAT file is generated during the Oracle SQL/Services installation. The contents of the file are generated based on how you answer the question “Enter root directory of your Oracle installation (if any).” You must stop and restart your dispatcher for this change to take effect.

If . . . Then . . .

Oracle7 is not installed on the server system (that is, you did not specify an Oracle root during Oracle SQL/Services installation)

Copy the entire subdirectory of files generated by the Oracle Network Manager for the server system to one of the following directories:

■ If you installed the Oracle SQL/Services 7.1 standard kit, copy the files to the SYS$COMMON:[SQLSRV.SQLNET.NETWORK.ADMIN] directory

■ If you installed the Oracle SQL/Services 7.1 multiversion kit, copy the files to the SYS$COMMON:[SQLSRV71.SQLNET.NETWORK.ADMIN] subdirectory

Oracle7 is installed on the server sys-tem (that is, you specified an Ora-cle root during the Oracle SQL/Ser-vices installation)

Copy the entire subdirectory of the files generated by the Oracle Network Manager for the server system to the [.NETWORK.ADMIN] subdirectory under your Oracle directory tree. You can determine the location of the Oracle directory tree as follows:

■ If you installed the Oracle SQL/Services standard kit, the location is stored in the SYS$MANAGER:SQLSRV_SQLNET.DAT1 file

■ If you installed the Oracle SQL/Services multiversion kit, the location is stored in the SYS$MANAGER:SQLSRV_SQLNET71.DAT1 file

In the Oracle directory tree, the SQL*Net configuration files are stored in the [.NETWORK.ADMIN] subdirectory.

If you have an existing configuration that is not represented by the network definition you have generated, and you want to maintain the configuration data in the directory, you can do so by adding the new configuration data into the existing configuration data file.



The configuration files generated for the server system include the following:

– LISTENER.ORA

– SQLNET.ORA

– TNSNAMES.ORA

– TNSNAV.ORA

Client systemFor Microsoft Windows platforms, you should have installed Oracle SQL*Net on your client systems:

■ Locate the SQL*Net configuration files directory. Typically, you specify the loca-tion as follows:

device-letter:\installation-root\NETWORK\ADMIN

For example, C:\ORAWIN\NETWORK\ADMIN

■ If you do not have an existing configuration, or if your existing configuration is represented by the network definition you have generated, copy the entire sub-directory of the files generated by the Oracle Network Manager for the client systems to the configuration files directory. The generated files include the fol-lowing:

– SQLNET.ORA

– TNSNAMES.ORA

– TNSNAV.ORA

The Oracle Network Manager does not generate subdirectories for any client systems that you did not include in your network definition. However, you can use the client files from a subdirectory for any of the communities to which the client belongs.

Optionally, you can use the Oracle NetFetch utility to simplify this process. You can find the Oracle NetFetch utility in the same program group as the Oracle Network Manager. The Oracle NetFetch utility requires that either:

– The directory where you generated your network definition be network shared to the client.

In this case, run the Oracle NetFetch utility on the client system, enter the network definition from the network-shared drive, and export the configu-ration files to the configuration directory.



– The directory where the configuration files must be copied on the client must be shared on the network to the system where you generated the net-work definition.

In this case, run the Oracle NetFetch utility on the system where you gener-ated your network definition, enter the network definition from the local directory, and export the configuration files to the configuration directory on the shared network drive.

■ If you have an existing configuration that is not represented by the network def-inition you have generated and you want to maintain the configuration data in the directory, add the new configuration data into the existing configuration data file.

For OpenVMS client platforms, you should have installed Oracle SQL*Net on your client system. Use the following steps to install SQL*Net on client systems:

■ Locate the SQL*Net configuration files directory.

Typically, you specify the location as follows:

ORA_ROOT:[NETWORK.ADMIN]

■ If you do not have an existing configuration or if your existing configuration is represented by the network definition you have generated, copy the entire directory of the files generated by the Oracle Network Manager for the client systems to the configuration files directory.

■ If you have an existing configuration, you can optionally maintain the configu-ration data in the directory.

Correcting Server System SQL*Net Configuration FilesThe version of Oracle Network Manager supplied with SQL*Net for Rdb7 gener-ates one line in the LISTENER.ORA file that Oracle SQL/Services does not support.

To comment out this unsupported line, set default to the directory containing the LISTENER.ORA file and enter the following command:

$ EDIT/TPU/NOSECTION/COMMAND=SYS$LIBRARY:RDB_NATCONN_ALTER_LISTENER.TPU/NODISPLAY LISTENER.ORA

Also, the Oracle Network Manager might generate a DESCRIPTION_LIST con-struct that Oracle SQL/Services does not support. Use the following DCL com-mand to determine if the Oracle Network Manager has generated this construct:

$ SEARCH *.ORA DESCRIPTION_LIST

Step 5: Starting the OCI Dispatcher and OCI Service


If this construct is present, you cannot connect to the SQL*Net for Rdb7 service. In this case, modify your network definition to avoid complex definitions involving search lists of alternate nodes and services and regenerate your configuration files.

In a cluster environment, all systems might share the same directory for SQL*Net configuration files. If the same set of network addresses is used by all systems, (such as when all systems use TCP/IP port number 1526 for an Oracle SQL/Services dispatcher), you can use the same listener name definition for all sys-tems by removing the Host or Node line from the listener name definition.

The following example shows a valid listener definition:

Example 3–2 Listener Definition

LISTENER_TCP = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp_com.world) (PROTOCOL = TCP) (Port = 1526) ) )

3.6 Step 5: Starting the OCI Dispatcher and OCI ServiceWhether you are using the supplied OCI_DISP dispatcher or have defined your own dispatcher, the OCI dispatcher cannot be started until you have completed Step 4. Therefore, you must start your OCI dispatcher before you can connect to your database with SQL*Net for Rdb7.

Note: A set of sample files that you can examine and compare against your own files is included in the following directory:

SYS$COMMON:[SQLSRVnn.SQLNET.NETWORK.ADMIN]*.ORA_SAMPLE

The sample files, generated by Oracle Network Manager, should be used only as an example of how your files should look. Node names and Port settings in the sample files will not match your files.

nn is the version number for Oracle SQL/Services.

Connecting Using SQL*Net for Rdb7


To start the OCI dispatcher, run the Oracle SQL/Services Manager GUI and con-nect to the server system. Perform the following steps:

1. Click on the plus signs (+) to the left of the server and to the left of Dispatchers, if necessary, to expose the tree of dispatchers.

2. Click on your OCI dispatcher, then either click on the Start button on the tool-bar or click on the Actions pull-down menu, and select the Start option.

3. Optionally, you can double click on your OCI dispatcher to confirm that it is now running.

4. Because your OCI dispatcher is now ready to run, you can enable AutoStart for your OCI dispatcher by clicking on On in the AutoStart box.

If you did not start your OCI service in Step 3 (see Section 3.4), you can start it now:

1. If necessary, click on the plus signs (+) to the left of Services to expand the tree of services.

2. Double click on your OCI service to obtain a status display. If the OCI service is not running, either click on the Start button on the toolbar or click on the Actions pull-down menu and select the Start option.

You can enable AutoStart for your OCI service in the same manner as your OCI dispatcher.

3.7 Connecting Using SQL*Net for Rdb7Once your installation is complete and your database has been prepared, you can access the database in exactly the same way you would access an Oracle7 database. On a PC, you can invoke the PLUS32 or PLUS33 application by entering a com-mand similar to the following at the DOS prompt:

Example 3–3 Invoking the PLUS32 Application

PLUS32 your-username/your-password@OCI_SAMPLE

The PLUS32 application is a PC application that you can install as part of Devel-oper/2000. The user name and password must be a valid OpenVMS user name and password on your server system.

In Example 3–3, the variable OCI_SAMPLE defines the TNS name that accesses the OCI service on your server. You can set up the TNSNAMES.ORA file following Steps 3 and 4 in Sections 3.4 and 3.5, respectively.

Defining Character Sets


3.8 Defining Character SetsSQL*Net for Rdb7 supports several character sets, including some multibyte charac-ter sets.

The character sets shown in Table 3–3 are supported on your server system. To determine which character sets are supported on your client system, refer to your client-specific documentation.

Table 3–3 lists the character sets supported by SQL*Net for Rdb7.

Table 3–3 Supported Character Sets

Languages Supported Character Set Type

Brazilian PortugueseCanadian FrenchCzechoslovakianDanishDutchFinnishFrenchGermanGreekHungarianIcelandicItalianMexican SpanishNorwegianPolishPortugueseRussianSlovakSpanishSwedishTurkish

.WE8DEC Default

DEC-Hanzi ZHS16CGB2312-80 Multibyte

DEC-Kanji JA16VMS Multibyte

DEC-Korean KO16KSC5601 Multibyte

DEC-SICGCC ZHT32CNS11643-86 Multibyte

Japanese Shift-JIS JA16SJIS Multibyte

Latin/Arabic AR8ISO8859P6 Multibyte



By default, SQL*Net for Rdb7 supports the US7ASCII character set (defined as US7ASCII using the NLS_LANG logical name). For example, to specify DEC_MCS, which provides all characters for the Western European languages, define the NLS_LANG logical name to be “.WE8DEC”.

Example 3–4 specifies only the Western European character set.

Example 3–4 Specifying the Western European Character Set

define NLS_LANG “.WE8DEC”

3.8.1 Defining Character Sets on Server SystemsOn your server system, use the NLS_LANG logical name to define a character set other than the default US7ASCII character set. The format of the NLS_LANG logi-cal name is as follows:

define NLS_LANG “[<language>][_<territory>].<character_set>”

Example 3–5 specifies the French language and territory and the Western European character set.

Example 3–5 Specifying the French Language and Territory

define NLS_LANG “FRENCH_FRANCE.WE8DEC”

Latin/Cyrillic CL8ISO8859P5 Multibyte

Latin/Greek EL8ISO8859P7 Multibyte

Latin/Hebrew IW8ISO8859P8 Multibyte

Traditional Chinese ZHT16BIG5 Multibyte

Note: Specifying a language or territory on the server system has no effect.

Table 3–3 Supported Character Sets

Languages Supported Character Set Type



Alternatively, you can specify the language and territory using the Oracle SQL ALTER SESSION statement. For example:

ALTER SESSION SET NLS_LANGUAGE=<language> NLS_TERRITORY=<territory>

3.8.2 Defining Character Sets on Client SystemsBecause of the wide variety of client systems and operating system platforms avail-able, this section cannot describe all the possibilities for defining character sets on client systems. For information about specifying character sets on your client sys-tem, see your platform-specific documentation.

3.8.3 Rules and RecommendationsNote the following rules and recommendations when you specify a character set:

■ The character set is specific to the session.

■ You can specify only one character set for a given session.

All character data that is sent to the database or requested from the database is assumed to be in the defined character set.

■ If you use a character set other than US7ASCII, specify the desired character set on both the client and server systems.

If the character set on your client system is not compatible with the character set on the server, SQL*Net will attempt to translate the character set. However, the results of the translation may not be as you expect.

3.8.4 Error MessagesThe language you specify determines which language is used for Oracle error mes-sages and boilerplate text, including month and day names in dates. Note the fol-lowing for Rdb7 and Oracle7 error messages:

■ By default, Rdb7 error messages are supplied in English, unless you use a spe-cially translated error message file and define the file to the Rdb7 database.

The language specified when you attach to the database is used for the dura-tion of the session. You cannot change to another error message file during the session.

■ Oracle7 error message files are provided in all of the supported languages listed in Table 3–3.

Error Messages Returned to OCI Client Applications


Sorting, collating, and comparisons are done according to the Rdb7 semantics. Refer to the Oracle Rdb SQL Reference Manual for more information.

3.9 SQL*Net Errors and Trace OutputAt run time, SQL*Net writes error messages to the SQLNET.LOG file and writes trace messages to the SQLNET.TRC file. The default directory is the login directory of the process owner on OpenVMS, or the current directory of the executable image on Microsoft platforms.

SQLNET.LOG and SQLNET.TRC are the default file names. You can change both the file name and the directory location through the Oracle Network Manager when you create or change a SQL*Net configuration. Do this for any client process by editing the Logging and Tracing tab of a client profile object.

3.10 Error Messages Returned to OCI Client ApplicationsThis section describes error messages that are frequently encountered and returned to OCI client applications.

3.10.1 Logon ErrorERROR: ORA-01017: invalid username/password; logon denied

Cause: You supplied invalid logon information.

Action: Log in again supplying the correct information. For more information, see the Oracle SQL/Services executor log file.

3.10.2 Database Setup ErrorERROR: ORA-00904: invalid column name

Database not setup correctly for SQL*Net for RdbFor details, look in Oracle SQL/Services executor log file<...full file specification of the executor log...>Cause: The database is not set up correctly for SQL*Net for Rdb7.

Action: For more information about this error, see the Oracle SQL/Services executor log file. See Step 2 in Section 3.3 for instructions to help you prepare your database.

3.10.3 SQL Initialization File ErrorERROR: ORA-00900: invalid SQL statement

Error in executing SQL initialization file



For details, look in Oracle SQL/Services executor log file<...full file specification of the executor log...>Cause: Error when executing SQL initialization file.

Action: For details, see the Oracle SQL/Services executor log file.

3.10.4 Errors When Attaching to an Rdb7 Database or When Oracle SQL/Services Database Service Is Not Available

ERROR: ORA-03113: end-of-file on communication channelThis error is returned due to a variety of reasons. See the Oracle SQL/Services executor log file, the monitor log file, and the dispatcher log file for more information. See Section 3.11 for information about inspecting these log files.Cause: The Oracle SQL/Services service did not start, or it is unavailable.

Action: Check to see if the service was started, and if not, start the service. If the service failed, check the Oracle SQL/Services monitor log file for a pointer to the failed executor log. Then, check the executor log file for more informa-tion.

Cause: The Oracle SQL/Services service name requested is invalid.

Action: Look in the Oracle SQL/Services dispatcher log file to see if there is an error entry about the service being requested. If the Oracle SQL/Services ser-vice name requested is invalid, modify the Oracle SQL/Services service name in the SQL*Net configuration file accordingly.

Cause: The protocol is not set to OCI.

Action: Alter your dispatcher and specify PROTOCOL OCI. Then, stop and restart your Oracle SQL/Services dispatcher so the change can take effect.

3.10.5 Errors When Oracle SQL/Services Server or OCI Dispatcher Is Not AvailableERROR: ORA-12203: TNS:unable to connect to destination

Cause: Oracle SQL/Services and the OCI_DISP dispatcher are not started.

Action: Start Oracle SQL/Services and the OCI_DISP dispatcher.

Cause: The OCI_DISP dispatcher failed.

Action: If the dispatcher failed, look in the Oracle SQL/Services monitor log file for a pointer to the failed dispatcher log. Then, look in the dispatcher log file for the failure reason.



If you see the following entry in the dispatcher log file, the dispatcher cannot find the definition of the listener name used in the Oracle SQL/Services dis-patcher specification:

%SQLSRV-E-TNSFAILURE, Oracle SQL*Net TNS nlpagas() service has failed%SQLSRV-E-ERROR_TEXT, Error text: listener

Check the SQL*Net configuration file LISTENER.ORA. The location of this file varies depending on your installation. The subdirectory containing your SQL*Net configuration file is stored in the following location:

■ For standard installations: SYS$MANAGER:SQLSRV_SQLNET.DAT

■ For multiversion installations: SYS$MANAGER:SQLSRV_SQLNETnn.DAT(where nn represents the Oracle SQL/Services version number)

The [.NETWORK.ADMIN] subdirectory under the location stored in this file contains your LISTENER.ORA file.

3.10.6 Error When SQL*Net Service Name Is Not DefinedERROR: ORA-12154: TNS:could not resolve service name

Cause: The SQL*Net service name might be defined improperly.

Action: Check the SQL*Net configuration files to see if the SQL*Net service name is defined properly. If you are using a file-based SQL*Net configuration, look in the TNSNAMES.ORA file. The location of this file varies depending on where your client system is installed:

■ On an OpenVMS system, the location is ORA_ROOT:[NETWORK.ADMIN]

■ On any Microsoft Windows system, the location is <Oracle installa-tion directory>\network\admin

For example, c:\orawin\network\admin

Note: In some situations, Oracle clients are unable to interpret the error codes returned by Oracle SQL/Services. Therefore, you should always check the Oracle SQL/Services log files for accurate explanations.

Inspecting Oracle SQL/Services Log Files for Errors


3.11 Inspecting Oracle SQL/Services Log Files for ErrorsIf errors occur when you are running the Oracle SQL/Services and SQL*Net for Rdb7 software, the error messages and other event messages are logged in the Ora-cle SQL/Services log files, as follows:

■ Errors that involve networking and connection are logged in the Oracle SQL/Services dispatcher log files located in the SYS$SYSROOT:[SYSMGR] directory.

The following entries are written to the dispatcher log file every time a client successfully disconnects from Oracle SQL/Services (without errors):

------------EVENT BEGIN: EVENT_LOG at Tue Oct 8 1996 18:40:01.055------------%SQLSRV-I-EVENT_LOG, event logged at line 1674 in file COM_TNS.C;1%SQLSRV-E-TNSFAILURE, SQL*Net TNS nsrecv() service has failed%SQLSRV-E-TNSEXTENDED, SQL*Net TNS error codes: primary (12537) secondary (0)------------EVENT END : EVENT_LOG at Tue Oct 8 1996 18:40:01.172-------------

------------EVENT BEGIN: EVENT_LOG at Tue Oct 8 1996 18:40:01.519-------------%SQLSRV-I-EVENT_LOG, event logged at line 284 in file DISP.C;1%SQLSRV-W-EXCEPTION_RAISE, Exception raised: DBS_TNSFAILED%SQLSRV-I-CONNECTNAME, Connect : CONNECT_0000001%SQLSRV-I-CONNECTSTATE, Connect state: 4%SQLSRV-I-USERNAME, User name: dtmtest1%SQLSRV-I-NODENAME, Node : unknown%SQLSRV-I-SERVICENAME, Service : NATCONN_OCI------------EVENT END : EVENT_LOG at Tue Oct 8 1996 18:40:01.527-------------

You can ignore these entries.

■ Errors incurred during the following situations are logged in the Oracle SQL/Services executor log files:

– Authentication of the client-side user name and passwords

– Oracle SQL/Services OCI service and SQL*Net for Rdb7 problems

You can find the executor log files in the login directory of the service owner of the Oracle SQL/Services service.

For example, if a user signs on with an invalid password for the ALPHASR-VAPI account, the following entries are written to the executor log file:

------------EVENT BEGIN: EVENT_LOG at Tue Oct 8 1996 18:23:38.419-------------%SQLSRV-I-EVENT_LOG, event logged at line 1774 in file DBS_PROT_VMS.C;1Error authenticating user name

Calling Rdb7 Stored Procedures in OCI Programs


SQLSRV-E-AUTH_FAILURE, Authentication/authorization failureSQLSRV-E-ACCDENDAY, Access denied; Attempt to access account ALPHASRVAPI with an invalid password------------EVENT END : EVENT_LOG at Tue Oct 8 1996 18:23:38.438-------------

3.12 Calling Rdb7 Stored Procedures in OCI Programs SQL*Net for Rdb7 supports calling Rdb7 stored procedures using PL/SQL blocks in an OCI program.

Example 3–6 shows a sample of a PL/SQL block in an OCI program.

Example 3–6 PL/SQL Block in an OCI Program

. . .

/* After connecting to Oracle and opening a cursor, parse the statement */ strcpy((char *) sql_statement, "begin sel_salary2(:i, :o, :ii, :oo) ; end;"); fname = 0; lname = 0; if (oparse(&cda, sql_statement, -1, 1, 2)) oci_error(&cda);

/*Bind the host variables. 3 is the data types code for integer. */ if (obndrv(&cda, (ub1 *) ":i", -1, (ub1 *) &empnum, sizeof (int), 3, -1, 0, 0, -1, -1)) oci_error(&cda); if (obndrv(&cda, (ub1 *) ":ii", -1, (ub1 *) &salary, sizeof (int), 3, -1, 0, 0, -1, -1)) oci_error(&cda); if (obndrv(&cda, (ub1 *) ":oo", -1, (ub1 *) &lname, sizeof (int), 3, -1, 0, 0, -1, -1)) oci_error(&cda); if (obndrv(&cda, (ub1 *) ":o", -1, (ub1 *) &fname, sizeof (int), 3, -1, 0, 0, -1, -1)) oci_error(&cda);

/* Query the user for an employee number. */ printf("Enter the employee number: "); gets(empnum_stg); empnum = atoi(empnum_stg);/* printf("The employee number is : %d\n", empnum); */

Note: This new feature does not include support for PL/SQL cli-ents using Oracle Forms applications.

1

2

Sample Setup Procedures


/* Query the user for the employee’s salary. */ printf("Enter the employee’s salary: "); gets(salary_stg); salary = atoi(salary_stg);

/* Execute the PL/SQL block which excutes the called stored procedure. */ if (oexec(&cda)) oci_error(&cda); printf("The employee’s fname is: %d and lname is %d\n", fname, lname);

. . .

END;In Example 3–6, note the following about the callout lines:

This line includes the begin statement that starts the anonymous block used to call an Rdb7 or a PL/SQL stored procedure.

This line includes OCI calls to bind the host variables.

Note that input and output variables are handled in OCI programming differ-ently from the way the variables are handled in the Oracle Rdb7 dynamic SQL interface. Refer to the Programmer’s Guide to the Oracle Call Interface for complete information about how to handle input/output variables.

3.13 Sample Setup ProceduresThis section provides information about the sample for Oracle SQL/Services and SQL*Net for Rdb7 that is provided with the SQL*Net for Rdb7 installation. The fol-lowing shows what is provided with the sample:

The sample files for a standard kit installation are located in the following directory:

Component Description

OCI_SAMPLE_DB.COM Creates a SQL*Net for Rdb7 database

OCI_SAMPLE_CREATE.SQS Adds an Oracle SQL/Services OCI service and OCI dis-patcher

OCI_SAMPLE_START.SQS Starts the OCI service and OCI dispatcher

OCIDNET.NET Oracle Network Manager binary data files for DECnet

OCITCPIP.NET Oracle Network Manager binary data files for TCP/IP

1

2

Sample Setup Procedures


SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRV]

The sample files for a multiversion kit installation are located in the following direc-tory:

SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRV71]

Preparing and Running the SampleThe Oracle SQL/Services installation procedure creates the following components of the sample:

■ A sample SQL*Net for Rdb7 database located in:

SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRV[71]]OCI_SAMPLE.*

This database is created during the installation using the OCI_SAMPLE_DB.COM procedure.

■ A sample SQL*Net for Rdb7 dispatcher (OCI_DISP) and service (OCI_SAM-PLE).

These Oracle SQL/Services objects are created during the installation using the SYS$MANAGER:SQLSRV_CREATE_OCI[71].SQS (Oracle SQL/Services) script.

To run the sample on your system, perform the following steps:

1. Modify the sample SQL*Net configuration provided in the Oracle Network Manager binary data file, as follows:

a. Copy the OCIDNET.NET (for the DECnet network transport) and/or the OCITCPIP.NET (for the TCP/IP network transport) file to a system on which the Oracle Network Manager is available. (The samples are created by Oracle Network Manager Version 3.1.0.5.1.)

b. Edit the SQL*Net configuration stored in the OCIDNET.NET or OCITCPIP.NET file.

Note: If you have an existing SQL*Net configuration, do not use the net-work files provided with the kit. Installing these can delete your existing configuration files and replace them with a default configuration. See Section 3.5.1 if you need more information about preserving existing SQL*Net definitions.

Referencing an Rdb7 Database as a Database Link


c. Modify the system name in the listener object:

* Double click on the OCI_LISTENER_SERVER object

* Click on the Address tab

* Double click on the Address item

* Edit the system name in the Node/Host box

* Click on the OK button

* Click on the pull-down File/Generate menu to generate configuration files

d. Copy the SQL*Net server configuration files to the server system. See Section 3.5.3 for information about the destination directory.

e. Correct the SQL*Net server configuration files on the server system (you perform this step in the same manner as described in Section 3.5.3).

f. Copy the SQL*Net client configuration files to the client system (you per-form this step in the same manner as described in Section 3.5.3).

2. Start the service and the dispatcher by using the GUI-based Oracle SQL/Ser-vices Manager or in command line mode. For example, the following com-mand lines start the service for a multiversion installation:

$ SQLSRV_MANAGE71 :== $SYS$COMMON:[SYSEXE]SQLSRV_MANAGE71.EXE$ SQLSRV_MANAGE71 -i SYS$COMMON:[SYSHLP.EXAMPLES.SQLSRV71]OCI_SAMPLE_START.SQS

3. Run an Oracle client to use the sample database. Use OCI_SAMPLE as the con-nect name.

3.14 Referencing an Rdb7 Database as a Database LinkBeginning with Oracle SQL/Services Release 7.1.2, you can use Oracle7 SQL to establish a connection to a remote Rdb7 database. To make the database link, use the Oracle7 SQL DATABASE LINK clause. Once the database link is created, you can reference any table or tables in the Rdb7 database, including data dictionary tables. You can join tables from the Rdb7 database with each other and with tables in the Oracle7 database. Only one Rdb7 database can participate in an update trans-action with any number of Oracle7 databases.

The following sections describe how to make a database link and lists restrictions for using this new feature.



3.14.1 CREATE DATABASE LINK ExampleTo make a database link to an Rdb7 database, use the CREATE DATABASE LINK statement. The following line shows the syntax for the CREATE DATABASE LINK statement:

CREATE DATABASE LINK <service> [CONNECT TO username IDENTIFIED BY password] USING <connect>;

In the command line, supply the <service> and <connect> names as follows:

■ The <service> parameter must be the service identifier of the Oracle SQL/Ser-vices service. The service name must consist of 8 or fewer characters.

■ The <connect> parameter must be the SQL*Net connect string from the TNSNAMES.ORA file. The connect string specifies the database or databases to which you want to attach.

For example, assume that your service name is my_serv and the connect string is my_conn as shown in the TNSNAMES.ORA file in Example 3–7.

Example 3–7 TNSNAMES.ORA File

my_conn = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = sqs_node) (PORT = 1234) ) (CONNECT_DATA = (SERVICE = my_serv) )

Example 3–8 shows the CREATE DATABASE LINK statement for establishing a connection to the Rdb7 my_serv database service.

Example 3–8 Database Link Definition

CREATE DATABASE LINK my_serv [CONNECT TO username IDENTIFIED BY password] USING ‘my_conn’;

If the service name you supply is incorrect, the following error message is returned when you attempt to use the database link:

ORA-2085: database link <link_name> connects to <other_name>

Note: In configurations using a release of the Oracle Network Manager prior to Version 3.1.0.5.1, the SERVICE entry in the TNSNAMES.ORA file might be shown instead as SID (service iden-tifier). For example:

(CONNECT_DATA = (SID = my_serv)



In the error message, the variable <other_name> is the service name of the data-base that you tried to connect to, but the variable does not match the name speci-fied in <link_name>.

3.14.2 Database Link RestrictionsThis section describes restrictions for the database links feature:

■ Only one Rdb7 database can participate in an update transaction with any num-ber of Oracle7 databases. If you try to update a second Rdb7 database, the fol-lowing error message is displayed:

ORA-2047: cannot join the distributed transaction in progress

■ Rdb7 cannot participate in the two-phase commit transaction with Oracle7.

The Rdb7 database will commit the transaction in the first (prepare) phase of the two-phase commit, so if the transaction is rolled back at the Oracle data-base, the transaction may be in an inconsistent state.

■ You cannot use standard DML and database links to update the Rdb7 database with data from an Oracle7 database. However, you can use PL/SQL state-ments in the Oracle7 database server to update the Rdb7 database. Also, you can use Oracle Rdb Distributed Technology to access Oracle7 from Rdb7.

■ There is a restriction on Oracle SQL UPDATE and DELETE statements that con-tain subqueries. All tables referenced in an UPDATE or DELETE statement for the Rdb7 database must belong to the Rdb7 database.

Example 3–9 shows a valid update statement.

Example 3–9 Valid Oracle SQL UPDATE Statement

UPDATE emp@rdb SET sal = sal * 1.1 WHERE deptno=(SELECT deptmp FROM emp@rdb WHERE dname = ‘RESEARCH’);

Example 3–10 shows an update statement that will not work.

Example 3–10 Invalid Oracle SQL UPDATE Statement

UPDATE emp@rdb SET sal = sal * 1.1 WHERE deptno=(SELECT deptno FROM depts WHERE dname = ‘RESEARCH’);

The statement fails because it attempts to update the DEPTS local table on the Oracle7 database. When you try to execute the statement in Example 3–10, Oracle7 Server returns the following error:

Removing SQL*Net for Rdb7 from Your Database


ORA-2025: all tables in the SQL statement must be at the remote database

Example 3–11 shows how you can work around this problem:

Example 3–11 Using PL/SQL When DML Does Not Work

SQL>SQL> DECLARE CURSOR acur IS SELECT * FROM dept WHERE dname = ’RESEARCH’; 2 BEGIN 3 FOR rec IN acur LOOP 4 UPDATE emp@rdb SET sal = sal * 1.1 WHERE deptno = rec.deptno; 5 END LOOP; 6 END; 7 /

PL/SQL procedure successfully completed.

SQL>

3.15 Removing SQL*Net for Rdb7 from Your DatabaseIf you need to remove the SQL*Net for Rdb7 tables and views after you have com-mitted the changes, use the following script for removing the definitions:

■ For standard installations, enter:

SQL> @SYS$LIBRARY:RDB_NATCONN_DROP.SQL

■ For multiversion installations, enter:

SQL> @SYS$LIBRARY:RDB_NATCONN_DROP71.SQL

This command deletes all the tables, views, domains, stored procedures and exter-nal procedures installed in your database for SQL*Net for Rdb7. Often, you cannot delete one or a subset of the domains because you have current references to these domains. This is true of any of the database objects. Because each situation is

Note: For multiversion installations, you must include the Oracle SQL/Services release number when you run the RDB_NATCONN_PRE-PARE.SQL script. Make sure you include the Oracle SQL/Services release number, not the Rdb7 release number.

Removing SQL*Net for Rdb7 from Your Database


unique, it is not feasible to describe a solution in this documentation. You must evaluate each problem individually to decide how to handle situations that arise.

SQL ALTER SESSION Statement 4-1

4SQL ALTER SESSION Statement

This chapter explains how to use the Oracle SQL ALTER SESSION statement to con-trol specific aspects of SQL*Net for Rdb7 operations.

ALTER SESSION Statement



You can use the Oracle SQL ALTER SESSION statement with SQL*Net for Rdb7 to:

■ Change the values of National Language Support (NLS) parameters

■ Change the server logging level

■ Change the schema emulation mode

SQL*Net for Rdb7 supports a subset of the ALTER SESSION SET NLS controls that are supported by the Oracle7 Server. In addition, the ALTER SESSION statement supports controls that are unique to the SQL*Net for Rdb7 environment.

EnvironmentYou can use the ALTER SESSION statement:

■ In an Oracle SQL/Services service SQL initialization file

■ On the command line if the OCI client has a SQL command line interface

FormatALTER SESSION

SET ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}SET NLS_LANGUAGE=nls_valueSET NLS_TERRITORY=nls_valueSET NLS_DATE_FORMAT=nls_valueSET NLS_DATE_LANGUAGE=nls_valueSET NLS_NUMERIC_CHARACTERS=nls_valueSET NLS_ISO_CURRENCY=nls_valueSET NLS_CURRENCY=nls_valueSET NLS_SORT=nls_valueSET SCHEMA EMULATION {STRICT | RELAXED}

LOG {BRIEF | FULL | OFF}



Arguments

SET NLS keyword = nls_valueChanges the values of NLS parameters. All the SET NLS keywords are identical in syntax and meaning as described for Oracle SQL statements. Refer to the Oracle7 Server SQL Reference Manual for complete information.

SET ISOLATION LEVEL READ COMMITTEDSET ISOLATION LEVEL SERIALIZABLEDefines the degree to which the read operations of one transaction can be affected by the update operations of other concurrently executing transactions. Isolation lev-els affect only read/write transactions. Read-only transactions always read from the snapshot file if it is enabled.

For example, you implement the SET ISOLATION LEVEL control in the ALTER SESSION statement, as follows:

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLEALTER SESSION SET ISOLATION_LEVEL READ COMMITTED

The SET ISOLATION LEVEL argument is a synonym for the Oracle Rdb SQL DECLARE TRANSACTION ISOLATION LEVEL statement. Refer to the Oracle7 Server SQL Language Reference Manual and to the Oracle Rdb Guide to SQL Program-ming for more information about isolation levels in Rdb7.

SET SCHEMA EMULATION RELAXEDSET SCHEMA EMULATION STRICTAllows you to choose between a relaxed or strict schema emulation layer. The schema emulation control is unique to SQL*Net for Rdb7.

The schema emulation layer is mostly transparent. However, because it is an emula-tion layer and not an exact implementation of the Oracle7 multischema model, you may encounter compatibility problems with some OCI clients. For this reason, SQL*Net for Rdb7 provides two schema emulation modes, STRICT and RELAXED:

■ STRICT schema emulation mode

This is the default mode in which tables and views that you create using an explicit schema that differs from the current schema are recorded in the ORA_OBJECTS table. Each row in the ORA_OBJECTS table defines a database object and the schema to which it belongs. In order to create a table or view out-side your schema in STRICT schema emulation mode, you must have write access to the ORA_OBJECTS table much in the same way as you must have suf-



ficient Oracle privileges to create database objects outside your schema in an Oracle7 environment.

In addition, SQL*Net for Rdb7 verifies references to tables and views that include a schema other than the current schema while in STRICT schema emu-lation mode. If the specified object does not belong to the specified schema, SQL*Net for Rdb7 generates an error condition.

By default, tables are in the schema and are named after the user who created them.

■ RELAXED schema emulation mode

SQL*Net for Rdb7 does not record created tables and views in the ORA_OBJECTS table. All tables and views are created in your current schema and write access to ORA_OBJECTS is not required. SQL*Net for Rdb7 does not verify references to tables and views that include a schema.

By default, tables are in the RDB_SCHEMA schema.

LOG BRIEFLOG FULLLOG OFFEnables or disables logging of information in the Oracle SQL/Services log file. You can use SQL*Net for Rdb7 logging to see which Oracle SQL statements are being sent from the client to the server. This ALTER SESSION clause is unique to SQL*Net for Rdb7.

The default mode is to perform FULL logging during logon processing. Logging is turned OFF by default for the remainder of the session. When logging is OFF, SQL*Net for Rdb7 does not record processing information in the server log.

The following list describes the BRIEF and FULL logging modes:

■ BRIEF logging mode provides only the most critical information needed to diagnose problems and understand how SQL*Net for Rdb7 interacts with the client. Brief logging records the following information in the server log:

– All SQL statements requested by the client

– Client SQL statements after SQL*Net for Rdb7 has performed modifications

– Server error messages

– Row fetch count

BRIEF logging is usually sufficient to diagnose user problems or to better understand which Oracle SQL statements are generated by client applications.



■ FULL logging provides a great quantity of information in the SQL*Net for Rdb7 log file. FULL logging includes all the information included with BRIEF logging and in addition:

– All internal SQL statements generated and executed by SQL*Net for Rdb7

– OCI protocol events, such as parse, describe ,and execute

– Reloading of SQL*Net for Rdb7 internal cache

– Synchronization of the ORA_OBJECTS table with the RDB$RELATIONS table

– Schema emulation information, including schema_name.object_name refer-ences

– SQLDA information used to communicate between SQL*Net for Rdb7 and Rdb7 SQL

FULL logging is the best source of information when you need to diagnose a cli-ent-side problem occurring with SQL*Net for Rdb7. Also, if you need to submit a problem report to Oracle Corporation, you should include a full session log file with your problem report.

Usage Notes■ Use of SQL*Net for Rdb7 with multischema Rdb7 databases is not supported at

this time.

■ While Rdb7 supports the ANSI multischema database model, the majority of Rdb7 databases that might be accessed with OCI through SQL*Net for Rdb7 exist in single schema form. Moreover, the minority of Rdb7 databases that do exist in multischema form are unlikely to contain a schema configuration that is compatible with the typical Oracle7 environment. Therefore, SQL*Net for Rdb7 provides a strict or relaxed schema emulation layer.

– The schema emulation layer allows OCI client applications to operate with single schema Rdb7 databases as though the Rdb7 database contained a schema configuration typical of that found in an Oracle7 database. The schema emulation layer provides a virtual schema environment similar to that of Oracle7.

Note: The form and content of the server log file is subject to change.



– In addition, the SQL*Net for Rdb7 data dictionary provides views and tables that emulate the predefined Oracle7 schemas and schema objects. However, you cannot use the same name for two different database objects in different schemas as you can with Oracle7.

To present this restriction to OCI clients in a way that is most like an Oracle7 environment, the schema emulation layer implicitly defines a pri-vate synonym within the current schema to each object in the database. If you were to define private synonyms in the current schema in an Oracle7 environment, you would encounter the same unique name requirement as with SQL*Net for Rdb7.

■ When you choose the STRICT schema emulation mode, when you log on and whenever you delete a table or view, SQL*Net for Rdb7 attempts to resynchro-nize the ORA_OBJECTS table with RDB$RELATIONS, as follows:

– If ORA_OBJECTS references a database object that no longer exists in RDB$RELATIONS, SQL*Net for Rdb7 attempts to correct ORA_OBJECTS by deleting the obsolete records from ORA_OBJECTS.

– If you do not have write access to ORA_OBJECTS (similar to not having the Oracle privileges to delete objects outside your schema), SQL*Net for Rdb7 generates an error condition and rolls back the current transaction.

– If you were attempting to delete a table or view, the effect of the rollback is to restore the table or view because you do not have sufficient privilege to delete it.

– If you were attempting to log on, the logon attempt fails.

This condition occurs only if the ORA_OBJECTS table or database objects are manipulated outside SQL*Net for Rdb7. The condition is corrected automatically when a user who has write access to the ORA_OBJECTS table logs on to the system.

Tables and Relations A-1

ATables and Relations

This appendix contains information about:

■ Oracle metadata views that emulate the Oracle7 data dictionary

■ Current session information stored in the ORA_SESSION table

■ Recommendations for 31-character table names

■ Table and view definitions needed to store application data in your Rdb7 data-base

A.1 Oracle Metadata ViewsWhen you install SQL*Net for Rdb7:

■ You create a set of procedures and functions that SQL*Net for Rdb7 uses to help manage your session.

■ You create a large set of relations and views used to emulate the Oracle7 data dictionary.

These relations and views are how OCI client applications select Oracle meta-data. The set of relations and views is not complete and neither is the data in them, because not all pieces of Oracle metadata exist in an Rdb7 database.

Table A–1 provides information about table content for columns that contain static or constant information.

The column labeled “Oracle Relations” in Table A–1 lists Oracle relations that con-tain a fixed value that either:

■ Contains data that is not meaningful

■ Is difficult to obtain from an Rdb7 database

Oracle Metadata Views

A-2 Guide to SQL*Net for Rdb7

The column labeled “Columns with Fixed Values” in Table A–1 lists Oracle col-umns with fixed values. If the “Columns with Fixed Values” column of Table A–1 is empty, the relation provides meaningful information.

Note: ALL_USERS view: Every time a user logs on to a database, that user is added to the ORA_ALL_USERS table. The USER_ID is generated by SQL*Net for Rdb7 for each user.

Table A–1 Installed SQL*Net for Rdb7 Relations

Oracle Relations Columns with Fixed Values

V$NLS_PARAMETERS

ACCESSIBLE_COLUMNS DATA_DEFAULT

ALL_CATALOG

ALL_COL_COMMENTS COMMENTS

ALL_CONSTRAINTS SEARCH_CONDITION1, DELETE_RULE, STATUS

ALL_CONS_COLUMNS2

ALL_DB_LINKS HOST, CREATED

ALL_INDEXES TABLE_TYPE, TABLESPACE_NAME, INI_TRANS,MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT,MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,PCT_FREE, BLEVEL, LEAF_BLOCKS, STATUS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR

ALL_IND_COLUMNS

ALL_OBJECTS STATUS

ALL_SEQUENCES

ALL_SYNONYMS

ALL_TABLES TABLESPACE_NAME, CLUSTER_NAME, PCT_FREE,PCT_USED, INI_TRANS, MAX_TRANS,INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS,MAX_EXTENTS, PCT_INCREASE, BACKED_UP, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT,AVG_ROW_LEN

Oracle Metadata Views


ALL_TAB_COLUMNS DEFAULT_LENGTH,DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE,HIGH_VALUE, DENSITY

ALL_TAB_COMMENTS COMMENTS

ALL_TRIGGERS TRIGGERING_EVENT, REFERENCING_NAMESWHEN_CLAUSE, STATUS, DESCRIPTION, TRIGGER_BODY

ALL_TRIGGER_COLS COLUMN_NAME, COLUMN_LIST, COLUMN_USAGE

ALL_USERS

ALL_VIEWS TEXT_LENGTH, TEXT

CAT

DBA_CATALOG

DBA_COL_COMMENTS COMMENTS

DBA_OBJECTS STATUS

DBA_TABLES TABLESPACE_NAME, CLUSTER_NAME, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, BACKED_UP, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN

DUAL

USER_CATALOG

PRODUCT_PRIVS

PRODUCT_PROFILE

PRODUCT_USER_PROFILE

ROLE_SYS_PRIVS

TOOL_MODULE

USER_COL_COMMENTS COMMENTS

USER_CONSTRAINTS SEARCH_CONDITION1, DELETE_RULE, STATUS

USER_CONS_COLUMNS

USER_DB_LINKS HOST, CREATED

Table A–1 Installed SQL*Net for Rdb7 Relations(Cont.)

Current Session Information


A.2 Current Session InformationThe ORA_SESSION table stores information specific to the current session.

1 The SEARCH_CONDITION column will contain either “<column_name> IS NOT NULL” or “/* CHECK constraint information unavailable */ when the CONSTRAINT_TYPE is ‘C’ ”.

2 The ALL_CONS_COLUMNS system table will not show the columns that are involved in a table-level CHECK constraint.

USER_INDEXES TABLE_TYPE, TABLESPACE_NAME, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE, BLEVEL, LEAF_BLOCKS, STATUS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR

USER_IND_COLUMN

USER_OBJECTS STATUS

USER_SEQUENCES

USER_SYNONYMS

USER_SYS_PRIVS

USER_TABLES TABLESPACE_NAME, CLUSTER_NAME, PCT_FREE,PCT_USED, INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, BACKED_UP, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN

USER_TAB_COLUMNS DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, HIGH_VALUE, DENSITY

USER_TAB_COMMENTS COMMENTS

USER_TRIGGERS TRIGGERING_EVENT,REFERENCING_NAMES,WHEN_CLAUSE, STATUS, DESCRIPTION, TRIGGER_BODY

USER_TRIGGER_COLS COLUMN_NAME, COLUMN_LIST, COLUMN_USAGE

USER_USERS DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE

USER_VIEWS TEXT_LENGTH, TEXT

Table A–1 Installed SQL*Net for Rdb7 Relations(Cont.)

Handling 31-Character Object Names


ORA_SESSION is an Rdb7 global temporary table and its contents are unique to each session. (See the Oracle Rdb SQL Reference Manual for more information about temporary tables.)

The INFO_TYPE column in the ORA_SESSION table indicates the type of informa-tion stored in the INFO column in each row.

Table A–2 shows the type of information stored in the ORA_SESSION table.

A.3 Handling 31-Character Object NamesSQL*Net for Rdb7 supports 31-character object names. However, because it is unclear if all client applications support 31-character names, Oracle Corporation recommends that you use a maximum of 30-character object names.

To determine whether or not you have names with more than 30 characters, use the following queries:

Table A–2 ORA_SESSION Table

String Value in the INFO_TYPE Column Corresponding String Value in the INFO column

PROGRAM The name of the client program as reported to SQL*Net for Rdb7 by the client.

TERMINAL The name of the client terminal as reported to SQL*Net for Rdb7 by the client.

INIT_STATEMENTn An initialization statement stored by the ORA_INIT stored procedure. The variable n is the zero-based ordinal sequence number of the initialization statement.

Object Query

Constraint SELECT RDB$CONSTRAINT_NAME FROM RDB$RELATION_CONSTRAINTSWHERE CHARACTER_LENGTH(TRIM(RDB$CONSTRAINT_NAME)) > 30;

Field SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDSWHERE CHARACTER_LENGTH(TRIM(RDB$FIELD_NAME)) > 30;

Index SELECT RDB$INDEX_NAME FROM RDB$INDICESWHERE CHARACTER_LENGTH(TRIM(RDB$INDEX_NAME)) > 30;

Module SELECT RDB$MODULE_NAME FROM RDB$MODULESWHERE CHARACTER_LENGTH(TRIM(RDB$MODULE_NAME)) > 30;

Defining Data Types Recognized by Rdb7


In order to support 31-character names, the Oracle7 data dictionary (metadata tables) provided with SQL*Net for Rdb7, defines OBJECT_NAME a VARCHAR(31) instead of VARCHAR(30) data type.

In most cases, this minor difference does not affect applications. However, if this causes problems with your application, edit a copy of the SYS$LIBRARY:RDB_PREPARE_NATCONN.SQL script to modify the ORA_OBJECT_NAME domain to be the VARCHAR(30) data type. This change makes the Oracle7 data dictionary more compatible with what is typically pro-vided by Oracle7. Also, this change truncates your application object names with 31-characters to 30 characters.

A.4 Defining Data Types Recognized by Rdb7Many client applications come with SQL*Plus scripts that define the tables and views needed to store applications data in your Rdb7 database. You might need to modify these scripts to specify data types that Rdb7 can recognize.

SQL*Net for Rdb7 provides some domains. Also, consider using the ANSI data types because they make good substitutions that work with both Oracle7 and Rdb7.

Table A–3 provides some suggested substitutions you can use when you are con-verting Oracle7 scripts to Rdb7.

Routine SELECT RDB$ROUTINE_NAME FROM RDB$ROUTINESWHERE CHARACTER_LENGTH(TRIM(RDB$ROUTINE_NAME)) > 30;

Table SELECT RDB$RELATION_NAME FROM RDB$RELATIONSWHERE CHARACTER_LENGTH(TRIM(RDB$RELATION_NAME)) > 30;

Trigger SELECT RDB$TRIGGER_NAME FROM RDB$TRIGGERSWHERE CHARACTER_LENGTH(TRIM(RDB$TRIGGER_NAME)) > 30;

Table A–3 Oracle7 and Rdb7 Data Types

Oracle7 Data Types Rdb7 Data Type

NUMBER(P,S) DECIMAL(P,S)

NUMBER(P) DECIMAL(P)

NUMBER NUMBER or DOUBLE PRECISION1

VARCHAR2(L) VARCHAR(L)

Object Query



The data types in Table A–3 are a good place to start. However, as an application designer, you should examine the requirements of each application to determine the best substitutions when converting Oracle7 scripts to Rdb7.

1 Many Oracle programmers use NUMBER when they want INTEGER or BIGINT. This is acceptable for Oracle7 databases since NUMBER acts as either an INTEGER or a FLOAT. Decide what your application needs and choose the appropriate data type.

RAW(L) VARCHAR(L)

LONG RAW RDBLONGRAW or LIST OF BYTE VARYING

LONG RDB1LONG, RDB2LONG, RDB4LONG or VARCHAR 65238, 32634, 16317

Table A–3 Oracle7 and Rdb7 Data Types(Cont.)

Oracle7 Data Types Rdb7 Data Type

Common Installation Script B-1

BCommon Installation Script

This appendix contains information about using a common installation script.

B.1 Using a Common Installation ScriptYou can install the Rdb7 and Oracle7 servers using a common installation script. Because SQL*Net for Rdb7 is a server-side solution, you only need to set up the Oracle7 and Rdb7 DDL scripts required to set up the application.

Example B–1 shows an example of a common SQL*Plus script.

Example B–1 Common SQL*Plus Installation Script

/* The following objects are created by this script.DROP TRIGGER tsttrig;DROP TABLE tsttab2;DROP TABLE tsttab1;DROP PROCEDURE ora_ddl;*/

CREATE OR REPLACE PROCEDURE ora_ddl (oracle_ddl IN VARCHAR2, rdb_ddl IN VARCHAR2) AS cursor_name INTEGER; ret INTEGER;BEGIN cursor_name := dbms_sql.open_cursor; dbms_sql.parse(cursor_name, oracle_ddl, dbms_sql.v7); ret := dbms_sql.execute(cursor_name); dbms_sql.close_cursor(cursor_name);END;

12

Using a Common Installation Script

B-2 Guide to SQL*Net for Rdb7

In Example B–1, note the following about the callout lines:

The script creates a PL/SQL procedure named ora_ddl.

(You can use the ora_ddl procedure with any statement except the SELECT statement.)

The SQL*Plus script takes two strings as input:

1. The first string, oracle_ddl, contains your Oracle7 DDL input.

2. The second string, rdb_ddl, contains your Rdb7 DDL input.

Many applications create tables, views, and other data objects in the database. The manual, Oracle Rdb: A Comparison of SQL Dialects for Oracle and Oracle Rdb, describes language differences and offers suggestions for common language usage. How-ever, some database objects (such as triggers) currently do not have a common lan-guage. A future release of SQL*Net for Rdb7 will provide tools and new features to support both Oracle7 and Rdb7 syntax in the same script.

Note: The Oracle7 DDL created with the ora_ddl procedure requires that you have explicit privileges, not roles, that grant access to create or delete objects. For example, the DBA role does not provide enough authorization to create a table; this task requires that you have the CREATE ANY TABLE system privilege.

1

2

Index-1

IndexAACCESSIBLE_COLUMNS relation, A-2administration

privileges and roles required for Oracle7 DDL, B-2

ALL_CATALOG relation, A-2ALL_COL_COMMENTS relation, A-2ALL_CONS_COLUMNS relation, A-2ALL_CONSTRAINTS relation, A-2ALL_DB_LINKS relation, A-2ALL_IND_COLUMNS relation, A-2ALL_INDEXES relation, A-2ALL_OBJECTS relation, A-2ALL_SEQUENCES relation, A-2ALL_SYNONYMS relation, A-2ALL_TAB_COLUMNS relation, A-3ALL_TAB_COMMENTS relation, A-3ALL_TABLES relation, A-2ALL_TRIGGER_COLS relation, A-3ALL_TRIGGERS relation, A-3ALL_USERS relation, A-3ALL_VIEWS relation, A-3ALTER SESSION statement, 4-2

changing NLS parameters, 4-2data formatting, 2-2date and numeric data formatting, 2-3format, 4-2LOG clause, 4-4logging with LOG BRIEF, 2-4SCHEMA EMULATION clause, 2-6SET ISOLATION LEVEL clause, 4-3SET NLS clause, 4-3SET SCHEMA EMULATION clause, 4-3

specifying character sets, 3-26usage environment, 4-2

ANSI quotingfor statement parsing, 2-4

application developmentcommon SQL programs, 1-3

applicationsattaching to Rdb7 with universal service, 1-5executing multiple ATTACH statements, 1-5preattaching to one Rdb7 database, 1-5

AR8ISO8859P6 character set, 3-24ATTACH statement, 1-5attribute values

unsupported for SQL*Net for Rdb7 databases, 3-12

BBIGINT(2) data type, 2-5

CCAST function

handling with TO_DATE object, 2-5to match the DATE ANSI format, 2-3

CAT relation, A-3CHAR data types, 2-4character sets

default, 3-24defining on the server, 3-24error messages, 3-26example specifying French language and

territory, 3-25example specifying Western European, 3-25

Index-2

multibyte, 3-24rules and recommendations, 3-26specifying using the ALTER SESSION

statement, 3-26US7ASCII, 3-25

CL8ISO8859P5 character set, 3-25client applications

31-character table names, A-6error messages returned to, 3-27interacting with SQL*Net for Rdb7, 1-3operating system platforms, 1-2programming, 1-3querying, 2-7run against either Rdb or Oracle, 1-1using SQL*Plus scripts, A-6

client systemdistributing SQL*Net configuration files, 3-20error and trace messages, 3-27installing SQL*Net, 3-21running the Oracle NetFetch utility, 3-20

client systemscharacter set compatibility with server

systems, 3-26defining character sets, 3-24, 3-26rules and recommendations for specifying

character sets, 3-26client/server processing, 1-1columns

Oracle relations that have columns with fixed values, A-2

Community definitiondefining for SQL*Net, 3-16requirements, 3-16

configuration filescorrecting on server system, 3-21directory, 3-21distributing, 3-19LISTENER.ORA, 3-20SQLNET.ORA, 3-20TNSNAMES.ORA, 3-20TNSNAV.ORA, 3-20

configurationsmerging network files, 3-21OCI enables diverse combinations, 1-2Oracle SQL/Services, 3-10

SQL*Net, 3-15connect string

example supplying Rdb7 database name, 3-35supplying an Rdb7 database name, 1-5

CREATE ANY TABLE system privilege, B-2CREATE DATABASE LINK statement

example, 3-35cursor

management, 2-2SQL semantics, 2-2

Ddata formatting, 2-2

logging information with ALTER SESSION statement, 4-4

data typesconversion, 2-2decimal representation, 2-5DOUBLE PRECISION, 2-5recognizable by Rdb7, A-6SQL*Net for Rdb7 descriptions, 2-4substitutions when converting SQL*Plus scripts

to Rdb7, A-6database

accessing remote, 3-34accessing with OCI applications, 1-3attaching to Rdb7 by multiple client

applications, 1-5common SQL usage, B-2connecting using SQL*Net for Rdb7, 3-23connection using database links, 3-35defining the Rdb7 database to be served by

SQL*Net for Rdb7, 3-17links, 3-32multischema, 2-6preparing the Rdb7 environment, 3-7removing SQL*Net for Rdb7 access, 3-37unsupported attributes, 3-12

database linksexample definition, 3-35example of an SQL UPDATE statement, 3-36referencing an Rdb7 database, 3-32restrictions, 3-36

database objects

Index-3

installed for Oracle Data Dictionary emulation, 2-5

database servicespreattach one client application, 1-5

DATE ANSI format, 2-3DATE data type, 2-4

mimicking with DATE VMS date, 2-3date literals

formatting, 2-3DATE VMS data type

handling with TO_DATE object, 2-5DATE VMS date, 2-3DBA_CATALOG relation, A-3DBA_COL_COMMENTS relation, A-3DBA_OBJECTS relation, A-3DBA_TABLES relation, A-3DDL

enabling statements in an SQL initialization file, 2-6

Oracle Rdb mimics Oracle behavior, 2-2decimal representation, 2-5DECnet network transport, 3-10, 3-15DESCRIPTION_LIST construct

generated by Oracle Network Manager, 3-21Developer/2000

PLUS applications, 3-23removing scripts created by

RDBVUBLD.SQL, 3-9script, 3-9

directorychanging the location of the Oracle directory

tree, 3-19distributed transactions

using database links, 3-36domain

NUMBER AS DOUBLE PRECISION, 3-10subdirectory naming conventions, 3-18

DOUBLE PRECISION data type, 2-5DUAL relation, A-3

EEL8ISO8859P7 character set, 3-25emulation

multischema database, 2-6

Oracle Data Dictionary, 2-5error handling

DESCRIPTION_LIST construct, 3-21during installation, 3-9name conflict, 3-10SQL*Net errors, 3-27statement parsing, 2-3

error messagescharacter sets used, 3-26inspecting Oracle SQL/Services log and trace

files, 3-30logged to Oracle SQL/Services dispatcher log

files, 3-30logging with ALTER SESSION statement, 4-4ORA-2025, 3-37ORA-2047, 3-36ORA-2085, 3-35reporting problems to Oracle, 3-7returned to OCI client applications, 3-27SQLNET.LOG file, 3-27when specifying character sets, 3-26

executor log files, 3-30executor processes

prestarting, 1-5

Fformat

data, 2-2format string

providing with TO_CHAR function, 2-5functions

defining Oracle database, 3-7

GGrant Use

unsupported attribute value, 3-12graphical user interface

See GUIGUI

Oracle Network Manager, 3-13Oracle SQL/Services Manager, 3-10

Index-4

IINFO_TYPE column, A-5initialization file

initializing the SQL execution environment, 2-6syntax conventions when setting up universal

service, 1-5input and output variables

handling in OCI applications, 3-32installation

command to invoke VMSINSTAL, 3-4example SQL*Plus script, B-1handling errors, 3-9multiversion and standard kits, 3-4OCI_DISP dispatcher creation, 3-12relations and views created to emulate Oracle

Data Dictionary, A-1save sets for Oracle SQL/Services, 3-4verification, 3-7

INTEGER data type, 2-5isolation levels

setting with ALTER SESSION statement, 4-3IW8ISO8859P8 character set, 3-25

JJA16SJIS character set, 3-24JA16VMS character set, 3-24

KKO16KSC5601 character set, 3-24

Llanguages

supported character sets, 3-24LIST OF BYTE VARYING column, 2-4Listener definition

defining for SQL*Net, 3-16example, 3-22OCI_LISTENER, 3-17Oracle LISTENER, 3-17removing the host or node line, 3-22

LISTENER.ORA configuration fileon the server system, 3-20

removing the unsupported line, 3-21LOG clause

ALTER SESSION statement, 4-4logging

enabling in an SQL initialization file, 2-6error logging files, 3-30SQL*Net errors, 3-27

Mmanagement

Oracle SQL/Services Manager GUI, 3-10SQLSRV_MANAGE client utility, 3-10

message mapping with OCI, 2-1metadata

differences between Oracle Rdb and Oracle servers, 1-3

obtaining from Oracle Data Dictionary, 1-3Oracle Data Dictionary emulation, 2-5Rdb SQL dialect differs from Oracle Server, 1-3selected by OCI applications, A-1

multibyte character sets, 3-24multischema databases, 2-6multischema emulation, 2-6

setting relaxed or strict emulation, 4-3multiversion kit, 3-4

SQLSRVBMV071, 3-4SQLSRVBMVA071, 3-4

Nnative binary data types, 2-5native data types, 2-2network configuration files

merging, 3-21network definition files, 3-14

maintaining with the Oracle NetFetch utility, 3-21

modifying for DESCRIPTION_LIST construct, 3-22

samples, 3-14network transports, 3-10

community definitions, 3-16NLS parameters

changing with ALTER SESSION statement, 4-3

Index-5

NLS_LANG logical name, 3-25Node definition

defining for SQL*Net, 3-16NUMBER AS DOUBLE PRECISION domain, 3-10NUMBER data type, 2-5numeric data

formatting, 2-3numeric data types, 2-5

OOCI

binding host variables, 3-32example using PL/SQL blocks, 3-31

OCI applicationsconfiguring network communications, 3-10cursor management, 2-2message mapping, 2-1new and existing, 1-3run against Oracle or Rdb, 1-1select Oracle metadata, A-1using the emulated Oracle Data Dictionary, 2-5

OCI dispatcherconfiguring, 3-10creating a custom, 3-12defining, 3-12log files, 3-30OCI_DISP, 3-12starting, 3-22

OCI message protocol, 3-10OCI service

access, 3-12configuring, 3-10defining, 3-11OCI_SAMPLE defines TNS name for

access, 3-23starting, 3-22

OCI technology, 1-2connects client applications to Rdb7, 1-2

OCI_DISP dispatcher, 3-12starting, 3-22uses OCI_LISTENER for listener name, 3-17

OCI_LISTENER, 3-17OCI_SAMPLE variable, 3-23OCI_SAMPLE_CREATE.SQS sample

procedure, 3-32OCI_SAMPLE_DB.COM sample procedure, 3-32OCI_SAMPLE_START.SQS sample

procedure, 3-32OCIDNET.NET network configuration file, 3-14OCIDNET.NET sample procedure, 3-32OCITCPIP.NET network configuration file, 3-14OCITCPIP.NET sample procedure, 3-32operating systems

supported, 3-4ORA_ALL_USERS table, A-2ORA_INIT stored procedure

execute SQL initialization statements, 2-6storing DDL and other statements, 2-6

ORA_SESSION tablecurrent session information, A-4INFO_TYPE column, A-5

ORA-2025 error message, 3-37ORA-2047 error message, 3-36ORA-2085 error message, 3-35Oracle

relations that contain fixed values, A-2Oracle Call Interface (OCI), 1-2Oracle Data Dictionary

client queries, 1-3emulation, 2-5, 3-7, A-1multischema emulation, 2-6preparing to serve the Rdb7 database, 3-7supporting 31-character table names, A-6tables provided by Developer/2000, 3-9TO_CHAR object, 2-5TO_DATE object, 2-5TO_NUMBER object, 2-5USERENV function, 2-6

Oracle databasedefining Oracle functions, 3-7functions, 2-1

Oracle directory treechanging the location of, 3-19finding, 3-19

Oracle Level1 dialect, 2-1DATE VMS data type, 2-5related documentation, xiiSQL statement parsing, 2-3

Oracle NetFetch utility, 3-20

Index-6

Oracle Network Managerchanging error and trace file names, 3-27defining a network definition, 3-15directory of files generated by, 3-19generates a DESCRIPTION_LIST construct, 3-21generates sample definition files, 3-22GUI, 3-13Oracle NetFetch utility, 3-20specifying the OCI service name in the SID

parameter, 3-18supplying Rdb7 database name as a connect

string, 1-5warning about database omission, 3-17

Oracle Rdb Distributed Technologyto access Oracle7 from Rdb7, 3-36

Oracle Rdb7 databasedefining the SQL*Net definition, 3-17preparing the environment, 3-7

Oracle SQL ALTER SESSION statementSee ALTER SESSION statement

Oracle SQL/Servicesdefining the OCI dispatcher, 3-12defining the OCI service, 3-11executor log files, 3-30generating the client and server definition

files, 3-18initialization file, 2-6inspecting log files for errors, 3-30installation, 3-3, 3-4log files, 3-30multiversion and standard kit, 3-4save sets, 3-4setup examples, 3-32starting the OCI service and dispatcher, 3-22using the GUI to define the OCI service, 3-11

Oracle SQL/Services Managergraphical user interface (GUI), 3-10

Oracle System Identifier (SID)used for database links, 3-35

Oracle7accessing from Rdb7 using Oracle Rdb7

Distributed Technology, 3-36DATABASE LINK clause, 3-34DDL script example, B-1error messages, 3-26

required DDL privileges, B-2SQL UPDATE statement, 3-36

Pparsing

SQL statements, 2-3PL/SQL statements

blocks in OCI programs, 3-31example anonymous block calling stored

procedure, 3-32example usage when DML does not work, 3-37procedure example, B-2using over database links, 3-36

PLUS32 application program, 3-23PLUS33 application program, 3-23portable data types, 2-2privileges

CREATE ANY TABLE, B-2Pro* compilers

client applications access databases, 1-3PRODUCT_PRIVS relation, A-3PRODUCT_PROFILE relation, A-3PRODUCT_USER_PROFILE relation, A-3

RRDB$DBHANDLE alias

setting up in the ATTACH statement, 1-5RDB_NATCONN_DROP_RDBVUBLD script, 3-9RDB_NATCONN_PREPARE script, 3-9RDB_PREPARE_NATCONN script, A-6Rdb7

calling stored procedures, 3-31DDL script example, B-1referencing database tables from Oracle7, 3-34

Rdb7 error messages, 3-26RDBVUBLD.SQL script, 3-9

removing tables created by, 3-9relations

installed Oracle, A-2installed SQL*Net for Rdb7, A-2

ROLE_SYS_PRIVS relation, A-3

Index-7

Ssamples

files generated by Oracle Network Manager, 3-22

network definition files, 3-14preparing and running, 3-33setting up SQL*Net for Rdb7 and SQL/

Services, 3-32save sets

for Oracle SQL/Services installation, 3-4names of Oracle SQL/Services kits, 3-4

scriptsRDB_NATCONN_DROP_RDBVUBLD, 3-9RDB_NATCONN_PREPARE, 3-9RDBVUBLD.SQL, 3-9removing, 3-9SQL*Plus, A-6

servercorrecting SQL*Net configuration files, 3-21distributing SQL*Net configuration files, 3-19operating system independence, 1-2passing information to with ALTER SESSION

statement, 2-2server systems

defining character sets, 3-24example of defining character sets, 3-25rules and recommendations for specifying

character sets, 3-26server-side solution, 1-2service owner

unsupported attribute value, 3-12session information, A-4sessions

recommendations for character set specification, 3-26

specifying error message files for, 3-26SET ISOLATION LEVEL

READ COMMITTED clause, 4-3SERIALIZABLE clause, 4-3

SET NLS clauseALTER SESSION statement, 4-3

SET SCHEMA EMULATIONRELAXED clause, 4-3STRICT clause, 4-3

set up proceduresamples, 3-32

SID parameterused for database links, 3-35

SQL ALTER SESSION statementSee ALTER SESSION statement

SQL initialization file, 2-6syntax conventions, 1-5

SQL statementsaugmenting for common application

development, 2-1cursor semantics, 2-2emulating Oracle semantics, 1-1in an SQL initialization file, 2-6logging transfers between client and server, 4-4modifications made by SQL*Net for Rdb7, 2-4parsing, 2-3parsing failures, 2-4run against either Oracle or Rdb databases, 1-3using ALTER SESSION to specify character

sets, 3-26SQL*Net

configuration files directory, 3-21configuring, 3-13configuring with Oracle Network Manager

GUI, 3-13connect string, 3-35connects Oracle clients to Rdb7 servers, 1-1correcting server system configuration

files, 3-21creating configuration definitions, 3-15defining

Community, 3-16Listener, 3-16Node, 3-16Oracle Rdb7 database, 3-17

distributingconfiguration files, 3-19on client systems, 3-20on server systems, 3-19

errors and trace output, 3-27installing on OpenVMS client platforms, 3-21network configuration files, 3-14preserving existing definitions, 3-13supports calling Rdb7 stored procedures, 3-31

Index-8

translates character sets, 3-26SQL*Net for Rdb7

connecting a database, 3-23error logging, 3-30executor log files, 3-30installation and setup tasks, 3-1installed relations, A-2invoking the PLUS application, 3-23removing from a database, 3-37sample of creating, 3-32

SQL*Plus scriptsexample, B-2specifying Rdb7 data types, A-6

SQL_FUNCTIONS commandinvoking, 3-8

SQLNET.LOG file, 3-27SQLNET.ORA configuration file

on the server system, 3-20SQLNET.ORA file

on the client system, 3-20SQLNET.TRC file, 3-27SQLSRVBMV071 multiversion kit, 3-4SQLSRVBMVA071 multiversion kit, 3-4SQLSRVBS071 standard kit, 3-4SQLSRVBSA071 standard kit, 3-4standard kit, 3-4

installing Oracle functions, 3-8SQLSRVBS071, 3-4SQLSRVBSA071, 3-4

static table contents, A-1stored procedures

calling Rdb7, 3-31SYS$COMMON

SQLSRV.SQLNET.NETWORK.ADMIN directory, 3-6

SYS$MANAGERSQLSRV_SQLNET.DAT file, 3-19

Ttable names

conflicting, 3-10supporting 31 characters, A-5

tablesinstalled, A-1, B-1

TCP/IP network transport, 3-10, 3-15TINYINT column, 2-5TNSNAMES.ORA file, 3-35

example, 3-35OCI_SAMPLE defines TNS name, 3-23on the client system, 3-20on the server system, 3-20

TNSNAV.ORA configuration file, 3-20TO_CHAR function

Oracle Data Dictionary emulation, 2-5processing, 2-3providing a format string, 2-5

TO_DATE functionOracle Data Dictionary emulation, 2-5processing, 2-3statement parsing, 2-3

TO_NUMBER functionOracle Data Dictionary emulation, 2-5processing, 2-3

TOOL_MODULE relation, A-3trace messages

SQLNET.TRC file, 3-27tracing

SQL*Net output, 3-27TRANSACTION for Reuse Scope

unsupported attribute value, 3-12two-phase commit transaction

restriction for database links, 3-36

Uuniversal service

specifying the Rdb7 database, 1-5support, 1-5syntax conventions for SQL initialization

files, 1-5UPDATE statement

example usage over database links, 3-36invalid usage and workaround, 3-36

US7ASCII character set, 3-25USER_CATALOG relation, A-3USER_COL_COMMENTS relation, A-3USER_CONS_COLUMNS relation, A-3USER_CONSTRAINTS relation, A-3USER_DB_LINKS relation, A-3

Index-9

USER_IND_COLUMN relation, A-4USER_INDEXES relation, A-4USER_OBJECTS relation, A-4USER_SEQUENCES relation, A-4USER_SYNONYMS relation, A-4USER_SYS_PRIVS relation, A-4USER_TAB_COMMENTS relation, A-4USER_TABLES relation, A-4USER_TRIGGER_COLS relation, A-4USER_TRIGGERS relation, A-4USER_USERS relation, A-4USER_VIEWS relation, A-4USERENV function, 2-6

VV$NLS_PARAMETERS relation, A-2VARCHAR data types, 2-4, 2-6views

installed, A-1, B-1VMSINSTAL

installing Oracle SQL/Services, 3-4

W.WE8DEC character set, 3-24

ZZHS16CGB2312-80 character set, 3-24ZHT16BIG5 character set, 3-25ZHT32CNS11643-86 character set, 3-24

Index-10