Pro*COBOL Precompiler Programmer's Guide

What This Manual Has to Offer

Who Should Read This Manual?

How This Manual Is Organized

Text 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

Steps in Developing an Embedded SQL Application

Embedded SQL Statements

Embedded SQL Syntax

Static versus Dynamic SQL Statements

Embedded PL/SQL Blocks

Host Variables and Indicator Variables

Oracle Datatypes

Tables

Errors and Warnings

Programming Guidelines

The Declare Section

What is a Declare Section?

Precompiler Option DECLARE_SECTION

Using the INCLUDE Statement

Nested Programs

Support for Nested Programs

Conditional Precompilations

An Example

Defining Symbols

Separate Precompilations

Guidelines

Restrictions

Compiling and Linking

Sample Tables

Sample Data

Sample Programs: SAMPLE1.PCO

Connecting to Oracle

Default Databases and Connections

Using Username/Password

Automatic Logons

Changing Passwords at Runtime

Connect Without Alter Authorization

Advanced Connection Options

Connecting Using Net8

Concurrent Logons

Using Links

Embedding OCI (Oracle Call Interface) Calls

Setting Up the LDA

Remote and Multiple Connections

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 for Transaction Processing

Designing Applications

Obtaining Locks

Using PL/SQL

Developing X/Open Applications

Oracle-Specific Issues

The Oracle8i Datatypes

Internal Datatypes

External Datatypes

Host Variables

Declaring Host Variables

Referencing Host Variables

Indicator Variables

Using Indicator Variables

Declaring Indicator Variables

Referencing Indicator Variables

VARCHAR Variables

Declaring VARCHAR Variables

Implicit VARCHAR Group Items

Referencing VARCHAR Variables

Handling Character Data

Default for PIC X

Effects of the PICX Option

Fixed-Length Character Variables

Varrying-Length Variables

User-Specified Runtime Contexts

Universal ROWIDs

Subprogram SQLROWIDGET

National Language Support

Multi-Byte NLS Character Sets

Restrictions When NLS_LOCAL=YES

Character Strings in Embedded SQL

Embedded DDL

Blank Padding

Indicator Variables

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

Sample Program 4: Datatype Equivalencing

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

The PREFETCH Option

Embedding PL/SQL

Advantages of PL/SQL

Better Performance

Integration with Oracle8i

Embedding PL/SQL Blocks

Host Variables and PL/SQL

PL/SQL Examples

A More Complex Example

VARCHAR Pseudotype

Indicator Variables and PL/SQL

Handling NULLs

Handling Truncated Values

Host Tables and PL/SQL

ARRAYLEN Statement

Optional Keyword EXECUTE

Cursor Usage in Embedded PL/SQL

Stored PL/SQL and Java Subprograms

Creating Stored Subprograms

Calling a Stored PL/SQL or Java 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

Freeing a Cursor Variable

Restrictions on Cursor Variables

Error Conditions

Sample Program 11: Cursor Variables

What Is a Host Table?

Why Use Tables?

Declaring Host Tables

Referencing Host Tables

Using Indicator Tables

Oracle Restrictions

ANSI Restriction and Requirements

Tables in Data Manipulation Statements

Selecting into Tables

Batch Fetches

Using SQLERRD(3)

Number of Rows Fetched

Restrictions on Using Host Tables

Fetching NULLs

Fetching Truncated Values

Sample Program 3: Fetching in Batches

Inserting with Tables

Restrictions on Host Tables

Updating with Tables

Restrictions in UPDATE

Deleting with Tables

Restrictions in DELETE

Using Indicator Tables

Using the FOR Clause

Restrictions

Using the WHERE Clause

Mimicking the CURRENT OF Clause

Tables of Group Items as Host Variables

Sample Program 14: Tables of Group Items

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

WHENEVER Directive

Coding the WHENEVER Statement

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

Using Method 1

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

Using Method 3

Sample Program 8: Dynamic SQL Method 3

Using Oracle Method 4

Need for the SQLDA

The DESCRIBE Statement

What Is a SQLDA?

Implementing Method 4

Using the DECLARE STATEMENT Statement

Using Host Tables

Using PL/SQL

Basics of ANSI Dynamic SQL

Precompiler Options

Overview of ANSI SQL Statements

Sample Code

Oracle Extensions

Reference Semantics

Using Tables for Bulk Operations

ANSI Dynamic SQL Precompiler Options

Full Syntax of the Dynamic SQL Statements

ALLOCATE DESCRIPTOR

DEALLOCATE DESCRIPTOR

Use of EXECUTE IMMEDIATE

Use of DYNAMIC DECLARE CURSOR

OPEN Cursor

FETCH

CLOSE a Dynamic Cursor

Differences From Oracle Dynamic Method 4

Restrictions

Sample Programs: SAMPLE12.PCO

Meeting the Special Requirements of Method 4

What Makes Method 4 Special?

What Information Does the Database 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

Using SQLADR

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

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

What are LOBs?

LOBs vs. LONG and LONG RAW

LOB Locators

Temporary LOBs

LOB Buffering Subsystem

How to Use LOBs in Your Program

Two Ways to Access LOBs

LOB Locators in Your Application

Initializing a LOB

Rules for LOB Statements

For All LOB Statements

For the LOB Buffering Subsystem

LOB Statements

READ and WRITE Using the Polling Method

LOB Sample Program:LOBDEMO1.PCO

The Pro*COBOL Command

Case-Sensitivity

What Occurs during Precompilation?

About the Options

Precedence of Option Values

Macro and Micro Options

Determining Current Values

Configuration Files

Entering Options

On the Command Line

Inline

Scope of Options

Quick Reference

Using Pro*COBOL Precompiler Options

New Features of Release 8.1

User-Specified Runtime Contexts

SYSDBA/SYSOPER Privileges in CONNECT Statements

Tables of Group Items

WHENEVER DO CALL Branch

DECIMAL-POINT IS COMMA

Optional Division Headers

NESTED Option

DB2 Compatibility Features of Release 8.0

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 Returned

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 SQL Statements

Other New Features of Release 8.0

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

Error Message Codes

Migration From Earlier Releases

System-Specific References in this Manual

PIC N or Pic G Clause for Multi-byte NLS Characters

RETURN-CODE Special Register May Be Unpredictable.

Reserved Words and Keywords

Reserved Namespaces

What Causes Poor Performance?

How Can Performance be Improved?

Using Host Tables

Using PL/SQL and Java

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 Are 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 Statements

About the Statement 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)

ALLOCATE DESCRIPTOR (Executable Embedded SQL)

CALL (Executable Embedded SQL)

CLOSE (Executable Embedded SQL)

COMMIT (Executable Embedded SQL)

CONNECT (Executable Embedded SQL Extension)

CONTEXT ALLOCATE (Executable Embedded SQL Extension)

CONTEXT FREE (Executable Embedded SQL Extension)

CONTEXT USE (Oracle Embedded SQL Directive)

DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

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)

DESCRIBE DESCRIPTOR (Executable Embedded SQL)

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

EXECUTE (Executable Embedded SQL)

EXECUTE DESCRIPTOR (Executable Embedded SQL

EXECUTE IMMEDIATE (Executable Embedded SQL)

FETCH (Executable Embedded SQL)

FETCH DESCRIPTOR (Executable Embedded SQL)

FREE (Executable Embedded SQL Extension)

GET DESCRIPTOR (Executable Embedded SQL)

INSERT (Executable Embedded SQL)

LOB APPEND (Executable Embedded SQL Extension)

LOB ASSIGN (Executable Embedded SQL Extension)

LOB CLOSE (Executable Embedded SQL Extension)

LOB COPY (Executable Embedded SQL Extension)

LOB CREATE TEMPORARY (Executable Embedded SQL Extension)

LOB DESCRIBE (Executable Embedded SQL Extension)

LOB DISABLE BUFFERING (Executable Embedded SQL Extension)

LOB ENABLE BUFFERING (Executable Embedded SQL Extension)

LOB ERASE (Executable Embedded SQL Extension)

LOB FILE CLOSE ALL (Executable Embedded SQL Extension)

LOB FILE SET (Executable Embedded SQL Extension)

LOB FLUSH BUFFER (Executable Embedded SQL Extension)

LOB FREE TEMPORARY (Executable Embedded SQL Extension)

LOB LOAD (Executable Embedded SQL Extension)

LOB OPEN (Executable Embedded SQL Extension)

LOB READ (Executable Embedded SQL Extension)

LOB TRIM (Executable Embedded SQL Extension)

LOB WRITE (Executable Embedded SQL Extension)

OPEN (Executable Embedded SQL)

OPEN DESCRIPTOR (Executable Embedded SQL)

PREPARE (Executable Embedded SQL)

Usage Notes

ROLLBACK (Executable Embedded SQL)

SAVEPOINT (Executable Embedded SQL)

SELECT (Executable Embedded SQL)

SET DESCRIPTOR (Executable Embedded SQL)

UPDATE (Executable Embedded SQL)

VAR (Oracle Embedded SQL Directive)

WHENEVER (Embedded SQL Directive)

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

2 Precompiler Concepts

3 Database Concepts

4 Datatypes and Host Variables

5
Embedded SQL

6 Embedded PL/SQL

7 Host Tables

8 Error Handling and Diagnostics

9 Oracle Dynamic SQL

10 ANSI Dynamic SQL

11 Oracle Dynamic SQL: Method 4

12 User Exits

13 Large Objects (LOBs)

14 Precompiler Options

A New Features

B Operating System Dependencies

C Reserved Words, Keywords, and Namespaces

D
Performance Tuning

E Syntactic and Semantic Checking

F Embedded SQL Statements and Precompiler Directives