OracleRdb7 SQLReferenceManual Volume2
Oracle Rdb7 SQL Reference ManualVolume 2Release 7.0Part No. A42813-1
SQL Reference Manual, Volume 2Release 7.0Part No. A42813-1Copyright 1987, 1996, Oracle Corporation. All rights reserved.This software contains proprietary information of Oracle Corporation; it is provided undera license agreement containing restrictions on use and disclosure and is also protected bycopyright law. Reverse engineering of the software is prohibited.The information contained in this document is subject to change without notice. If you findany problems in the documentation, please report them to us in writing. Oracle Corporationdoes not warrant that this document is error free.Restricted Rights Legend Programs delivered subject to the DOD FAR Supplement are’commercial computer software’ and use, duplication and disclosure of the programs shallbe subject to the licensing restrictions set forth in the applicable Oracle license agreement.Otherwise, programs delivered subject to the Federal Acquisition Regulations are ’restrictedcomputer software’ and use, duplication and disclosure of the programs shall be subject tothe restrictions in FAR 52.227-14, Rights in Data—General, including Alternate III (June1987). 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 the licensee’sresponsibility to take all appropriate fail-safe, back up, redundancy and othermeasures to ensure the safe use of such applications if the programs are used forsuch purposes, and Oracle disclaims liability for any damages caused by such useof the programs.Oracle is a registered trademark of Oracle Corporation.Oracle CDD/Administrator, Oracle CDD/Repository, Oracle Rally, Oracle Rdb, Oracle RMU,Oracle SQL/Services, Distributed Option for Rdb, Rdb Transparent Gateway to RMS,Rdb Transparent Gateway to DB2, Rdb Transparent Gateway to Oracle, and Rdb7 aretrademarks of Oracle Corporation.All other product or company names mentioned are used for identification purposes only,and may be trademarks of their respective owners.
ContentsSend Us Your Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .viiPreface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ixTechnical Changes and New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii6 SQL StatementsALTER DATABASE Statement . . . . . . . . . . . . .ALTER DOMAIN Statement . . . . . . . . . . . . . . .ALTER INDEX Statement . . . . . . . . . . . . . . . . .ALTER STORAGE MAP Statement . . . . . . . . . .ALTER TABLE Statement . . . . . . . . . . . . . . . . .ATTACH Statement . . . . . . . . . . . . . . . . . . . . . .BEGIN DECLARE Statement . . . . . . . . . . . . . .CALL Statement for Simple Statements . . . . . .CALL Statement for Compound Statements . . .CASE Control Statement . . . . . . . . . . . . . . . . . .CLOSE Statement . . . . . . . . . . . . . . . . . . . . . . .COMMENT ON Statement . . . . . . . . . . . . . . . .COMMIT Statement . . . . . . . . . . . . . . . . . . . . .Compound Statement . . . . . . . . . . . . . . . . . . . . .CONNECT Statement . . . . . . . . . . . . . . . . . . . .CREATE CACHE Clause . . . . . . . . . . . . . . . . . .CREATE CATALOG Statement . . . . . . . . . . . . .CREATE COLLATING SEQUENCE Statement .CREATE DATABASE Statement . . . . . . . . . . . .CREATE DOMAIN Statement . . . . . . . . . . . . . .CREATE FUNCTION Statement . . . . . . . . . . . 56–1886–1946–1986–2036–2616–276iii
CREATE INDEX Statement . . . . . . . . . . . . . . . . . . . .CREATE MODULE Statement . . . . . . . . . . . . . . . . .CREATE OUTLINE Statement . . . . . . . . . . . . . . . . .CREATE PROCEDURE Statement . . . . . . . . . . . . . .Create Routine Statement . . . . . . . . . . . . . . . . . . . . .CREATE SCHEMA Statement . . . . . . . . . . . . . . . . . .CREATE STORAGE AREA Clause . . . . . . . . . . . . . .CREATE STORAGE MAP Statement . . . . . . . . . . . .CREATE TABLE Statement . . . . . . . . . . . . . . . . . . . .CREATE TRIGGER Statement . . . . . . . . . . . . . . . . .CREATE VIEW Statement . . . . . . . . . . . . . . . . . . . . .DECLARE ALIAS Statement . . . . . . . . . . . . . . . . . . .DECLARE CURSOR Statement . . . . . . . . . . . . . . . . .DECLARE CURSOR Statement, Dynamic . . . . . . . . .DECLARE CURSOR Statement, Extended Dynamic .DECLARE LOCAL TEMPORARY TABLE StatementDECLARE MODULE Statement . . . . . . . . . . . . . . . .DECLARE STATEMENT Statement . . . . . . . . . . . . .DECLARE TABLE Statement . . . . . . . . . . . . . . . . . .DECLARE TRANSACTION Statement . . . . . . . . . . .DECLARE Variable Statement . . . . . . . . . . . . . . . . ding Columns with Default Values to Tables . . . . . . . . . . . . . . . . and Line Qualifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Logical Name Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Updating Data Definitions While Users Are Attached to theDatabase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Updating to Database-Wide Parameters While Users Are Attachedto the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiiixviii.6–56.6–59
6–36–46–56–6ALTER and DROP Statements Causing or Not Causing StoredRoutine Invalidation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Using Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Availability of Row Data for Triggered Actions . . . . . . . . . . . . .Classes, Types, and Modes of Cursors . . . . . . . . . . . . . . . . . . . .6–3066–4086–4306–466v
Send Us Your CommentsOracle Corporation welcomes your comments and suggestions on the qualityand usefulness of this publication. Your input is an important part of theinformation used for revision.You can send comments to us in the following ways: Electronic mail — nedc doc@us.oracle.com FAX — 603-897-3334 Attn: Oracle Rdb Documentation Postal serviceOracle CorporationOracle Rdb DocumentationOne Oracle DriveNashua, NH 03062USAIf you like, you can use the following questionnaire to give us feedback. (Editthe online release notes file, extract a copy of this questionnaire, and send it tous.)NameTitleCompanyDepartmentMailing AddressTelephone NumberBook TitleVersion Number Did you find any errors? Is the information clearly presented? Do you need more information? If so, where?vii
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, pleaseindicate the chapter, section, and page number (if available).viii
PrefaceThis manual describes the syntax and semantics of all the statements andlanguage elements for the SQL (structured query language) interface to theOracle Rdb database software.Intended AudienceTo get the most out of this manual, you should be familiar with data processingprocedures, basic database management concepts and terminology, and theOpenVMS operating system.Operating System InformationYou can find information about the versions of the operating system andoptional software that are compatible with this version of Oracle Rdb in theOracle Rdb7 Installation and Configuration Guide.For information on the compatibility of other software products with thisversion of Oracle Rdb, refer to the Oracle Rdb7 Release Notes.Contact your Oracle representative if you have questions about thecompatibility of other software products with this version of Oracle Rdb.StructureThis manual is divided into three volumes. Volume 1 contains Chapter 1through Chapter 5 and an index. Volume 2 contains Chapter 6 and an index.Volume 3 contains Chapter 7, the appendixes, and an index.The index for each volume contains entries for the respective volume only anddoes not contain index entries from the other volumes in the set.The following table shows the contents of the chapters and appendixes inVolumes 1, 2, and 3 of the Oracle Rdb7 SQL Reference Manual:ix
Chapter 1Introduces SQL (structured query language) and brieflydescribes SQL functions. This chapter also describesconformance to the ANSI standard, how to read syntaxdiagrams, executable and nonexecutable statements,keywords and line terminators, and support for MultivendorIntegration Architecture.Chapter 2Describes the language and syntax elements common tomany SQL statements.Chapter 3Describes the syntax for the SQL module language and theSQL module processor command line.Chapter 4Describes the syntax of the SQL precompiler command line.Chapter 5Describes SQL routines.Chapter 6andChapter 7Describes in detail the syntax and semantics of the SQLstatements. These chapters include descriptions of datadefinition statements, data manipulation statements, andinteractive control commands.Appendix ADescribes the different types of errors encountered in SQLand where they are documented.Appendix BDescribes the SQL Communications Area and the messagevector.Appendix CDescribes the SQLSTATE error handling mechanism.Appendix DDescribes the SQL Descriptor Areas and how they are usedin dynamic SQL programs.Appendix ESummarizes the logical names and configuration parametersthat SQL recognizes for special purposes.Appendix FSummarizes the obsolete SQL features of the current OracleRdb version.Appendix GSummarizes the SQL functions that have been added tothe Oracle Rdb SQL interface for convergence with Oracle7SQL.IndexVolume 2 only.Related ManualsFor more information on Oracle Rdb, see the other manuals in thisdocumentation set, especially the following:x Oracle Rdb7 Guide to Database Design and Definition Oracle Rdb7 Guide to Database Performance and Tuning Oracle Rdb7 Introduction to SQL Oracle Rdb7 Guide to SQL Programming
ConventionsThis manual uses icons to identify information that is specific to an operatingsystem or platform. Where material pertains to more than one platform oroperating system, combination icons or generic icons are used. For example:Digital UNIXThis icon denotes the beginning of information specific to theDigital UNIX operating system.OpenVMS OpenVMSVAXAlphaThis icon combination denotes the beginning of informationspecific to both the OpenVMS VAX and OpenVMS Alphaoperating systems.The diamond symbol denotes the end of a section ofinformation specific to an operating system or platform.In examples, an implied carriage return occurs at the end of each line, unlessotherwise noted. You must press the Return key at the end of a line of input.Often in examples the prompts are not shown. Generally, they are shownwhere it is important to depict an interactive sequence exactly; otherwise, theyare omitted.Discussions in this manual that refer to VMScluster environments apply toboth VAXcluster systems that include only VAX nodes and VMScluster systemsthat include at least one Alpha node, unless indicated otherwise.The following conventions are also used in this manual:.Vertical ellipsis points in an example mean that information not directlyrelated to the example has been omitted.Horizontal ellipsis points in statements or commands mean that partsof the statement or command not directly related to the example havebeen omitted.e, f, tIndex entries in the printed manual may have a lowercase e, f, or tfollowing the page number; the e, f, or t is a reference to the example,figure, or table, respectively, on that page.boldfacetextBoldface type in text indicates a new term. Angle brackets enclose user-supplied names in syntax diagrams.xi
[]Brackets enclose optional clauses from which you can choose one ornone. The dollar sign represents the command language prompt. This symbolindicates that the command language interpreter is ready for input.UPPERCASEThe Digital UNIX operating system differentiates between lowercaseand uppercase characters. Examples, syntax descriptions, functiondefinitions, and literal strings that appear in text must be typed exactlyas shown.lowercaseReferences to ProductsThe Oracle Rdb documentation set to which this manual belongs often refers tothe following Oracle Corporation products by their abbreviated names:xii In this manual, Oracle Rdb refers to Oracle Rdb for OpenVMS and OracleRdb for Digital UNIX software. Version 7.0 of Oracle Rdb software is oftenreferred to as V7.0. The SQL interface to Oracle Rdb is referred to as SQL. This interface isthe Oracle Rdb implementation of the SQL standard ANSI X3.135-1992,ISO 9075:1992, commonly referred to as the ANSI/ISO SQL standard orSQL92. Oracle CDD/Repository software is referred to as the dictionary, the datadictionary, or the repository. Oracle ODBC Driver for Rdb software is referred to as the ODBC driver. OpenVMS means both the OpenVMS Alpha and OpenVMS VAX operatingsystem.
Technical Changes and New FeaturesThis section identifies the new and updated portions of this manual since itwas last released with V6.0.The Oracle Rdb7 Release Notes describes current limitations and restrictions.The major new features and technical changes for V6.1 that aredescribed in this manual are: INTEGER data type for SQL module language allows modifiersThe SQL module language syntax has been extended to allow specificationof precise INTEGER module parameters in the number of bits. New command line qualifiers for SQL module language and precompiledSQLTable 1 shows the new qualifiers for SQL module language and precompiledSQL and the appropriate platform.Table 1 Command Line QualifiersQualifier NameDigital UNIXOpenVMSAlphaOpenVMSVAXXXXXXXSQL Module Language[NO]ALIGN RECORDS–[no]alignX[NO]LOWERCASE PROCEDURENAMES–[no]lc procX[NO]C PROTOTYPES–[no]cprotoX(continued on next page)xiii
Table 1 (Cont.) Command Line ROLLBACK ON EXITXX[NO]EXTERNAL GLOBALSXXQualifier NameDigital UNIXSQL Module Language[NO]LONG SQLCODE–[no]lsqlcodeX[NO]EXTERNAL GLOBALS–[no]externXUSER DEFAULT–user usernameXPASSWORD DEFAULT–pass passwordX[NO]PACKAGE COMPILATIONROLLBACK ON EXIT–fidaX–int32X–int64X–plan file-specXSQL Precompiler[NO]DECLARE MESSAGEVECTOR–s ’–[no]msgvec’XUSER DEFAULT–s ’–user username’XPASSWORD DEFAULT–s ’–pass password’X–s ’–[no]extern’X–plan file-specXSee Chapter 3 and Chapter 4 for more information. xivAsynchronous creation of storage areas
You can specify whether Oracle Rdb creates storage areas serially, createsa specified number at the same time, or creates all areas at the same time.For information about the SQL syntax, see the ALTER DATABASEStatement, the CREATE DATABASE Statement, and the IMPORTStatement. Authenticating users for remote accessOracle Rdb lets you explicitly provide user name and password informationin SQL statements that attach to the database. In addition, it lets you passthe information to an SQL module language or precompiled SQL programby using a parameter and new command line qualifiers. You can also passthe information to Oracle Rdb by using configuration parameters. Selecting an outline to use for a queryUsing SQL syntax, you can specify the name of an outline to use for aquery.SQL statements affected by this feature are DECLARE CURSOR,DELETE, INSERT, SELECT, and UPDATE and select expression.OpenVMS OpenVMSVAXAlpha Notification of classes of operatorsUsing SQL syntax, you can specify which classes of operators are notifiedin the case of a catastrophic journaling event such as running out of diskspace. (This feature was available in V6.0 using the RMU interface.)For information about the SQL syntax, see the ALTER DATABASEStatement, the CREATE DATABASE Statement, and the IMPORTStatement. Specifying shutdown timeUsing SQL syntax, you can specify the number of minutes the databasesystem will wait after a catastrophic event before it shuts down thedatabase. (This feature was available in V6.0 using the RMU interface.)For information about the SQL syntax, see the ALTER DATABASEStatement, the CREATE DATABASE Statement, and the IMPORTStatement. Asynchronous batch-writesUsing SQL syntax, you can specify that processes write batches of modifieddata pages to disk asynchronously (the process does not stall while waitingfor the batch-write operation to complete). Asynchronous batch-writesimprove the performance of update applications without the loss of dataintegrity. (This feature was available in V6.0 using logical names to specifythe number of buffers used.)xv
For information about the SQL syntax, see the ALTER DATABASEStatement, the CREATE DATABASE Statement, and the IMPORTStatement. Asynchronous prefetchUsing SQL syntax, you can specify whether or not Oracle Rdb reducesthe amount of time that a process waits for pages to be read from disk byfetching pages before a process actually requests the pages.For information about the SQL syntax, see the ALTER DATABASEStatement, the CREATE DATABASE Statement, and the IMPORTStatement. Fast incremental backupUsing SQL syntax, you can specify whether Oracle Rdb checks each area’sSPAM pages or each database page to find changes during incrementalbackup.For information about the SQL syntax, see the ALTER DATABASEStatement, the CREATE DATABASE Statement, and the IMPORTStatement. Support for two new character setsOracle Rdb includes support for two new character sets: BIG5 andTACTIS. BIG5 is a fixed 2-octet character set. TACTIS is a single-octetcharacter set. TRIM built-in functionThe TRIM built-in function lets you remove leading and trailing charactersfrom a character string. POSITION built-in functionThe POSITION built-in function lets you search for a particular substringwithin another string.OpenVMS OpenVMSVAXAlphaxvi INTEGRATE statement has new argumentsOracle Rdb provides a finer level of definition integration between anOracle Rdb database and the CDD/Repository with the introduction ofthe DOMAIN and TABLE arguments to the INTEGRATE statement.In previous versions of Oracle Rdb, the INTEGRATE statement let youintegrate all Oracle Rdb database schema objects with the CDD/Repositorybut did not allow the integration of individual schema objects. WithOracle Rdb V6.1, the INTEGRATE statement lets you select specific OracleRdb schema objects (tables and domains) for integration. However, SQL
continues to let you integrate an entire database with the INTEGRATEstatement when that level of integration is required. SHOW DATABASE statement includes new informationThe output from the SHOW DATABASE statement includes informationabout the new database attributes, such as asynchronous batch-writes andshutdown time. LIKE predicate optimization in SQL queriesOracle Rdb has improved the performance of certain types of LIKEpredicates in SQL queries. Multistring commentsYou can now specify comments that contain more than one string literalseparated by a slash mark ( / ). This was implemented as a workaroundto the limitation that com
This manual uses icons to identify information that is specific to an operating system or platform. Where material pertains to more than one platform or operating system, combination icons or generic icons are used. For example: Digital UNIX This icon denotes the beginning of information specific to the Digital UNIX operating system. OpenVMS .
FAX — 603-897-3825 Attn: Oracle Rdb Postal service: Oracle Corporation Oracle Rdb Documentation One Oracle Drive Nashua, NH 03062-2804 USA If you would like a reply, please give your name, address, telephone number, and (optionally) electronic mail address. If you have problems with the software, please contact your local Oracle .
This manual provides the syntax, semantics, and reference material for Oracle RMU. Intended Audience To use this manual effectively, you should be familiar with data processing procedures, basic database management concepts and terminology, and the OpenVMS operating system. Structure
2.8.3 Using SQL Indirect Command Files . . . . 2-6 2.8.4 Controlling Session Output . . . 2-7 2.8.5 Using Editors with SQL . 2-8 2.8.6 Tailoring the Interactive SQL Environment . 2-9 3 Displaying Information About a Database
By Karen Hanmer Figure 1, All Shook Up, 1998. The foundation of the deceptively simple “flag book” structure is an accordion folded spine. Rows of flags attached to opposing sides of each of the spine’s “mountain” folds allow the artist to fragment and layer a number of complementar
EARTHQUAKE ENGINEERING Proceedings of 14th Symposiumon EarthquakeEngineering held at Indian Institute ofTechnology Roorkee December17-19,2010 Volume2 EDITORS AshwaniKumar M.L.Sharma ElitePublishingHousePvt Ltd 302, JMDHouse, 4-B,Ansari Road, Daryaganj, NewDelh
DAMN YOUR SPAM AKA THE SHUTTERS ARE DOWN cross-currents in culture number 10 spring 2000 free cut out and keep. PAGE 2 VARIANT VOLUME2 NUMBER 10 SPRING 2000 Variant volume 2 Number 10,Spring 2000 ISSN
parameter is interpreted as an delimiter-separated ordered list. For example: volumeName volume1;volume2 . login 10.0.1.2;10.1.2.3;10.2.3.4 . If a parameter contains fewer elements in the list than needed for the composite command, the last one in the list will be repeated. There must be at least on
Siklus Akuntansi Jasa BAGIAN PROYEK PENGEMBANGAN KURIKULUM DIREKTORAT PENDIDIKAN MENENGAH KEJURUAN DIREKTORAT JENDERAL PENDIDIKAN DASAR DAN MENENGAH DEPARTEMEN PENDIDIKAN NASIONAL 2003 Kode Modul: AK.26.D.2,3. BAGIAN PROYEK PENGEMBANGAN KURIKULUM DIREKTORAT PENDIDIKAN MENENGAH KEJURUAN DIREKTORAT JENDERAL PENDIDIKAN DASAR DAN MENENGAH DEPARTEMEN PENDIDIKAN NASIONAL 2003 Kode Modul: AK.26.D.2,3 .
