Table of Contents
- List of Tables
- Title and Copyright Information
- Preface
- 1 Getting Acquainted
-
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 Sample Program
- 2.4 Sample Tables
-
2.1
Key Concepts of Embedded SQL Programming
-
3
Meeting Program Requirements
- 3.1 The Declare Section
- 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.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.13 About Developing X/Open Applications
- 4 Using Embedded SQL
- 5 Using Embedded PL/SQL
-
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.7.1 ASACC
- 6.7.2 ASSUME_SQLCODE
- 6.7.3 AUTO_CONNECT
- 6.7.4 CHAR_MAP
- 6.7.5 CINCR
- 6.7.6 CLOSE_ON_COMMIT
- 6.7.7 CMAX
- 6.7.8 CMIN
- 6.7.9 CNOWAIT
- 6.7.10 CODE
- 6.7.11 COMMON_NAME
- 6.7.12 COMMON_PARSER
- 6.7.13 COMP_CHARSET
- 6.7.14 COMP_CHARSET
- 6.7.15 CONFIG
- 6.7.16 CPOOL
- 6.7.17 CPP_SUFFIX
- 6.7.18 CTIMEOUT
- 6.7.19 DB2_ARRAY
- 6.7.20 DBMS
- 6.7.21 DEF_SQLCODE
- 6.7.22 DEFINE
- 6.7.23 DURATION
- 6.7.24 DYNAMIC
- 6.7.25 ERRORS
- 6.7.26 ERRTYPE
- 6.7.27 EVENTS
- 6.7.28 FIPS
- 6.7.29 FORMAT
- 6.7.30 Globalization Support_LOCAL
- 6.7.31 HEADER
- 6.7.32 HOLD_CURSOR
- 6.7.33 HOST
- 6.7.34 IMPLICIT_SVPT
- 6.7.35 INAME
- 6.7.36 INCLUDE
- 6.7.37 IRECLEN
- 6.7.38 INTYPE
- 6.7.39 LINES
- 6.7.40 LITDELIM
- 6.7.41 LNAME
- 6.7.42 LRECLEN
- 6.7.43 LTYPE
- 6.7.44 MAXLITERAL
- 6.7.45 MAXOPENCURSORS
- 6.7.46 MAX_ROW_INSERT
- 6.7.47 MODE
- 6.7.48 MULTISUBPROG
- 6.7.49 NATIVE_TYPES
- 6.7.50 NLS_CHAR
- 6.7.51 NLS_LOCAL
- 6.7.52 OBJECTS
- 6.7.53 ONAME
- 6.7.54 ORACA
- 6.7.55 ORECLEN
- 6.7.56 OUTLINE
- 6.7.57 OUTLNPREFIX
- 6.7.58 PAGELEN
- 6.7.59 PARSE
- 6.7.60 PREFETCH
- 6.7.61 RELEASE_CURSOR
- 6.7.62 RUNOUTLINE
- 6.7.63 SELECT_ERROR
- 6.7.64 SQLCHECK
- 6.7.65 STMT_CACHE
- 6.7.66 SQLCHECK
- 6.7.67 THREADS
- 6.7.68 TYPE_CODE
- 6.7.69 UNSAFE_NULL
- 6.7.70 USERID
- 6.7.71 UTF16_CHARSET
- 6.7.72 VARCHAR
- 6.7.73 VERSION
- 6.7.74 XREF
- 6.8 Conditional Precompilations
- 6.9 Separate Precompilations
- 6.10 Compiling and Linking
-
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.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.4.1 Declaring the SQLCA
- 8.4.2 About Declaring the SQLCA in Pro*COBOL
- 8.4.3 About 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
About 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 Run-time 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.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.8 About Using Method 2
- 10.9 About Using Method 3
- 10.10 Using Method 4
- 10.11 About Using the DECLARE STATEMENT Statement
- 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.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
- C Performance Tuning
- D Syntactic and Semantic Checking
-
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.4 ALLOCATE (Executable Embedded SQL Extension)
- E.5 CLOSE (Executable Embedded SQL)
- E.6 COMMIT (Executable Embedded SQL)
- E.7 CONNECT (Executable Embedded SQL Extension)
- E.8 DECLARE CURSOR (Embedded SQL Directive)
- E.9 DECLARE DATABASE (Oracle Embedded SQL Directive)
-
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.12 DELETE (Executable Embedded SQL)
- E.13 DESCRIBE (Executable Embedded SQL)
- E.14 EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
- E.15 EXECUTE (Executable Embedded SQL)
- E.16 EXECUTE IMMEDIATE (Executable Embedded SQL)
- E.17 FETCH (Executable Embedded SQL)
- E.18 INSERT (Executable Embedded SQL)
- E.19 OPEN (Executable Embedded SQL)
- E.20 PREPARE (Executable Embedded SQL)
- E.21 ROLLBACK (Executable Embedded SQL)
- E.22 SAVEPOINT (Executable Embedded SQL)
- E.23 SELECT (Executable Embedded SQL)
- E.24 UPDATE (Executable Embedded SQL)
- E.25 VAR (Oracle Embedded SQL Directive)
- E.26 WHENEVER (Embedded SQL Directive)
- Index