Skip Headers
Oracle® Database Programmer's Guide to the Oracle Precompilers
11g Release 1 (11.1)
Part Number B31231-01
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
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 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
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
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
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
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
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
Additional Array Insert/Select Syntax
SQL99 Syntax Support
Fixing Execution Plans
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
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
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
