Oracle Rdb7(tm)
SQL Reference Manual

• ---

  Chapter 1 Introduction to SQL Syntax
  • 1.1 Conformance to Federal Information Processing Standard
  • 1.2 Using SQL
    • 1.2.1 Invoking SQL Images
  • 1.3 Samples Directory
  • 1.4 How to Read Syntax Diagrams
  • 1.5 Executable and Nonexecutable Statements
  • 1.6 Summary of SQL Statements
  • 1.7 Keywords and Line Terminators
    • 1.7.1 Statement Terminators and Comment Characters
  • 1.8 Support for Multivendor Integration Architecture

• ---

  Chapter 2 Language and Syntax Elements
  • 2.1 Supported Character Sets
    • 2.1.1 Logical Names for Character Sets
    • 2.1.2 Identifier Character Sets
    • 2.1.3 Default Character Sets
    • 2.1.4 National Character Sets
    • 2.1.5 Literal Character Sets
  • 2.2 User-Supplied Names
    • 2.2.1 Database Names
      • 2.2.1.1 Oracle Rdb Attach Specifications
      • 2.2.1.2 Repository Path Names
    • 2.2.2 Aliases
    • 2.2.3 Names in Multischema Databases
    • 2.2.4 Stored Names
    • 2.2.5 Authorization Identifiers
      • 2.2.5.1 Authorization Identifiers and Stored Modules
      • 2.2.5.2 Authorization Identifiers and Nonstored Modules
    • 2.2.6 Connection Names
    • 2.2.7 Catalog Names
    • 2.2.8 Schema Names
    • 2.2.9 Table and View Names
    • 2.2.10 Column Names
      • 2.2.10.1 Correlation Names
      • 2.2.10.2 Outer References
    • 2.2.11 Domain Names
    • 2.2.12 Trigger Names
    • 2.2.13 Index Names
    • 2.2.14 Cursor Names
    • 2.2.15 Constraint Names
    • 2.2.16 Storage Area Names
    • 2.2.17 Storage Map Names
    • 2.2.18 Statement Names (Dynamic SQL Only)
    • 2.2.19 Parameters, Routine Parameters, and SQL Variables
      • 2.2.19.1 Data Parameters and Indicator Parameters
      • 2.2.19.2 Host Structures and Indicator Arrays
      • 2.2.19.3 Multistatement Procedure Variables and Stored Routine Parameters
      • 2.2.19.4 External Routine Parameters
    • 2.2.20 Nonstored Module, Procedure, and Parameter Names (Module Language Only)
  • 2.3 Data Types
    • 2.3.1 Character Data Types
      • 2.3.1.1 Calculating the Maximum Length of a CHAR or VARCHAR Column
    • 2.3.2 DECIMAL and NUMERIC Data Types
    • 2.3.3 Fixed-Point Numeric Data Types
    • 2.3.4 Floating-Point Numeric Data Types
    • 2.3.5 Date-Time Data Types
    • 2.3.6 LIST OF BYTE VARYING Data Type
      • 2.3.6.1 On-Disk Format of Lists
    • 2.3.7 Data Type Conversions
      • 2.3.7.1 Conversion from Unsupported Data Types
      • 2.3.7.2 Conversion Between Supported Data Types
  • 2.4 Literals
    • 2.4.1 Numeric Literals
    • 2.4.2 Character String Literals
      • 2.4.2.1 Quoted Character String Literals
        • 2.4.2.1.1 Quoted Character String Literals Qualified by a Character Set
        • 2.4.2.1.2 Quoted Character String Literals Qualified by the National Character Set
      • 2.4.2.2 Hexadecimal Character String Literals
    • 2.4.3 Date-Time Literals
  • 2.5 SQL and DATATRIEVE Formatting Clauses
    • 2.5.1 QUERY HEADER Clause
    • 2.5.2 EDIT STRING Clause
  • 2.6 Value Expressions
    • 2.6.1 NULL Keyword Used as an Expression
    • 2.6.2 Built-In Functions
      • 2.6.2.1 CHARACTER_LENGTH Function
      • 2.6.2.2 OCTET_LENGTH Function
      • 2.6.2.3 CAST Function
      • 2.6.2.4 UPPER Function
      • 2.6.2.5 LOWER Function
      • 2.6.2.6 TRANSLATE Function
      • 2.6.2.7 SUBSTRING Function
      • 2.6.2.8 EXTRACT Function
      • 2.6.2.9 USER Function
      • 2.6.2.10 CURRENT_USER Function
      • 2.6.2.11 SESSION_USER Function
      • 2.6.2.12 SYSTEM_USER Function
      • 2.6.2.13 CURRENT_DATE Function
      • 2.6.2.14 CURRENT_TIME Function
      • 2.6.2.15 CURRENT_TIMESTAMP Function
      • 2.6.2.16 TRIM Function
      • 2.6.2.17 POSITION Function
    • 2.6.3 Aggregate Functions
      • 2.6.3.1 COUNT Function
      • 2.6.3.2 SUM Function
      • 2.6.3.3 AVG Function
      • 2.6.3.4 MAX Function
      • 2.6.3.5 MIN Function
    • 2.6.4 External Routines
    • 2.6.5 Database Keys
    • 2.6.6 String Concatenation Operator
    • 2.6.7 Arithmetic Expressions and Operators
    • 2.6.8 Conditional Expressions
      • 2.6.8.1 NULLIF Expressions
      • 2.6.8.2 COALESCE and NVL Expressions
      • 2.6.8.3 CASE Expressions
  • 2.7 Predicates
    • 2.7.1 Basic Predicate
    • 2.7.2 BETWEEN Predicate
    • 2.7.3 Complex Predicate
    • 2.7.4 CONTAINING Predicate
    • 2.7.5 EXISTS Predicate
    • 2.7.6 IN Predicate
    • 2.7.7 IS NULL Predicate
    • 2.7.8 LIKE Predicate
    • 2.7.9 Quantified Predicate
    • 2.7.10 SINGLE Predicate
    • 2.7.11 STARTING WITH Predicate
  • 2.8 Select Expressions and Column Select Expressions
    • 2.8.1 Select Expressions
    • 2.8.2 Column Select Expressions
  • 2.9 Context Structures
  • 2.10 Database Options
  • 2.11 Using Context Files with SQL Module Language and SQL Precompiler

• ---

  Chapter 3 SQL Module Language
  • 3.1 Overview of the SQL Module Language and Processor
  • 3.2 SQL Module Language Syntax
  • 3.3 Declaring the Length of Character Parameters
  • 3.4 Equivalent SQL and Host Language Data Types
  • 3.5 SQL Module Language Processor Command Line for OpenVMS
  • 3.6 SQL Module Language Processor Command Line for Digital UNIX

• ---

  Chapter 4 SQL Precompiler
  • 4.1 Embedding SQL Statements in Programs
    • 4.1.1 Embedding Module Clauses in Host Language Code
    • 4.1.2 Using the Two-Phase Commit Protocol in Embedded Programs
  • 4.2 SQL Precompiler Syntax
  • 4.3 SQL Precompiler Command Line for OpenVMS
  • 4.4 SQL Precompiler Command Line for Digital UNIX
  • 4.5 Host Language Variable Declarations Supported by the Precompiler
    • 4.5.1 Specifying Length of Character Parameters
    • 4.5.2 Supported Ada Variable Declarations
    • 4.5.3 Supported C Variable Declarations
    • 4.5.4 Supported COBOL Variable Declarations
    • 4.5.5 Supported FORTRAN Variable Declarations
    • 4.5.6 Supported Pascal Variable Declarations
    • 4.5.7 Supported PL/I Variable Declarations

• ---

  Chapter 5 SQL Routines
  • Command 1 sql_close_cursors
  • Command 2 sql_deregister_error_handler
  • Command 3 sql_get_error_handler
  • Command 4 sql$get_error_text
  • Command 5 sql_get_error_text
  • Command 6 sql_get_message_vector
  • Command 7 sql_register_error_handler
  • Command 8 sql_signal

• ---

  Chapter 6 SQL Statements
  • Command 9 ALTER DATABASE Statement
  • Command 10 ALTER DOMAIN Statement
  • Command 11 ALTER INDEX Statement
  • Command 12 ALTER STORAGE MAP Statement
  • Command 13 ALTER TABLE Statement
  • Command 14 ATTACH Statement
  • Command 15 BEGIN DECLARE Statement
  • Command 16 CALL Statement for Simple Statements
  • Command 17 CALL Statement for Compound Statements
  • Command 18 CASE Control Statement
  • Command 19 CLOSE Statement
  • Command 20 COMMENT ON Statement
  • Command 21 COMMIT Statement
  • Command 22 Compound Statement
  • Command 23 CONNECT Statement
  • Command 24 CREATE CACHE Clause
  • Command 25 CREATE CATALOG Statement
  • Command 26 CREATE COLLATING SEQUENCE Statement
  • Command 27 CREATE DATABASE Statement
  • Command 28 CREATE DOMAIN Statement
  • Command 29 CREATE FUNCTION Statement
  • Command 30 CREATE INDEX Statement
  • Command 31 CREATE MODULE Statement
  • Command 32 CREATE OUTLINE Statement
  • Command 33 CREATE PROCEDURE Statement
  • Command 34 Create Routine Statement
  • Command 35 CREATE SCHEMA Statement
  • Command 36 CREATE STORAGE AREA Clause
  • Command 37 CREATE STORAGE MAP Statement
  • Command 38 CREATE TABLE Statement
  • Command 39 CREATE TRIGGER Statement
  • Command 40 CREATE VIEW Statement
  • Command 41 DECLARE ALIAS Statement
  • Command 42 DECLARE CURSOR Statement
  • Command 43 DECLARE CURSOR Statement, Dynamic
  • Command 44 DECLARE CURSOR Statement, Extended Dynamic
  • Command 45 DECLARE LOCAL TEMPORARY TABLE Statement
  • Command 46 DECLARE MODULE Statement
  • Command 47 DECLARE STATEMENT Statement
  • Command 48 DECLARE TABLE Statement
  • Command 49 DECLARE TRANSACTION Statement
  • Command 50 DECLARE Variable Statement
  • Command 51 DELETE Statement
  • Command 52 DESCRIBE Statement
  • Command 53 DISCONNECT Statement
  • Command 54 DROP CATALOG Statement
  • Command 55 DROP COLLATING SEQUENCE Statement
  • Command 56 DROP CONSTRAINT Statement
  • Command 57 DROP DATABASE Statement
  • Command 58 DROP DOMAIN Statement
  • Command 59 DROP INDEX Statement
  • Command 60 DROP MODULE Statement
  • Command 61 DROP OUTLINE Statement
  • Command 62 DROP PATHNAME Statement
  • Command 63 Drop Routine Statement
  • Command 64 DROP SCHEMA Statement
  • Command 65 DROP STORAGE MAP Statement
  • Command 66 DROP TABLE Statement
  • Command 67 DROP TRIGGER Statement
  • Command 68 DROP VIEW Statement
  • Command 69 EDIT Statement
  • Command 70 END DECLARE Statement
  • Command 71 Execute (@) Statement
  • Command 72 EXECUTE Statement
  • Command 73 EXECUTE IMMEDIATE Statement
  • Command 74 EXIT Statement
  • Command 75 EXPORT Statement
  • Command 76 FETCH Statement
  • Command 77 FOR Control Statement
  • Command 78 GET DIAGNOSTICS Statement
  • Command 79 GRANT Statement
  • Command 80 GRANT Statement, ANSI/ISO-Style
  • Command 81 HELP Statement
  • Command 82 IF Control Statement
  • Command 83 IMPORT Statement
  • Command 84 INCLUDE Statement
  • Command 85 INSERT Statement
  • Command 86 INTEGRATE Statement
  • Command 87 LEAVE Control Statement
  • Command 88 LOOP Control Statement
  • Command 89 OPEN Statement
  • Command 90 Operating System Invocation ($) Statement
  • Command 91 PREPARE Statement
  • Command 92 PRINT Statement
  • Command 93 QUIT Statement
  • Command 94 RELEASE Statement
  • Command 95 RETURN Control Statement
  • Command 96 REVOKE Statement
  • Command 97 REVOKE Statement, ANSI/ISO-Style
  • Command 98 ROLLBACK Statement
  • Command 99 SELECT Statement: General Form
  • Command 100 SELECT Statement: Singleton Select
  • Command 101 SET Statement
  • Command 102 SET ALIAS Statement
  • Command 103 SET ALL CONSTRAINTS Statement
  • Command 104 SET ANSI Statement
  • Command 105 SET CATALOG Statement
  • Command 106 SET CHARACTER LENGTH Statement
  • Command 107 SET CONNECT Statement
  • Command 108 SET Control Statement
  • Command 109 SET DEFAULT CHARACTER SET Statement
  • Command 110 SET DEFAULT DATE FORMAT Statement
  • Command 111 SET DIALECT Statement
  • Command 112 SET FLAGS Statement
  • Command 113 SET HOLD CURSORS Statement
  • Command 114 SET IDENTIFIER CHARACTER SET Statement
  • Command 115 SET KEYWORD RULES Statement
  • Command 116 SET LITERAL CHARACTER SET Statement
  • Command 117 SET NAMES Statement
  • Command 118 SET NATIONAL CHARACTER SET Statement
  • Command 119 SET OPTIMIZATION LEVEL Statement
  • Command 120 SET QUOTING RULES Statement
  • Command 121 SET SCHEMA Statement
  • Command 122 SET TRANSACTION Statement
  • Command 123 SET VIEW UPDATE RULES Statement
  • Command 124 SHOW Statement
  • Command 125 Simple Statement
  • Command 126 SIGNAL Control Statement
  • Command 127 TRACE Control Statement
  • Command 128 TRUNCATE TABLE Statement
  • Command 129 UNDECLARE Variable Statement
  • Command 130 UPDATE Statement
  • Command 131 WHENEVER Statement

• ---

  Appendix A Error Messages
  • A.1 Types of Error Messages and Their Format
  • A.2 Error Message Documentation
  • A.3 Errors Generated When You Use SQL Statements
  • A.4 Identifying Precompiler and Module Language Errors
  • A.5 sqlmsg.mdf - Error Message File for Digital UNIX

• ---

  Appendix B The SQL Communications Area (SQLCA) and the Message Vector
  • B.1 The SQLCA
  • B.2 The Message Vector
  • B.3 Declarations of the SQLCA and the Message Vector

• ---

  Appendix C SQLSTATE
  • C.1 Definition of the SQLSTATE Status Parameter
  • C.2 Use of the SQLSTATE Status Parameter

• ---

  Appendix D The SQL Dynamic Descriptor Areas (SQLDA and SQLDA2)
  • D.1 Purpose of the SQLDA
  • D.2 How SQL and Programs Use the SQLDA
  • D.3 Declaring the SQLDA
  • D.4 Description of Fields in the SQLDA
  • D.5 Parameters Associated with the SQLDA: SQLSIZE and SQLDAPTR
  • D.6 Purpose of the SQLDA2
    • D.6.1 Declaring the SQLDA2
    • D.6.2 Description of Fields in the SQLDA2

• ---

  Appendix E Logical Names and Configuration Parameters Used by SQL

• ---

  Appendix F Obsolete SQL Syntax
  • F.1 Incompatible Syntax
    • F.1.1 Incompatible Syntax Containing the SCHEMA Keyword
      • F.1.1.1 CREATE SCHEMA Meaning Incompatible
      • F.1.1.2 SHOW SCHEMA Meaning Incompatible
      • F.1.1.3 DROP SCHEMA Meaning Incompatible
    • F.1.2 DROP TABLE Now Restricts by Default
    • F.1.3 Database Handle Names Restricted to 25 Characters
    • F.1.4 Deprecated Default Semantics of the ORDER BY Clause
    • F.1.5 Change to EXTERNAL NAMES IS Clause
  • F.2 Deprecated Syntax
    • F.2.1 Command Line Qualifiers
    • F.2.2 Deprecated Interactive SQL Statements
    • F.2.3 Constraint Conformance to the ANSI/ISO SQL Standard
    • F.2.4 Obsolete Keywords
  • F.3 Deprecated Logical Names
    • F.3.1 RDM$BIND_n_DISABLED Logical Names
    • F.3.2 RDB$CHARACTER_SET Logical Name
  • F.4 Reserved Words Deprecated as Identifiers
    • F.4.1 ANSI/ISO 1989 SQL Standard Reserved Words
    • F.4.2 ANSI/ISO 1992 SQL Standard Reserved Words
    • F.4.3 ANSI/ISO SQL3 Reserved Words
    • F.4.4 :VAX MACRO Reserved Symbols
  • F.5 Punctuation Changes
    • F.5.1 Single Quotation Marks Required for String Literals
    • F.5.2 Double Quotation Marks Required for ANSI/ISO SQL Delimited Identifiers
    • F.5.3 Colons Required Before Host Language Variables in SQL Module Language
  • F.6 Suppressing Diagnostic Messages

• ---

  Appendix G Oracle7 SQL Functions

• ---

  Index

• ---

  Examples
  • 2-1 Using Date-Time Data Types
  • 5-1 Using SQL Error Handling Routines
  • 6-1 Adding Columns with Default Values to Tables
  • 6-2 Updating the Database File Using Repository Definitions
  • 6-3 Modifying Repository Definitions Using the INTEGRATE Statement with the ALTER DICTIONARY Clause
  • 6-4 Storing Existing Database File Definitions in the Repository
  • 6-5 Modifying Repository Field Using the INTEGRATE DOMAIN Statement with the ALTER DICTIONARY Clause
  • B-1 Fields in the SQLCA
  • B-2 Including Error Literals in a COBOL Program
  • B-3 Ada SQLCA and Message Vector Declaration
  • B-4 BASIC SQLCA and Message Vector Declaration
  • B-5 C SQLCA and Message Vector Declaration
  • B-6 COBOL SQLCA and Message Vector Declaration
  • B-7 FORTRAN SQLCA and Message Vector Declaration
  • B-8 Pascal SQLCA and Message Vector Declaration
  • B-9 PL/I SQLCA and Message Vector Declaration
  • C-1 Declaring SQLSTATE in a C Program
  • D-1 Declaration of the SQLDA in Ada
  • D-2 Declaration of the SQLDA in BASIC
  • D-3 Declaration of the SQLDA in C
  • D-4 Declaration of the SQLDA in PL/I
  • D-5 Declaration of the SQLDA2 in Ada
  • D-6 Declaration of the SQLDA2 in BASIC
  • D-7 Declaration of the SQLDA2 in C

• ---

  Figures
  • 1-1 Sample Syntax Diagram (FETCH)
  • 2-1 Authorization Identifiers and Stored Modules
  • 2-2 Table with a List Column
  • 2-3 Chained List Format
  • 2-4 Indexed List Format
  • B-1 Fields of the Message Vector

• ---

  Tables
  • 1-Command Line Qualifiers
  • 2-Logical Name Changes
  • 1-1 Summary of SQL Statements
  • 2-1 Supported Character Sets
  • 2-2 Number of Octets Used by Characters in Character Sets
  • 2-3 Character Sets Used for the Identifier Character Set
  • 2-4 Summary of User-Supplied Names Used in SQL
  • 2-5 Stored and SQL Names
  • 2-6 Indicator Parameters and Null Values
  • 2-7 Comparison of SQL Keywords with OpenVMS Data Types
  • 2-8 Interval Qualifiers
  • 2-9 Fields in Year-Month INTERVAL Columns
  • 2-10 Fields in Day-Time INTERVAL Columns
  • 2-11 Format of Text Strings Converted to or from DATE VMS Data Type
  • 2-12 Conversion Rules
  • 2-13 Embedding Quotation Marks in Literals
  • 2-14 CDO Edit Strings Supported by SQL
  • 2-15 Alphabetic and Alphanumeric Replacement Edit String Characters
  • 2-16 Numeric Replacement Edit String Characters
  • 2-17 Alphanumeric Insertion Edit String Characters
  • 2-18 Numeric Insertion Edit String Characters
  • 2-19 Alphanumeric and Numeric Insertion Edit String Characters
  • 2-20 Numeric Floating Insertion Edit String Characters
  • 2-21 Floating-Point, Null Value, and Missing Value Edit String Characters
  • 2-22 Date Replacement Edit String Characters
  • 2-23 Built-In Functions
  • 2-24 Translation Names and Allowable Translations
  • 2-25 Aggregate Functions
  • 2-26 Valid Operators Involving Date-Time and Interval Values
  • 2-27 Conditional Expressions
  • 2-28 SQL Conditional Operators
  • 2-29 Boolean Operator: AND
  • 2-30 Boolean Operator: OR
  • 2-31 Boolean Operator: NOT
  • 2-32 Escape Character Sequences
  • 2-33 Wildcard Characters
  • 2-34 Quantified Predicate Result Table
  • 2-35 Summary of Different Forms of the Select Statement
  • 2-36 Database Options
  • 2-37 VIDA Database Options
  • 3-1 Default Passing Mechanism for Host Languages to SQL Modules
  • 3-2 SQL and Corresponding OpenVMS Data Types for Module Language
  • 3-3 Ada Declarations for SQL Formal Parameter Data Types
  • 3-4 BASIC Declarations for SQL Formal Parameter Data Types
  • 3-5 C Declarations for SQL Formal Parameter Data Types
  • 3-6 COBOL Declarations for SQL Formal Parameter Data Types
  • 3-7 FORTRAN Declarations for SQL Formal Parameter Data Types
  • 3-8 Pascal Declarations for SQL Formal Parameter Data Types
  • 3-9 PL/I Declarations for SQL Formal Parameter Data Types
  • 3-10 Examples of the -- list Qualifier
  • 3-11 Examples of the -- o Qualifier
  • 3-12 Mapping Qualifiers in OpenVMS to Qualifiers in Digital UNIX for SQL Module Language Processor Command Line
  • 4-1 Ending Embedded SQL Statements
  • 4-2 Examples of the -- list Qualifier
  • 4-3 Examples of the -- o Qualifier
  • 4-4 Mapping Qualifiers in OpenVMS to Qualifiers in Digital UNIX for SQL Precompiler Command Line
  • 4-5 Precompiler Data Type Mapping
  • 4-6 Ada Declarations for SQL Data Types
  • 4-7 C Declarations for SQL Data Types
  • 4-8 COBOL Declarations for SQL Data Types
  • 4-9 FORTRAN Declarations for SQL Data Types
  • 4-10 Pascal Declarations for SQL Data Types
  • 4-11 PL/I Declarations for SQL Data Types
  • 5-1 Sections in the Routine Template
  • 5-2 Relationship Between sql_message_vector and RDB$MESSAGE_VECTOR
  • 6-1 Updating Data Definitions While Users Are Attached to the Database
  • 6-2 Updating to Database-Wide Parameters While Users Are Attached to the Database
  • 6-3 ALTER and DROP Statements Causing or Not Causing Stored Routine Invalidation
  • 6-4 Using Temporary Tables
  • 6-5 Availability of Row Data for Triggered Actions
  • 6-6 Classes, Types, and Modes of Cursors
  • 6-7 SQL Privileges for Databases, Tables, Columns, Modules, and External Routines
  • 6-8 Privilege Override Capability
  • 6-9 SQL Statements That Can Be Dynamically Executed
  • 6-10 Logical Names for Internationalization of SET Statements
  • 6-11 Dialect Settings
  • 6-12 Debug Flag Keywords
  • 6-13 Phenomena Permitted at Each Isolation Level
  • 6-14 SQL Share Modes
  • 6-15 Comparison of Row Locking for Updates
  • 6-16 Effects of Lock Specifications on Multiuser Access
  • 6-17 Defaults for the SET and DECLARE TRANSACTION Statements
  • A-1 Explanation of Error Message Severity Codes
  • A-2 SQL Errors Generated at Run Time
  • B-1 Values Returned to the SQLCODE Field
  • B-2 Including the Error Literals File in Programs
  • C-1 SQLSTATE Status Parameter Values---Sorted by SQLSTATE Class and Subclass
  • D-1 Fields in the SQLDA
  • D-2 Codes for SQLTYPE Field of SQLDA and SQLDA2
  • D-3 Fields in the SQLDA2
  • D-4 Codes for Interval Qualifiers in the SQLDA2
  • D-5 Codes for Date-Time Data Types in the SQLDA2
  • D-6 Values for the SQLCHAR_SET_NAME Field
  • E-1 Summary of SQL Logical Names and Configuration Parameters
  • E-2 Valid Equivalence Names for RDB$CHARACTER_SET Logical Name
  • F-1 Deprecated Keywords for SQL
  • F-2 Obsolete SQL Keywords
  • F-3 Logical Name Changes
  • G-1 Built-In Oracle7 SQL Functions
  • G-2 Optional Oracle7 SQL Functions

Library Product

Copyright ©1996, Oracle Corporation All rights reserved.