Pro*COBOL Precompiler Programmer's Guide
Release 8.0
A58232-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

What This Manual Has to Offer

Who Should Read This Manual?

How This Manual Is Organized

Conventions Used in This Manual

Syntax Description

Sample Programs

Does the Pro*COBOL Precompiler Meet Industry Standards?

Your Comments Are Welcome

1 Introduction

What Is Pro*COBOL?

Language Alternatives

Why Use the Pro*COBOL Precompiler?

Why Use PL/SQL?

What Does Pro*COBOL Offer?

2 Learning the Basics

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

Datatype Equivalencing

Private SQL Areas, Cursors, and Active Sets

Errors and Warnings

Steps in Developing an Embedded SQL Application

The Format of SQL Statements

INCLUDE Statements

Oracle8 Datatypes

Declaring and Referencing Host Variables

VARCHAR Variables

Host Variable Guidelines

Declaring and Referencing Indicator Variables

A Program Example 1: Simple Query

3 Writing a Pro*COBOL Program

Programming Guidelines

Case-insensitivity

Continuation Lines

Embedded SQL Syntax

Figurative Constants

Host Variable Names

Hyphenated Names

MAXLITERAL Default

Multi-Byte (NCHAR) Datatypes

When NLS_LOCAL=YES

Paragraph Names

REDEFINES Clause

Relational Operators

Sentence Terminator

FILLER is Allowed

Required Declarations and SQL Statements

Declare Section is Optional

Precompiler Option DECLARE_SECTION

Using the INCLUDE Statement

Declaring Host Variables

Referencing Host Variables

Nested Programs

Support for Nested Programs

Sample Nested Program

Indicator Variables

Declaring Indicator Variables

Referencing Indicator Variables

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 Connect Syntax

4 Advanced Pro*COBOL Programs

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

RAW and LONG RAW Values

Embedding PL/SQL

VARCHAR Variables

Multi-Byte NCHAR Features When NLS_LOCAL=YES

Indicator Variables

National Language Support

Multi-Byte NLS Character Sets

Character Strings in Embedded SQL

Indicator Variables

Embedding OCI (Oracle Call Interface) Calls

Setting Up the LDA

Remote and Multiple Connections

Developing X/Open Applications

Oracle-Specific Issues

5 Using Embedded SQL

Using Host Variables

Output versus Input Host Variables

Using Indicator Variables

Input Variables

Output Variables

Inserting Nulls

Handling Returned Nulls

Testing for Nulls

Fetching Truncated Values

The Basic SQL Statements

Using Subqueries

Using the WHERE Clause

Declaring a Cursor

Opening a Cursor

Fetching from a Cursor

Closing a Cursor

Using the CURRENT OF Clause

A Typical Sequence of Statements

Sample Program 2: Cursor Operations

Sample Program 4: Datatype Equivalencing

6 Using Embedded PL/SQL

Advantages of PL/SQL

Better Performance

Integration with Oracle8

Cursor FOR Loops

User-defined Records

Embedding PL/SQL Blocks

Using Host Variables

A More Complex Example

VARCHAR Pseudotype

Using Indicator Variables

Handling Truncated Values

Using Host Tables

ARRAYLEN Statement

Optional Keyword EXECUTE

Stored Subprograms

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

Error Conditions

Sample Programs

7 Running the Pro*COBOL Precompiler

The Pro*COBOL Command

What Occurs during Precompilation?

Precompiler Options

Precedence of Option Values

Macro and Micro Options

Case Sensitivity

Configuration Files

Entering Options

On the Command Line

Scope of Options

Quick Reference

Using Pro*COBOL Options

CLOSE_ON_COMMIT

DECLARE_SECTION

Conditional Precompilations

Defining Symbols

Separate Precompilations

Compiling and Linking

8 Defining and Controlling Transactions

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

Designing Applications

Obtaining Locks

9 Error Handling and Diagnostics

The Need for Error Handling

Error Handling Alternatives

SQLCODE and SQLSTATE

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

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

10 Using Host Tables

What Is a Host Table?

Why Use Tables?

Declaring Host Tables

Dimensioning Tables

Using Tables in SQL Statements

Selecting into Tables

Number of Rows Fetched

Fetching Truncated Values

Inserting with Tables

Updating with Tables

Deleting with Tables

Using Indicator Tables

Using the FOR Clause

Using the WHERE Clause

Mimicking the CURRENT OF Clause

Using SQLERRD(3)

Sample Program 3: Fetching in Batches

11 Using Dynamic SQL

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

Sample Program 6: Dynamic SQL Method 1

The USING Clause

Sample Program 7: Dynamic SQL Method 2

Sample Program 8: Dynamic SQL Method 3

Need for the SQLDA

The DESCRIBE Statement

What Is a SQLDA?

Implementing Method 4

Using the DECLARE STATEMENT Statement

Using Host Tables

12 Using Dynamic SQL: Advanced Concepts

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)

Purpose of the SQLDA

Multiple SQLDAs

Declaring a SQLDA

The SQLDA Variables

Some Preliminaries

Converting Data

Coercing Datatypes

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

13 Writing User Exits

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

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

Naming the Exit

Connecting to Oracle

Issuing I/O Calls

Using Host Variables

Updating Tables

Issuing Commands

EXEC TOOLS Statements

EXEC TOOLS MESSAGE

A New Features

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

B Operating System Dependencies

System-Specific References in this Manual

INCLUDE Statements

MAXLITERAL Default

PIC N Clause for Multi-byte NLS Characters

C Oracle8 Reserved Words, Keywords, and Namespaces

Oracle8 Reserved Words and Keywords

Oracle8 Reserved Namespaces

D Performance Tuning

What Causes Poor Performance?

How Can Performance be Improved?

Using Host Tables

Using Embedded PL/SQL

Optimizing SQL Statements

Optimizer Hints

Taking Advantage of Row-Level Locking

Eliminating Unnecessary Parsing

Handling Explicit Cursors

Using the Cursor Management Options

E Syntactic and Semantic Checking

What Is Syntactic and Semantic Checking?

Controlling the Type and Extent of Checking

Specifying SQLCHECK=SEMANTICS

Enabling a Semantic Check

F Embedded SQL Commands and Precompiler Directives

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

Multi-part Diagrams

Database Objects

ALLOCATE (Executable Embedded SQL Extension)

CLOSE (Executable Embedded SQL)

COMMIT (Executable Embedded SQL)

CONNECT (Executable Embedded SQL Extension)

DECLARE CURSOR (Embedded SQL Directive)

DECLARE DATABASE (Oracle Embedded SQL Directive)

DECLARE STATEMENT (Embedded SQL Directive)

DECLARE TABLE (Oracle Embedded SQL Directive)

DELETE (Executable Embedded SQL)

DESCRIBE (Executable Embedded SQL)

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

EXECUTE (Executable Embedded SQL)

EXECUTE IMMEDIATE (Executable Embedded SQL)

FETCH (Executable Embedded SQL)

INSERT (Executable Embedded SQL)

OPEN (Executable Embedded SQL)

PREPARE (Executable Embedded SQL)

ROLLBACK (Executable Embedded SQL)

SAVEPOINT (Executable Embedded SQL)

SELECT (Executable Embedded SQL)

UPDATE (Executable Embedded SQL)

VAR (Oracle Embedded SQL Directive)

WHENEVER (Embedded SQL Directive)

Copyright © 1997 Oracle Corporation.
All Rights Reserved.