Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Structure
• 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 Program Example
• 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
  • Using the CHARF Datatype Specifier
  • Guidelines
• Globalization Support
• Mmultibyteultibyte 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
  • Setting Up the LDA
  • Remote and Multiple Connections
• Developing X/Open Applications
  • Oracle-Specific Issues
  • Connecting to Oracle
  • Transaction Control
  • OCI Calls
  • Linking

4  Using 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
  • 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
  • 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
• Embedding PL/SQL Blocks
• Using Host Variables
  • An Example
  • A More Complex Example
  • VARCHAR Pseudotype
• Using Indicator Variables
  • Handling Nulls
  • Handling Truncated Values
• Using Host Arrays
  • ARRAYLEN Statement
• Using Cursors
  • An Alternative
• Stored Subprograms
  • Creating Stored Subprograms
  • Calling a Stored Subprogram
  • Remote Access
  • Getting Information about Stored Subprograms
• 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
  • Using Configuration Files
  • Setting Option Values
• Scope of Options
• Quick Reference
• Using the Precompiler Options
  • ASACC
  • ASSUME_SQLCODE
  • AUTO_CONNECT
  • COMMON_NAME
  • CONFIG
  • DBMS
  • DEFINE
  • ERRORS
  • FIPS
  • FORMAT
  • HOLD_CURSOR
  • HOST
  • INAME
  • INCLUDE
  • IRECLEN
  • LITDELIM
  • LNAME
  • LRECLEN
  • LTYPE
  • MAXLITERAL
  • MAXOPENCURSORS
  • MODE
  • MULTISUBPROG
  • Globalization Support_LOCAL
  • ONAME
  • ORACA
  • ORECLEN
  • PAGELEN
  • RELEASE_CURSOR
  • SELECT_ERROR
  • SQLCHECK
  • UNSAFE_NULL
  • USERID
  • VARCHAR
  • 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
• Using the COMMIT Statement
• 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
  • Using the LOCK TABLE Statement
• Fetching Across Commits
• Handling Distributed Transactions
• Guidelines
  • Designing Applications
  • Obtaining Locks
  • Using PL/SQL

8 Error Handling and Diagnostics

• The Need for Error Handling
• Error Handling Alternatives
  • SQLCODE and SQLSTATE
  • SQLCA
  • ORACA
• Using Status Variables when MODE={ANSI|ANSI14}
  • Some Historical Information
  • Release 1.5
  • Release 1.6
  • Release 1.7
  • Declaring Status Variables
  • Declaring SQLCODE
  • Declaring SQLSTATE
  • Status Variable Combinations
  • Status Variable Values
  • SQLCODE Values
  • SQLSTATE Values
• Using the SQL Communications Area
  • Declaring the SQLCA
  • Declaring the SQLCA in Pro*COBOL
  • 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
• Using the Oracle Communications Area
  • Declaring the ORACA
  • Enabling the ORACA
  • What's in 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
  • An Example

9 Using Host Arrays

• What Is a Host Array?
• Why Use Arrays?
• Declaring Host Arrays
  • Dimensioning Arrays
  • Restrictions
• Using Arrays in SQL Statements
• Selecting into Arrays
  • Batch Fetches
  • Number of Rows Fetched
  • Restrictions
  • Fetching Nulls
  • Fetching Truncated Values
  • Inserting with Arrays
  • Updating with Arrays
  • Deleting with Arrays
  • Restrictions
• Using Indicator Arrays
• Using the FOR Clause
  • Restrictions
  • In a SELECT Statement
  • With the CURRENT OF Clause
• Using the WHERE Clause
• Mimicking the CURRENT OF Clause
• 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
• Using Method 1
  • The EXECUTE IMMEDIATE Statement
  • An Example
• Using Method 2
  • The USING Clause
  • An Example
• 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
• Using the DECLARE STATEMENT Statement
  • Usage of Host Arrays
• 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
• Precompiling and Compiling a User Exit
• Using the GENXTB Utility
• 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

• Fetching NULLs without Using Indicator Variables
  • Using DBMS=V6
  • Using DBMS=V7 and MODE=ORACLE
  • Related Error Messages

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
• 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
  • Parameter Interactions

D Syntactic and Semantic Checking

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

E Migrating to Oracle Database Version 7

• Fetching Nulls
  • Fetching Truncated Values
  • Handling Character Strings
  • Using VARCHAR2 versus CHAR
  • On Input
  • On Output

F 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)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Related Topics
• CLOSE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• COMMIT (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keyword and Parameters
  • Usage Notes
  • Related Topics
• CONNECT (Executable Embedded SQL Extension)
  • Purpose
  • Prerequisites
  • Syntax
  • Keyword and Parameters
  • Usage Notes
  • Related Topics
• DECLARE CURSOR (Embedded SQL Directive)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• DECLARE DATABASE (Oracle Embedded SQL Directive)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• DECLARE STATEMENT (Embedded SQL Directive)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example I
  • Example II
  • Related Topics
• DECLARE TABLE (Oracle Embedded SQL Directive)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• DELETE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• DESCRIBE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• EXECUTE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• EXECUTE IMMEDIATE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• FETCH (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• INSERT (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example I
  • Example II
  • Related Topics
• OPEN (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• PREPARE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• ROLLBACK (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example I
  • Example II
  • Distributed Transactions
  • Example III
  • Related Topics
• SAVEPOINT (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Related Topics
• SELECT (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• UPDATE (Executable Embedded SQL)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Examples
  • Related Topics
• VAR (Oracle Embedded SQL Directive)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics
• WHENEVER (Embedded SQL Directive)
  • Purpose
  • Prerequisites
  • Syntax
  • Keywords and Parameters
  • Usage Notes
  • Example
  • Related Topics

Index