Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

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

1.1 The Pro*COBOL Precompiler

1.1.1 Language Alternatives

1.2 Advantages of the Pro*COBOL Precompiler

1.3 The SQL Language

1.4 The PL/SQL Language

1.5 Pro*COBOL Features and Benefits

2 Precompiler Concepts

2.1 Key Concepts of Embedded SQL Programming

2.1.1 Steps in Developing an Embedded SQL Application

2.1.2 Embedded SQL Statements

2.1.2.1 Executable versus Declarative Statements

2.1.3 Embedded SQL Syntax

2.1.4 Static Versus Dynamic SQL Statements

2.1.5 Embedded PL/SQL Blocks

2.1.6 Host Variables and Indicator Variables

2.1.7 Oracle Datatypes

2.1.9 Errors and Warnings

2.1.9.1 SQLCODE/SQLSTATE Status Variables

2.1.9.2 SQLCA Status Variable

2.1.9.3 WHENEVER Statement

2.1.9.5 Precompiler Options and Error Handling

2.2 Programming Guidelines

2.2.1 Abbreviations

2.2.2 Case-Insensitivity

2.2.3 COBOL Versions Supported

2.2.4 Coding Areas

2.2.7 Continuation Lines

2.2.8 Copy Statements

2.2.9 Decimal-Point is Comma

2.2.10 Delimiters

2.2.11 Division Headers that are Optional

2.2.12 Embedded SQL Syntax

2.2.13 Figurative Constants

2.2.14 File Length

2.2.15 FILLER is Allowed

2.2.16 Host Variable Names

2.2.17 Hyphenated Names

2.2.18 Level Numbers

2.2.19 MAXLITERAL Default

2.2.20 Multibyte Datatypes

2.2.21 NULLs in SQL

2.2.22 Paragraph and Section Names

2.2.23 REDEFINES Clause

2.2.24 Relational Operators

2.2.25 Sentence Terminator

2.3 The Declare Section

2.3.1 Contents of a Declare Section

2.3.1.1 An Example

2.3.2 Precompiler Option DECLARE_SECTION

2.3.3 Using the INCLUDE Statement

2.3.3.1 Filename Extensions

2.3.3.2 Search Paths

2.4 Nested Programs

2.4.1 Support for Nested Programs

2.4.1.1 Declaring the SQLCA

2.4.1.2 Nested Program Example

2.5 Conditional Precompilations

2.5.1 An Example

2.5.2 Defining Symbols

2.6 Separate Precompilations

2.6.1 Guidelines

2.6.1.1 Referencing Cursors

2.6.1.2 Specifying MAXOPENCURSORS

2.6.1.3 Using a Single SQLCA

2.6.1.4 Using a Single DATE_FORMAT

2.6.2 Restrictions

2.7 Compiling and Linking

2.8 Sample DEPT and EMP Tables

2.8.1 Sample DEPT and EMP Data

2.9 Sample EMP Program: SAMPLE1.PCO

3 Database Concepts

3.1 Connecting to Oracle

3.2 Default Databases and Connections

3.2.1 Concurrent Logons

3.2.2 Using Username/Password

3.2.2.1 Named Database Connections

3.2.3 Automatic Logons

3.2.3.1 The AUTO_CONNECT Precompiler Option

3.2.4 Changing Passwords at Runtime

3.2.5 Connect Without Alter Authorization

3.2.5.1 Standard CONNECT

3.2.5.2 SYSDBA or SYSOPER Privileges

3.2.6 Using Links

3.4 How Transactions Guard a Database

3.5 Beginning and Ending Transactions

3.6 Using the COMMIT Statement

3.6.1 WITH HOLD Clause in DECLARE CURSOR Statements

3.6.2 CLOSE_ON_COMMIT Precompiler Option

3.7 Using the ROLLBACK Statement

3.7.1 Statement-Level Rollbacks

3.8 Using the SAVEPOINT Statement

3.9 Using the RELEASE Option

3.10 Using the SET TRANSACTION Statement

3.11 Overriding Default Locking

3.11.1 Using the FOR UPDATE OF Clause

3.11.1.1 Restrictions

3.12 Fetching Across Commits

3.12.1 Using the LOCK TABLE Statement

3.13 Handling Distributed Transactions

3.14 Guidelines for Transaction Processing

3.14.1 Designing Applications

3.14.2 Obtaining Locks

3.14.3 Using PL/SQL

3.14.4 X/Open Applications

4 Datatypes and Host Variables

4.1 The Oracle9i Datatypes

4.1.1 Internal Datatypes

4.1.2 External Datatypes

4.1.2.5 DECIMAL

4.1.2.6 DISPLAY

4.1.2.8 INTEGER

4.1.2.10 LONG RAW

4.1.2.11 LONG VARCHAR

4.1.2.12 LONG VARRAW

4.1.2.13 NUMBER

4.1.2.14 OVER-PUNCH

4.1.2.17 STRING

4.1.2.18 UNSIGNED

4.1.2.19 VARCHAR

4.1.2.20 VARCHAR2

4.1.2.21 VARNUM

4.1.2.22 VARRAW

4.1.2.23 SQL Pseudocolumns and Functions

4.2 Datetime and Interval Datatype Descriptors

4.3 Host Variables

4.3.1 Declaring Host Variables

4.3.1.1 Example Declarations

4.3.1.2 Initialization

4.3.1.3 Restrictions

4.3.2 Referencing Host Variables

4.3.2.1 Group Items as Host Variables

4.3.2.2 Restrictions

4.4 Indicator Variables

4.4.1 Using Indicator Variables

4.4.1.1 On Input

4.4.1.2 On Output

4.4.2 Declaring Indicator Variables

4.4.3 Referencing Indicator Variables

4.4.3.1 Use in Where Clauses

4.4.3.2 Avoid Error Messages

4.4.3.3 ANSI Requirements

4.4.3.4 Indicator Variables for Multibyte NCHAR Variables

4.4.3.5 Indicator Variables with Host Group Items

4.5 VARCHAR Variables

4.5.1 Declaring VARCHAR Variables

4.5.2 Implicit VARCHAR Group Items

4.5.3 Referencing VARCHAR Variables

4.6 Handling Character Data

4.6.1 Default for PIC X

4.6.2 Effects of the PICX Option

4.6.3 Fixed-Length Character Variables

4.6.3.1 On Input

4.6.3.2 On Output

4.6.4 Varying-Length Variables

4.6.4.1 On Input

4.6.4.2 On Output

4.7 Universal ROWIDs

4.7.1 Subprogram SQLROWIDGET

4.8 Globalization Support

4.9 Multibyte Globalization Support Character Sets

4.9.1 NLS_LOCAL=YES Restrictions

4.9.2 Character Strings in Embedded SQL

4.9.3 Embedded DDL

4.9.4 Blank Padding

4.9.5 Indicator Variables

4.10 Datatype Conversion

4.11 Explicit Control Over DATE String Format

4.12 Datatype Equivalencing

4.12.1 Usefulness of Equivalencing

4.12.2 Host Variable Equivalencing

4.12.2.1 CONVBUFSZ Clause in VAR Statement

4.12.2.2 An Example

4.12.3 Using the CHARF Datatype Specifier

4.12.4 Guidelines

4.12.5 RAW and LONG RAW Values

4.13 Sample Program 4: Datatype Equivalencing

5 Embedded SQL

5.1 Using Host Variables

5.1.1 Output Versus Input Host Variables

5.2 Using Indicator Variables

5.2.1 Input Variables

5.2.2 Output Variables

5.2.3 Inserting NULLs

5.2.4 Handling Returned NULLs

5.2.5 Fetching NULLs

5.2.6 Testing for NULLs

5.2.7 Fetching Truncated Values

5.3 The Basic SQL Statements

5.3.1 Selecting Rows

5.3.1.1 Available Clauses

5.3.2 Inserting Rows

5.3.3 DML Returning Clause

5.3.4 Using Subqueries

5.3.5 Updating Rows

5.3.6 Deleting Rows

5.3.7 Using the WHERE Clause

5.4.1 Declaring a Cursor

5.4.2 Opening a Cursor

5.4.3 Fetching from a Cursor

5.4.4 Closing a Cursor

5.4.5 Using the CURRENT OF Clause

5.4.6 Restrictions

5.4.7 A Typical Sequence of Statements

5.4.8 Positioned Update

5.4.9 The PREFETCH Precompiler Option

5.5 Sample Program 2: Cursor Operations

6 Embedded PL/SQL

6.1 Embedding PL/SQL

6.1.1 Host Variables

6.1.2 VARCHAR Variables

6.1.3 Indicator Variables

6.1.3.1 Handling NULLs

6.1.3.2 Handling Truncated Values

6.2 Advantages of PL/SQL

6.2.1 Better Performance

6.2.2 Integration with Oracle9i

6.2.3 Cursor FOR Loops

6.2.4 Subprograms

6.2.4.1 Parameter Modes

6.2.6 PL/SQL Tables

6.2.7 User-Defined Records

6.3 Embedding PL/SQL Blocks

6.4 Host Variables and PL/SQL

6.4.1 PL/SQL Examples

6.4.2 A More Complex PL/SQL Example

6.4.3 VARCHAR Pseudotype

6.5 Indicator Variables and PL/SQL

6.5.1 Handling NULLs

6.5.2 Handling Truncated Values

6.6 Host Tables and PL/SQL

6.6.1 ARRAYLEN Statement

6.6.1.1 Optional Keyword EXECUTE to ARRAYLEN Statement

6.7 Cursor Usage in Embedded PL/SQL

6.8 Stored PL/SQL and Java Subprograms

6.8.1 Creating Stored Subprograms

6.8.2 Calling a Stored PL/SQL or Java Subprogram

6.8.2.1 Anonymous PL/SQL Block

6.8.2.2 CALL Statement

6.8.2.3 CALL Example

6.8.3 Using Dynamic PL/SQL

6.8.4 Subprograms Restriction

6.9 Sample Program 9: Calling a Stored Procedure

6.9.1 Remote Access

6.10 Cursor Variables

6.10.1 Declaring a Cursor Variable

6.10.2 Allocating a Cursor Variable

6.10.3 Opening a Cursor Variable

6.10.3.1 Opening Indirectly through a Stored PL/SQL Procedure

6.10.3.2 Opening Directly from Your Pro*COBOL Application

6.10.4 Fetching from a Cursor Variable

6.10.5 Closing a Cursor Variable

6.10.6 Freeing a Cursor Variable

6.10.7 Restrictions on Cursor Variables

6.10.8 Sample Program 11: Cursor Variables

6.10.8.1 SAMPLE11.SQL

6.10.8.2 SAMPLE11.PCO

7 Host Tables

7.1 Host Tables

7.2 Advantages of Host Tables

7.3 Tables in Data Manipulation Statements

7.3.1 Declaring Host Tables

7.3.1.1 Restrictions

7.3.2 Referencing Host Tables

7.3.3 Using Indicator Tables

7.3.4 Host Group Item Containing Tables

7.3.5 Oracle Restrictions

7.3.6 ANSI Restriction and Requirements

7.4 Selecting into Tables

7.4.1 Batch Fetches

7.4.2 Using SQLERRD(3)

7.4.3 Number of Rows Fetched

7.4.4 Restrictions on Using Host Tables

7.4.5 Fetching NULLs

7.4.6 Fetching Truncated Values

7.4.7 Sample Program 3: Fetching in Batches

7.5 Inserting with Tables

7.5.1 Restrictions on Host Tables

7.6 Updating with Tables

7.6.1 Restrictions in UPDATE

7.7 Deleting with Tables

7.7.1 Restrictions in DELETE

7.8 Using Indicator Tables

7.9 The FOR Clause

7.9.1 Restrictions

7.9.1.1 In a SELECT Statement

7.9.1.2 With the CURRENT OF Clause

7.10 The WHERE Clause

7.11 Mimicking the CURRENT OF Clause

7.12 Tables of Group Items as Host Variables

7.13 Sample Program 14: Tables of Group Items

8 Error Handling and Diagnostics

8.1 Why Error Handling is Needed

8.2 Error Handling Alternatives

8.2.3 ANSI SQLSTATE Variable

8.2.4 Declaring SQLSTATE

8.2.4.1 SQLSTATE Values

8.3 Using the SQL Communications Area

8.3.1 Contents of the SQLCA

8.3.2 Declaring the SQLCA

8.3.3 Key Components of Error Reporting

8.3.3.1 Status Codes

8.3.3.2 Warning Flags

8.3.3.3 Rows-Processed Count

8.3.3.4 Parse Error Offset

8.3.3.5 Error Message Text

8.3.4 SQLCA Structure

8.3.4.1 SQLCAID

8.3.4.2 SQLCABC

8.3.4.3 SQLCODE

8.3.4.4 SQLERRM

8.3.4.5 SQLERRD

8.3.4.6 SQLWARN

8.3.5 PL/SQL Considerations

8.3.6 Getting the Full Text of Error Messages

8.3.8 WHENEVER Directive

8.3.8.1 Conditions

8.3.8.2 SQLWARNING

8.3.8.3 SQLERROR

8.3.8.4 NOT FOUND or NOTFOUND

8.3.8.5 Actions

8.3.8.6 CONTINUE

8.3.8.7 DO CALL

8.3.8.8 DO PERFORM

8.3.8.9 GOTO or GO TO

8.3.9 Coding the WHENEVER Statement

8.3.9.1 DO PERFORM

8.3.9.2 DO CALL

8.3.9.4 Careless Usage: Examples

8.3.10 Getting the Text of SQL Statements

8.4 Using the Oracle Communications Area

8.4.1 Contents of the ORACA

8.4.2 Declaring the ORACA

8.4.3 Enabling the ORACA

8.4.4 Choosing Runtime Options

8.4.5 ORACA Structure

8.4.5.1 ORACAID

8.4.5.2 ORACABC

8.4.5.3 ORACCHF

8.4.5.4 ORADBGF

8.4.5.5 ORAHCHF

8.4.5.6 ORASTXTF

8.4.5.7 Diagnostics

8.4.5.8 ORASTXT

8.4.5.9 ORASFNM

8.4.5.10 ORASLNR

8.4.5.11 Cursor Cache Statistics

8.4.5.12 ORAHOC

8.4.5.13 ORAMOC

8.4.5.14 ORACOC

8.4.5.15 ORANOR

8.4.5.16 ORANPR

8.4.5.17 ORANEX

8.4.6 ORACA Example Program

8.5 How Errors Map to SQLSTATE Codes

8.5.1 Status Variable Combinations

9 Oracle Dynamic SQL

9.1 Dynamic SQL

9.2 Advantages and Disadvantages of Dynamic SQL

9.3 When to Use Dynamic SQL

9.4 Requirements for Dynamic SQL Statements

9.5 How Dynamic SQL Statements Are Processed

9.6 Methods for Using Dynamic SQL

9.6.5 Guidelines

9.6.5.1 Avoiding Common Errors

9.7 Using Method 1

9.7.1 The EXECUTE IMMEDIATE Statement

9.7.2 An Example

9.8 Sample Program 6: Dynamic SQL Method 1

9.9 Using Method 2

9.9.1 The USING Clause

9.10 Sample Program 7: Dynamic SQL Method 2

9.11 Using Method 3

9.12 Sample Program 8: Dynamic SQL Method 3

9.13 Using Oracle Method 4

9.13.1 Need for the SQLDA

9.13.2 The DESCRIBE Statement

9.13.3 SQLDA Contents

9.13.4 Implementing Method 4

9.14 Using the DECLARE STATEMENT Statement

9.15 Using Host Tables

9.16 Using PL/SQL

9.16.1 With Method 1

9.16.2 With Method 2

9.16.3 With Method 3

9.16.4 With Method 4

10 ANSI Dynamic SQL

10.1 Basics of ANSI Dynamic SQL

10.1.1 Precompiler Options

10.2 Overview of ANSI SQL Statements

10.3 Sample Code

10.4 Oracle Extensions

10.4.1 Reference Semantics

10.4.2 Using Tables for Bulk Operations

10.5 ANSI Dynamic SQL Precompiler Options

10.6 Full Syntax of the Dynamic SQL Statements

10.6.1 ALLOCATE DESCRIPTOR

10.6.1.1 Variables

10.6.1.2 Examples

10.6.2 DEALLOCATE DESCRIPTOR

10.6.3 GET DESCRIPTOR

10.6.4 SET DESCRIPTOR

10.6.4.1 Example

10.6.5 Use of PREPARE

10.6.6 DESCRIBE INPUT

10.6.7 DESCRIBE OUTPUT

10.6.9 Use of EXECUTE IMMEDIATE

10.6.10 Use of DYNAMIC DECLARE CURSOR

10.6.11 OPEN Cursor

10.6.13 CLOSE a Dynamic Cursor

10.6.14 Differences From Oracle Dynamic Method 4

10.6.15 Restrictions

10.7 Sample Programs: SAMPLE12.PCO

11 Oracle Dynamic SQL: Method 4

11.1 Meeting the Special Requirements of Method 4

11.1.1 Advantages of Method 4

11.1.2 Information the Database Needs

11.1.3 Where the Information is Stored

11.1.4 How Information is Obtained

11.2 Understanding the SQL Descriptor Area (SQLDA)

11.2.1 Purpose of the SQLDA

11.2.2 Multiple SQLDAs

11.2.3 Declaring a SQLDA

11.3 The SQLDA Variables

11.4 Prerequisite Knowledge

11.4.1 Using SQLADR

11.4.2 Converting Data

11.4.3 Coercing Datatypes

11.4.4 Handling NULL/Not NULL Datatypes

11.5 The Basic Steps

11.6 A Closer Look at Each Step

11.6.1 Declare a Host String

11.6.2 Declare the SQLDAs

11.6.3 Set the Maximum Number to DESCRIBE

11.6.4 Initialize the Descriptors

11.6.5 Store the Query Text in the Host String

11.6.6 PREPARE the Query from the Host String

11.6.7 DECLARE a Cursor

11.6.8 DESCRIBE the Bind Variables

11.6.9 Reset Number of Place-Holders

11.6.10 Get Values for Bind Variables

11.6.11 OPEN the Cursor

11.6.12 DESCRIBE the Select List

11.6.13 Reset Number of Select-List Items

11.6.14 Reset Length/Datatype of Each Select-List Item

11.6.15 FETCH Rows from the Active Set

11.6.16 Get and Process Select-List Values

11.6.17 CLOSE the Cursor

11.7 Using Host Tables with Method 4

11.8 Sample Program 10: Dynamic SQL Method 4

12 Multithreaded Applications

12.1 Introduction to Threads

12.2 Runtime Contexts in Pro*COBOL

12.3 Runtime Context Usage Models

12.3.1 Multiple Threads Sharing a Single Runtime Context

12.3.2 Multiple Threads Sharing Multiple Runtime Contexts

12.4 User Interface Features for Multithreaded Applications

12.4.1 THREADS Option

12.4.2 Embedded SQL Statements and Directives for Runtime Contexts

12.4.2.1 Host Tables of SQL-CONTEXT Are Not Allowed

12.4.2.2 EXEC SQL ENABLE THREADS

12.4.2.3 EXEC SQL CONTEXT ALLOCATE

12.4.2.4 EXEC SQL CONTEXT USE

12.4.2.5 EXEC SQL CONTEXT FREE

12.4.3 Communication with Pro*C/C++ Programs

12.4.4 Multithreading Programming Considerations

12.4.4.1 Restrictions on Multithreading

12.4.5 Multiple Context Examples

12.4.5.1 Example 1

12.4.5.2 Example 2

12.4.5.3 Example 3

12.4.5.4 Example 4

12.4.5.5 Example 5

12.5 Multithreaded Example

13 Large Objects (LOBs)

13.1 Using LOBs

13.1.1 Internal LOBs

13.1.2 External LOBs

13.1.3 Security for BFILEs

13.1.4 LOBs Compared with LONG and LONG RAW

13.1.5 LOB Locators

13.1.6 Temporary LOBs

13.1.7 LOB Buffering Subsystem

13.2 How to Use LOBs

13.2.1 LOB Locators in Your Application

13.2.2 Initializing a LOB

13.2.2.1 Internal LOBs

13.2.2.2 External LOBs

13.2.2.3 Temporary LOBs

13.2.2.4 Freeing LOBs

13.3 Rules for LOB Statements

13.3.1 For All LOB Statements

13.3.2 For the LOB Buffering Subsystem

13.3.3 For Host Variables

13.4 LOB Statements

13.4.5 CREATE TEMPORARY

13.4.6 DISABLE BUFFERING

13.4.7 ENABLE BUFFERING

13.4.9 FILE CLOSE ALL

13.4.10 FILE SET

13.4.11 FLUSH BUFFER

13.4.12 FREE TEMPORARY

13.4.13 LOAD FROM FILE

13.4.18 DESCRIBE

13.4.19 READ and WRITE Using the Polling Method

13.5 LOB Sample Program: LOBDEMO1.PCO

14 Precompiler Options

14.1 The procob Command

14.1.1 Case-Sensitivity

14.2 Actions During Precompilation

14.3 About the Options

14.3.1 Precedence of Option Values

14.3.2 Macro and Micro Options

14.3.3 Determining Current Values

14.4 Entering Precompiler Options

14.4.1 On the Command Line

14.4.2.1 Advantages

14.4.2.2 Scope of EXEC ORACLE

14.4.3 Configuration Files

14.5 Scope of Precompiler Options

14.6 Quick Reference

14.7 Using Pro*COBOL Precompiler Options

14.7.2 ASSUME_SQLCODE

14.7.3 AUTO_CONNECT

14.7.4 CLOSE_ON_COMMIT

14.7.6 DATE_FORMAT

14.7.8 DECLARE_SECTION

14.7.10 DYNAMIC

14.7.11 END_OF_FETCH

14.7.15 HOLD_CURSOR

14.7.18 INCLUDE

14.7.19 IRECLEN

14.7.20 LITDELIM

14.7.22 LRECLEN

14.7.24 MAXLITERAL

14.7.25 MAXOPENCURSORS

14.7.28 NLS_LOCAL

14.7.31 ORECLEN

14.7.32 PAGELEN

14.7.34 PREFETCH

14.7.35 RELEASE_CURSOR

14.7.36 SELECT_ERROR

14.7.37 SQLCHECK

14.7.38 THREADS

14.7.39 TYPE_CODE

14.7.40 UNSAFE_NULL

14.7.42 VARCHAR

A New Features

A.1 New Features of Release 9.0.1

A.1.1 Globalization Support

A.1.2 New Datetime Datatypes

A.2 New Features of Release 8.1

A.2.1 Multithreading Applications Supported

A.2.2 CALL Statement

A.2.3 Calling Java Methods

A.2.4 LOB Support

A.2.5 ANSI Dynamic SQL

A.2.6 PREFETCH Option

A.2.7 DML Returning Clause

A.2.8 Universal ROWIDs

A.2.9 SYSDBA/SYSOPER Privileges in CONNECT Statements

A.2.10 Tables of Group Items

A.2.11 WHENEVER DO CALL Branch

A.2.12 DECIMAL-POINT IS COMMA

A.2.13 Optional Division Headers

A.2.14 NESTED Option

A.3 DB2 Compatibility Features of Release 8.0

A.3.1 Optional Declare Section

A.3.2 Support of Additional Datatypes

A.3.3 Support of Group Items as Host Variables

A.3.4 Implicit Form of VARCHAR Group Items

A.3.5 Explicit Control Over the END-OF-FETCH SQLCODE Returned

A.3.6 Support of the WITH HOLD Clause in the DECLARE CURSOR Statement

A.3.7 New Precompiler Option CLOSE_ON_COMMIT

A.3.8 Support for DSNTIAR

A.3.9 Date String Format Precompiler Option

A.3.10 Any Terminator Allowed After SQL Statements

A.4 Other New Features of Release 8.0

A.4.1 New Name for Configuration File

A.4.2 Support of Other Additional Datatypes

A.4.3 Support of Nested Programs

A.4.4 Support for REDEFINES and FILLER

A.4.5 New Precompiler Option PICX

A.4.6 Optional CONVBUFSZ Clause in VAR Statement

A.4.7 Improved Error Reporting

A.4.8 Changing Password When Connecting

A.4.9 Error Message Codes

A.5 Migration From Earlier Releases

B Operating System Dependencies

B.1 System-Specific References in this Manual

B.1.1 COBOL Versions

B.1.2 Host Variables

B.1.2.1 Declaring

B.1.3 INCLUDE Statements

B.1.4 MAXLITERAL Default

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

B.1.6 RETURN-CODE Special Register May Be Unpredictable.

B.1.7 Byte-Order of Binary Data

C Reserved Words, Keywords, and Namespaces

C.1 Reserved Words and Keywords

C.2 Reserved Namespaces

D Performance Tuning

D.1 Causes of Poor Performance

D.2 Improving Performance

D.3 Using Host Tables

D.4 Using PL/SQL and Java

D.5 Optimizing SQL Statements

D.5.1 Optimizer Hints

D.5.1.1 Giving Hints

D.6 Using Indexes

D.7 Taking Advantage of Row-Level Locking

D.8 Eliminating Unnecessary Parsing

D.8.1 Handling Explicit Cursors

D.8.1.1 Cursor Control

D.8.2 Using the Cursor Management Options

D.8.2.1 Private SQL Areas and Cursor Cache

D.8.2.2 Resource Use

D.8.2.3 Infrequent Execution

D.8.2.4 Frequent Execution

D.8.2.5 Effect on the Shared SQL Area

D.8.2.6 Embedded PL/SQL Considerations

D.8.2.7 Parameter Interactions

D.9 Avoiding Unnecessary Reparsing

E Syntactic and Semantic Checking

E.1 Syntactic and Semantic Checking Basics

E.2 Controlling the Type and Extent of Checking

E.3 Specifying SQLCHECK=SEMANTICS

E.3.1 Enabling a Semantic Check

E.3.1.1 Connecting to Oracle

E.3.1.2 Using DECLARE TABLE

F Embedded SQL Statements and Precompiler Directives

F.1 Summary of Precompiler Directives and Embedded SQL Statements

F.2 About the Statement Descriptions

F.3 How to Read Syntax Diagrams

F.3.1 Statement Terminator

F.3.2 Required Keywords and Parameters

F.3.3 Optional Keywords and Parameters

F.3.4 Syntax Loops

F.3.5 Multi-part Diagrams

F.3.6 Oracle Names

F.4 ALLOCATE (Executable Embedded SQL Extension)

F.5 ALLOCATE DESCRIPTOR (Executable Embedded SQL)

F.6 CALL (Executable Embedded SQL)

F.7 CLOSE (Executable Embedded SQL)

F.8 COMMIT (Executable Embedded SQL)

F.9 CONNECT (Executable Embedded SQL Extension)

F.10 CONTEXT ALLOCATE (Executable Embedded SQL Extension)

F.11 CONTEXT FREE (Executable Embedded SQL Extension)

F.12 CONTEXT USE (Oracle Embedded SQL Directive)

F.13 DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

F.14 DECLARE CURSOR (Embedded SQL Directive)

F.15 DECLARE DATABASE (Oracle Embedded SQL Directive)

F.16 DECLARE STATEMENT (Embedded SQL Directive)

F.17 DECLARE TABLE (Oracle Embedded SQL Directive)

F.18 DELETE (Executable Embedded SQL)

F.19 DESCRIBE (Executable Embedded SQL)

F.20 DESCRIBE DESCRIPTOR (Executable Embedded SQL)

F.21 ENABLE THREADS (Executable Embedded SQL Extension)

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

F.23 EXECUTE (Executable Embedded SQL)

F.24 EXECUTE DESCRIPTOR (Executable Embedded SQL

F.25 EXECUTE IMMEDIATE (Executable Embedded SQL)

F.26 FETCH (Executable Embedded SQL)

F.27 FETCH DESCRIPTOR (Executable Embedded SQL)

F.28 FREE (Executable Embedded SQL Extension)

F.29 GET DESCRIPTOR (Executable Embedded SQL)

F.30 INSERT (Executable Embedded SQL)

F.31 LOB APPEND (Executable Embedded SQL Extension)

F.32 LOB ASSIGN (Executable Embedded SQL Extension)

F.33 LOB CLOSE (Executable Embedded SQL Extension)

F.34 LOB COPY (Executable Embedded SQL Extension)

F.35 LOB CREATE TEMPORARY (Executable Embedded SQL Extension)

F.36 LOB DESCRIBE (Executable Embedded SQL Extension)

F.37 LOB DISABLE BUFFERING (Executable Embedded SQL Extension)

F.38 LOB ENABLE BUFFERING (Executable Embedded SQL Extension)

F.39 LOB ERASE (Executable Embedded SQL Extension)

F.40 LOB FILE CLOSE ALL (Executable Embedded SQL Extension)

F.41 LOB FILE SET (Executable Embedded SQL Extension)

F.42 LOB FLUSH BUFFER (Executable Embedded SQL Extension)

F.43 LOB FREE TEMPORARY (Executable Embedded SQL Extension)

F.44 LOB LOAD (Executable Embedded SQL Extension)

F.45 LOB OPEN (Executable Embedded SQL Extension)

F.46 LOB READ (Executable Embedded SQL Extension)

F.47 LOB TRIM (Executable Embedded SQL Extension)

F.48 LOB WRITE (Executable Embedded SQL Extension)

F.49 OPEN (Executable Embedded SQL)

F.50 OPEN DESCRIPTOR (Executable Embedded SQL)

F.51 PREPARE (Executable Embedded SQL)

F.52 ROLLBACK (Executable Embedded SQL)

F.53 SAVEPOINT (Executable Embedded SQL)

F.54 SELECT (Executable Embedded SQL)

F.55 SET DESCRIPTOR (Executable Embedded SQL)

F.56 UPDATE (Executable Embedded SQL)

F.57 VAR (Oracle Embedded SQL Directive)

F.58 WHENEVER (Embedded SQL Directive)

Index