Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Pro*COBOL?

• Oracle9i Release 2 (9.2) New 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 Database 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
    • Executable versus Declarative Statements
  • Embedded SQL Syntax
  • Static Versus Dynamic SQL Statements
  • Embedded PL/SQL Blocks
  • Host Variables and Indicator Variables
  • Oracle Datatypes
  • Tables
  • Errors and Warnings
    • SQLCODE/SQLSTATE Status Variables
    • SQLCA Status Variable
    • WHENEVER Statement
    • ORACA
    • Precompiler Options and Error Handling
• 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
    • An Example
  • Precompiler Option DECLARE_SECTION
  • Using the INCLUDE Statement
    • Filename Extensions
    • Search Paths
• Nested Programs
  • Support for Nested Programs
    • Declaring the SQLCA
    • Nested Program Example
• Conditional Precompilations
  • An Example
  • Defining Symbols
• Separate Precompilations
  • Guidelines
    • Referencing Cursors
    • Specifying MAXOPENCURSORS
    • Using a Single SQLCA
    • Using a Single DATE_FORMAT
  • 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
    • Named Database Connections
  • Automatic Logons
    • The AUTO_CONNECT Precompiler Option
  • Changing Passwords at Runtime
  • Connect Without Alter Authorization
    • Standard CONNECT
    • SYSDBA or SYSOPER Privileges
  • 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
    • Restrictions
• 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 Oracle Database 10g Datatypes
  • Internal Datatypes
  • External Datatypes
    • CHAR
    • CHARF
    • CHARZ
    • DATE
    • DECIMAL
    • DISPLAY
    • FLOAT
    • INTEGER
    • LONG
    • LONG RAW
    • LONG VARCHAR
    • LONG VARRAW
    • NUMBER
    • OVER-PUNCH
    • RAW
    • ROWID
    • STRING
    • UNSIGNED
    • VARCHAR
    • VARCHAR2
    • VARNUM
    • VARRAW
    • SQL Pseudocolumns and Functions
• Datetime and Interval Datatype Descriptors
• Host Variables
  • Declaring Host Variables
    • Example Declarations
    • Initialization
    • Restrictions
  • Referencing Host Variables
    • Group Items as Host Variables
    • Restrictions
• Indicator Variables
  • Using Indicator Variables
    • On Input
    • On Output
  • Declaring Indicator Variables
  • Referencing Indicator Variables
    • Use in Where Clauses
    • Avoid Error Messages
    • ANSI Requirements
    • Indicator Variables for Multibyte NCHAR Variables
    • Indicator Variables with Host Group Items
• 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
    • On Input
    • On Output
  • Varying-Length Variables
    • On Input
    • On Output
• 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
  • Various Combinations of PIC X/PIC N Variables and NCHAR/CHAR Columns
    • PIC X and NCHAR Column
    • PIC N and CHAR column
• Datatype Conversion
• Explicit Control Over DATE String Format
• Datatype Equivalencing
  • Usefulness of Equivalencing
  • Host Variable Equivalencing
    • CONVBUFSZ Clause in VAR Statement
    • An Example
  • 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
    • Available Clauses
  • 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
    • Handling NULLs
    • Handling Truncated Values
  • SQLCHECK
• Advantages of PL/SQL
  • Better Performance
  • Integration with Oracle9i
  • Cursor FOR Loops
  • Subprograms
    • Parameter Modes
  • 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
    • Optional Keyword EXECUTE to 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
    • Anonymous PL/SQL Block
    • CALL Statement
    • CALL Example
  • 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
    • Opening Indirectly through a Stored PL/SQL Procedure
    • Opening Directly from Your Pro*COBOL Application
  • Fetching from a Cursor Variable
  • Closing a Cursor Variable
  • Freeing a Cursor Variable
  • Restrictions on Cursor Variables
  • Sample Program 11: Cursor Variables
    • SAMPLE11.SQL
    • SAMPLE11.PCO

7 Host Tables

• Host Tables
• Advantages of Host Tables
• Tables in Data Manipulation Statements
  • Declaring Host Tables
    • Restrictions
  • 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
    • In a SELECT Statement
    • With the CURRENT OF Clause
• 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
    • SQLSTATE Values
• Using the SQL Communications Area
  • Contents of the SQLCA
  • Declaring the SQLCA
  • Key Components of Error Reporting
    • Status Codes
    • Warning Flags
    • Rows-Processed Count
    • Parse Error Offset
    • Error Message Text
  • SQLCA Structure
    • SQLCAID
    • SQLCABC
    • SQLCODE
    • SQLERRM
    • SQLERRD
    • SQLWARN
    • SQLEXT
  • PL/SQL Considerations
  • Getting the Full Text of Error Messages
  • DSNTIAR
  • WHENEVER Directive
    • Conditions
    • SQLWARNING
    • SQLERROR
    • NOT FOUND or NOTFOUND
    • Actions
    • CONTINUE
    • DO CALL
    • DO PERFORM
    • GOTO or GO TO
    • STOP
  • Coding the WHENEVER Statement
    • DO PERFORM
    • DO CALL
    • Scope
    • Careless Usage: Examples
  • 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
    • ORACAID
    • ORACABC
    • ORACCHF
    • ORADBGF
    • ORAHCHF
    • ORASTXTF
    • Diagnostics
    • ORASTXT
    • ORASFNM
    • ORASLNR
    • Cursor Cache Statistics
    • ORAHOC
    • ORAMOC
    • ORACOC
    • ORANOR
    • ORANPR
    • ORANEX
  • 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
    • Avoiding Common Errors
• 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
    • Variables
    • Examples
  • DEALLOCATE DESCRIPTOR
  • GET DESCRIPTOR
  • SET DESCRIPTOR
    • Example
  • 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
    • Host Tables of SQL-CONTEXT Are Not Allowed
    • EXEC SQL ENABLE THREADS
    • EXEC SQL CONTEXT ALLOCATE
    • EXEC SQL CONTEXT USE
    • EXEC SQL CONTEXT FREE
  • Communication with Pro*C/C++ Programs
  • Multithreading Programming Considerations
    • Restrictions on Multithreading
  • Multiple Context Examples
    • Example 1
    • Example 2
    • Example 3
    • Example 4
    • Example 5
• 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
    • Internal LOBs
    • External LOBs
    • Temporary LOBs
    • Freeing LOBs
• 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
    • Advantages
    • Scope of EXEC ORACLE
  • Configuration Files
• Scope of Precompiler Options
• Quick Reference
• Using Pro*COBOL Precompiler Options
  • ASACC
  • ASSUME_SQLCODE
  • AUTO_CONNECT
  • CHARSET_PICX
  • CHARSET_PICN
  • 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 Operating System Dependencies

• System-Specific References in this Manual
  • COBOL Versions
  • Host Variables
    • Declaring
    • Naming
  • 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

B Reserved Words, Keywords, and Namespaces

• Reserved Words and Keywords
• Reserved Namespaces

C Performance Tuning

• Causes of Poor Performance
• Improving Performance
• Using Host Tables
• Using PL/SQL and Java
• Optimizing SQL Statements
  • Optimizer Hints
    • Giving Hints
• Using Indexes
• Taking Advantage of Row-Level Locking
• Eliminating Unnecessary Parsing
  • Handling Explicit Cursors
    • Cursor Control
  • Using the Cursor Management Options
    • Private SQL Areas and Cursor Cache
    • Resource Use
    • Infrequent Execution
    • Frequent Execution
    • Effect on the Shared SQL Area
    • Embedded PL/SQL Considerations
    • Parameter Interactions
• Avoiding Unnecessary Reparsing

D Syntactic and Semantic Checking

• Syntactic and Semantic Checking Basics
• Controlling the Type and Extent of Checking
• Specifying SQLCHECK=SEMANTICS
  • Enabling a Semantic Check
    • Connecting to Oracle
    • Using DECLARE TABLE

E 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