List of Tables
Title and Copyright Information
Preface
- Intended Audience
- Structure
- Documentation Accessibility
- Related Documents
- Conventions
1 Getting Acquainted
- 1.1 What Is an Oracle Precompiler?
  - 1.1.1 Language Alternatives
- 1.2 Why Use an Oracle Precompiler?
- 1.3 Why Use SQL?
- 1.4 Why Use PL/SQL?
- 1.5 What Do the Oracle Precompilers Offer?
- 1.6 Do the Oracle Precompilers Meet Industry Standards?
2 Learning the Basics
- 2.1 Key Concepts of Embedded SQL Programming
- 2.2 Steps in Developing an Embedded SQL Application
- 2.3 A Sample Program
- 2.4 Sample Tables
  - 2.4.1 Sample Data
3 Meeting Program Requirements
- 3.1 The Declare Section
  - 3.1.1 An Example
- 3.2 INCLUDE Statements
- 3.3 The SQLCA
- 3.4 Oracle Datatypes
- 3.5 Datatype Conversion
  - 3.5.1 DATE Values
  - 3.5.2 RAW and LONG RAW Values
- 3.6 Declaring and Referencing Host Variables
- 3.7 Declaring and Referencing Indicator Variables
- 3.8 Datatype Equivalencing
- 3.9 Globalization Support
- 3.10 Multibyte Globalization Support Character Sets
- 3.11 Concurrent Logons
- 3.12 Embedding OCI (Oracle Call Interface) Calls
  - 3.12.1 About Setting Up the LDA
  - 3.12.2 Remote and Multiple Connections
- 3.13 About Developing X/Open Applications
4 Using Embedded SQL
- 4.1 About Using Host Variables
  - 4.1.1 Output versus Input Host Variables
- 4.2 About Using Indicator Variables
- 4.3 The Basic SQL Statements
- 4.4 Cursors
- 4.5 Cursor Variables
5 Using Embedded PL/SQL
- 5.1 Advantages of PL/SQL
- 5.2 About Embedding PL/SQL Blocks
- 5.3 About Using Host Variables
- 5.4 About Using Indicator Variables
  - 5.4.1 Handling Nulls
  - 5.4.2 Handling Truncated Values
- 5.5 About Using Host Arrays
  - 5.5.1 ARRAYLEN Statement
- 5.6 About Using Cursors
  - 5.6.1 An Alternative
- 5.7 Stored Subprograms
- 5.8 About Using Dynamic PL/SQL
  - 5.8.1 Restriction
6 Running the Oracle Precompilers
- 6.1 The Precompiler Command
- 6.2 What Occurs during Precompilation?
- 6.3 Precompiler Options
- 6.4 Entering Options
- 6.5 Scope of Options
- 6.6 Quick Reference
- 6.7 About Using the Precompiler Options
- 6.8 Conditional Precompilations
  - 6.8.1 An Example
  - 6.8.2 Defining Symbols
- 6.9 Separate Precompilations
  - 6.9.1 Guidelines
  - 6.9.2 Restrictions
- 6.10 Compiling and Linking
  - 6.10.1 System-Dependent
  - 6.10.2 Multibyte Globalization Support Compatibility
7 Defining and Controlling Transactions
- 7.1 Some Terms You Should Know
- 7.2 How Transactions Guard Your Database
- 7.3 How to Begin and End Transactions
- 7.4 About Using the COMMIT Statement
- 7.5 About Using the ROLLBACK Statement
  - 7.5.1 Statement-Level Rollbacks
- 7.6 About Using the SAVEPOINT Statement
- 7.7 About Using the RELEASE Option
- 7.8 About Using the SET TRANSACTION Statement
- 7.9 About Overriding Default Locking
- 7.10 About Fetching Across Commits
- 7.11 About Handling Distributed Transactions
- 7.12 Guidelines
8 Error Handling and Diagnostics
- 8.1 The Need for Error Handling
- 8.2 Error Handling Alternatives
- 8.3 About Using Status Variables when MODE={ANSI|ANSI14}
- 8.4 About Using the SQL Communications Area
- 8.5 About Using the Oracle Communications Area
9 Using Host Arrays
- 9.1 What Is a Host Array?
- 9.2 Why Use Arrays?
- 9.3 Declaring Host Arrays
  - 9.3.1 Dimensioning Arrays
  - 9.3.2 Restrictions
- 9.4 About Using Arrays in SQL Statements
- 9.5 About Selecting into Arrays
- 9.6 About Using Indicator Arrays
- 9.7 About Using the FOR Clause
- 9.8 About Using the WHERE Clause
- 9.9 About Mimicking the CURRENT OF Clause
- 9.10 About Using SQLERRD(3)
10 Using Dynamic SQL
- 10.1 What Is Dynamic SQL?
- 10.2 Advantages and Disadvantages of Dynamic SQL
- 10.3 When to Use Dynamic SQL
- 10.4 Requirements for Dynamic SQL Statements
- 10.5 How Dynamic SQL Statements Are Processed
- 10.6 Methods for Using Dynamic SQL
- 10.7 About Using Method 1
  - 10.7.1 The EXECUTE IMMEDIATE Statement
  - 10.7.2 An Example
- 10.8 About Using Method 2
  - 10.8.1 The USING Clause
  - 10.8.2 An Example
- 10.9 About Using Method 3
- 10.10 Using Method 4
- 10.11 About Using the DECLARE STATEMENT Statement
  - 10.11.1 Usage of Host Arrays
- 10.12 About Using PL/SQL
11 Writing User Exits
- 11.1 What Is a User Exit?
- 11.2 Why Write a User Exit?
- 11.3 Developing a User Exit
- 11.4 Writing a User Exit
- 11.5 Calling a User Exit
- 11.6 Passing Parameters to a User Exit
- 11.7 Returning Values to a Form
- 11.8 An Example
- 11.9 About Precompiling and Compiling a User Exit
- 11.10 About Using the GENXTB Utility
- 11.11 About Linking a User Exit into SQL*Forms
- 11.12 Guidelines for SQL*Forms User Exits
- 11.13 EXEC TOOLS Statements
A New Features
- A.1 About Fetching NULLs without Using Indicator Variables
  - A.1.1 About Using DBMS=V7 and MODE=ORACLE
  - A.1.2 Related Error Messages
- A.2 Additional Array Insert/Select Syntax
- A.3 SQL99 Syntax Support
- A.4 About Fixing Execution Plans
- A.5 About Using Implicit Buffered Insert
- A.6 Dynamic SQL Statement Caching
- A.7 Scrollable Cursors
- A.8 Platform Endianness Support
- A.9 Flexible B Area Length
B Oracle Reserved Words, Keywords, and Namespaces
- B.1 Oracle Reserved Words
- B.2 Oracle Keywords
- B.3 PL/SQL Reserved Words
- B.4 Oracle Reserved Namespaces
C Performance Tuning
- C.1 What Causes Poor Performance?
- C.2 How Can Performance be Improved?
- C.3 Using Host Arrays
- C.4 Using Embedded PL/SQL
- C.5 Optimizing SQL Statements
- C.6 About Using Indexes
- C.7 Taking Advantage of Row-Level Locking
- C.8 About Eliminating Unnecessary Parsing
D Syntactic and Semantic Checking
- D.1 What Is Syntactic and Semantic Checking?
- D.2 About Controlling the Type and Extent of Checking
- D.3 About Specifying SQLCHECK=SEMANTICS
E Embedded SQL Commands and Directives
- E.1 Summary of Precompiler Directives and Embedded SQL Commands
- E.2 About The Command Descriptions
- E.3 How to Read Syntax Diagrams
  - E.3.1 Required Keywords and Parameters
  - E.3.2 Optional Keywords and Parameters
  - E.3.3 Syntax Loops
  - E.3.4 Multi-part Diagrams
  - E.3.5 Database Objects
- E.4 ALLOCATE (Executable Embedded SQL Extension)
  - E.4.1 Allocate Purpose
  - E.4.2 Allocate Prerequisites
  - E.4.3 Allocate Syntax
  - E.4.4 Allocate Keywords and Parameters
  - E.4.5 Allocate Usage Notes
  - E.4.6 Allocate Related Topics
- E.5 CLOSE (Executable Embedded SQL)
  - E.5.1 CLOSE Purpose
  - E.5.2 CLOSE Prerequisites
  - E.5.3 CLOSE Syntax
  - E.5.4 CLOSE Keywords and Parameters
  - E.5.5 CLOSE Usage Notes
  - E.5.6 CLOSE Example
  - E.5.7 CLOSE Related Topics
- E.6 COMMIT (Executable Embedded SQL)
  - E.6.1 COMMIT Purpose
  - E.6.2 COMMIT Prerequisites
  - E.6.3 COMMIT Syntax
  - E.6.4 COMMIT Keyword and Parameters
  - E.6.5 COMMIT Usage Notes
  - E.6.6 COMMIT Related Topics
- E.7 CONNECT (Executable Embedded SQL Extension)
  - E.7.1 CONNECT Purpose
  - E.7.2 CONNECT Prerequisites
  - E.7.3 CONNECT Syntax
  - E.7.4 CONNECT Keyword and Parameters
  - E.7.5 CONNECT Usage Notes
  - E.7.6 CONNECT Related Topics
- E.8 DECLARE CURSOR (Embedded SQL Directive)
  - E.8.1 DECLARE CURSOR Purpose
  - E.8.2 DECLARE CURSOR Prerequisites
  - E.8.3 DECLARE CURSOR Syntax
  - E.8.4 DECLARE CURSOR Keywords and Parameters
  - E.8.5 DECLARE CURSOR Usage Notes
  - E.8.6 DECLARE CURSOR Example
  - E.8.7 DECLARE CURSOR Related Topics
- E.9 DECLARE DATABASE (Oracle Embedded SQL Directive)
  - E.9.1 DECLARE DATABASE Purpose
  - E.9.2 DECLARE DATABASE Prerequisites
  - E.9.3 DECLARE DATABASE Syntax
  - E.9.4 DECLARE DATABASE Keywords and Parameters
  - E.9.5 DECLARE DATABASE Usage Notes
  - E.9.6 DECLARE DATABASE Example
  - E.9.7 DECLARE DATABASE Related Topics
- E.10 DECLARE STATEMENT (Embedded SQL Directive)
  - E.10.1 DECLARE STATEMENT Purpose
  - E.10.2 DECLARE STATEMENT Prerequisites
  - E.10.3 DECLARE STATEMENT Syntax
  - E.10.4 DECLARE STATEMENT Keywords and Parameters
  - E.10.5 DECLARE STATEMENT Usage Notes
  - E.10.6 DECLARE STATEMENT Example I
  - E.10.7 DECLARE STATEMENT Example II
  - E.10.8 DECLARE STATEMENT Related Topics
- E.11 DECLARE TABLE (Oracle Embedded SQL Directive)
  - E.11.1 DECLARE TABLE Purpose
  - E.11.2 DECLARE TABLE Prerequisites
  - E.11.3 DECLARE TABLE Syntax
  - E.11.4 DECLARE TABLE Keywords and Parameters
  - E.11.5 DECLARE TABLE Usage Notes
  - E.11.6 DECLARE TABLE Example
  - E.11.7 DECLARE TABLE Related Topics
- E.12 DELETE (Executable Embedded SQL)
  - E.12.1 DELETE Purpose
  - E.12.2 DELETE Prerequisites
  - E.12.3 DELETE Syntax
  - E.12.4 DELETE Keywords and Parameters
  - E.12.5 DELETE Usage Notes
  - E.12.6 DELETE Example
  - E.12.7 DELETE Related Topics
- E.13 DESCRIBE (Executable Embedded SQL)
  - E.13.1 DESCRIBE Purpose
  - E.13.2 DESCRIBE Prerequisites
  - E.13.3 DESCRIBE Syntax
  - E.13.4 DESCRIBE Keywords and Parameters
  - E.13.5 DESCRIBE Usage Notes
  - E.13.6 DESCRIBE Example
  - E.13.7 DESCRIBE Related Topics
- E.14 EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
  - E.14.1 EXECUTE ... END-EXEC Purpose
  - E.14.2 EXECUTE ... END-EXEC Prerequisites
  - E.14.3 EXECUTE ... END-EXEC Syntax
  - E.14.4 EXECUTE ... END-EXEC Keywords and Parameters
  - E.14.5 EXECUTE ... END-EXEC Usage Notes
  - E.14.6 EXECUTE ... END-EXEC Example
  - E.14.7 EXECUTE ... END-EXEC Related Topics
- E.15 EXECUTE (Executable Embedded SQL)
  - E.15.1 EXECUTE Purpose
  - E.15.2 EXECUTE Prerequisites
  - E.15.3 EXECUTE Syntax
  - E.15.4 EXECUTE Keywords and Parameters
  - E.15.5 EXECUTE Usage Notes
  - E.15.6 EXECUTE Example
  - E.15.7 EXECUTE Related Topics
- E.16 EXECUTE IMMEDIATE (Executable Embedded SQL)
  - E.16.1 EXECUTE IMMEDIATE Purpose
  - E.16.2 EXECUTE IMMEDIATE Prerequisites
  - E.16.3 EXECUTE IMMEDIATE Syntax
  - E.16.4 EXECUTE IMMEDIATE Keywords and Parameters
  - E.16.5 EXECUTE IMMEDIATE Usage Notes
  - E.16.6 EXECUTE IMMEDIATE Example
  - E.16.7 EXECUTE IMMEDIATE Related Topics
- E.17 FETCH (Executable Embedded SQL)
  - E.17.1 FETCH Purpose
  - E.17.2 FETCH Prerequisites
  - E.17.3 FETCH Syntax
  - E.17.4 FETCH Keywords and Parameters
  - E.17.5 FETCH Usage Notes
  - E.17.6 FETCH Example
  - E.17.7 FETCH Related Topics
- E.18 INSERT (Executable Embedded SQL)
  - E.18.1 INSERT Purpose
  - E.18.2 INSERT Prerequisites
  - E.18.3 INSERT Syntax
  - E.18.4 INSERT Keywords and Parameters
  - E.18.5 INSERT Usage Notes
  - E.18.6 INSERT Example I
  - E.18.7 INSERT Example II
  - E.18.8 INSERT Related Topics
- E.19 OPEN (Executable Embedded SQL)
  - E.19.1 OPEN Purpose
  - E.19.2 OPEN Prerequisites
  - E.19.3 OPEN Syntax
  - E.19.4 OPEN Keywords and Parameters
  - E.19.5 OPEN Usage Notes
  - E.19.6 OPEN Example
  - E.19.7 OPEN Related Topics
- E.20 PREPARE (Executable Embedded SQL)
  - E.20.1 PREPARE Purpose
  - E.20.2 PREPARE Prerequisites
  - E.20.3 PREPARE Syntax
  - E.20.4 PREPARE Keywords and Parameters
  - E.20.5 PREPARE Usage Notes
  - E.20.6 PREPARE Example
  - E.20.7 PREPARE Related Topics
- E.21 ROLLBACK (Executable Embedded SQL)
  - E.21.1 ROLLBACK Purpose
  - E.21.2 ROLLBACK Prerequisites
  - E.21.3 ROLLBACK Syntax
  - E.21.4 ROLLBACK Keywords and Parameters
  - E.21.5 ROLLBACK Usage Notes
  - E.21.6 ROLLBACK Example I
  - E.21.7 ROLLBACK Example II
  - E.21.8 ROLLBACK Distributed Transactions
  - E.21.9 ROLLBACK Example III
  - E.21.10 ROLLBACK Related Topics
- E.22 SAVEPOINT (Executable Embedded SQL)
  - E.22.1 SAVEPOINT Purpose
  - E.22.2 SAVEPOINT Prerequisites
  - E.22.3 SAVEPOINT Syntax
  - E.22.4 SAVEPOINT Keywords and Parameters
  - E.22.5 SAVEPOINT Usage Notes
  - E.22.6 SAVEPOINT Related Topics
- E.23 SELECT (Executable Embedded SQL)
  - E.23.1 SELECT Purpose
  - E.23.2 SELECT Prerequisites
  - E.23.3 SELECT Syntax
  - E.23.4 SELECT Keywords and Parameters
  - E.23.5 SELECT Usage Notes
  - E.23.6 SELECT Example
  - E.23.7 SELECT Related Topics
- E.24 UPDATE (Executable Embedded SQL)
  - E.24.1 UPDATE Purpose
  - E.24.2 UPDATE Prerequisites
  - E.24.3 UPDATE Syntax
  - E.24.4 UPDATE Keywords and Parameters
  - E.24.5 UPDATE Usage Notes
  - E.24.6 UPDATE Examples
  - E.24.7 UPDATE Related Topics
- E.25 VAR (Oracle Embedded SQL Directive)
  - E.25.1 VAR Purpose
  - E.25.2 VAR Prerequisites
  - E.25.3 VAR Syntax
  - E.25.4 VAR Keywords and Parameters
  - E.25.5 VAR Usage Notes
  - E.25.6 VAR Example
  - E.25.7 VAR Related Topics
- E.26 WHENEVER (Embedded SQL Directive)
  - E.26.1 WHENEVER Purpose
  - E.26.2 WHENEVER Prerequisites
  - E.26.3 WHENEVER Syntax
  - E.26.4 WHENEVER Keywords and Parameters
  - E.26.5 WHENEVER Usage Notes
  - E.26.6 WHENEVER Example
  - E.26.7 WHENEVER Related Topics
Index