1/21

Contents

List of Tables

Title and Copyright Information

Preface

• Intended Audience
• Structure
• Documentation Accessibility
• Related Documents
• Conventions

1 Getting Acquainted

• What Is an Oracle Precompiler?
  • Language Alternatives
• Why Use an Oracle Precompiler?
• Why Use SQL?
• Why Use PL/SQL?
• What Do the Oracle Precompilers Offer?
• Do the Oracle Precompilers Meet Industry Standards?
  • Requirements
  • Compliance
  • FIPS Flagger
  • FIPS Option
  • Certification

2 Learning the Basics

• Key Concepts of Embedded SQL Programming
  • Embedded SQL Statements
  • Executable versus Declarative Statements
  • Embedded SQL Syntax
  • Static versus Dynamic SQL Statements
  • Embedded PL/SQL Blocks
  • Host and Indicator Variables
  • Oracle Datatypes
  • Arrays
  • Datatype Equivalencing
  • Private SQL Areas, Cursors, and Active Sets
  • Transactions
  • Errors and Warnings
• Steps in Developing an Embedded SQL Application
• A Sample Program
• Sample Tables
  • Sample Data

3 Meeting Program Requirements

• The Declare Section
  • An Example
• INCLUDE Statements
• The SQLCA
• Oracle Datatypes
  • Internal Datatypes
  • CHAR
  • DATE
  • LONG
  • LONG RAW
  • MLSLABEL
  • NUMBER
  • RAW
  • ROWID
  • VARCHAR2
  • SQL Pseudocolumns and Functions
  • ROWLABEL Column
  • External Datatypes
  • CHAR
  • CHARF
  • CHARZ
  • DATE
  • DECIMAL
  • DISPLAY
  • FLOAT
  • INTEGER
  • LONG
  • LONG RAW
  • LONG VARCHAR
  • LONG VARRAW
  • MLSLABEL
  • NUMBER
  • RAW
  • ROWID
  • STRING
  • UNSIGNED
  • VARCHAR
  • VARCHAR2
  • VARNUM
  • VARRAW
• Datatype Conversion
  • DATE Values
  • RAW and LONG RAW Values
• Declaring and Referencing Host Variables
  • Some Examples
  • VARCHAR Variables
  • Host Variable Guidelines
• Declaring and Referencing Indicator Variables
  • INDICATOR Keyword
  • An Example
  • Indicator Variable Guidelines
• Datatype Equivalencing
  • Why Equivalence Datatypes?
  • Host Variable Equivalencing
  • An Example
  • About Using the CHARF Datatype Specifier
  • Guidelines
• Globalization Support
• Multibyte Globalization Support Character Sets
  • Character Strings in Embedded SQL
  • Dynamic SQL
  • Embedded DDL
  • Multibyteultibyte Globalization Support Host Variables
  • Restrictions
  • Blank Padding
  • Indicator Variables
• Concurrent Logons
  • Some Preliminaries
  • Default Databases and Connections
  • Explicit Logons
  • Single Explicit Logons
  • Multiple Explicit Logons
  • Implicit Logons
  • Single Implicit Logons
  • Multiple Implicit Logons
• Embedding OCI (Oracle Call Interface) Calls
  • About Setting Up the LDA
  • Remote and Multiple Connections
• About Developing X/Open Applications
  • Oracle-Specific Issues
  • About Connecting to Oracle
  • Transaction Control
  • OCI Calls
  • Linking

4  Using Embedded SQL

• About Using Host Variables
  • Output versus Input Host Variables
• About 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
  • 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
  • A Complete Example
• Cursor Variables
  • About Declaring a Cursor Variable
  • Allocating a Cursor Variable
  • Opening a Cursor Variable
  • Fetching from a Cursor Variable
  • Closing a Cursor Variable

5 Using Embedded PL/SQL

• Advantages of PL/SQL
  • Better Performance
  • Integration with Oracle
  • Cursor FOR Loops
  • Subprograms
  • Parameter Modes
  • Packages
  • PL/SQL Tables
  • User-defined Records
• About Embedding PL/SQL Blocks
• About Using Host Variables
  • An Example
  • A More Complex Example
  • VARCHAR Pseudotype
• About Using Indicator Variables
  • Handling Nulls
  • Handling Truncated Values
• About Using Host Arrays
  • ARRAYLEN Statement
• About Using Cursors
  • An Alternative
• Stored Subprograms
  • Creating Stored Subprograms
  • Calling a Stored Subprogram
  • Remote Access
  • Getting Information about Stored Subprograms
• About Using Dynamic PL/SQL
  • Restriction

6 Running the Oracle Precompilers

• The Precompiler Command
• What Occurs during Precompilation?
• Precompiler Options
  • Default Values
  • Determining Current Values
  • Case Sensitivity
  • Configuration Files
• Entering Options
  • On the Command Line
  • Inline
  • Advantages
  • Scope of EXEC ORACLE
  • From a Configuration File
  • Advantages
  • About Using Configuration Files
  • About Setting Option Values
• Scope of Options
• Quick Reference
• About Using the Precompiler Options
  • ASACC
  • ASSUME_SQLCODE
  • AUTO_CONNECT
  • CHAR_MAP
  • CINCR
  • CLOSE_ON_COMMIT
  • CMAX
  • CMIN
  • CNOWAIT
  • CODE
  • COMMON_NAME
  • COMMON_PARSER
  • COMP_CHARSET
  • COMP_CHARSET
  • CONFIG
  • CPOOL
  • CPP_SUFFIX
  • CTIMEOUT
  • DB2_ARRAY
  • DBMS
  • DEF_SQLCODE
  • DEFINE
  • DURATION
  • DYNAMIC
  • ERRORS
  • ERRTYPE
  • EVENTS
  • FIPS
  • FORMAT
  • Globalization Support_LOCAL
  • HEADER
  • HOLD_CURSOR
  • HOST
  • IMPLICIT_SVPT
  • INAME
  • INCLUDE
  • IRECLEN
  • INTYPE
  • LINES
  • LITDELIM
  • LNAME
  • LRECLEN
  • LTYPE
  • MAXLITERAL
  • MAXOPENCURSORS
  • MAX_ROW_INSERT
  • MODE
  • MULTISUBPROG
  • NATIVE_TYPES
  • NLS_CHAR
  • NLS_LOCAL
  • OBJECTS
  • ONAME
  • ORACA
  • ORECLEN
  • OUTLINE
  • OUTLNPREFIX
  • PAGELEN
  • PARSE
  • PREFETCH
  • RELEASE_CURSOR
  • RUNOUTLINE
  • SELECT_ERROR
  • SQLCHECK
  • STMT_CACHE
  • SQLCHECK
  • THREADS
  • TYPE_CODE
  • UNSAFE_NULL
  • USERID
  • UTF16_CHARSET
  • VARCHAR
  • VERSION
  • XREF
• Conditional Precompilations
  • An Example
  • Defining Symbols
• Separate Precompilations
  • Guidelines
  • Restrictions
• Compiling and Linking
  • System-Dependent
  • Multibyte Globalization Support Compatibility

7 Defining and Controlling Transactions

• Some Terms You Should Know
• How Transactions Guard Your Database
• How to Begin and End Transactions
• About Using the COMMIT Statement
• About Using the ROLLBACK Statement
  • Statement-Level Rollbacks
• About Using the SAVEPOINT Statement
• About Using the RELEASE Option
• About Using the SET TRANSACTION Statement
• About Overriding Default Locking
  • About Using the FOR UPDATE OF Clause
  • Restrictions
  • About Using the LOCK TABLE Statement
• About Fetching Across Commits
• About Handling Distributed Transactions
• Guidelines
  • About Designing Applications
  • About Obtaining Locks
  • About Using PL/SQL

8 Error Handling and Diagnostics

• The Need for Error Handling
• Error Handling Alternatives
  • SQLCODE and SQLSTATE
  • SQLCA
  • ORACA
• About Using Status Variables when MODE={ANSI|ANSI14}
  • Some Historical Information
  • Release 1.5
  • Release 1.6
  • Release 1.7
  • About Declaring Status Variables
  • Declaring SQLCODE
  • Declaring SQLSTATE
  • Status Variable Combinations
  • Status Variable Values
  • SQLCODE Values
  • SQLSTATE Values
• About Using the SQL Communications Area
  • Declaring the SQLCA
  • About Declaring the SQLCA in Pro*COBOL
  • About Declaring the SQLCA in Pro*FORTRAN
  • What's in 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
  • SQLERRP
  • SQLERRD
  • SQLWARN
  • SQLEXT
  • PL/SQL Considerations
  • Getting the Full Text of Error Messages
  • Using the WHENEVER Statement
  • SQLWARNING
  • SQLERROR
  • NOT FOUND
  • CONTINUE
  • DO
  • GOTO
  • STOP
  • Some Examples
  • Scope
  • Guidelines
  • Getting the Text of SQL Statements
• About Using the Oracle Communications Area
  • Declaring the ORACA
  • Enabling the ORACA
  • What's in the ORACA?
  • Choosing Run-time Options
  • ORACA Structure
  • ORACAID
  • ORACABC
  • ORACCHF
  • ORADBGF
  • ORAHCHF
  • ORASTXTF
  • Diagnostics
  • ORASTXT
  • ORASFNM
  • ORASLNR
  • Cursor Cache Statistics
  • ORAHOC
  • ORAMOC
  • ORACOC
  • ORANOR
  • ORANPR
  • ORANEX
  • An Example

9 Using Host Arrays

• What Is a Host Array?
• Why Use Arrays?
• Declaring Host Arrays
  • Dimensioning Arrays
  • Restrictions
• About Using Arrays in SQL Statements
• About Selecting into Arrays
  • Batch Fetches
  • Number of Rows Fetched
  • Restrictions
  • About Fetching Nulls
  • About Fetching Truncated Values
  • About Inserting with Arrays
  • About Updating with Arrays
  • About Deleting with Arrays
  • Restrictions
• About Using Indicator Arrays
• About Using the FOR Clause
  • Restrictions
  • In a SELECT Statement
  • With the CURRENT OF Clause
• About Using the WHERE Clause
• About Mimicking the CURRENT OF Clause
• About Using SQLERRD(3)

10 Using Dynamic SQL

• What Is 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
• About Using Method 1
  • The EXECUTE IMMEDIATE Statement
  • An Example
• About Using Method 2
  • The USING Clause
  • An Example
• About Using Method 3
  • PREPARE
  • DECLARE
  • OPEN
  • FETCH
  • CLOSE
  • An Example
• Using Method 4
  • Need for the SQLDA
  • The DESCRIBE Statement
  • What Is a SQLDA?
  • Implementing Method 4
• About Using the DECLARE STATEMENT Statement
  • Usage of Host Arrays
• About Using PL/SQL
  • With Method 1
  • With Method 2
  • With Method 3
  • With Method 4
  • Caution

11 Writing User Exits

• What Is a User Exit?
• Why Write a User Exit?
• Developing a User Exit
• Writing a User Exit
  • Requirements for Variables
  • The IAF GET Statement
  • The IAF PUT Statement
• Calling a User Exit
• Passing Parameters to a User Exit
• Returning Values to a Form
  • The IAP Constants
  • Using the SQLIEM Function
  • Using WHENEVER
• An Example
• About Precompiling and Compiling a User Exit
• About Using the GENXTB Utility
• About Linking a User Exit into SQL*Forms
• Guidelines for SQL*Forms User Exits
  • Naming the Exit
  • Connecting to Oracle
  • Issuing I/O Calls
  • Using Host Variables
  • Updating Tables
  • Issuing Commands
• EXEC TOOLS Statements
  • EXEC TOOLS SET
  • EXEC TOOLS GET
  • EXEC TOOLS SET CONTEXT
  • EXEC TOOLS GET CONTEXT
  • EXEC TOOLS MESSAGE

A New Features

• About Fetching NULLs without Using Indicator Variables
  • About Using DBMS=V7 and MODE=ORACLE
  • Related Error Messages
• Additional Array Insert/Select Syntax
• SQL99 Syntax Support
• About Fixing Execution Plans
• About Using Implicit Buffered Insert
• Dynamic SQL Statement Caching
• Scrollable Cursors
• Platform Endianness Support
• Flexible B Area Length

B Oracle Reserved Words, Keywords, and Namespaces

• Oracle Reserved Words
• Oracle Keywords
• PL/SQL Reserved Words
• Oracle Reserved Namespaces

C Performance Tuning

• What Causes Poor Performance?
• How Can Performance be Improved?
• Using Host Arrays
• Using Embedded PL/SQL
• Optimizing SQL Statements
  • Optimizer Hints
  • Giving Hints
  • Trace Facility
• About Using Indexes
• Taking Advantage of Row-Level Locking
• About Eliminating Unnecessary Parsing
  • About Handling Explicit Cursors
  • Cursor Control
  • About Using the Cursor Management Options
  • Private SQL Areas and Cursor Cache
  • Resource Use
  • Infrequent Execution
  • Frequent Execution
  • Parameter Interactions

D Syntactic and Semantic Checking

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

E Embedded SQL Commands and Directives

• Summary of Precompiler Directives and Embedded SQL Commands
• About The Command Descriptions
• How to Read Syntax Diagrams
  • Required Keywords and Parameters
  • Optional Keywords and Parameters
  • Syntax Loops
  • Multi-part Diagrams
  • Database Objects
• ALLOCATE (Executable Embedded SQL Extension)
  • Allocate Purpose
  • Allocate Prerequisites
  • Allocate Syntax
  • Allocate Keywords and Parameters
  • Allocate Usage Notes
  • Allocate Related Topics
• CLOSE (Executable Embedded SQL)
  • CLOSE Purpose
  • CLOSE Prerequisites
  • CLOSE Syntax
  • CLOSE Keywords and Parameters
  • CLOSE Usage Notes
  • CLOSE Example
  • CLOSE Related Topics
• COMMIT (Executable Embedded SQL)
  • COMMIT Purpose
  • COMMIT Prerequisites
  • COMMIT Syntax
  • COMMIT Keyword and Parameters
  • COMMIT Usage Notes
  • COMMIT Related Topics
• CONNECT (Executable Embedded SQL Extension)
  • CONNECT Purpose
  • CONNECT Prerequisites
  • CONNECT Syntax
  • CONNECT Keyword and Parameters
  • CONNECT Usage Notes
  • CONNECT Related Topics
• DECLARE CURSOR (Embedded SQL Directive)
  • DECLARE CURSOR Purpose
  • DECLARE CURSOR Prerequisites
  • DECLARE CURSOR Syntax
  • DECLARE CURSOR Keywords and Parameters
  • DECLARE CURSOR Usage Notes
  • DECLARE CURSOR Example
  • DECLARE CURSOR Related Topics
• DECLARE DATABASE (Oracle Embedded SQL Directive)
  • DECLARE DATABASE Purpose
  • DECLARE DATABASE Prerequisites
  • DECLARE DATABASE Syntax
  • DECLARE DATABASE Keywords and Parameters
  • DECLARE DATABASE Usage Notes
  • DECLARE DATABASE Example
  • DECLARE DATABASE Related Topics
• DECLARE STATEMENT (Embedded SQL Directive)
  • DECLARE STATEMENT Purpose
  • DECLARE STATEMENT Prerequisites
  • DECLARE STATEMENT Syntax
  • DECLARE STATEMENT Keywords and Parameters
  • DECLARE STATEMENT Usage Notes
  • DECLARE STATEMENT Example I
  • DECLARE STATEMENT Example II
  • DECLARE STATEMENT Related Topics
• DECLARE TABLE (Oracle Embedded SQL Directive)
  • DECLARE TABLE Purpose
  • DECLARE TABLE Prerequisites
  • DECLARE TABLE Syntax
  • DECLARE TABLE Keywords and Parameters
  • DECLARE TABLE Usage Notes
  • DECLARE TABLE Example
  • DECLARE TABLE Related Topics
• DELETE (Executable Embedded SQL)
  • DELETE Purpose
  • DELETE Prerequisites
  • DELETE Syntax
  • DELETE Keywords and Parameters
  • DELETE Usage Notes
  • DELETE Example
  • DELETE Related Topics
• DESCRIBE (Executable Embedded SQL)
  • DESCRIBE Purpose
  • DESCRIBE Prerequisites
  • DESCRIBE Syntax
  • DESCRIBE Keywords and Parameters
  • DESCRIBE Usage Notes
  • DESCRIBE Example
  • DESCRIBE Related Topics
• EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
  • EXECUTE ... END-EXEC Purpose
  • EXECUTE ... END-EXEC Prerequisites
  • EXECUTE ... END-EXEC Syntax
  • EXECUTE ... END-EXEC Keywords and Parameters
  • EXECUTE ... END-EXEC Usage Notes
  • EXECUTE ... END-EXEC Example
  • EXECUTE ... END-EXEC Related Topics
• EXECUTE (Executable Embedded SQL)
  • EXECUTE Purpose
  • EXECUTE Prerequisites
  • EXECUTE Syntax
  • EXECUTE Keywords and Parameters
  • EXECUTE Usage Notes
  • EXECUTE Example
  • EXECUTE Related Topics
• EXECUTE IMMEDIATE (Executable Embedded SQL)
  • EXECUTE IMMEDIATE Purpose
  • EXECUTE IMMEDIATE Prerequisites
  • EXECUTE IMMEDIATE Syntax
  • EXECUTE IMMEDIATE Keywords and Parameters
  • EXECUTE IMMEDIATE Usage Notes
  • EXECUTE IMMEDIATE Example
  • EXECUTE IMMEDIATE Related Topics
• FETCH (Executable Embedded SQL)
  • FETCH Purpose
  • FETCH Prerequisites
  • FETCH Syntax
  • FETCH Keywords and Parameters
  • FETCH Usage Notes
  • FETCH Example
  • FETCH Related Topics
• INSERT (Executable Embedded SQL)
  • INSERT Purpose
  • INSERT Prerequisites
  • INSERT Syntax
  • INSERT Keywords and Parameters
  • INSERT Usage Notes
  • INSERT Example I
  • INSERT Example II
  • INSERT Related Topics
• OPEN (Executable Embedded SQL)
  • OPEN Purpose
  • OPEN Prerequisites
  • OPEN Syntax
  • OPEN Keywords and Parameters
  • OPEN Usage Notes
  • OPEN Example
  • OPEN Related Topics
• PREPARE (Executable Embedded SQL)
  • PREPARE Purpose
  • PREPARE Prerequisites
  • PREPARE Syntax
  • PREPARE Keywords and Parameters
  • PREPARE Usage Notes
  • PREPARE Example
  • PREPARE Related Topics
• ROLLBACK (Executable Embedded SQL)
  • ROLLBACK Purpose
  • ROLLBACK Prerequisites
  • ROLLBACK Syntax
  • ROLLBACK Keywords and Parameters
  • ROLLBACK Usage Notes
  • ROLLBACK Example I
  • ROLLBACK Example II
  • ROLLBACK Distributed Transactions
  • ROLLBACK Example III
  • ROLLBACK Related Topics
• SAVEPOINT (Executable Embedded SQL)
  • SAVEPOINT Purpose
  • SAVEPOINT Prerequisites
  • SAVEPOINT Syntax
  • SAVEPOINT Keywords and Parameters
  • SAVEPOINT Usage Notes
  • SAVEPOINT Related Topics
• SELECT (Executable Embedded SQL)
  • SELECT Purpose
  • SELECT Prerequisites
  • SELECT Syntax
  • SELECT Keywords and Parameters
  • SELECT Usage Notes
  • SELECT Example
  • SELECT Related Topics
• UPDATE (Executable Embedded SQL)
  • UPDATE Purpose
  • UPDATE Prerequisites
  • UPDATE Syntax
  • UPDATE Keywords and Parameters
  • UPDATE Usage Notes
  • UPDATE Examples
  • UPDATE Related Topics
• VAR (Oracle Embedded SQL Directive)
  • VAR Purpose
  • VAR Prerequisites
  • VAR Syntax
  • VAR Keywords and Parameters
  • VAR Usage Notes
  • VAR Example
  • VAR Related Topics
• WHENEVER (Embedded SQL Directive)
  • WHENEVER Purpose
  • WHENEVER Prerequisites
  • WHENEVER Syntax
  • WHENEVER Keywords and Parameters
  • WHENEVER Usage Notes
  • WHENEVER Example
  • WHENEVER Related Topics

Index