Oracle Rdb7(tm) Guide to SQL Programming

Oracle Rdb7^tm

Guide to SQL Programming

Release 7.0

Part No. A42867-1

Corporate Copyright Notice

IMPORTANT - READ CAREFULLY BEFORE VIEWING AND/OR USING THIS DOCUMENTATION IN ANY WAY. NO PART OF THIS DOCUMENTATION MAY BE REPRODUCED OR TRANSMITTED IN ANY FORM OR BY ANY MEANS, ELECTRONIC OR MECHANICAL, FOR ANY PURPOSE, WITHOUT THE EXPRESS WRITTEN PERMISSION OF ORACLE CORPORATION.

This software 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 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.

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 disclosure 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 the 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 is a registered trademark of Oracle Corporation, Redwood City, California. Oracle CDD/Repository, Oracle Rally, Oracle Rdb, and Rdb7 are trademarks of Oracle Corporation, Redwood City, California.

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

Send Us Your Comments

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.

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 service

Oracle Corporation 
Oracle Rdb Documentation 
One Oracle Drive
Nashua, NH  03062
USA

If you like, you can use the following questionnaire to give us feedback. (Edit the online release notes file, extract a copy of this questionnaire, and send it to us.)

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).

Preface

About This Manual

This manual describes how to design and develop host language application programs that use SQL (structured query language) to store, modify, and retrieve data from Oracle Rdb databases.

Most businesses need to carefully manage information to remain viable. They must find a way to collect, store, and process the information essential to their businesses. They must ensure a way to keep data secure, consistent, and up-to-date. These days, more and more companies are turning to industry-standard database software tools, such as SQL, to provide such support.

Intended Audience

This manual is intended for programmers who write and maintain database applications. To profit fully from this manual, you should already understand the following:

Programming in one or more host languages
SQL language syntax
Basic concepts and terminology of relational database management systems

If you are unfamiliar with SQL, you should begin by reading the Oracle Rdb7 Introduction to SQL before proceeding. That companion manual introduces you to the SQL interface.

How This Manual Is Organized

In this manual, chapters are grouped by category to make accessing information easier. Brief descriptions of each chapter are shown in the following table:

SQL Programming Overview
Chapter 1	Introduces the SQL programming interfaces.
Developing Application Programs That Use SQL
Chapter 2	Provides general information about developing host language programs and describes methods for including SQL statements in programs.
Chapter 3	Introduces the SQL module processor.
Chapter 4	Describes the common elements in writing a procedure in SQL module language and explains how to write single-statement and multistatement SQL module procedures.
Chapter 5	Describes how to process SQL modules and host language files to create an executable image.
Chapter 6	Describes how to embed SQL statements in host language source files and how to process those files using the SQL precompiler.
Chapter 7	Describes how to create an executable image by linking object modules and how to debug and run your program.
Chapter 8	Provides information on declaring and using parameters in host language source files.
Chapter 9	Describes how to use date-time data types in programs.
Run-Time Processing
Chapter 10	Describes how to detect run-time errors, retrieve error messages, and either recover from errors or roll back a transaction.
Chapter 11	Describes how to use dynamic SQL.
Programmatic Structures
Chapter 12	Describes how to use compound statements.
Chapter 13	Describes how to create and use stored procedures and functions.
Chapter 14	Describes how to create and use external procedures and functions.
Your Program's Context
Chapter 15	Describes how to attach to and detach from databases, including databases that reside on remote nodes.
Chapter 16	Describes how to specify and start a transaction and discusses options for data access.
Chapter 17	Introduces the concept of SQL connections for use in querying, testing, and prototyping programs.
Data Manipulation in Programs
Chapter 18	Describes table and list cursors and how to use cursors to retrieve data.
Chapter 19	Describes how to insert, update, and delete data in a database.
Chapter 20	Describes how to write programs for multischema databases.
Appendix A	Describes the SQL options for handling international data that is not in English and provides information about collating sequences used by Oracle Rdb.

Related Manuals

For more information on Oracle Rdb, see the other manuals in the documentation set, especially the following:

Oracle Rdb7 Introduction to SQL
Oracle Rdb7 SQL Reference Manual

Refer to the Oracle Rdb7 Release Notes for descriptions of all manuals in the Oracle Rdb documentation set.

SQL Standards

SQL is both a data definition (DDL) and data manipulation (DML) language for relational databases. Using the SQL interface, you can create a database, load it with data, and read and update both data and data definitions. The SQL interface to Oracle Rdb conforms to the entry-level of the SQL standard ANSI X3.135-1992, ISO 9075:1992, commonly referred to as the ANSI/ISO SQL standard or SQL92.

Conventions

In this manual, Oracle Rdb refers to Oracle Rdb for OpenVMS and software. Version 7.0 of Oracle Rdb software is often referred to as V7.0.

Oracle CDD/Repository software is referred to as the dictionary, the data dictionary, or the repository.

OpenVMS means both the OpenVMS Alpha and OpenVMS VAX operating system.

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:
This icon denotes the beginning of information specific to the Digital UNIX operating system.

This icon combination denotes the beginning of information specific to both the OpenVMS VAX and OpenVMS Alpha operating systems.

The diamond symbol denotes the end of a section of information specific to an operating system or platform.

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

Examples do not always include prompts. Generally, prompts are shown when depicting interactive sequences exactly; otherwise, they are omitted.

The following conventions are also used in this manual:

e, f, t Index entries in the printed manual may have a lowercase e, f, or t following the page number; the e, f, or t is a reference to the example, figure, or table, respectively, on that page.

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

... Horizontal ellipsis points in statements mean that parts of the statement not directly related to the example have been omitted.

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

< > Angle brackets enclose user-supplied names.

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

$ The dollar sign represents the DIGITAL Command Language prompt in OpenVMS and the Bourne shell prompt in Digital UNIX.

Next | Contents

Library
Product

e, f, t	Index entries in the printed manual may have a lowercase e, f, or t following the page number; the e, f, or t is a reference to the example, figure, or table, respectively, on that page.
. . .	Vertical ellipsis points in an example mean that information not directly related to the example has been omitted.
...	Horizontal ellipsis points in statements mean that parts of the statement not directly related to the example have been omitted.
boldface text	Boldface type in text indicates a term defined in the text.
< >	Angle brackets enclose user-supplied names.
[ ]	Brackets enclose optional clauses from which you can choose one or none.
$	The dollar sign represents the DIGITAL Command Language prompt in OpenVMS and the Bourne shell prompt in Digital UNIX.

Oracle Rdb7tm