Programmer's Guide to the Oracle Pro*COBOL Precompiler
Release 8.0
A54659-01

Library

Product

Index


Next

Contents

Title and Copyright Information

Preface

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?
Requirements
Compliance
Certification
MIA/SPIRIT
Your Comments Are Welcome

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

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

Chapter 3 Writing a Pro*COBOL Program

Programming Guidelines
Abbreviations
Case-insensitivity
COBOL Versions
Coding Area
Commas
Comments
Continuation Lines
Delimiters
Embedded SQL Syntax
Figurative Constants
File Length
Host Variable Names
Hyphenated Names
Level Numbers
MAXLITERAL Default
Multi-Byte (NCHAR) Datatypes
When NLS_LOCAL=YES
Nulls
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
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

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

Chapter 5 Using 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
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
Sample Program 4: Datatype Equivalencing

Chapter 6 Using Embedded PL/SQL

Advantages of PL/SQL
Better Performance
Integration with Oracle8
Cursor FOR Loops
Subprograms
Packages
PL/SQL Tables
User-defined Records
Embedding PL/SQL Blocks
Using Host Variables
An Example
A More Complex Example
VARCHAR Pseudotype
Using Indicator Variables
Handling Nulls
Handling Truncated Values
Using Host Tables
ARRAYLEN Statement
Using Cursors
An Alternative
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
Restrictions
Error Conditions
Sample Programs

Chapter 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
Inline
Scope of Options
Quick Reference
Using Pro*COBOL Options
ASACC
ASSUME_SQLCODE
AUTO_CONNECT
CLOSE_ON_COMMIT
CONFIG
DATE_FORMAT
DBMS
DECLARE_SECTION
DEFINE
END_OF_FETCH
ERRORS
FIPS
FORMAT
HOLD_CURSOR
HOST
INAME
INCLUDE
IRECLEN
LITDELIM
LNAME
LRECLEN
LTYPE
MAXLITERAL
MAXOPENCURSORS
MODE
NLS_LOCAL
ONAME
ORACA
ORECLEN
PAGELEN
PICX
RELEASE_CURSOR
SELECT_ERROR
SQLCHECK
UNSAFE_NULL
USERID
VARCHAR
XREF
Conditional Precompilations
An Example
Defining Symbols
Separate Precompilations
Guidelines
Restrictions
Compiling and Linking

Chapter 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
Guidelines
Designing Applications
Obtaining Locks
Using PL/SQL

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

Chapter 10 Using Host Tables

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
Restrictions
Updating with Tables
Restrictions
Deleting with Tables
Restrictions
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

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

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

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

Appendix 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

Appendix B Operating System Dependencies

System-Specific References in this Manual
COBOL Versions
Host Variables
INCLUDE Statements
MAXLITERAL Default
PIC N Clause for Multi-byte NLS Characters

Appendix C Oracle8 Reserved Words, Keywords, and Namespaces

Oracle8 Reserved Words and Keywords
Oracle8 Reserved Namespaces

Appendix D Performance Tuning

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

Appendix 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

Appendix 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
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
Usage Notes
Example I
Example II
Related Topics
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
Usage Notes
Example I
Example II
Related Topics
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
Usage Notes
Example I
Example II
Example III
Related Topics
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

Index



Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library

Product

Index