Preface

This manual is a comprehensive user's guide and reference to the Oracle Pro*COBOL Precompiler. It shows you how to develop COBOL programs that use the database languages SQL and PL/SQL to access and manipulate Oracle data. See Oracle9i SQL Reference and PL/SQL User's Guide and Reference for more information on SQL and PL/SQL.

This preface contains these topics:

Audience

The Pro*COBOL Precompiler Programmer's Guide is intended for anyone developing new COBOL applications or converting existing applications to run in the Oracle9i environment. Written especially for programmers, this comprehensive treatment of Pro*COBOL will also be of value to systems analysts, project managers, and others interested in embedded SQL applications.

To use this manual effectively, you need a working knowledge of the following subjects:

Applications programming in COBOL
The SQL database language
Oracle9i concepts and terminology

In This Manual

This manual shows you how the Oracle Pro*COBOL Precompiler and embedded SQL can benefit your entire applications development process. It gives you lessons in 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 manual is its emphasis on getting the most out of Pro*COBOL and embedded SQL. To help you master these tools, this manual shows you key methodology, 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 manual. For that kind of information, refer to your system-specific Oracle documentation

For information about migrating your applications to Oracle9i, see Oracle9i Database Migration.

Organization

This book presents a programmer's guide to Pro*COBOL in fourteen chapters and six appendixes:

Chapter 1, Introduction

This chapter introduces you to Pro*COBOL. You look at its role in developing application programs that manipulate Oracle data and find out what are its key benefits and features.

Chapter 2, Precompiler Concepts

This chapter explains how embedded SQL programs work. Then the guidelines for programming in Pro*COBOL are presented. Compilation issues are discussed and the sample Oracle tables used in this guide are presented, as is the first of the demo programs, SAMPLE1.PCO.

Chapter 3, Database Concepts

This chapter describes transaction processing. You learn the basic techniques that safeguard the consistency of your database. You then learn how to connect to a database and how to connect to multiple distributed databases.

Chapter 4, Datatypes and Host Variables

The internal and external datatypes are defined at length. Then you are shown how to use the datatypes in your COBOL program. Then runtime contexts and ROWIDs are explained, followed by Globalization Support, datatype conversion and datatype equivalencing (with a sample program).

Chapter 5, 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 6, Embedded PL/SQL

This 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 in either PL/SQL or Java, host tables, and dynamic PL/SQL.

Chapter 7, Host Tables

This chapter looks at using host (COBOL) tables to improve program performance. You learn how to manipulate Oracle data using tables, how to operate on all the elements of a table with a single SQL statement, and how to limit the number of table elements processed.

Chapter 8, Error Handling and Diagnostics

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, Oracle Dynamic SQL

This chapter shows you how to take advantage of dynamic SQL. You are taught three methods, from simple to complex, for writing flexible programs that let users build SQL statements interactively at run time.

Chapter 10, ANSI Dynamic SQL

ANSI Dynamic SQL, Method 4, is presented. This method supports all Oracle datatypes, while the older Oracle Method 4 does not support cursor variables, tables of group items, DML Returning Clause, and LOBs. ANSI Method 4 uses embedded SQL statements that set up descriptor areas in memory. ANSI SQL should be used for all new applications.

Chapter 11, Oracle Dynamic SQL: Method 4

This chapter shows you how to maintain existing applications that use dynamic SQL Method 4. Numerous examples are used to illustrate the method.

Chapter 12, Multithreaded Applications

How to write multithreaded applications is discussed in this chapter. (Your compiler must support multithreading.)

Chapter 13, Large Objects (LOBs)

This chapter presents large object datatypes (BLOBs, CLOBs, NCLOBs, and BFILEs). The embedded SQL commands that provide functionality comparable to OCI and PL/SQl are presented and used in sample code.

Chapter 14, Precompiler Options

This chapter details the requirements for running the Pro*COBOL precompiler and lists the precompiler options. You learn what happens during precompilation, how to issue the Pro*COBOL command, and how to specify the many useful precompiler options.

Appendix A, New Features

This appendix highlights the improvements and new features introduced in releases 8.1 and 8.0 of Pro*COBOL.

Appendix B, Operating System Dependencies

Some details of Pro*COBOL programming vary from one system to another. Therefore, you are occasionally referred to other manuals for system-specific information. For convenience, this appendix collects all such external issues.

Appendix C, Reserved Words, Keywords, and Namespaces

This appendix refers you to a table of reserved words that have a special meaning to Pro*COBOL, and presents the namespaces that are reserved for Oracle libraries.

Appendix D, Performance Tuning

This appendix gives you some simple methods for improving the performance of your applications.

Appendix E, 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 F, Embedded SQL Statements and Precompiler Directives

This appendix contains descriptions of precompiler directives, embedded SQL commands, and Oracle embedded SQL extensions. The purpose, prerequisites, syntax diagrams, keywords, parameters, usage notes, examples, and related topics are presented for each statement and directive.

Sample Pro*COBOL Programs

This manual provides several Pro*COBOL programs to help you in writing your own. These programs illustrate the key concepts and features of Pro*COBOL programming and demonstrate techniques that let you take full advantage of SQL's power and flexibility.

Each complete sample program in this manual is available on-line in the demo directory. However, the exact filenames are system-dependent. For exact filenames, see your Oracle system-specific documentation. We present sample code developed for the Solaris operating system in this manual.

The Pro*COBOL Precompiler and Industry Standards

SQL has become the standard language for relational database management systems. This section describes how the Pro*COBOL Precompiler conforms to the latest SQL standards established by the following organizations:

American National Standards Institute (ANSI)
International Standards Organization (ISO)
U.S. National Institute of Standards and Technology (NIST)

Those organizations have adopted SQL as defined in the following publications:

ANSI Document ANSI X3.135-1992, Database Language SQL
ANSI Document ANSI X3.168-1992, Database Language Embedded SQL
International Standard ISO/IEC 9075:1992, Database Language SQL
NIST Federal Information Processing Standard FIPS PUB 127-2, Database Language SQL

Requirements

ANSI X3.135-1992 (known informally as SQL92) specifies a "conforming SQL language" and, to allow implementation in stages, defines three language levels:

Full SQL
Intermediate SQL (a subset of Full SQL)
Entry SQL (a subset of Intermediate SQL)

A conforming SQL implementation must support at least Entry SQL.

ANSI X3.168-1992 specifies the syntax and semantics for embedding SQL statements in application programs written in a standard programming language such as COBOL-74 and COBOL-85.

ISO/IEC 9075-1992 fully adopts the ANSI standards.

FIPS PUB 127-2, which applies to RDBMS software acquired for federal use, also adopts the ANSI/ISO 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

 http://www.ansi.org

For a copy of the ISO standard, write to the national standards office of any ISO participant. For a copy of the NIST standard, write to:

National Technical Information Service

U.S. Department of Commerce

Springfield, VA 22161, USA

    http://www.nist.gov

Compliance

The Pro*COBOL precompiler complies 100% with the ANSI, ISO, and NIST standards. As required, they support Entry SQL and provide a FIPS Flagger.

FIPS Flagger

According to FIPS PUB 127-1:

"An implementation that provides additional facilities not specified by this standard shall also provide an option to flag nonconforming SQL language or conforming SQL language that may be processed in a nonconforming manner."

To meet this requirement, the Pro*COBOL Precompiler provides the FIPS Flagger, which flags ANSI extensions. An extension is any SQL element that violates ANSI format or syntax rules, except rules enforcing privileges. For a list of Oracle extensions to standard SQL, see the Oracle9i SQL Reference.

You can use the FIPS Flagger to identify

Nonconforming SQL elements that might have to be modified if you move the application to a conforming environment
Conforming SQL elements that might behave differently in another processing environment

Thus, the FIPS Flagger helps you develop portable applications.

FIPS Option

An option named FIPS governs the FIPS Flagger. To enable the FIPS Flagger, you specify FIPS=YES inline or on the command line. For more information about the command-line option FIPS, see "FIPS".

Certification

The NIST tested the Pro*COBOL Precompiler for ANSI Entry SQL compliance using the SQL Test Suite, which consists of nearly 300 test programs. Specifically, the programs tested for conformance to the COBOL embedded SQL standards. As a result, the Pro*COBOL Precompiler was certified 100% ANSI-compliant.

For more information about the tests, write to

National Computer Systems Laboratory

Attn.: Software Standards Testing Program

National Institute of Standards and Technology

Gaithersburg, MD 20899

USA

       http://www.nist.gov

MIA/SPIRIT

The Pro*COBOL Precompiler provides Globalization Support (formerly called National Language Support or NLS) of multibyte 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.

Conventions

This section describes the conventions used in the text and code examples of the this documentation set. It describes:

Conventions in Text

We use various conventions in text to help you more quickly identify special terms. The following table describes those conventions and provides examples of their use.

Convention	Meaning	Example
Bold	Bold typeface indicates terms that are defined in the text or terms that appear in a glossary, or both.	The C datatypes such as ub4, sword, or OCINumber are valid. When you specify this clause, you create an index-organized table.
Italics	Italic typeface indicates book titles, emphasis, syntax clauses, or placeholders.	Oracle9i Database Concepts You can specify the parallel_clause. Run `U``old_release``.SQL` where old_release refers to the release you installed prior to upgrading.
UPPERCASE monospace (fixed-width font)	Uppercase monospace typeface indicates elements supplied by the system. Such elements include parameters, privileges, datatypes, RMAN keywords, SQL keywords, SQL*Plus or utility commands, packages and methods, as well as system-supplied column names, database objects and structures, user names, and roles.	You can specify this clause only for a `NUMBER` column. You can back up the database using the `BACKUP` command. Query the `TABLE_NAME` column in the `USER_TABLES` data dictionary view. Specify the `ROLLBACK_SEGMENTS` parameter. Use the `DBMS_STATS`.`GENERATE_STATS` procedure.
lowercase monospace (fixed-width font)	Lowercase monospace typeface indicates executables and sample user-supplied elements. Such elements include computer and database names, net service names, and connect identifiers, as well as user-supplied database objects and structures, column names, packages and classes, user names and roles, program units, and parameter values.	Enter `sqlplus` to open SQL*Plus. The `department_id`, `department_name`, and `location_id` columns are in the `hr.departments` table. Set the `QUERY_REWRITE_ENABLED` initialization parameter to `true.` Connect as `oe` user.

Conventions in Code Examples

Code examples illustrate SQL, PL/SQL, SQL*Plus, or other command-line statements. They are displayed in a monospace (fixed-width) font and separated from normal text as shown in this example:

SELECT username FROM dba_users WHERE username = 'MIGRATE';

The following table describes typographic conventions used in code examples and provides examples of their use.

Convention	Meaning	Example
[ ]	Brackets enclose one or more optional items. Do not enter the brackets.	`DECIMAL (digits [ , precision ])`
{ }	Braces enclose two or more items, one of which is required. Do not enter the braces.	`{ENABLE \| DISABLE}`
\|	A vertical bar represents a choice of two or more options within brackets or braces. Enter one of the options. Do not enter the vertical bar.	`{ENABLE \| DISABLE}` `[COMPRESS \| NOCOMPRESS]`
`...`	Horizontal ellipsis points indicate either: That we have omitted parts of the code that are not directly related to the example That you can repeat a portion of the code	`CREATE TABLE ... AS subquery;` `SELECT col1, col2, ... , col``n` `FROM employees;`
. . .	Vertical ellipsis points indicate that we have omitted several lines of code not directly related to the example.
Other notation	You must enter symbols other than brackets, braces, vertical bars, and ellipsis points as it is shown.	`acctbal NUMBER(11,2);` `acct CONSTANT NUMBER(4) := 3;`
`Italics`	Italicized text indicates variables for which you must supply particular values.	`CONNECT SYSTEM/``system_password`
`UPPERCASE`	Uppercase typeface indicates elements supplied by the system. We show these terms in uppercase in order to distinguish them from terms you define. Unless terms appear in brackets, enter them in the order and with the spelling shown. However, because these terms are not case sensitive, you can enter them in lowercase.	`SELECT last_name, employee_id FROM employees;` `SELECT * FROM USER_TABLES;` `DROP TABLE hr.employees;`
`lowercase`	Lowercase typeface indicates programmatic elements that you supply. For example, lowercase indicates names of tables, columns, or files.	`SELECT last_name, employee_id FROM employees;` `sqlplus hr/hr`

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

JAWS, a Windows screen reader, may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, JAWS may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle Corporation does not own or control. Oracle Corporation neither evaluates nor makes any representations regarding the accessibility of these Web sites.