Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

In This Manual

Organization

Related Documentation

Sample Pro*COBOL Programs

The Pro*COBOL Precompiler and Industry Standards

Requirements

Compliance

FIPS Flagger

FIPS Option

Certification

MIA/SPIRIT

Conventions

Documentation Accessibility

What's New in Pro*COBOL?

Oracle9i Release 2 (9.2) New Features in Pro*COBOL

Oracle9i Release 1 (9.0.1) Deprecated or Desupported Features in Pro*COBOL

Oracle9i Release 1 (9.0.1) New Features in Pro*COBOL

Oracle8i Release 8.1.6 New Features in Pro*COBOL

Oracle8i Release 8.1.5 New Features in Pro*COBOL

Oracle8i Release 8.1.3 New Features in Pro*COBOL

Oracle8 Release 8.0 New Features in Pro*COBOL

1 Introduction

The Pro*COBOL Precompiler

Language Alternatives

Advantages of the Pro*COBOL Precompiler

The SQL Language

The PL/SQL Language

Pro*COBOL Features and Benefits

2 Precompiler Concepts

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

Abbreviations

Case-Insensitivity

COBOL Versions Supported

Coding Areas

Commas

Comments

Continuation Lines

Copy Statements

Decimal-Point is Comma

Delimiters

Division Headers that are Optional

Embedded SQL Syntax

Figurative Constants

File Length

FILLER is Allowed

Host Variable Names

Hyphenated Names

Level Numbers

MAXLITERAL Default

Multibyte Datatypes

NULLs in SQL

Paragraph and Section Names

REDEFINES Clause

Relational Operators

Sentence Terminator

The Declare Section

Contents of 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 DEPT and EMP Tables

Sample DEPT and EMP Data

Sample EMP Program: SAMPLE1.PCO

3 Database Concepts

Connecting to Oracle

Default Databases and Connections

Concurrent Logons

Using Username/Password

Automatic Logons

Changing Passwords at Runtime

Connect Without Alter Authorization

Using Links

Key Terms

How Transactions Guard a Database

Beginning and Ending 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

Fetching Across Commits

Using the LOCK TABLE Statement

Handling Distributed Transactions

Guidelines for Transaction Processing

Designing Applications

Obtaining Locks

Using PL/SQL

X/Open Applications

4 Datatypes and Host Variables

The Oracle9i Datatypes

Internal Datatypes

External Datatypes

Datetime and Interval Datatype Descriptors

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

Varying-Length Variables

Universal ROWIDs

Subprogram SQLROWIDGET

Globalization Support

Multibyte Globalization Support Character Sets

NLS_LOCAL=YES Restrictions

Character Strings in Embedded SQL

Embedded DDL

Blank Padding

Indicator Variables

Datatype Conversion

Explicit Control Over DATE String Format

Datatype Equivalencing

Usefulness of Equivalencing

Host Variable Equivalencing

Using the CHARF Datatype Specifier

Guidelines

RAW and LONG RAW Values

Sample Program 4: Datatype Equivalencing

5 Embedded SQL

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

Selecting Rows

Inserting Rows

DML Returning Clause

Using Subqueries

Updating Rows

Deleting Rows

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

Positioned Update

The PREFETCH Precompiler Option

Sample Program 2: Cursor Operations

6 Embedded PL/SQL

Embedding PL/SQL

Host Variables

VARCHAR Variables

Indicator Variables

SQLCHECK

Advantages of PL/SQL

Better Performance

Integration with Oracle9i

Cursor FOR Loops

Subprograms

Packages

PL/SQL Tables

User-Defined Records

Embedding PL/SQL Blocks

Host Variables and PL/SQL

PL/SQL Examples

A More Complex PL/SQL Example

VARCHAR Pseudotype

Indicator Variables and PL/SQL

Handling NULLs

Handling Truncated Values

Host Tables and PL/SQL

ARRAYLEN Statement

Cursor Usage in Embedded PL/SQL

Stored PL/SQL and Java Subprograms

Creating Stored Subprograms

Calling a Stored PL/SQL or Java Subprogram

Using Dynamic PL/SQL

Subprograms Restriction

Sample Program 9: Calling a Stored Procedure

Remote Access

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

Sample Program 11: Cursor Variables

7 Host Tables

Host Tables

Advantages of Host Tables

Tables in Data Manipulation Statements

Declaring Host Tables

Referencing Host Tables

Using Indicator Tables

Host Group Item Containing Tables

Oracle Restrictions

ANSI Restriction and Requirements

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

The FOR Clause

Restrictions

The WHERE Clause

Mimicking the CURRENT OF Clause

Tables of Group Items as Host Variables

Sample Program 14: Tables of Group Items

8 Error Handling and Diagnostics

Why Error Handling is Needed

Error Handling Alternatives

SQLCA

ORACA

ANSI SQLSTATE Variable

Declaring SQLSTATE

Using the SQL Communications Area

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

Contents of the ORACA

Declaring the ORACA

Enabling the ORACA

Choosing Runtime Options

ORACA Structure

ORACA Example Program

How Errors Map to SQLSTATE Codes

Status Variable Combinations

9 Oracle Dynamic SQL

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

Method 1

Method 2

Method 3

Method 4

Guidelines

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

PREPARE

DECLARE

OPEN

FETCH

CLOSE

Sample Program 8: Dynamic SQL Method 3

Using Oracle Method 4

Need for the SQLDA

The DESCRIBE Statement

SQLDA Contents

Implementing Method 4

Using the DECLARE STATEMENT Statement

Using Host Tables

Using PL/SQL

With Method 1

With Method 2

With Method 3

With Method 4

Caution

10 ANSI Dynamic 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

GET DESCRIPTOR

SET DESCRIPTOR

Use of PREPARE

DESCRIBE INPUT

DESCRIBE OUTPUT

EXECUTE

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

11 Oracle Dynamic SQL: Method 4

Meeting the Special Requirements of Method 4

Advantages of Method 4

Information the Database Needs

Where the Information is Stored

How Information is Obtained

Understanding the SQL Descriptor Area (SQLDA)

Purpose of the SQLDA

Multiple SQLDAs

Declaring a SQLDA

The SQLDA Variables

Prerequisite Knowledge

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

12 Multithreaded Applications

Introduction to Threads

Runtime Contexts in Pro*COBOL

Runtime Context Usage Models

Multiple Threads Sharing a Single Runtime Context

Multiple Threads Sharing Multiple Runtime Contexts

User Interface Features for Multithreaded Applications

THREADS Option

Embedded SQL Statements and Directives for Runtime Contexts

Communication with Pro*C/C++ Programs

Multithreading Programming Considerations

Multiple Context Examples

Multithreaded Example

13 Large Objects (LOBs)

Using LOBs

Internal LOBs

External LOBs

Security for BFILEs

LOBs Compared with LONG and LONG RAW

LOB Locators

Temporary LOBs

LOB Buffering Subsystem

How to Use LOBs

LOB Locators in Your Application

Initializing a LOB

Rules for LOB Statements

For All LOB Statements

For the LOB Buffering Subsystem

For Host Variables

LOB Statements

APPEND

ASSIGN

CLOSE

COPY

CREATE TEMPORARY

DISABLE BUFFERING

ENABLE BUFFERING

ERASE

FILE CLOSE ALL

FILE SET

FLUSH BUFFER

FREE TEMPORARY

LOAD FROM FILE

OPEN

READ

TRIM

WRITE

DESCRIBE

READ and WRITE Using the Polling Method

LOB Sample Program: LOBDEMO1.PCO

14 Precompiler Options

The procob Command

Case-Sensitivity

Actions During Precompilation

About the Options

Precedence of Option Values

Macro and Micro Options

Determining Current Values

Entering Precompiler Options

On the Command Line

Inline

Configuration Files

Scope of Precompiler Options

Quick Reference

Using Pro*COBOL Precompiler Options

ASACC

ASSUME_SQLCODE

AUTO_CONNECT

CLOSE_ON_COMMIT

CONFIG

DATE_FORMAT

DBMS

DECLARE_SECTION

DEFINE

DYNAMIC

END_OF_FETCH

ERRORS

FIPS

FORMAT

HOLD_CURSOR

HOST

INAME

INCLUDE

IRECLEN

LITDELIM

LNAME

LRECLEN

LTYPE

MAXLITERAL

MAXOPENCURSORS

MODE

NESTED

NLS_LOCAL

ONAME

ORACA

ORECLEN

PAGELEN

PICX

PREFETCH

RELEASE_CURSOR

SELECT_ERROR

SQLCHECK

THREADS

TYPE_CODE

UNSAFE_NULL

USERID

VARCHAR

XREF

A New Features

New Features of Release 9.0.1

Globalization Support

New Datetime Datatypes

New Features of Release 8.1

Multithreading Applications Supported

CALL Statement

Calling Java Methods

LOB Support

ANSI Dynamic SQL

PREFETCH Option

DML Returning Clause

Universal ROWIDs

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

B Operating System Dependencies

System-Specific References in this Manual

COBOL Versions

Host Variables

INCLUDE Statements

MAXLITERAL Default

PIC N or Pic G Clause for Multi-byte Globalization Support Characters

RETURN-CODE Special Register May Be Unpredictable.

Byte-Order of Binary Data

C Reserved Words, Keywords, and Namespaces

Reserved Words and Keywords

Reserved Namespaces

D Performance Tuning

Causes of Poor Performance

Improving Performance

Using Host Tables

Using PL/SQL and Java

Optimizing SQL Statements

Optimizer Hints

Using Indexes

Taking Advantage of Row-Level Locking

Eliminating Unnecessary Parsing

Handling Explicit Cursors

Using the Cursor Management Options

Avoiding Unnecessary Reparsing

E Syntactic and Semantic Checking

Syntactic and Semantic Checking Basics

Controlling the Type and Extent of Checking

Specifying SQLCHECK=SEMANTICS

Enabling a Semantic Check

F Embedded SQL Statements and Precompiler Directives

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

Oracle Names

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)

ENABLE THREADS (Executable Embedded SQL Extension)

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)

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)

Index