| Programmer's Guide to the Oracle Precompilers, 1.8 |  Contents |
 Index |
| Programmer's Guide to the Oracle Precompilers, 1.8 | Contents |
Index |
This manual is a comprehensive user's guide and on-the-job reference to the Oracle Pro*COBOL and Pro*FORTRAN Precompilers. It shows you step-by-step how to develop applications that use the powerful database language SQL to access and manipulate Oracle data. It explores a full range of topics--from underlying concepts to advanced programming techniques--and uses clear, hands-on examples to teach you all you need to know.
This guide shows you how the Oracle Precompilers and embedded SQL can benefit your entire applications development process. It gives you the know-how to design and develop applications that harness the power of Oracle. And, as quickly as possible, it helps you become proficient in writing embedded SQL programs.
An important feature of this guide is its emphasis on getting the most out of the Oracle Precompilers and embedded SQL. To help you master these tools, this guide shows you all the "tricks of the trade" including ways to improve program performance. It also includes many program examples to better your understanding and demonstrate the usefulness of embedded SQL.
Note: You will not find installation instructions or system-specific information in this guide. For information about migrating your applications from Oracle Version 6 to Oracle7, see Appendix E.
Anyone developing new applications or converting existing applications to run in the Oracle7 environment will benefit from reading this guide. Written especially for programmers, this comprehensive treatment of the Oracle Precompilers will also be of value to systems analysts, project managers, and others interested in embedded SQL applications.
To use this guide effectively, you need a working knowledge of the following subjects:
Release 1.8 of the Oracle Precompilers introduces a new command-line option, UNSAFE_NULL. With UNSAFE_NULL=YES, you can disable ORA-01405 messages when precompiling applications that fetch data into host variables that do not have associated indicator variables.
For more information, see Appendix A.
This guide contains eleven chapters and five appendices. Chapters 1 and 2 give you your bearings, then Chapters 3, 4, 5, and 6 lead you through the essentials of embedded SQL programming. After reading these chapters, you will be able to write and run useful embedded SQL applications. Chapters 7, 8, 9, 10, and 11 cover advanced topics. A brief summary of what you will find in each chapter and appendix follows.
Chapter 1: Getting Acquainted This chapter introduces you to the Oracle Precompilers. You look at their role in developing application programs that manipulate Oracle data and find out what they allow your applications to do.
Chapter 2: Learning the Basics This chapter explains how embedded SQL programs do their work. You examine the special environment in which they operate, the impact of this environment on the design of your applications, the key concepts of embedded SQL programming, and the steps you take in developing an application.
Chapter 3: Meeting Program RequirementsThis chapter shows you how to meet embedded SQL program requirements. You learn the embedded SQL commands that declare variables, declare communications areas, and connect to an Oracle database. You also learn about the Oracle datatypes, National Language Support (NLS), data conversion, and how to take advantage of datatype equivalencing. In addition, this chapter shows you how to embed Oracle Call Interface (OCI) calls in your program and how to develop X/Open applications.
Chapter 4: Using Embedded SQL This chapter teaches you the essentials of embedded SQL programming. You learn how to use host variables, indicator variables, cursors, cursor variables, and the fundamental SQL commands that insert, update, select, and delete Oracle data.
Chapter 5: Using Embedded PL/SQLThis chapter shows you how to improve performance by embedding PL/SQL transaction processing blocks in your program. You learn how to use PL/SQL with host variables, indicator variables, cursors, stored subprograms, host arrays, and dynamic SQL.
Chapter 6: Running the Oracle Precompilers This chapter details the requirements for running an Oracle Precompiler. You learn what happens during precompilation, how to issue the precompiler command, how to specify the many useful precompiler options, how to do conditional and separate precompilations, and how to embed OCI calls in your host program.
Chapter 7: Defining and Controlling Transactions This chapter describes transaction processing. You learn the basic techniques that safeguard the consistency of your database.
Chapter 8: Handling Runtime Errors This chapter provides an in-depth discussion of error reporting and recovery. You learn how to detect and handle errors using the status variable SQLSTATE, the SQLCA structure, and the WHENEVER statement. You also learn how to diagnose problems using the ORACA.
Chapter 9: Using Host Arrays This chapter looks at using arrays to improve program performance. You learn how to manipulate Oracle data using arrays, how to operate on all the elements of an array with a single SQL statement, and how to limit the number of array elements processed.
Chapter 10: Using Dynamic SQL This chapter shows you how to take advantage of dynamic SQL. You are taught four methods--from simple to complex--for writing flexible programs that, among other things, let users build SQL statements interactively at run time.
Chapter 11: Writing User Exits This chapter focuses on writing user exits for your SQL*Forms or Oracle Forms applications. First, you learn the commands that allow a Forms application to interface with user exits. Then, you learn how to write and link a Forms user exit.
Appendix A: New Features This appendix highlights the improvements and new features introduced with Release 1.8 of the Oracle Precompilers.
Appendix B: Oracle Reserved Words, Keywords, and Namespaces This appendix lists words that have a special meaning to Oracle and namespaces that are reserved for Oracle libraries.
Appendix C: Performance Tuning This appendix gives you some simple, easy-to-apply methods for improving the performance of your applications.
Appendix D: Syntactic and Semantic Checking This appendix shows you how to use the SQLCHECK option to control the type and extent of syntactic and semantic checking done on embedded SQL statements and PL/SQL blocks.
Appendix E: Migrating to Oracle7 Oracle7 conforms fully to the new ANSI/ISO SQL standard. As a result, Oracle7 and Oracle Version 6 behave differently in a few areas. By pointing out those areas, this appendix helps you migrate your application programs to Oracle7.
Appendix F: Embedded SQL Commands and Directives This appendix contains descriptions of precompiler directives, embedded SQL commands, and Oracle embedded SQL extensions. These commands are prefaced in your source code with the keywords, EXEC SQL.
Important terms being defined for the first time are italicized. In discussions, UPPER CASE is used for database objects and SQL keywords, and italicized lower case is used for the names of variables, constants, and parameters.
With a few exceptions, program examples are written in pseudocode to avoid language-specific issues. Oriented to professional programmers, the pseudocode is somewhat formal. In the following example, a mail order for books is processed:
-- process book order
IF prepaid OR (credit_rating > 2) THEN
set order_total = 0; -- initialize order_total
FOR EACH book
IF catalog_number is valid THEN
EXEC SQL SELECT QTY INTO :quantity_on_hand
FROM STOCK
WHERE CATNO = :catalog_number;
IF quantity_on_hand > 0 THEN
set line_price = catalog_price;
set line_total = line_price * quantity;
add line_total to order_total;
subtract quantity from quantity_on_hand;
create order_line;
ELSE
create back_order;
ENDIF;
ELSE
display 'Invalid catalog number';
ENDIF;
ENDFOR;
create customer_order;
ELSE
display 'Credit rating too low';
ENDIF;
This example has the following important features:
The following notation is used in this guide: < >
Angle brackets enclose the name of a syntactic element.
.
A dot separates an object name from a component name and so qualifies a reference.
. .
Two dots separate the lowest and highest values in a range.
. . .
An ellipsis shows that statements or clauses irrelevant to the discussion were left out.
#
This character is used in text to represent blank spaces when referring to the content of a database column.
Embedded SQL syntax is described using a variant of Backus-Naur Form (BNF), which includes the following symbols: [ ]
Brackets enclose optional items.
{ }
Braces enclose items only one of which is required.
|
A vertical bar separates alternatives within brackets or braces.
. . .
An ellipsis shows that the preceding parameter can be repeated.
The Pro*COBOL and Pro*FORTRAN Precompilers, Release 1.8, comply fully with the ANSI/ISO SQL standards. Compliance with these standards was certified by the National Institute of Standards and Technology (NIST). To flag extensions to ANSI/ISO SQL, a FIPS Flagger is provided.
ANSI standard X3.135-1992 (known informally as SQL92) provides three levels of compliance:
ANSI standard X3.168-1992 specifies the syntax and semantics for embedding SQL statements in application programs written in standard programming languages such as COBOL-74, COBOL-85, and FORTRAN77.
A conforming SQL implementation must support at least Entry SQL. The Oracle Pro*COBOL and Pro*FORTRAN Precompilers do conform to Entry SQL92.
NIST standard FIPS PUB 127-1, which applies to RDBMS software acquired for federal use, also adopts the ANSI standards. In addition, it specifies minimum sizing parameters for database constructs and requires a "FIPS Flagger" to identify ANSI extensions.
For copies of the ANSI standards, write to
American National Standards Institute
1430 Broadway
New York, NY 10018
USA
The Pro*COBOL Precompiler provides National Language Support (NLS) of multi-byte character data by complying with the Multivendor Integration Architecture (MIA) specification, Version 1.3, and the Service Providers Integrated Requirements for Information Technology (SPIRIT) specification, Issue 2.
The Oracle Corporation technical staff values your comments. As we write and revise, your opinions are the most important feedback we receive. Please use the Reader's Comment Form at the back of this guide to tell us what you like and dislike about this Oracle publication. If you prefer, you can fax us at (415) 506-7200 or write to us at the following address:
Languages Documentation Manager
Oracle Corporation
500 Oracle Parkway
Redwood Shores, CA 94065
Fax: (415) 506-7200
| |
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
Contents |
Index |