Skip Headers
Oracle® Database Programmer's Guide to the Oracle Precompilers
10
g
Release 2 (10.2)
Part Number B14354-01
Home
Book List
Index
Master Index
Feedback
Next
View PDF
Contents
List of Figures
List of Tables
Title and Copyright Information
Preface
Intended Audience
Documentation Accessibility
Structure
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?
1.6.1
Requirements
1.6.2
Compliance
1.6.3
FIPS Flagger
1.6.4
FIPS Option
1.6.5
Certification
2
Learning the Basics
2.1
Key Concepts of Embedded SQL Programming
2.1.1
Embedded SQL Statements
2.1.2
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
Host and Indicator Variables
2.1.7
Oracle Datatypes
2.1.8
Arrays
2.1.9
Datatype Equivalencing
2.1.10
Private SQL Areas, Cursors, and Active Sets
2.1.11
Transactions
2.1.12
Errors and Warnings
2.2
Steps in Developing an Embedded SQL Application
2.3
A Program Example
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.4.1
Internal Datatypes
3.4.2
CHAR
3.4.3
DATE
3.4.4
LONG
3.4.5
LONG RAW
3.4.6
MLSLABEL
3.4.7
NUMBER
3.4.8
RAW
3.4.9
ROWID
3.4.10
VARCHAR2
3.4.11
SQL Pseudocolumns and Functions
3.4.12
ROWLABEL Column
3.4.13
External Datatypes
3.4.14
CHAR
3.4.15
CHARF
3.4.16
CHARZ
3.4.17
DATE
3.4.18
DECIMAL
3.4.19
DISPLAY
3.4.20
FLOAT
3.4.21
INTEGER
3.4.22
LONG
3.4.23
LONG RAW
3.4.24
LONG VARCHAR
3.4.25
LONG VARRAW
3.4.26
MLSLABEL
3.4.27
NUMBER
3.4.28
RAW
3.4.29
ROWID
3.4.30
STRING
3.4.31
UNSIGNED
3.4.32
VARCHAR
3.4.33
VARCHAR2
3.4.34
VARNUM
3.4.35
VARRAW
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.6.1
Some Examples
3.6.2
VARCHAR Variables
3.6.3
Host Variable Guidelines
3.7
Declaring and Referencing Indicator Variables
3.7.1
INDICATOR Keyword
3.7.2
An Example
3.7.3
Indicator Variable Guidelines
3.8
Datatype Equivalencing
3.8.1
Why Equivalence Datatypes?
3.8.2
Host Variable Equivalencing
3.8.3
An Example
3.8.4
Using the CHARF Datatype Specifier
3.8.5
Guidelines
3.9
Globalization Support
3.10
Mmultibyteultibyte Globalization Support Character Sets
3.10.1
Character Strings in Embedded SQL
3.10.2
Dynamic SQL
3.10.3
Embedded DDL
3.10.4
Multibyteultibyte Globalization Support Host Variables
3.10.5
Restrictions
3.10.6
Blank Padding
3.10.7
Indicator Variables
3.11
Concurrent Logons
3.11.1
Some Preliminaries
3.11.2
Default Databases and Connections
3.11.3
Explicit Logons
3.11.4
Single Explicit Logons
3.11.5
Multiple Explicit Logons
3.11.6
Implicit Logons
3.11.7
Single Implicit Logons
3.11.8
Multiple Implicit Logons
3.12
Embedding OCI (Oracle Call Interface) Calls
3.12.1
Setting Up the LDA
3.12.2
Remote and Multiple Connections
3.13
Developing X/Open Applications
3.13.1
Oracle-Specific Issues
3.13.2
Connecting to Oracle
3.13.3
Transaction Control
3.13.4
OCI Calls
3.13.5
Linking
4
Using Embedded SQL
4.1
Using Host Variables
4.1.1
Output versus Input Host Variables
4.2
Using Indicator Variables
4.2.1
Input Variables
4.2.2
Output Variables
4.2.3
Inserting Nulls
4.2.4
Handling Returned Nulls
4.2.5
Fetching Nulls
4.2.6
Testing for Nulls
4.2.7
Fetching Truncated Values
4.3
The Basic SQL Statements
4.3.1
Selecting Rows
4.3.2
Available Clauses
4.3.3
Inserting Rows
4.3.4
Using Subqueries
4.3.5
Updating Rows
4.3.6
Deleting Rows
4.3.7
Using the WHERE Clause
4.4
Cursors
4.4.1
Declaring a Cursor
4.4.2
Opening a Cursor
4.4.3
Fetching from a Cursor
4.4.4
Closing a Cursor
4.4.5
Using the CURRENT OF Clause
4.4.6
Restrictions
4.4.7
A Typical Sequence of Statements
4.4.8
A Complete Example
4.5
Cursor Variables
4.5.1
Declaring a Cursor Variable
4.5.2
Allocating a Cursor Variable
4.5.3
Opening a Cursor Variable
4.5.4
Fetching from a Cursor Variable
4.5.5
Closing a Cursor Variable
5
Using Embedded PL/SQL
5.1
Advantages of PL/SQL
5.1.1
Better Performance
5.1.2
Integration with Oracle
5.1.3
Cursor FOR Loops
5.1.4
Subprograms
5.1.5
Parameter Modes
5.1.6
Packages
5.1.7
PL/SQL Tables
5.1.8
User-defined Records
5.2
Embedding PL/SQL Blocks
5.3
Using Host Variables
5.3.1
An Example
5.3.2
A More Complex Example
5.3.3
VARCHAR Pseudotype
5.4
Using Indicator Variables
5.4.1
Handling Nulls
5.4.2
Handling Truncated Values
5.5
Using Host Arrays
5.5.1
ARRAYLEN Statement
5.6
Using Cursors
5.6.1
An Alternative
5.7
Stored Subprograms
5.7.1
Creating Stored Subprograms
5.7.2
Calling a Stored Subprogram
5.7.3
Remote Access
5.7.4
Getting Information about Stored Subprograms
5.8
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.3.1
Default Values
6.3.2
Determining Current Values
6.3.3
Case Sensitivity
6.3.4
Configuration Files
6.4
Entering Options
6.4.1
On the Command Line
6.4.2
Inline
6.4.3
Advantages
6.4.4
Scope of EXEC ORACLE
6.4.5
From a Configuration File
6.4.6
Advantages
6.4.7
Using Configuration Files
6.4.8
Setting Option Values
6.5
Scope of Options
6.6
Quick Reference
6.7
Using the Precompiler Options
6.7.1
ASACC
6.7.2
ASSUME_SQLCODE
6.7.3
AUTO_CONNECT
6.7.4
COMMON_NAME
6.7.5
CONFIG
6.7.6
DBMS
6.7.7
DEFINE
6.7.8
ERRORS
6.7.9
FIPS
6.7.10
FORMAT
6.7.11
HOLD_CURSOR
6.7.12
HOST
6.7.13
INAME
6.7.14
INCLUDE
6.7.15
IRECLEN
6.7.16
LITDELIM
6.7.17
LNAME
6.7.18
LRECLEN
6.7.19
LTYPE
6.7.20
MAXLITERAL
6.7.21
MAXOPENCURSORS
6.7.22
MODE
6.7.23
MULTISUBPROG
6.7.24
Globalization Support_LOCAL
6.7.25
ONAME
6.7.26
ORACA
6.7.27
ORECLEN
6.7.28
PAGELEN
6.7.29
RELEASE_CURSOR
6.7.30
SELECT_ERROR
6.7.31
SQLCHECK
6.7.32
UNSAFE_NULL
6.7.33
USERID
6.7.34
VARCHAR
6.7.35
XREF
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
Using the COMMIT Statement
7.5
Using the ROLLBACK Statement
7.5.1
Statement-Level Rollbacks
7.6
Using the SAVEPOINT Statement
7.7
Using the RELEASE Option
7.8
Using the SET TRANSACTION Statement
7.9
Overriding Default Locking
7.9.1
Using the FOR UPDATE OF Clause
7.9.2
Restrictions
7.9.3
Using the LOCK TABLE Statement
7.10
Fetching Across Commits
7.11
Handling Distributed Transactions
7.12
Guidelines
7.12.1
Designing Applications
7.12.2
Obtaining Locks
7.12.3
Using PL/SQL
8
Error Handling and Diagnostics
8.1
The Need for Error Handling
8.2
Error Handling Alternatives
8.2.1
SQLCODE and SQLSTATE
8.2.2
SQLCA
8.2.3
ORACA
8.3
Using Status Variables when MODE={ANSI|ANSI14}
8.3.1
Some Historical Information
8.3.2
Release 1.5
8.3.3
Release 1.6
8.3.4
Release 1.7
8.3.5
Declaring Status Variables
8.3.6
Declaring SQLCODE
8.3.7
Declaring SQLSTATE
8.3.8
Status Variable Combinations
8.3.9
Status Variable Values
8.3.10
SQLCODE Values
8.3.11
SQLSTATE Values
8.4
Using the SQL Communications Area
8.4.1
Declaring the SQLCA
8.4.2
Declaring the SQLCA in Pro*COBOL
8.4.3
Declaring the SQLCA in Pro*FORTRAN
8.4.4
What's in the SQLCA?
8.4.5
Key Components of Error Reporting
8.4.6
Status Codes
8.4.7
Warning Flags
8.4.8
Rows-Processed Count
8.4.9
Parse Error Offset
8.4.10
Error Message Text
8.4.11
SQLCA Structure
8.4.12
SQLCAID
8.4.13
SQLCABC
8.4.14
SQLCODE
8.4.15
SQLERRM
8.4.16
SQLERRP
8.4.17
SQLERRD
8.4.18
SQLWARN
8.4.19
SQLEXT
8.4.20
PL/SQL Considerations
8.4.21
Getting the Full Text of Error Messages
8.4.22
Using the WHENEVER Statement
8.4.23
SQLWARNING
8.4.24
SQLERROR
8.4.25
NOT FOUND
8.4.26
CONTINUE
8.4.27
DO
8.4.28
GOTO
8.4.29
STOP
8.4.30
Some Examples
8.4.31
Scope
8.4.32
Guidelines
8.4.33
Getting the Text of SQL Statements
8.5
Using the Oracle Communications Area
8.5.1
Declaring the ORACA
8.5.2
Enabling the ORACA
8.5.3
What's in the ORACA?
8.5.4
Choosing Runtime Options
8.5.5
ORACA Structure
8.5.6
ORACAID
8.5.7
ORACABC
8.5.8
ORACCHF
8.5.9
ORADBGF
8.5.10
ORAHCHF
8.5.11
ORASTXTF
8.5.12
Diagnostics
8.5.13
ORASTXT
8.5.14
ORASFNM
8.5.15
ORASLNR
8.5.16
Cursor Cache Statistics
8.5.17
ORAHOC
8.5.18
ORAMOC
8.5.19
ORACOC
8.5.20
ORANOR
8.5.21
ORANPR
8.5.22
ORANEX
8.5.23
An Example
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
Using Arrays in SQL Statements
9.5
Selecting into Arrays
9.5.1
Batch Fetches
9.5.2
Number of Rows Fetched
9.5.3
Restrictions
9.5.4
Fetching Nulls
9.5.5
Fetching Truncated Values
9.5.6
Inserting with Arrays
9.5.7
Updating with Arrays
9.5.8
Deleting with Arrays
9.5.9
Restrictions
9.6
Using Indicator Arrays
9.7
Using the FOR Clause
9.7.1
Restrictions
9.7.2
In a SELECT Statement
9.7.3
With the CURRENT OF Clause
9.8
Using the WHERE Clause
9.9
Mimicking the CURRENT OF Clause
9.10
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.6.1
Method 1
10.6.2
Method 2
10.6.3
Method 3
10.6.4
Method 4
10.6.5
Guidelines
10.6.6
Avoiding Common Errors
10.7
Using Method 1
10.7.1
The EXECUTE IMMEDIATE Statement
10.7.2
An Example
10.8
Using Method 2
10.8.1
The USING Clause
10.8.2
An Example
10.9
Using Method 3
10.9.1
PREPARE
10.9.2
DECLARE
10.9.3
OPEN
10.9.4
FETCH
10.9.5
CLOSE
10.9.6
An Example
10.10
Using Method 4
10.10.1
Need for the SQLDA
10.10.2
The DESCRIBE Statement
10.10.3
What Is a SQLDA?
10.10.4
Implementing Method 4
10.11
Using the DECLARE STATEMENT Statement
10.11.1
Usage of Host Arrays
10.12
Using PL/SQL
10.12.1
With Method 1
10.12.2
With Method 2
10.12.3
With Method 3
10.12.4
With Method 4
10.12.5
Caution
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.4.1
Requirements for Variables
11.4.2
The IAF GET Statement
11.4.3
The IAF PUT Statement
11.5
Calling a User Exit
11.6
Passing Parameters to a User Exit
11.7
Returning Values to a Form
11.7.1
The IAP Constants
11.7.2
Using the SQLIEM Function
11.7.3
Using WHENEVER
11.8
An Example
11.9
Precompiling and Compiling a User Exit
11.10
Using the GENXTB Utility
11.11
Linking a User Exit into SQL*Forms
11.12
Guidelines for SQL*Forms User Exits
11.12.1
Naming the Exit
11.12.2
Connecting to Oracle
11.12.3
Issuing I/O Calls
11.12.4
Using Host Variables
11.12.5
Updating Tables
11.12.6
Issuing Commands
11.13
EXEC TOOLS Statements
11.13.1
EXEC TOOLS SET
11.13.2
EXEC TOOLS GET
11.13.3
EXEC TOOLS SET CONTEXT
11.13.4
EXEC TOOLS GET CONTEXT
11.13.5
EXEC TOOLS MESSAGE
A
New Features
A.1
Fetching NULLs without Using Indicator Variables
A.1.1
Using DBMS=V6
A.1.2
Using DBMS=V7 and MODE=ORACLE
A.1.3
Related Error Messages
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.5.1
Optimizer Hints
C.5.2
Giving Hints
C.5.3
Trace Facility
C.6
Using Indexes
C.7
Taking Advantage of Row-Level Locking
C.8
Eliminating Unnecessary Parsing
C.8.1
Handling Explicit Cursors
C.8.2
Cursor Control
C.8.3
Using the Cursor Management Options
C.8.4
Private SQL Areas and Cursor Cache
C.8.5
Resource Use
C.8.6
Infrequent Execution
C.8.7
Frequent Execution
C.8.8
Parameter Interactions
D
Syntactic and Semantic Checking
D.1
What Is Syntactic and Semantic Checking?
D.2
Controlling the Type and Extent of Checking
D.3
Specifying SQLCHECK=SEMANTICS
D.3.1
Enabling a Semantic Check
D.3.2
Connecting to Oracle
D.3.3
Using DECLARE TABLE
E
Migrating to Oracle Database Version 7
E.1
Fetching Nulls
E.1.1
Fetching Truncated Values
E.1.2
Handling Character Strings
E.1.3
Using VARCHAR2 versus CHAR
E.1.4
On Input
E.1.5
On Output
F
Embedded SQL Commands and Directives
F.1
Summary of Precompiler Directives and Embedded SQL Commands
F.2
About The Command Descriptions
F.3
How to Read Syntax Diagrams
F.3.1
Required Keywords and Parameters
F.3.2
Optional Keywords and Parameters
F.3.3
Syntax Loops
F.3.4
Multi-part Diagrams
F.3.5
Database Objects
F.4
ALLOCATE (Executable Embedded SQL Extension)
F.4.1
Purpose
F.4.2
Prerequisites
F.4.3
Syntax
F.4.4
Keywords and Parameters
F.4.5
Usage Notes
F.4.6
Related Topics
F.5
CLOSE (Executable Embedded SQL)
F.5.1
Purpose
F.5.2
Prerequisites
F.5.3
Syntax
F.5.4
Keywords and Parameters
F.5.5
Usage Notes
F.5.6
Example
F.5.7
Related Topics
F.6
COMMIT (Executable Embedded SQL)
F.6.1
Purpose
F.6.2
Prerequisites
F.6.3
Syntax
F.6.4
Keyword and Parameters
F.6.5
Usage Notes
F.6.6
Related Topics
F.7
CONNECT (Executable Embedded SQL Extension)
F.7.1
Purpose
F.7.2
Prerequisites
F.7.3
Syntax
F.7.4
Keyword and Parameters
F.7.5
Usage Notes
F.7.6
Related Topics
F.8
DECLARE CURSOR (Embedded SQL Directive)
F.8.1
Purpose
F.8.2
Prerequisites
F.8.3
Syntax
F.8.4
Keywords and Parameters
F.8.5
Usage Notes
F.8.6
Example
F.8.7
Related Topics
F.9
DECLARE DATABASE (Oracle Embedded SQL Directive)
F.9.1
Purpose
F.9.2
Prerequisites
F.9.3
Syntax
F.9.4
Keywords and Parameters
F.9.5
Usage Notes
F.9.6
Example
F.9.7
Related Topics
F.10
DECLARE STATEMENT (Embedded SQL Directive)
F.10.1
Purpose
F.10.2
Prerequisites
F.10.3
Syntax
F.10.4
Keywords and Parameters
F.10.5
Usage Notes
F.10.6
Example I
F.10.7
Example II
F.10.8
Related Topics
F.11
DECLARE TABLE (Oracle Embedded SQL Directive)
F.11.1
Purpose
F.11.2
Prerequisites
F.11.3
Syntax
F.11.4
Keywords and Parameters
F.11.5
Usage Notes
F.11.6
Example
F.11.7
Related Topics
F.12
DELETE (Executable Embedded SQL)
F.12.1
Purpose
F.12.2
Prerequisites
F.12.3
Syntax
F.12.4
Keywords and Parameters
F.12.5
Usage Notes
F.12.6
Example
F.12.7
Related Topics
F.13
DESCRIBE (Executable Embedded SQL)
F.13.1
Purpose
F.13.2
Prerequisites
F.13.3
Syntax
F.13.4
Keywords and Parameters
F.13.5
Usage Notes
F.13.6
Example
F.13.7
Related Topics
F.14
EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
F.14.1
Purpose
F.14.2
Prerequisites
F.14.3
Syntax
F.14.4
Keywords and Parameters
F.14.5
Usage Notes
F.14.6
Example
F.14.7
Related Topics
F.15
EXECUTE (Executable Embedded SQL)
F.15.1
Purpose
F.15.2
Prerequisites
F.15.3
Syntax
F.15.4
Keywords and Parameters
F.15.5
Usage Notes
F.15.6
Example
F.15.7
Related Topics
F.16
EXECUTE IMMEDIATE (Executable Embedded SQL)
F.16.1
Purpose
F.16.2
Prerequisites
F.16.3
Syntax
F.16.4
Keywords and Parameters
F.16.5
Usage Notes
F.16.6
Example
F.16.7
Related Topics
F.17
FETCH (Executable Embedded SQL)
F.17.1
Purpose
F.17.2
Prerequisites
F.17.3
Syntax
F.17.4
Keywords and Parameters
F.17.5
Usage Notes
F.17.6
Example
F.17.7
Related Topics
F.18
INSERT (Executable Embedded SQL)
F.18.1
Purpose
F.18.2
Prerequisites
F.18.3
Syntax
F.18.4
Keywords and Parameters
F.18.5
Usage Notes
F.18.6
Example I
F.18.7
Example II
F.18.8
Related Topics
F.19
OPEN (Executable Embedded SQL)
F.19.1
Purpose
F.19.2
Prerequisites
F.19.3
Syntax
F.19.4
Keywords and Parameters
F.19.5
Usage Notes
F.19.6
Example
F.19.7
Related Topics
F.20
PREPARE (Executable Embedded SQL)
F.20.1
Purpose
F.20.2
Prerequisites
F.20.3
Syntax
F.20.4
Keywords and Parameters
F.20.5
Usage Notes
F.20.6
Example
F.20.7
Related Topics
F.21
ROLLBACK (Executable Embedded SQL)
F.21.1
Purpose
F.21.2
Prerequisites
F.21.3
Syntax
F.21.4
Keywords and Parameters
F.21.5
Usage Notes
F.21.6
Example I
F.21.7
Example II
F.21.8
Distributed Transactions
F.21.9
Example III
F.21.10
Related Topics
F.22
SAVEPOINT (Executable Embedded SQL)
F.22.1
Purpose
F.22.2
Prerequisites
F.22.3
Syntax
F.22.4
Keywords and Parameters
F.22.5
Usage Notes
F.22.6
Related Topics
F.23
SELECT (Executable Embedded SQL)
F.23.1
Purpose
F.23.2
Prerequisites
F.23.3
Syntax
F.23.4
Keywords and Parameters
F.23.5
Usage Notes
F.23.6
Example
F.23.7
Related Topics
F.24
UPDATE (Executable Embedded SQL)
F.24.1
Purpose
F.24.2
Prerequisites
F.24.3
Syntax
F.24.4
Keywords and Parameters
F.24.5
Usage Notes
F.24.6
Examples
F.24.7
Related Topics
F.25
VAR (Oracle Embedded SQL Directive)
F.25.1
Purpose
F.25.2
Prerequisites
F.25.3
Syntax
F.25.4
Keywords and Parameters
F.25.5
Usage Notes
F.25.6
Example
F.25.7
Related Topics
F.26
WHENEVER (Embedded SQL Directive)
F.26.1
Purpose
F.26.2
Prerequisites
F.26.3
Syntax
F.26.4
Keywords and Parameters
F.26.5
Usage Notes
F.26.6
Example
F.26.7
Related Topics
Index
