Table of Contents
- List of Tables
- Title and Copyright Information
- Preface
- Changes in This Release for Pro*COBOL Programmer's Guide
-
Part I Introduction and Concepts
- 1 Introduction
-
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.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.8 Tables
- 2.1.9 Errors and Warnings
- 2.1.10 SQL99 Syntax Support
-
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.5 Commas
- 2.2.6 Comments
- 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.4 Nested Programs
- 2.5 Conditional Precompilations
- 2.6 Separate Precompilations
- 2.7 Compiling and Linking
- 2.8 Sample DEPT and EMP Tables
- 2.9 Sample EMP Program: SAMPLE1.PCO
-
2.1
Key Concepts of Embedded SQL Programming
-
3
Database Concepts
- 3.1 Connecting to Oracle
- 3.2 Default Databases and Connections
- 3.3 Key Terms
- 3.4 How Transactions Guard a Database
- 3.5 Beginning and Ending Transactions
- 3.6 Using the COMMIT Statement
- 3.7 Using the ROLLBACK Statement
- 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.12 Fetching Across Commits
- 3.13 Handling Distributed Transactions
- 3.14 Guidelines for Transaction Processing
-
4
Datatypes and Host Variables
-
4.1
The Oracle Database Datatypes
- 4.1.1 Internal Datatypes
-
4.1.2
External Datatypes
- 4.1.2.1 CHAR
- 4.1.2.2 CHARF
- 4.1.2.3 CHARZ
- 4.1.2.4 DATE
- 4.1.2.5 DECIMAL
- 4.1.2.6 DISPLAY
- 4.1.2.7 FLOAT
- 4.1.2.8 INTEGER
- 4.1.2.9 LONG
- 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.15 RAW
- 4.1.2.16 ROWID
- 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.4 Indicator Variables
- 4.5 VARCHAR Variables
- 4.6 Handling Character Data
- 4.7 Universal ROWIDs
- 4.8 Globalization Support
- 4.9 Unicode Support for Pro*COBOL
- 4.10 Datatype Conversion
- 4.11 Explicit Control Over DATE String Format
- 4.12 Datatype Equivalencing
- 4.13 Platform Endianness Support
- 4.14 Sample Program 4: Datatype Equivalencing
-
4.1
The Oracle Database Datatypes
- 5 Embedded SQL
-
6
Embedded PL/SQL
- 6.1 Embedding PL/SQL
- 6.2 Advantages of PL/SQL
- 6.3 Embedding PL/SQL Blocks
- 6.4 Host Variables and PL/SQL
- 6.5 Indicator Variables and PL/SQL
- 6.6 Host Tables and PL/SQL
- 6.7 Cursor Usage in Embedded PL/SQL
- 6.8 Stored PL/SQL and Java Subprograms
- 6.9 Sample Program 9: Calling a Stored Procedure
- 6.10 Cursor Variables
-
7
Host Tables
- 7.1 Host Tables
- 7.2 Advantages of Host Tables
- 7.3 Tables in Data Manipulation Statements
- 7.4 Selecting into Tables
- 7.5 Inserting with Tables
- 7.6 Updating with Tables
- 7.7 Deleting with Tables
- 7.8 Using Indicator Tables
- 7.9 The FOR 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
- 7.14 Additional Array Insert/Select Syntax
- 7.15 Using Implicit Buffered Insert
-
8
Error Handling and Diagnostics
- 8.1 Why Error Handling is Needed
- 8.2 Error Handling Alternatives
-
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.4 SQLCA Structure
- 8.3.5 PL/SQL Considerations
- 8.3.6 Getting the Full Text of Error Messages
- 8.3.7 DSNTIAR
- 8.3.8 WHENEVER Directive
- 8.3.9 Coding the WHENEVER Statement
- 8.3.10 Getting the Text of SQL Statements
- 8.4 Using the Oracle Communications Area
- 8.5 How Errors Map to SQLSTATE Codes
-
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.7 Using Method 1
- 9.8 Sample Program 6: Dynamic SQL Method 1
- 9.9 Using Method 2
- 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.14 Using the DECLARE STATEMENT Statement
- 9.15 Using Host Tables
- 9.16 Using PL/SQL
- 9.17 Dynamic SQL Statement Caching
-
Part II Applications
-
10
ANSI Dynamic SQL
- 10.1 Basics of ANSI Dynamic SQL
- 10.2 Overview of ANSI SQL Statements
- 10.3 Sample Code
- 10.4 Oracle Extensions
- 10.5 ANSI Dynamic SQL Precompiler Options
-
10.6
Full Syntax of the Dynamic SQL Statements
- 10.6.1 ALLOCATE DESCRIPTOR
- 10.6.2 DEALLOCATE DESCRIPTOR
- 10.6.3 GET DESCRIPTOR
- 10.6.4 SET DESCRIPTOR
- 10.6.5 Use of PREPARE
- 10.6.6 DESCRIBE INPUT
- 10.6.7 DESCRIBE OUTPUT
- 10.6.8 EXECUTE
- 10.6.9 Use of EXECUTE IMMEDIATE
- 10.6.10 Use of DYNAMIC DECLARE CURSOR
- 10.6.11 OPEN Cursor
- 10.6.12 FETCH
- 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.2 Understanding the SQL Descriptor Area (SQLDA)
- 11.3 The SQLDA Variables
- 11.4 Prerequisite Knowledge
- 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.4 User Interface Features for Multithreaded Applications
- 12.5 Multithreaded Example
-
13
Large Objects (LOBs)
- 13.1 Using LOBs
- 13.2 How to Use LOBs
- 13.3 Rules for LOB Statements
-
13.4
LOB Statements
- 13.4.1 APPEND
- 13.4.2 ASSIGN
- 13.4.3 CLOSE
- 13.4.4 COPY
- 13.4.5 CREATE TEMPORARY
- 13.4.6 DISABLE BUFFERING
- 13.4.7 ENABLE BUFFERING
- 13.4.8 ERASE
- 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.14 OPEN
- 13.4.15 READ
- 13.4.16 TRIM
- 13.4.17 WRITE
- 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.2 Actions During Precompilation
- 14.3 About the Options
- 14.4 Entering Precompiler Options
- 14.5 Scope of Precompiler Options
- 14.6 Quick Reference
-
14.7
Using Pro*COBOL Precompiler Options
- 14.7.1 ASACC
- 14.7.2 ASSUME_SQLCODE
- 14.7.3 AUTO_CONNECT
- 14.7.4 CHARSET_PICX
- 14.7.5 CHARSET_PICN
- 14.7.6 CLOSE_ON_COMMIT
- 14.7.7 COMMON_PARSER
- 14.7.8 CONFIG
- 14.7.9 DATE_FORMAT
- 14.7.10 DB2_ARRAY
- 14.7.11 DBMS
- 14.7.12 DECLARE_SECTION
- 14.7.13 DEFINE
- 14.7.14 DYNAMIC
- 14.7.15 END_OF_FETCH
- 14.7.16 ERRORS
- 14.7.17 File_ID
- 14.7.18 FIPS
- 14.7.19 FORMAT
- 14.7.20 HOLD_CURSOR
- 14.7.21 HOST
- 14.7.22 IMPLICIT_SVPT
- 14.7.23 INAME
- 14.7.24 INCLUDE
- 14.7.25 IRECLEN
- 14.7.26 LITDELIM
- 14.7.27 LNAME
- 14.7.28 LRECLEN
- 14.7.29 LTYPE
- 14.7.30 MAX_ROW_INSERT
- 14.7.31 MAXLITERAL
- 14.7.32 MAXOPENCURSORS
- 14.7.33 MODE
- 14.7.34 NESTED
- 14.7.35 NLS_LOCAL
- 14.7.36 ONAME
- 14.7.37 ORACA
- 14.7.38 ORECLEN
- 14.7.39 OUTLINE
- 14.7.40 OUTLNPREFIX
- 14.7.41 PAGELEN
- 14.7.42 PICN_ENDIAN
- 14.7.43 PICX
- 14.7.44 PLAN_BASELINE
- 14.7.45 PLAN_PREFIX
- 14.7.46 PLAN_RUN
- 14.7.47 PLAN_FIXED
- 14.7.48 PLAN_ENABLED
- 14.7.49 MEMFORPREFETCH
- 14.7.50 PREFETCH
- 14.7.51 RELEASE_CURSOR
- 14.7.52 RUNOUTLINE
- 14.7.53 SELECT_ERROR
- 14.7.54 SQLCHECK
- 14.7.55 STMT_CACHE
- 14.7.56 TYPE_CODE
- 14.7.57 UNSAFE_NULL
- 14.7.58 USERID
- 14.7.59 VARCHAR
- 14.7.60 XREF
-
10
ANSI Dynamic SQL
- A Operating System Dependencies
- B Reserved Words, Keywords, and Namespaces
-
C
Performance Tuning
- C.1 Causes of Poor Performance
- C.2 Improving Performance
- C.3 Using Host Tables
- C.4 Using PL/SQL and Java
- C.5 Optimizing SQL Statements
- C.6 SQL Statement Caching
- C.7 Using Indexes
- C.8 Taking Advantage of Row-Level Locking
- C.9 Eliminating Unnecessary Parsing
- C.10 Avoiding Unnecessary Reparsing
- C.11 About Using Oracle Connection Manager in Traffic Director Mode
- D Syntactic and Semantic Checking
-
E
Embedded SQL Statements and Precompiler Directives
- E.1 Summary of Precompiler Directives and Embedded SQL Statements
- E.2 About the Statement Descriptions
- E.3 How to Read Syntax Diagrams
- E.4 ALLOCATE (Executable Embedded SQL Extension)
- E.5 ALLOCATE DESCRIPTOR (Executable Embedded SQL)
- E.6 CALL (Executable Embedded SQL)
- E.7 CLOSE (Executable Embedded SQL)
- E.8 COMMIT (Executable Embedded SQL)
- E.9 CONNECT (Executable Embedded SQL Extension)
- E.10 CONTEXT ALLOCATE (Executable Embedded SQL Extension)
- E.11 CONTEXT FREE (Executable Embedded SQL Extension)
- E.12 CONTEXT USE (Oracle Embedded SQL Directive)
- E.13 DEALLOCATE DESCRIPTOR (Embedded SQL Statement)
- E.14 DECLARE CURSOR (Embedded SQL Directive)
- E.15 DECLARE DATABASE (Oracle Embedded SQL Directive)
- E.16 DECLARE STATEMENT (Embedded SQL Directive)
- E.17 DECLARE TABLE (Oracle Embedded SQL Directive)
- E.18 DELETE (Executable Embedded SQL)
- E.19 DESCRIBE (Executable Embedded SQL)
- E.20 DESCRIBE DESCRIPTOR (Executable Embedded SQL)
- E.21 ENABLE THREADS (Executable Embedded SQL Extension)
- E.22 EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
- E.23 EXECUTE (Executable Embedded SQL)
- E.24 EXECUTE DESCRIPTOR (Executable Embedded SQL
- E.25 EXECUTE IMMEDIATE (Executable Embedded SQL)
- E.26 FETCH (Executable Embedded SQL)
- E.27 FETCH DESCRIPTOR (Executable Embedded SQL)
- E.28 FREE (Executable Embedded SQL Extension)
- E.29 GET DESCRIPTOR (Executable Embedded SQL)
- E.30 INSERT (Executable Embedded SQL)
- E.31 LOB APPEND (Executable Embedded SQL Extension)
- E.32 LOB ASSIGN (Executable Embedded SQL Extension)
- E.33 LOB CLOSE (Executable Embedded SQL Extension)
- E.34 LOB COPY (Executable Embedded SQL Extension)
- E.35 LOB CREATE TEMPORARY (Executable Embedded SQL Extension)
- E.36 LOB DESCRIBE (Executable Embedded SQL Extension)
- E.37 LOB DISABLE BUFFERING (Executable Embedded SQL Extension)
- E.38 LOB ENABLE BUFFERING (Executable Embedded SQL Extension)
- E.39 LOB ERASE (Executable Embedded SQL Extension)
- E.40 LOB FILE CLOSE ALL (Executable Embedded SQL Extension)
- E.41 LOB FILE SET (Executable Embedded SQL Extension)
- E.42 LOB FLUSH BUFFER (Executable Embedded SQL Extension)
- E.43 LOB FREE TEMPORARY (Executable Embedded SQL Extension)
- E.44 LOB LOAD (Executable Embedded SQL Extension)
- E.45 LOB OPEN (Executable Embedded SQL Extension)
- E.46 LOB READ (Executable Embedded SQL Extension)
- E.47 LOB TRIM (Executable Embedded SQL Extension)
- E.48 LOB WRITE (Executable Embedded SQL Extension)
- E.49 OPEN (Executable Embedded SQL)
- E.50 OPEN DESCRIPTOR (Executable Embedded SQL)
- E.51 PREPARE (Executable Embedded SQL)
- E.52 ROLLBACK (Executable Embedded SQL)
- E.53 SAVEPOINT (Executable Embedded SQL)
- E.54 SELECT (Executable Embedded SQL)
- E.55 SET DESCRIPTOR (Executable Embedded SQL)
- E.56 UPDATE (Executable Embedded SQL)
- E.57 VAR (Oracle Embedded SQL Directive)
- E.58 WHENEVER (Embedded SQL Directive)
- F Pro*COBOL for Windows
- Index