Skip Headers
Pro*COBOL® Programmer's Guide
Relea se 9.2
Part Number A96109-03
Home
Book List
< /a>
Index
Master Index
Feedback
Next
View PDF
Contents
List of Figures
List of Tables
Title and Copyright Information
Send Us Your Comments
Preface
Intended Audience
Documentation Accessibility
Structure
Related Documents
Conventions
What's New in Pro*COBOL?
Oracl e9
i
Release 2 (9.2) New Features in Pro*COBOL
Oracle9
i
Release 1 (9.0. 1) Deprecated or Desupported Features in Pro*COBOL
Oracle9
i
Release 1 (9.0.1) N ew Features in Pro*COBOL
Oracle8
i
Release 8.1.6 New Features in Pro*COBOL
Oracle8
i
Release 8.1.5 New Features in Pro*COBOL
Oracle8
i
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 Preco mpiler
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
P recompiler Concepts
2.1
Key Concepts of Embedded SQL Programming
2.1.1
Steps in Developing an Embe dded 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
Hos t Variables and Indicator Variables
2.1.7
Oracle Datatypes< /a>
2.1.8
Tables
2.1.9
Errors and Warnings
2. 1.9.1
SQLCODE/SQLSTATE Status Variables
2.1.9.2
SQLC A Status Variable
2.1.9.3
WHENEVER Statement
< a href="pco02prc.htm#sthref120">
2.1.9.4
ORACA
2.1.9.5
Precompiler Options and Error Handling
2.2
Programming Guidelines
2.2.1< /span> Abbreviations
2.2.2
Case-Insensitivity
2.2.3
COBOL Versions Supported
2.2.4
Coding Areas
2.2.5
C ommas
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
< span class="secnum">2.2.11 Division Headers that are Optional
2.2.12
Embedded SQL Syntax
2.2.13
Figurative Cons tants
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< /a>
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
< span class="secnum">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
< dd>
3.2.2.1
Named Database Connections
< a href="pco03dbc.htm#sthref319">
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< /span> Standard CONNECT
3.2.5.2
SYSDBA or SYSOPER Privilege s
3.2.6
Using Links
3.3
Key Terms
3.4
How Transactions Guard a Database
3.5
Beginni ng and Ending Transactions
3.6
Using the COMMIT Statement
3.6.1
WITH HOLD Clause in DECLARE CURSOR Statement s
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 T RANSACTION 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.1
CHAR
4.1.2.2
CHARF
4.1.2.3
CH ARZ
4.1.2.4
DATE
4.1.2.5
DECIMAL
4.1.2.6
DIS PLAY
4.1.2.7
FLOAT
4.1.2.8
INTEGER
4.1.2.9
L ONG
4.1.2.10
LONG RAW
4.1.2.11
LONG VARCHAR
4.1.2.1 2
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.3.1
Declaring Host Variables
4.3.1.1
Example Declarations
4.3.1.2
Initializat ion
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
I ndicator Variables
4.4.1
Using Indicator Variable s
4.4.1.1
On Input
4.4.1.2
On Output
4.4.2
Declaring Indicator Variables
4.4.3
Refe rencing 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
Fix ed-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
Charac ter 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 St ring Format
4.12
Datatype Equivalencing
4.12.1
Usefulness of Equivalencing
4.12.2
Host Variable Equivalencing
< span class="secnum">4.12.2.1 CONVBUFSZ Clause in VAR Statement
4.12.2.2
An Example
4.12.3
Using the C HARF 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 V ariables
5.2.2
Output Variables
5.2.3
Inserting NULLs
5.2.4
Handling Returned NULLs
5.2.5
Fetching NULL s
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 Clause s
5.3.2
Inserting Rows
5.3.3
DML Returning Clause
5.3.4
Using Subqueries
5.3.5
Updating Rows< /a>
5.3.6
Deleting Rows
5.3.7
Using the WHERE Clause
5.4
Cursors
5.4.1
Declaring a Curs or
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 O F Clause
5.4.6
Restrictions
5.4.7
A Typical Sequence of Statements
5.4.8
Positioned Update
5.4.9
The PR EFETCH Precompiler Option
5.5
Sample Program 2: C ursor 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 NU LLs
6.1.3.2
Handling Truncated Values
6.1.4
SQLCHECK
6.2
Advantages of PL/SQL
6. 2.1
Better Performance
6.2.2
Integration with Oracle 9
i
6.2.3
Cursor FOR Loops
6.2.4
Subprograms
6.2.4.1
Parameter Modes
6.2.5
Packages
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 Pr ogram 9: Calling a Stored Procedure
6.9.1
Remote Access
6.10
Cursor Variables
< dd>
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 Var iable
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 Hos t 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 Con taining 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
Usin g 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< /a>
7.5.1
Restrictions on Host Tables
< /dd>
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 Ind icator 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.1
SQLCA
8.2.2 ORACA
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 S QLCA
8.3.3
Key Components of Error Reporting
8.3.3.1
Status Codes
8.3.3.2
Warning Flags
8.3.3.3< /span> 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
SQ LERRD
8.3.4.6
SQLWARN
8.3.4.7
SQLEXT
8 .3.5
PL/SQL Considerations
8.3.6
Getting the Full T ext of Error Messages
8.3.7
DSNTIAR
8.3.8
WHENEVER Directive
8.3.8.1
Conditions
8.3.8.2
S QLWARNING
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.8.10
STOP
8.3.9
Coding the WHENEVER Statement
8.3.9.1
DO PERFORM
8.3.9.2 DO CALL
8.3.9.3
Scope
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
Declarin g the ORACA
8.4.3
Enabling the ORACA
8.4.4
Choosing Runtime Options
< span class="secnum">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
< span class="secnum">8.4.5.10 ORASLNR
8.4.5.11
Curso r 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 Exa mple Program
8.5
How Errors Map to SQLSTATE Code s
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
H ow Dynamic SQL Statements Are Processed
9.6
Methods for Us ing Dynamic SQL
9.6.1
Method 1
9.6.2
Method 2
9.6.3
Method 3
9.6.4
Method 4
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
U sing Method 3
9.11.1
PREPARE
9.11.2
DECLARE
9.11.3
OPEN
9.11.4
FETCH
9.11.5
CLOSE
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 H ost 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
9.16.5
Caution
10
ANSI Dynamic SQL
< span class="secnum">10.1 Basics of ANSI Dynamic SQL
10.1.1
Precompiler Options
10.2
Overv iew of ANSI SQL Statements
10.3
Sample Code
< a href="pco10ady.htm#sthref1520">
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 DESC RIPTOR
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
< dd>
10.6.4.1
Example
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
U se 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
Restri ctions
10.7
Sample Programs: SAMPLE12.PCO
11
Oracle Dynamic SQL: Method 4
11.1
Meeting the Special Requir ements 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)
< span class="secnum">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 D ata
11.4.3
Coercing Datatypes
11.4.4
Handling NULL/Not NULL Datatypes
11.5
The Basic Steps
11.6< /span> 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
< /dd>
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 Selec t-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
R untime Contexts in Pro*COBOL
12.3
Runtime Context Usage Mo dels
12.3.1
Multiple Threads Sharing a Single Ru ntime Context
12.3.2
Multiple Threads Sharing Multiple Run time Contexts
12.4
User Interface Features for M ultithreaded Applications
12.4.1
THREADS Option< /a>
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
< span class="secnum">12.4.5.4 Example 4
12.4.5.5
Exa mple 5
12.5
Multithreaded Example
< /dd>
13
Large Objects (LOBs)
13.1
Using LOBs
13.1.1
Internal LOBs
13.1.2 External LOBs
13.1.3
Secur ity 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.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.1.1
Case-Sensitivity
14.2
Actions During Precompila tion
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
Ente ring Precompiler Options
14.4.1
On the Command L ine
14.4.2
Inline
14.4.2.1
Advantages
14.4.2.2
Scope of EXEC ORACLE
14.4.3
Conf iguration Files
14.5
Scope of Precompiler Option s
14.6
Quick Reference
14.7
Using Pro*COBOL Precompiler Options
14.7.1
ASACC
14.7.2
ASSUM E_SQLCODE
14.7.3
AUTO_CONNECT
14.7.4
CLOSE_ON_COMMIT
14.7.5
CONFIG
14.7.6
DATE_FORMAT
14.7.7
DBMS
14.7.8
DECLARE_SECTION
14.7.9
DEFINE
14.7.10
DYNAMIC
14.7.11
END_OF_FETCH
14.7.12
E RRORS
14.7.13
FIPS
14.7.14
FORMAT
14.7.15
HOLD_CURSOR
14.7.16
HOST
14.7.17
INAME
14.7.18< /span> INCLUDE
14.7.19
IRECLEN
14.7.20
LITDELIM
14.7.21
LNAME
14.7.22
LRECLEN
14.7.23
LTYPE
14.7.24
MAXLITERAL
14.7.25
MAXOPENCURSORS
14.7.26
MODE
14.7.27 NESTED
14.7.28
NLS_LOCAL
14.7.29
ONAME
14.7.30
ORACA
14.7.31
ORECLE N
14.7.32
PAGELEN
14.7.33
PICX
14.7.34
PR EFETCH
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.41
USERID
14.7.42
VARCHAR
14.7.43
XREF
A
New Features
A .1
New Features of Release 9.0.1
A.1.1
Gl obalization Support
A.1.2
New Datetime Datatypes
< /dl>
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
< /dd>
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 Divis ion 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
Suppo rt of the WITH HOLD Clause in the DECLARE CURSOR Statement
A.3.7< /span> New Precompiler Option CLOSE_ON_COMMIT
A.3.8
Suppor t for DSNTIAR
A.3.9
Date String Format Precompiler Option< /a>
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.2.2
Naming
B.1.3
INCLUDE Statements
B.1.4
MAXLI TERAL Default
B.1.5
PIC N or Pic G Clause for Multi-byte G lobalization 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 Tun ing
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 Unne cessary Parsing
D.8.1
Handling Explicit Cursors< /a>
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< /a>
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 t o Oracle
E.3.1.2
Using DECLARE TABLE
F
Embedded SQL S tatements and Precompiler Directives
F.1
Summa ry 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-p art 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 Embe dded SQL Extension)
F.12
CONTEXT USE (Oracle Embedded SQL Directive)
F.13
DEALLOCATE DESCRIPTOR (Embedded SQL Statem ent)
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 DES CRIPTOR (Executable Embedded SQL
F.25
EXECUTE IMMEDIATE (E xecutable Embedded SQL)
F.26
FETCH (Executable Embedded SQ L)
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< /span> 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 (Ex ecutable Embedded SQL Extension)
F.47
LOB TRIM (Executable Embedded SQL Extension)
F.48
LOB WRITE (Executable Embedd ed SQL Extension)
F.49
OPEN (Executable Embedded SQL)
< /dd>
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 DESCR IPTOR (Executable Embedded SQL)
F.56
UPDATE (Executable Em bedded SQL)
F.57
VAR (Oracle Embedded SQL Directive)
F.58
WHENEVER (Embedded SQL Directive)
Index
<
br />
Master Index