Pro*COBOL Precompiler Programmer's Guide
Release 8.1.5

A68023-01

Library

Product

Index

Next

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
Text Conventions Used in This Manual
Notation
Syntax Description
Sample Programs
Does the Pro*COBOL Precompiler Meet Industry Standards?
Requirements
Compliance
FIPS Flagger
FIPS Option
Certification
MIA/SPIRIT
Your Comments Are Welcome

1 Introduction

What Is Pro*COBOL?
Language Alternatives
Why Use the Pro*COBOL Precompiler?
Why Use SQL?
Why Use PL/SQL?
What Does Pro*COBOL Offer?

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
Optional Divisions
Embedded SQL Syntax
Figurative Constants
File Length
FILLER is Allowed
Host Variable Names
Hyphenated Names
Level Numbers
MAXLITERAL Default
Multi-Byte Datatypes
NLS_LOCAL=YES
Null Characters in COBOL Statements
NULLs in SQL
Paragraph Names
REDEFINES Clause
Relational Operators
Sentence Terminator
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

3 Database Concepts

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

4 Datatypes and Host Variables

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

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
Sample Program 2: Cursor Operations
The PREFETCH Option

6 Embedded PL/SQL

Embedding PL/SQL
Host Variables
VARCHAR Variables
Indicator Variables
SQLCHECK
Advantages of PL/SQL
Better Performance
Integration with Oracle8i
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 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

7 Host Tables

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

8 Error Handling and Diagnostics

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

9 Oracle 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
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
What Is a SQLDA?
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
Attention:
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
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

12 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
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
Naming the Exit
Connecting to Oracle
Issuing I/O Calls
Using Host Variables
Updating Tables
Issuing Commands
EXEC TOOLS Statements
EXEC TOOLS SET
EXEC TOOLS GET
EXEC TOOLS MESSAGE

13 Large Objects (LOBs)

What are LOBs?
Internal LOBs
External LOBs
Security for BFILEs
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
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 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
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
TYPE_CODE
UNSAFE_NULL
USERID
VARCHAR
XREF

A New Features

New Features of Release 8.1
CALL Statement
Calling Java Methods
LOB Support
ANSI Dynamic SQL
PREFETCH Option
DML Returning Clause
Universal ROWIDs
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

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 NLS Characters
RETURN-CODE Special Register May Be Unpredictable.

C Reserved Words, Keywords, and Namespaces

Reserved Words and Keywords
Reserved Namespaces

D
Performance Tuning

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

E Syntactic and Semantic Checking

What Are Syntactic and Semantic Checking?
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
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)


Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Index