Contents

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Related Documents
• Conventions

What's New in Pro*C/C++?

• Oracle10g Release 2 (10.2) New Features in Pro*C/C++
• Oracle9i Release 2 (9.2) New Features in Pro*C/C++
• Oracle9i Release 1 (9.0.1) New Features in Pro*C/C++
• Oracle8i Release 8.1.5 New Features in Pro*C/C++
• Oracle8i Release 8.1.4 New Features in Pro*C/C++
• Oracle8i Release 8.1.3 New Features in Pro*C/C++

Part I Introduction and Concepts

1 Introduction

• What is an Oracle Precompiler?
• Why Use the Oracle Pro*C/C++ Precompiler
• Why Use SQL
• Why Use PL/SQL
• Pro*C/C++ Precompiler Benefits
• Frequently Asked Questions
  • What is a VARCHAR?
  • Does Pro*C/C++ Generate Calls to the Oracle Call Interface?
  • Why Not Code Using SQLLIB Calls and Not Use Pro*C/C++?
  • Can I Call A PL/SQL Stored Procedure From a Pro*C/C++ Program?
  • Can I Write C++ Code, and Precompile It Using Pro*C/C++?
  • Can I Use Bind Variables Anywhere in a SQL Statement?
  • I Am Confused By Character Handling in Pro*C/C++.
  • Is There Anything Special About Character Pointers?
  • Why Does SPOOL Not Work in Pro*C/C++?
  • Where Can I Find The On-line Versions of the Example Programs?
  • How Can I Compile and Link My Application?
  • Does Pro*C/C++ Now Support Using Structures As Host Variables?
  • Is It Possible to Have Recursive Functions In Pro*C/C++ If I Use Embedded SQL In the Function?
  • Can I Use Any Release of Pro*C/C++ with Any Version of the Oracle Server?
  • When My Application Runs Under Oracle9i, I Keep Getting an Ora-1405 Error (Fetched Column Value Is NULL).
  • Are All SQLLIB Functions Private?
  • How Does Oracle9i Support The New Object Types?
  • Compatibility, Upgrading and Migration

2 Precompiler Concepts

• Key Concepts of Embedded SQL Programming
  • Embedded SQL Statements
    • Executable Statements and Directives
  • 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
• Guidelines for Programming
  • Comments
  • Constants
  • Declare Section
  • Delimiters
  • File Length
  • Function Prototyping
    • ANSI_C
    • KR_C
    • CPP
  • Hint Length
  • Host Variable Names
  • Line Continuation
  • Line Length
  • MAXLITERAL Default Value
  • Operators
  • Statement Terminator
• Conditional Precompilation
  • Symbol Definition
  • Example SELECT Statement
• Precompile Separately
  • Guidelines
    • Referencing Cursors
    • Specifying MAXOPENCURSORS
    • Use a Single SQLCA
• Compile and Link
• Example Tables
  • Example Data
• Example Program: A Simple Query

3 Database Concepts

• Connect to the Database
  • Using the ALTER AUTHORIZATION Clause to Change Passwords
    • Standard CONNECT
    • Change Password on CONNECT
  • Connecting Using Oracle Net
  • Automatic Connects
    • The AUTO_CONNECT Precompiler Option
    • SYSDBA or SYSOPER System Privileges
• Advanced Connection Options
  • Some Preliminaries
  • Concurrent Logons
  • Default Databases and Connections
  • Explicit Connections
    • Single Explicit Connection
    • Multiple Explicit Connections
    • Ensuring Data Integrity
  • Implicit Connections
    • Single Implicit Connections
    • Multiple Implicit Connections
• Definitions of Transactions Terms
• How Transactions Guard Your Database
• How to Begin and End Transactions
• Using the COMMIT Statement
• Using the SAVEPOINT Statement
• The ROLLBACK Statement
  • Statement-Level Rollbacks
• The RELEASE Option
• The SET TRANSACTION Statement
• Override Default Locking
  • Using FOR UPDATE OF
    • Restrictions
  • Using LOCK TABLE
• Fetch Across COMMITs
• Distributed Transactions Handling
• Guidelines
  • Designing Applications
  • Obtaining Locks
  • Using PL/SQL

4 Datatypes and Host Variables

• Oracle Datatypes
  • Internal Datatypes
  • External Datatypes
    • VARCHAR2
    • NUMBER
    • INTEGER
    • FLOAT
    • STRING
    • VARNUM
    • LONG
    • VARCHAR
    • ROWID
    • DATE
    • RAW
    • VARRAW
    • LONG RAW
    • UNSIGNED
    • LONG VARCHAR
    • LONG VARRAW
    • CHAR
    • CHARZ
    • CHARF
  • Additional External Datatypes
    • Datetime and Interval Datatypes
    • ANSI DATE
    • TIMESTAMP
    • TIMESTAMP WITH TIME ZONE
    • TIMESTAMP WITH LOCAL TIME ZONE
    • INTERVAL YEAR TO MONTH
    • INTERVAL DAY TO SECOND
    • Avoiding Unexpected Results Using Datetime
• Host Variables
  • Host Variable Declaration
    • Storage-Class Specifiers
    • Type Qualifiers
  • Host Variable Referencing
    • Restrictions
• Indicator Variables
  • The INDICATOR Keyword
  • Example of INDICATOR Variable Usage
  • INDICATOR Variable Guidelines
  • Oracle Restrictions
• VARCHAR Variables
  • VARCHAR Variable Declaration
  • VARCHAR Variable Referencing
  • Return NULLs to a VARCHAR Variable
  • Insert NULLs Using VARCHAR Variables
  • Pass VARCHAR Variables to a Function
  • Find the Length of the VARCHAR Array Component
  • Example Program: Using sqlvcp()
• Cursor Variables
  • Declare a Cursor Variable
  • Allocate a Cursor Variable
  • Open a Cursor Variable
    • Opening in a Standalone Stored Procedure
    • Return Types
  • Closing and Freeing a Cursor Variable
  • Cursor Variables with the OCI (Release 7 Only)
  • Restrictions
  • Example: cv_demo.sql and sample11.pc
    • cv_demo.sql
    • sample11.pc
• CONTEXT Variables
• Universal ROWIDs
  • SQLRowidGet()
• Host Structures
  • Host Structures and Arrays
  • PL/SQL Records
  • Nested Structures and Unions
  • Host Indicator Structures
  • Example Program: Cursor and a Host Structure
• Pointer Variables
  • Pointer Variable Declaration
  • Pointer Variable Referencing
  • Structure Pointers
• Globalization Support
• NCHAR Variables
  • CHARACTER SET [IS] NCHAR_CS
  • Environment Variable NLS_NCHAR
  • CONVBUFSZ Clause in VAR
  • Character Strings in Embedded SQL
  • Strings Restrictions
  • Indicator Variables

5 Advanced Topics

• Character Data
  • Precompiler Option CHAR_MAP
  • Inline Usage of the CHAR_MAP Option
  • Effect of the DBMS and CHAR_MAP Options
    • On Input
    • On Input
    • On Output
  • VARCHAR Variables and Pointers
    • On Input
    • On Output
  • Unicode Variables
    • Restrictions on Unicode Variable Usage
• Datatype Conversion
• Datatype Equivalencing
  • Host Variable Equivalencing
  • User-Defined Type Equivalencing
    • REFERENCE Clause
  • CHARF External Datatype
  • The EXEC SQL VAR and TYPE Directives
  • Example: Datatype Equivalencing (sample4.pc):
• The C Preprocessor
  • How the Pro*C/C++ Preprocessor Works
  • Preprocessor Directives
    • Directives Ignored
  • ORA_PROC Macro
  • Location of Header File Specification
  • Some Preprocessor Examples
    • Using #define
    • Other Preprocessor Restrictions
  • SQL Statements Not Allowed in #include
  • Include the SQLCA, ORACA, and SQLDA
  • EXEC SQL INCLUDE and #include Summary
  • Defined Macros
  • Include Files
• Precompiled Header Files
  • Precompiled Header File Creation
  • Use of the Precompiled Header Files
  • Examples
    • Redundant File Inclusion
    • Multiple Precompiled Header Files
  • Effects of Options
    • DEFINE and INCLUDE Options
    • CODE and PARSE Options
  • Usage Notes
• The Oracle Preprocessor
  • Symbol Definition
  • An Oracle Preprocessor Example
• Evaluation of Numeric Constants
  • Numeric Constants in Pro*C/C++
  • Numeric Constant Rules and Examples
• SQLLIB Extensions for OCI Release 8 Interoperability
  • Runtime Context in the OCI Release 8 Environment
  • Parameters in the OCI Release 8 Environment Handle
• Interface to OCI Release 8
  • SQLEnvGet()
  • SQLSvcCtxGet()
  • Embedded OCI Release 8 Calls
• Embedded OCI Release 7 Calls
  • Set Up the LDA
  • Remote and Multiple Connections
• New Names for SQLLIB Public Functions
• X/Open Application Development
  • Oracle-Specific Issues
    • Connecting to Oracle
    • Transaction Control
    • OCI Calls (Release 7 Only)
    • Linking

6 Embedded SQL

• Host Variables
  • Output versus Input Host Variables
• Indicator Variables
  • Insert NULLs
  • Returned NULLs
  • Fetch NULLs
  • Test for NULLs
  • Truncated Values
• The Basic SQL Statements
  • The SELECT Statement
    • Available Clauses
  • The INSERT Statement
    • Using Subqueries
  • The UPDATE Statement
  • The DELETE Statement
  • The WHERE Clause
• The DML Returning Clause
• Cursors
  • The DECLARE CURSOR Statement
  • The OPEN Statement
  • The FETCH Statement
  • The CLOSE Statement
• Scrollable Cursors
  • Using Scrollable Cursors
    • DECLARE SCROLL CURSOR
    • OPEN
    • FETCH
    • CLOSE
  • The CLOSE_ON_COMMIT Precompiler Option
  • The PREFETCH Precompiler Option
• Optimizer Hints
  • Issuing Hints
• The CURRENT OF Clause
  • Restrictions
• The Cursor Statements
• A Complete Example Using Non-Scrollable Cursor
• A Complete Example Using Scrollable Cursor
• Positioned Update

7 Embedded PL/SQL

• Advantages of PL/SQL
  • Better Performance
  • Integration with Oracle
  • Cursor FOR Loops
  • Procedures and Functions
  • Packages
  • PL/SQL Tables
  • User-Defined Records
• Embedded PL/SQL Blocks
• Host Variables
  • Example: Using Host Variables with PL/SQL
  • Complex Example
  • VARCHAR Pseudotype
  • Restriction
• Indicator Variables
  • NULLs Handling
  • Truncated Values
• Host Arrays
  • ARRAYLEN Statement
  • Optional Keyword EXECUTE
• Cursor Usage in Embedded PL/SQL
• Stored PL/SQL and Java Subprograms
  • Creating Stored Subprograms
  • Calling a Stored PL/SQL or Java Subprogram
    • Anonymous PL/SQL Block
    • Remote Access
    • The CALL Statement
    • CALL Example
  • Getting Information about Stored Subprograms
• External Procedures
  • Restrictions on External Procedures
  • Creating the External Procedure
  • SQLExtProcError()
• Using Dynamic SQL

8 Host Arrays

• Why Use Arrays?
• Declaring Host Arrays
  • Restrictions
  • Maximum Size of Arrays
• Using Arrays in SQL Statements
  • Referencing Host Arrays
  • Using Indicator Arrays
  • Oracle Restrictions
  • ANSI Restriction and Requirements
• Selecting into Arrays
  • Cursor Fetches
  • Using sqlca.sqlerrd[2]
  • Number of Rows Fetched
  • Scrollable Cursor Fetches
  • Sample Program 3: Host Arrays
  • Sample Program: Host Arrays Using Scrollable Cursor
    • Scroll Demo2.pc
  • Host Array Restrictions
  • Fetching NULLs
  • Fetching Truncated Values
• Inserting with Arrays
  • Inserting with Arrays Restrictions
• Updating with Arrays
  • Updating with Arrays Restrictions
• Deleting with Arrays
  • Deleting with Arrays Restrictions
• Using the FOR Clause
  • FOR Clause Restrictions
    • In a SELECT Statement
    • With the CURRENT OF Clause
• Using the WHERE Clause
• Arrays of Structs
  • Arrays of Structs Usage
  • Restrictions on Arrays of Structs
  • Declaring an Array of Structs
  • Variables Guidelines
  • Declaring a Pointer to an Array of Structs
  • Examples
    • Example 1: A Simple Array of Structs of Scalars
    • Example 2: Using Mixed Scalar Arrays with An Array of Structs
    • Example 3: Using Multiple Arrays of Structs with a Cursor
    • Example 4: Individual Array and Struct Member Referencing
    • Example 5: Using Indicator Variables, a Special Case
    • Example 6: Using a Pointer to an Array of Structs
• Mimicking CURRENT OF

9 Handling Runtime Errors

• The Need for Error Handling
• Error Handling Alternatives
  • Status Variables
  • The SQL Communications Area
• The SQLSTATE Status Variable
  • Declaring SQLSTATE
  • SQLSTATE Values
  • Using SQLSTATE
    • If You Declare SQLSTATE
    • If You Do not Declare SQLSTATE
• Declaring SQLCODE
• Key Components of Error Reporting Using the SQLCA
  • Status Codes
  • Warning Flags
  • Rows-Processed Count
  • Parse Error Offsets
  • Error Message Text
• Using the SQL Communications Area (SQLCA)
  • Declaring the SQLCA
  • SQLCA Contents
  • SQLCA Structure
    • sqlcaid
    • sqlcabc
    • sqlcode
    • sqlerrm
    • sqlerrp
    • sqlerrd
    • sqlwarn
    • sqlext
  • PL/SQL Considerations
• Getting the Full Text of Error Messages
• Using the WHENEVER Directive
  • WHENEVER Conditions
    • SQLWARNING
    • SQLERROR
    • NOT FOUND
  • WHENEVER Actions
    • CONTINUE
    • DO
    • DO BREAK
    • DO CONTINUE
    • GOTO label_name
    • STOP
  • WHENEVER Examples
  • Use of DO BREAK and DO CONTINUE
  • Scope of WHENEVER
  • Guidelines for WHENEVER
    • Placing the Statements
    • Handling End-of-Data Conditions
    • Avoiding Infinite Loops
    • Maintaining Addressability
    • Returning After an Error
• Obtaining the Text of SQL Statements
  • Restrictions
  • Example Program
• Using the Oracle Communications Area (ORACA)
  • Declaring the ORACA
  • Enabling the ORACA
  • ORACA Contents
  • Choosing Runtime Options
  • Structure of the ORACA
    • oracaid
    • oracabc
    • oracchf
    • oradbgf
    • orahchf
    • orastxtf
    • Diagnostics
    • orastxt
    • orasfnm
    • oraslnr
    • Cursor Cache Statistics
    • orahoc
    • oramoc
    • oracoc
    • oranor
    • oranpr
    • oranex
  • ORACA Example

10 Precompiler Options

• The Precompiler Command
  • Case Sensitivity
• Precompiler Options
  • Configuration Files
  • Precedence of Option Values
  • Macro and Micro Options
  • What Occurs During Precompilation?
  • Scope of Options
• Quick Reference
• Entering Options
  • On the Command Line
  • Inline
    • Uses for EXEC ORACLE
    • Scope of EXEC ORACLE
• Using the Precompiler Options
  • AUTO_CONNECT
  • CHAR_MAP
  • CINCR
  • CLOSE_ON_COMMIT
  • CMAX
  • CMIN
  • CNOWAIT
  • CODE
  • COMP_CHARSET
  • CONFIG
  • CPOOL
  • CPP_SUFFIX
  • CTIMEOUT
  • DBMS
  • DEF_SQLCODE
  • DEFINE
  • DURATION
  • DYNAMIC
  • ERRORS
  • ERRTYPE
  • EVENTS
  • FIPS
  • HEADER
  • HOLD_CURSOR
  • INAME
  • INCLUDE
  • INTYPE
  • LINES
  • LNAME
  • LTYPE
  • MAXLITERAL
  • MAXOPENCURSORS
  • MODE
  • NATIVE_TYPES
  • NLS_CHAR
  • NLS_LOCAL
  • OBJECTS
  • ONAME
  • ORACA
  • PAGELEN
  • PARSE
  • PREFETCH
  • RELEASE_CURSOR
  • SELECT_ERROR
  • SQLCHECK
  • SYS_INCLUDE
  • THREADS
  • TYPE_CODE
  • UNSAFE_NULL
  • USERID
  • UTF16_CHARSET
  • VARCHAR
  • VERSION

11 Multithreaded Applications

• What are Threads?
• Runtime Contexts in Pro*C/C++
• Runtime Context Usage Models
  • Multiple Threads Sharing a Single Runtime Context
  • Multiple Threads Sharing Multiple Runtime Contexts
• User Interface Features for Multithreaded Applications
  • THREADS Option
  • Embedded SQL Statements and Directives
    • EXEC SQL ENABLE THREADS
    • EXEC SQL CONTEXT ALLOCATE
    • EXEC SQL CONTEXT USE
    • EXEC SQL CONTEXT FREE
  • CONTEXT USE Examples
  • Programming Considerations
• Multithreaded Example
• Connection Pooling
  • Using the Connection Pooling Feature
    • How to Enable Connection Pooling
    • Command Line Options for Connection Pooling
    • Example
    • Performance Tuning
  • Demo Program:1
    • Example
  • Demo Program:2
    • Case 1: By varying CMIN
    • Case 2: By varying CMAX
    • Example

Part II Applications

12 C++ Applications

• Understanding C++ Support
  • No Special Macro Processing
• Precompiling for C++
  • Code Generation
  • Parsing Code
  • Output Filename Extension
  • System Header Files
• Example Programs
  • cppdemo1.pc
  • cppdemo2.pc
  • cppdemo3.pc

13 Oracle 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
  • Example Program: Dynamic SQL Method 1
• Using Method 2
  • The USING Clause
  • Example Program: Dynamic SQL Method 2
• Using Method 3
  • PREPARE
  • DECLARE
  • OPEN
  • FETCH
  • CLOSE
  • Example Program: Dynamic SQL Method 3
• Using Method 4
  • Need for the SQLDA
  • The DESCRIBE Statement
  • What is a SQLDA?
  • Implementing Oracle Method 4
  • Restriction
• Using the DECLARE STATEMENT Statement
  • Using Host Arrays
• Using PL/SQL
  • With Method 1
  • With Method 2
  • With Method 3
  • With Oracle Method 4

14 ANSI Dynamic SQL

• Basics of ANSI Dynamic SQL
  • Precompiler Options
• Overview of ANSI SQL Statements
  • Example Code
• Oracle Extensions
  • Reference Semantics
  • Using Arrays for Bulk Operations
  • Support for Arrays of Structs
  • Support for Object Types
• ANSI Dynamic SQL Precompiler Options
• Full Syntax of the Dynamic SQL Statements
  • ALLOCATE DESCRIPTOR
  • DEALLOCATE DESCRIPTOR
  • GET DESCRIPTOR
  • SET DESCRIPTOR
  • Use of PREPARE
  • DESCRIBE INPUT
  • DESCRIBE OUTPUT
  • EXECUTE
  • Use of EXECUTE IMMEDIATE
  • Use of DYNAMIC DECLARE CURSOR
  • OPEN Cursor
  • FETCH
  • CLOSE a Dynamic Cursor
  • Differences From Oracle Dynamic Method 4
  • Restrictions
• Example Programs
  • ansidyn1.pc
  • ansidyn2.pc

15 Oracle Dynamic SQL: Method 4

• Meeting the Special Requirements of Method 4
  • What Makes Method 4 Special?
  • What Information Does Oracle Need?
  • Where Is the Information Stored?
  • How is the SQLDA Referenced?
  • How is the Information Obtained?
• Understanding the SQLDA
  • Purpose of the SQLDA
  • Multiple SQLDAs
  • Declaring a SQLDA
  • Allocating a SQLDA
• Using the SQLDA Variables
  • The N Variable
  • The V Variable
  • The L Variable
  • The T Variable
  • The I Variable
  • The F Variable
  • The S Variable
  • The M Variable
  • The C Variable
  • The X Variable
  • The Y Variable
  • The Z Variable
• Some Preliminaries
  • Converting Data
    • Internal Datatypes
    • External Datatypes
  • Coercing Datatypes
    • Extracting Precision and Scale
  • Handling NULL/Not NULL Datatypes
• The Basic Steps
• A Closer Look at Each Step
  • Declare a Host String
  • Declare the SQLDAs
  • Allocate Storage Space for the Descriptors
  • Set the Maximum Number to DESCRIBE
  • Put the Query Text in the Host String
  • PREPARE the Query from the Host String
  • DECLARE a Cursor
  • DESCRIBE the Bind Variables
  • Reset Number of Placeholders
  • Get Values and Allocate Storage for Bind Variables
  • OPEN the Cursor
  • DESCRIBE the Select List
  • Reset Number of Select-List Items
  • Reset Length/Datatype of Each Select-list Item
  • FETCH Rows from the Active Set
  • Get and Process Select-List Values
  • Deallocate Storage
  • CLOSE the Cursor
  • Using Host Arrays
  • sample12.pc
• Example Program: Dynamic SQL Method 4
• Sample Program : Dynamic SQL Method 4 using Scrollable Cursors

16 LOBs

• What are LOBs?
  • Internal LOBs
  • External LOBs
  • Security for BFILEs
  • LOBs versus LONG and LONG RAW
  • LOB Locators
  • Temporary LOBs
  • LOB Buffering Subsystem
• How to Use LOBs in Your Program
  • Three Ways to Access LOBs
  • LOB Locators in Your Application
  • Initializing a LOB
    • Internal LOBs
    • External LOBs
    • Temporary LOBs
    • Freeing LOBs
• Rules for LOB Statements
  • For All LOB Statements
  • For the LOB Buffering Subsystem
  • For Host Variables
• LOB Statements
  • APPEND
  • ASSIGN
  • CLOSE
  • COPY
  • CREATE TEMPORARY
  • DISABLE BUFFERING
  • ENABLE BUFFERING
  • ERASE
  • FILE CLOSE ALL
  • FILE SET
  • FLUSH BUFFER
  • FREE TEMPORARY
  • LOAD FROM FILE
  • OPEN
  • READ
  • TRIM
  • WRITE
  • DESCRIBE
• LOBs and the Navigational Interface
  • Transient Objects
  • Persistent Objects
  • Navigational Interface Example
• LOB Program Examples
  • READ a BLOB, Write a File Example
  • Read a File, WRITE a BLOB Example
  • lobdemo1.pc

17 Objects

• Introduction to Objects
  • Object Types
  • REFs to Object Types
  • Type Inheritance
• Using Object Types in Pro*C/C++
  • NULL Indicators
• The Object Cache
  • Persistent Versus Transient Copies of Objects
• Associative Interface
  • When to Use the Associative Interface
  • ALLOCATE
  • FREE
  • CACHE FREE ALL
  • Accessing Objects Using the Associative Interface
• Navigational Interface
  • When to Use the Navigational Interface
  • Rules Used in the Navigational Statements
  • OBJECT CREATE
  • OBJECT DEREF
  • OBJECT RELEASE
  • OBJECT DELETE
  • OBJECT UPDATE
  • OBJECT FLUSH
  • Navigational Access to Objects
• Converting Object Attributes and C Types
  • OBJECT SET
  • OBJECT GET
• Object Options Set/Get
  • CONTEXT OBJECT OPTION SET
  • CONTEXT OBJECT OPTION GET
• New Precompiler Options for Objects
  • VERSION
  • DURATION
  • OBJECTS
  • INTYPE
  • ERRTYPE
  • SQLCHECK Support for Objects
  • Type Checking at Runtime
• An Object Example in Pro*C/C++
  • Associative Access
  • Navigational Access
• Example Code for Type Inheritance
• Example Code for Navigational Access
• Using C Structures
• Using REFs
  • Generating a C Structure for a REF
  • Declaring REFs
  • Using REFs in Embedded SQL
• Using OCIDate, OCIString, OCINumber, and OCIRaw
  • Declaring OCIDate, OCIString, OCINumber, OCIRaw
  • Use of the OCI Types in Embedded SQL
  • Manipulating the OCI Types
• Summarizing the New Database Types in Pro*C/C++
• Restrictions on Using Oracle Datatypes in Dynamic SQL

18 Collections

• Collections
  • Nested Tables
  • Varrays
  • C and Collections
• Descriptors for Collections
  • Declarations for Host and Indicator Variables
  • Manipulating Collections
    • Autonomous Collection Access
    • Collection Element Access
  • Rules for Access
    • Autonomous Access
    • Element Access
  • Indicator Variables
    • Autonomous Bindings
    • Element Bindings
• OBJECT GET and SET
• Collection Statements
  • COLLECTION GET
  • COLLECTION SET
  • COLLECTION RESET
  • COLLECTION APPEND
  • COLLECTION TRIM
  • COLLECTION DESCRIBE
    • Notes on the Table
  • Rules for the Use of Collections
• Collection Example Code
  • Type and Table Creation
  • GET and SET Example
  • DESCRIBE Example
  • RESET Example
  • Example Program:coldemo1.pc

19 The Object Type Translator

• OTT Overview
• What is the Object Type Translator
  • Creating Types in the Database
  • Invoking OTT
    • Command Line
    • Configuration File
    • INTYPE File
  • The OTT Command Line
    • OTT
    • Userid
    • INTYPE
    • OUTTYPE
    • CODE
    • HFILE
    • INITFILE
    • INITFUNC
  • The INTYPE File
  • OTT Datatype Mappings
    • Mapping Object Datatypes to C
    • OTT Type Mapping Example
  • NULL Indicator Structs
  • OTT Support for Type Inheritance
    • Substitutable Object Attributes
  • The OUTTYPE File
• Using OTT with OCI Applications
  • Accessing and Manipulating Objects with OCI
  • Calling the Initialization Function
  • Tasks of the Initialization Function
• Using OTT with Pro*C/C++ Applications
• OTT Reference
  • OTT Command Line Syntax
  • OTT Parameters
    • USERID
    • INTYPE
    • OUTTYPE
    • CODE
    • INITFILE
    • INITFUNC
    • HFILE
    • CONFIG
    • ERRTYPE
    • CASE
    • SCHEMA_NAMES
    • TRANSITIVE
  • Where OTT Parameters Can Appear
  • Structure of the INTYPE File
    • INTYPE File Type Specifications
  • Nested #include File Generation
  • SCHEMA_NAMES Usage
  • Default Name Mapping
  • Restriction
    • File Name Comparison

20 User Exits

• What Is a User Exit?
• Why Write a User Exit?
• Developing a User Exit
• Writing a User Exit
  • Requirements for Variables
• EXEC TOOLS Statements
  • Writing a Toolset User Exit
  • EXEC TOOLS SET
  • EXEC TOOLS GET
  • EXEC TOOLS SET CONTEXT
  • EXEC TOOLS GET CONTEXT
  • EXEC TOOLS MESSAGE
• 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
• Example Program: A User Exit
• Using the GENXTB Utility
• Linking a User Exit into SQL*Forms
• Guidelines
  • Naming the Exit
  • Connecting to Oracle
  • Issuing I/O Calls
  • Using Host Variables
  • Updating Tables
  • Issuing Commands

Part III Appendixes

A New Features

• New In This Release
  • New External Datatypes
• New In Previous Releases
  • Array of Structs
  • Precompiled Header Files
  • CALL Statement
  • Changing Passwords at Runtime
  • Support for National Character Sets
  • CHAR_MAP Precompiler Option
  • New Names for SQLLIB Functions
  • New Actions in WHENEVER Statement
  • Object Type Support
  • Object Type Translator
  • LOB Support
  • ANSI Dynamic SQ
  • Collections
• Miscellaneous Topics
  • Unicode Support
  • UTF16_CHARSET Option
  • PREFETCH Option
  • External Procedures
  • Calling Java from PL/SQL
  • DML Returning Clause
  • Universal ROWID
  • SYSDBA/SYSOPER Privileges in CONNECT Statements
  • CLOSE_ON_COMMIT Precompiler Option
  • Character Strings
  • Error Message Codes
  • LINES Option
• Migration From Earlier Releases

B Reserved Words, Keywords, and Namespaces

• Reserved Words and Keywords
• 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
  • Trace Facility
• Using Indexes
• Taking Advantage of Row-Level Locking
• Eliminating Unnecessary Parsing
  • Handling Explicit Cursors
    • Cursor Control
  • Using the Cursor Management Options
    • SQL Areas and Cursor Cache
    • Resource Use
    • Infrequent Execution
    • Frequent Execution
    • Embedded PL/SQL Considerations
    • Parameter Interactions
• Avoiding Unnecessary Reparsing
• Using Connection Pooling

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 the Oracle server
    • Using DECLARE TABLE
    • Using DECLARE TYPE
• Specifying SQLCHECK=SYNTAX
• Entering the SQLCHECK Option

E System-Specific References

• System-Specific Information
  • Location of Standard Header Files
  • Specifying Location of Included Files for the C Compiler
  • ANSI C Support
  • Struct Component Alignment
  • Size of an Integer and ROWID
  • Byte Ordering
  • Connecting to the Oracle Server
  • Linking in an XA Library
  • Location of the Pro*C/C++ Executable
  • System Configuration File
  • INCLUDE Option Syntax
  • Compiling and Linking
  • User Exits

F Embedded SQL Statements and Directives

• Summary of Precompiler Directives and Embedded SQL Statements
• About The Statement Descriptions
• How to Read Syntax Diagrams
  • Required Keywords and Parameters
  • Optional Keywords and Parameters
  • Syntax Loops
  • Multipart Diagrams
  • Oracle Names
  • Statement Terminator
• ALLOCATE (Executable Embedded SQL Extension)
• ALLOCATE DESCRIPTOR (Executable Embedded SQL)
• CACHE FREE ALL (Executable Embedded SQL Extension)
• CALL (Executable Embedded SQL)
• CLOSE (Executable Embedded SQL)
• COLLECTION APPEND (Executable Embedded SQL Extension)
• COLLECTION DESCRIBE (Executable Embedded SQL Extension)
• COLLECTION GET (Executable Embedded SQL Extension)
• COLLECTION RESET (Executable Embedded SQL Extension)
• COLLECTION SET (Executable Embedded SQL Extension)
• COLLECTION TRIM (Executable Embedded SQL Extension)
• COMMIT (Executable Embedded SQL)
• CONNECT (Executable Embedded SQL Extension)
• CONTEXT ALLOCATE (Executable Embedded SQL Extension)
• CONTEXT FREE (Executable Embedded SQL Extension)
• CONTEXT OBJECT OPTION GET (Executable Embedded SQL Extension)
• CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext)
• CONTEXT USE (Oracle Embedded SQL Directive)
• DEALLOCATE DESCRIPTOR (Embedded SQL Statement)
• DECLARE CURSOR (Embedded SQL Directive)
• DECLARE DATABASE (Oracle Embedded SQL Directive)
• DECLARE STATEMENT (Embedded SQL Directive)
• DECLARE TABLE (Oracle Embedded SQL Directive)
• DECLARE TYPE (Oracle Embedded SQL Directive)
• DELETE (Executable Embedded SQL)
• DESCRIBE (Executable Embedded SQL Extension)
• DESCRIBE DESCRIPTOR (Executable Embedded SQL)
• ENABLE THREADS (Executable Embedded SQL Extension)
• EXECUTE ... END-EXEC (Executable Embedded SQL Extension)
• EXECUTE (Executable Embedded SQL)
• EXECUTE DESCRIPTOR (Executable Embedded SQL)
• EXECUTE IMMEDIATE (Executable Embedded SQL)
• FETCH (Executable Embedded SQL)
• FETCH DESCRIPTOR (Executable Embedded SQL)
• FREE (Executable Embedded SQL Extension)
• GET DESCRIPTOR (Executable Embedded SQL)
• INSERT (Executable Embedded SQL)
• LOB APPEND (Executable Embedded SQL Extension)
• LOB ASSIGN (Executable Embedded SQL Extension)
• LOB CLOSE (Executable Embedded SQL Extension)
• LOB COPY (Executable Embedded SQL Extension)
• LOB CREATE TEMPORARY (Executable Embedded SQL Extension)
• LOB DESCRIBE (Executable Embedded SQL Extension)
• LOB DISABLE BUFFERING (Executable Embedded SQL Extension)
• LOB ENABLE BUFFERING (Executable Embedded SQL Extension)
• LOB ERASE (Executable Embedded SQL Extension)
• LOB FILE CLOSE ALL (Executable Embedded SQL Extension)
• LOB FILE SET (Executable Embedded SQL Extension)
• LOB FLUSH BUFFER (Executable Embedded SQL Extension)
• LOB FREE TEMPORARY (Executable Embedded SQL Extension)
• LOB LOAD (Executable Embedded SQL Extension)
• LOB OPEN (Executable Embedded SQL Extension)
• LOB READ (Executable Embedded SQL Extension)
• LOB TRIM (Executable Embedded SQL Extension)
• LOB WRITE (Executable Embedded SQL Extension)
• OBJECT CREATE (Executable Embedded SQL Extension)
• OBJECT DELETE (Executable Embedded SQL Extension)
• OBJECT DEREF (Executable Embedded SQL Extension)
• OBJECT FLUSH (Executable Embedded SQL Extension)
• OBJECT GET (Executable Embedded SQL Extension)
• OBJECT RELEASE (Executable Embedded SQL Extension)
• OBJECT SET (Executable Embedded SQL Extension)
• OBJECT UPDATE (Executable Embedded SQL Extension)
• OPEN (Executable Embedded SQL)
• OPEN DESCRIPTOR (Executable Embedded SQL)
• PREPARE (Executable Embedded SQL)
• REGISTER CONNECT (Executable Embedded SQL Extension)
• ROLLBACK (Executable Embedded SQL)
• SAVEPOINT (Executable Embedded SQL)
• SELECT (Executable Embedded SQL)
• SET DESCRIPTOR (Executable Embedded SQL)
• TYPE (Oracle Embedded SQL Directive)
• UPDATE (Executable Embedded SQL)
• VAR (Oracle Embedded SQL Directive)
• WHENEVER (Embedded SQL Directive)

Index