Programmer's Guide to the Oracle Pro*COBOL Precompiler

What This Manual Has to Offer

Who Should Read This Manual?

How This Manual Is Organized

Conventions Used in This Manual

Notation

Syntax Description

Sample Programs

Does the Pro*COBOL Precompiler Meet Industry Standards?

Your Comments Are Welcome

What Is Pro*COBOL?

Language Alternatives

Why Use the Pro*COBOL Precompiler?

Why Use SQL?

Why Use PL/SQL?

What Does Pro*COBOL Offer?

Key Concepts of Embedded SQL Programming

Embedded SQL Statements

Embedded SQL Syntax

Static versus Dynamic SQL Statements

Embedded PL/SQL Blocks

Host and Indicator Variables

Oracle Datatypes

Tables

Datatype Equivalencing

Private SQL Areas, Cursors, and Active Sets

Transactions

Errors and Warnings

Steps in Developing an Embedded SQL Application

The Format of SQL Statements

INCLUDE Statements

The SQLCA

Oracle8 Datatypes

Declaring and Referencing Host Variables

VARCHAR Variables

Host Variable Guidelines

Declaring and Referencing Indicator Variables

Sample Tables

Sample Data

A Program Example 1: Simple Query

Programming Guidelines

Required Declarations and SQL Statements

Declare Section is Optional

Precompiler Option DECLARE_SECTION

Using the INCLUDE Statement

Caution

Error Handling

Host Variables

Declaring Host Variables

Referencing Host Variables

Nested Programs

Support for Nested Programs

Sample Nested Program

Indicator Variables

Declaring Indicator Variables

Referencing Indicator Variables

Host Tables

Declaring Host Tables

Referencing Host Tables

Using Indicator Tables

VARCHAR Variables

Declaring VARCHAR Variables

Implicit VARCHAR Group Items

Referencing VARCHAR Variables

Handling Character Data

New Default for PIC X

Effects of the PICX Option

Fixed-Length Character Variables

Restrictions When NLS_LOCAL=YES

Variable-Length Variables

Connecting to Oracle

Connecting Using Net8

Automatic Logons

Concurrent Logons

Some Preliminaries

Default Databases and Connections

Explicit Logons

Implicit Logons

Changing Passwords at Runtime

Using the New Connect Syntax

The Oracle8 Datatypes

Internal Datatypes

External Datatypes

Datatype Conversion

Explicit Control Over DATE String Format

Datatype Equivalencing

Why Equivalence Datatypes?

Host Variable Equivalencing

Using the CHARF Datatype Specifier

Guidelines

RAW and LONG RAW Values

Embedding PL/SQL

Host Variables

VARCHAR Variables

Multi-Byte NCHAR Features When NLS_LOCAL=YES

Indicator Variables

SQLCHECK

National Language Support

Multi-Byte NLS Character Sets

Character Strings in Embedded SQL

Embedded DDL

Blank Padding

Indicator Variables

Embedding OCI (Oracle Call Interface) Calls

Setting Up the LDA

Remote and Multiple Connections

Developing X/Open Applications

Oracle-Specific Issues

Using Host Variables

Output versus Input Host Variables

Using Indicator Variables

Input Variables

Output Variables

Inserting Nulls

Handling Returned Nulls

Fetching Nulls

Testing for Nulls

Fetching Truncated Values

The Basic SQL Statements

Using the WHERE Clause

Cursors

Declaring a Cursor

Opening a Cursor

Fetching from a Cursor

Closing a Cursor

Using the CURRENT OF Clause

Restrictions

A Typical Sequence of Statements

Sample Program 2: Cursor Operations

Sample Program 4: Datatype Equivalencing

Advantages of PL/SQL

Better Performance

Integration with Oracle8

Embedding PL/SQL Blocks

Using Host Variables

An Example

A More Complex Example

VARCHAR Pseudotype

Using Indicator Variables

Handling Nulls

Handling Truncated Values

Creating Stored Subprograms

Calling a Stored Subprogram

Sample Program 9: Calling a Stored Procedure

Getting Information about Stored Subprograms

Using Dynamic PL/SQL

Subprograms Restriction

Cursor Variables

Declaring a Cursor Variable

Allocating a Cursor Variable

Opening a Cursor Variable

Fetching from a Cursor Variable

Closing a Cursor Variable

Restrictions

Error Conditions

Sample Programs

The Pro*COBOL Command

What Occurs during Precompilation?

Precompiler Options

Precedence of Option Values

Macro and Micro Options

Using Pro*COBOL Options

Conditional Precompilations

An Example

Defining Symbols

Separate Precompilations

Guidelines

Restrictions

Compiling and Linking

Some Terms You Should Know

How Transactions Guard Your Database

How to Begin and End Transactions

Using the COMMIT Statement

WITH HOLD Clause in DECLARE CURSOR Statements

CLOSE_ON_COMMIT Precompiler Option

Using the ROLLBACK Statement

Statement-Level Rollbacks

Using the SAVEPOINT Statement

Using the RELEASE Option

Using the SET TRANSACTION Statement

Overriding Default Locking

Using the FOR UPDATE OF Clause

Using the LOCK TABLE Statement

Fetching Across Commits

Handling Distributed Transactions

Guidelines

Designing Applications

Obtaining Locks

Using PL/SQL

The Need for Error Handling

Error Handling Alternatives

SQLCODE and SQLSTATE

SQLCA

ORACA

Using Status Variables when MODE={ANSI|ANSI14}

Some Historical Information

Declaring Status Variables

Status Variable Combinations

Status Variable Values

Using the SQL Communications Area

What's in the SQLCA?

Declaring the SQLCA

Key Components of Error Reporting

SQLCA Structure

PL/SQL Considerations

Getting the Full Text of Error Messages

DSNTIAR

Using the WHENEVER Statement

Using the WHENEVER Statement in COBOL

Getting the Text of SQL Statements

Using the Oracle Communications Area

What's in the ORACA?

Declaring the ORACA

Enabling the ORACA

Choosing Runtime Options

ORACA Structure

ORACA Example

What Is a Host Table?

Why Use Tables?

Declaring Host Tables

Dimensioning Tables

Restrictions

Using Tables in SQL Statements

Selecting into Tables

Batch Fetches

Number of Rows Fetched

Restrictions

Fetching Nulls

Fetching Truncated Values

Inserting with Tables

Using Indicator Tables

Using the FOR Clause

Restrictions

Using the WHERE Clause

Mimicking the CURRENT OF Clause

Using SQLERRD(3)

Sample Program 3: Fetching in Batches

What Is Dynamic SQL?

Advantages and Disadvantages of Dynamic SQL

When to Use Dynamic SQL

Requirements for Dynamic SQL Statements

How Dynamic SQL Statements Are Processed

Methods for Using Dynamic SQL

The EXECUTE IMMEDIATE Statement

An Example

Sample Program 6: Dynamic SQL Method 1

Using Method 2

The USING Clause

Sample Program 7: Dynamic SQL Method 2

Sample Program 8: Dynamic SQL Method 3

Using Method 4

Need for the SQLDA

The DESCRIBE Statement

What Is a SQLDA?

Implementing Method 4

Using the DECLARE STATEMENT Statement

Meeting the Special Requirements of Method 4

What Makes Method 4 Special?

What Information Does Oracle8 Need?

Where Is the Information Stored?

How Is the Information Obtained?

Understanding the SQL Descriptor Area (SQLDA)

Handling Null/Not Null Datatypes

The Basic Steps

A Closer Look at Each Step

Declare a Host String

Declare the SQLDAs

Set the Maximum Number to DESCRIBE

Initialize the Descriptors

Store the Query Text in the Host String

PREPARE the Query from the Host String

DECLARE a Cursor

DESCRIBE the Bind Variables

Reset Number of place-holders

Get Values for Bind Variables

OPEN the Cursor

DESCRIBE the Select List

Reset Number of Select-List Items

Reset Length/Datatype of Each Select-List Item

FETCH Rows from the Active Set

Get and Process Select-List Values

CLOSE the Cursor

Using Host Tables with Method 4

Sample Program 10: Dynamic SQL Method 4

What Is a User Exit?

Why Write a User Exit?

Developing a User Exit

Writing a User Exit

Requirements for Variables

The IAF GET Statement

The IAF PUT Statement

Calling a User Exit

Passing Parameters to a User Exit

Returning Values to a Form

The IAP Constants

Using the SQLIEM Function

Using WHENEVER

Sample Program 5: Oracle Forms User Exit

Precompiling and Compiling a User Exit

Using the GENXTB Utility

Linking a User Exit into SQL*Forms

Guidelines for SQL*Forms User Exits

EXEC TOOLS Statements

EXEC TOOLS SET

EXEC TOOLS GET

EXEC TOOLS MESSAGE

DB2 Compatibility Features

Optional DECLARE SECTION

Support of Additional Datatypes

Support of Group Items as Host Variables

Implicit Form of VARCHAR Group Items

Explicit Control Over the END_OF_FETCH SQLCODE Number

Support of the WITH HOLD Clause in the DECLARE CURSOR Statement

New Precompiler Option CLOSE_ON_COMMIT

Support for DSNTIAR

Date String Format Precompiler Option

Any Terminator Allowed after END-EXEC

Other New Features

New Name for Configuration File

Support of Other Additional Datatypes

Support of Nested Programs

Support for REDEFINES and FILLER

New Precompiler Option PICX

Optional CONVBUFSZ Clause in VAR Statement

Improved Error Reporting

Changing Password When Connecting

System-Specific References in this Manual

PIC N Clause for Multi-byte NLS Characters

Oracle8 Reserved Words and Keywords

Oracle8 Reserved Namespaces

What Causes Poor Performance?

How Can Performance be Improved?

Using Host Tables

Using Embedded PL/SQL

Optimizing SQL Statements

Optimizer Hints

Trace Facility

Using Indexes

Taking Advantage of Row-Level Locking

Eliminating Unnecessary Parsing

Handling Explicit Cursors

Using the Cursor Management Options

What Is Syntactic and Semantic Checking?

Controlling the Type and Extent of Checking

Specifying SQLCHECK=SEMANTICS

Enabling a Semantic Check

Summary of Precompiler Directives and Embedded SQL Commands

About The Command Descriptions

How to Read Syntax Diagrams

Statement Terminator

Required Keywords and Parameters

Optional Keywords and Parameters

Syntax Loops

Multi-part Diagrams

Database Objects

ALLOCATE (Executable Embedded SQL Extension)

Purpose

Prerequistes

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

CLOSE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

COMMIT (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keyword and Parameters

Usage Notes

Example

Related Topics

CONNECT (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keyword and Parameters

Usage Notes

Example

Related Topics

DECLARE CURSOR (Embedded SQL Directive)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

DECLARE DATABASE (Oracle Embedded SQL Directive)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

DECLARE STATEMENT (Embedded SQL Directive)

Purpose

Prerequisites

Syntax

Keywords and Parameters

DECLARE TABLE (Oracle Embedded SQL Directive)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

DELETE (Executable Embedded SQL)

Purpose

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

DESCRIBE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

EXECUTE ... END-EXEC (Executable Embedded SQL Extension)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

EXECUTE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Note

Example

Related Topics

EXECUTE IMMEDIATE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

FETCH (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

INSERT (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

OPEN (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

PREPARE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

ROLLBACK (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

SAVEPOINT (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

SELECT (Executable Embedded SQL)

Purpose

Prerequisites

Keywords and Parameters

Usage Notes

Example

Related Topics

UPDATE (Executable Embedded SQL)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Examples

Related Topics

VAR (Oracle Embedded SQL Directive)

Purpose

Prerequisites

Keywords and Parameters

Usage Notes

Example

Related Topics

WHENEVER (Embedded SQL Directive)

Purpose

Prerequisites

Syntax

Keywords and Parameters

Usage Notes

Example

Related Topics

Contents

Title and Copyright Information

Preface

Chapter 1 Introduction

Chapter 2 Learning the Basics

Chapter 3 Writing a Pro*COBOL Program

Chapter 4 Advanced Pro*COBOL Programs

Chapter 5 Using Embedded SQL

Chapter 6 Using Embedded PL/SQL

Chapter 7 Running the Pro*COBOL Precompiler

Chapter 8 Defining and Controlling Transactions

Chapter 9 Error Handling and Diagnostics

Chapter 10 Using Host Tables

Chapter 11 Using Dynamic SQL

Chapter 12 Using Dynamic SQL: Advanced Concepts

Chapter 13 Writing User Exits

Appendix A New Features

Appendix B Operating System Dependencies

Appendix C Oracle8 Reserved Words, Keywords, and Namespaces

Appendix D Performance Tuning

Appendix E Syntactic and Semantic Checking

Appendix F Embedded SQL Commands and Precompiler Directives

Index