Programmer's Guide to the Oracle Pro*C/C++ Precompiler

What This Guide Has to Offer

Who Should Read This Guide?

How the Pro*C/C++ Guide Is Organized

Conventions Used in This Guide

Notation

ANSI/ISO Compliance

Requirements

Migrating an Application

Your Comments Are Welcome.

What Is an Oracle Precompiler?

Why Use the Oracle Pro*C/C++ Precompiler?

Why Use SQL?

Why Use PL/SQL?

What Does the Pro*C/C++ Precompiler Offer?

Does the Oracle Pro*C/C++ Precompiler Meet Industry Standards?

Migrating an Application from Earlier Releases

Frequently Asked Questions

Key Concepts of Embedded SQL Programming

Embedded SQL 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

Sample Tables

Sample Data

Sample Program: A Simple Query

Support for the C Preprocessor

How the Pro*C/C++ Preprocessor Works

Preprocessor Directives

ORA_PROC Macro

Specifying the Location of Header Files

Some Preprocessor Examples

SQL Statements Not Allowed in #include

Including the SQLCA, ORACA, and SQLDA

EXEC SQL INCLUDE and #include Summary

Defined Macros

Migrating From Earlier Pro*C/C++ Releases

Character Strings

Deprecated Precompiler Option

Programming Guidelines

MAXLITERAL Default Value

Operators

Statement Labels

Statement Terminator

Precompile-time Evaluation of Numeric Constants

Using Numeric Constants in Pro*C/C++

Numeric Constant Rules and Examples

Declaring Host Variables

Referencing Host Variables

Indicator Variables

Using the Keyword INDICATOR

Host Structures and Arrays

PL/SQL Records

Nested Structures and Unions

Host Indicator Structures

Sample Program: Cursor and a Host Structure

Pointer Variables

Declaring Pointer Variables

Referencing Pointer Variables

Structure Pointers

VARCHAR Variables

Declaring VARCHAR Variables

Referencing VARCHAR Variables

Returning NULLs to a VARCHAR Variable

Inserting NULLs Using VARCHAR Variables

Passing VARCHAR Variables to a Function

Finding the Length of the VARCHAR Array Component

Sample Program: Using sqlvcp()

Handling Character Data

Precompiler Option CHAR_MAP

Inline Usage of the CHAR_MAP Option

Effect of the DBMS and CHAR_MAP Options

VARCHAR Variables and Pointers

Datatype Conversion

Datatype Equivalencing

Host Variable Equivalencing

User-Defined Type Equivalencing

CHARF External Datatype

Using the EXEC SQL VAR and TYPE Directives

Sample Program: Datatype Equivalencing

National Language 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

Handling LOB Types

Using LOBs in Embedded SQL

Cursor Variables

Declaring a Cursor Variable

Allocating a Cursor Variable

Opening a Cursor Variable

Closing a Cursor Variable

Using Cursor Variables with the OCI (Release 7 Only)

Restrictions

A Sample Program

Connecting to Oracle8

Connecting Using Net8

Automatic Connects

Concurrent Connections

Some Preliminaries

Default Databases and Connections

Explicit Connections

Implicit Connections

Changing Passwords at Runtime

Using the ALTER AUTHORIZATION Clause

Example

Embedding (OCI Release 7) Calls)

Setting Up the LDA

Remote and Multiple Connections

New Names for SQLLIB Public Functions

Developing Multi-threaded Applications

Runtime Contexts in Pro*C/C++

Runtime Context Usage Models

User-interface Features for Multi-threaded Applications

Programming Considerations

Example

SQLLIB Extensions for OCI Release 8 Interoperability

Establishing and Terminating a Runtime Context and OCI Release 8 Environment

Parameters in the OCI Release 8 Environment Handle

Interfacing to OCI Release 8

SQLEnvGet()

SQLSvcCtxGet()

Embedding OCI Calls

Developing X/Open Applications

Oracle-Specific Issues

Using Host Variables

Output versus Input Host Variables

Using Indicator Variables

Inserting Nulls

Handling Returned Nulls

Fetching Nulls

Testing for Nulls

Fetching Truncated Values

The Basic SQL Statements

Using the SELECT Statement

Available Clauses

Using the INSERT Statement

Using Subqueries

Using the UPDATE Statement

Using the DELETE Statement

Using the WHERE Clause

Using Cursors

Using the DECLARE CURSOR Statement

Using the OPEN Statement

Using the FETCH Statement

Using the CLOSE Statement

Optimizer Hints

Issuing Hints

Using the CURRENT OF Clause

Restrictions

Using All the Cursor Statements

A Complete Example

Advantages of PL/SQL

Better Performance

Integration with Oracle

Cursor FOR Loops

Procedures and Functions

Packages

PL/SQL Tables

User-Defined Records

Embedding PL/SQL Blocks

Using Host Variables

Restrictions on Host Variables

An Example

A More Complex Example

VARCHAR Pseudotype

Restriction

Using Indicator Variables

Handling Nulls

Handling Truncated Values

Creating Stored Subprograms

Calling a Stored Subprogram

Getting Information about Stored Subprograms

Using Dynamic SQL

Understanding C++ Support

No Special Macro Processing

Precompiling for C++

Code Emission

Parsing Code

Output Filename Extension

Introduction to Objects

Object Types

Nested Tables

Varying Length Arrays

REFs

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

Navigational Access to Objects

Converting Object Attributes and C Types

OBJECT SET

OBJECT GET

New Precompiler Options for Objects

SQLCHECK Support for Objects

Type Checking at Runtime

Using Objects in Pro*C/C++

Using Collection Types

Structures for Collection Object Types

Declarations for Host and Indicator Variables

Handling Collection Object Types

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 Oracle8 Datatypes in Dynamic SQL

The Precompiler Command

What Occurs during Precompilation?

Using the Precompiler Options

Conditional Precompilations

Defining Symbols

An Example

Guidelines for Precompiling Separately

Compiling and Linking

Some Terms You Should Know

How Transactions Guard Your Database

How to Begin and End Transactions

Using the COMMIT Statement

Using the SAVEPOINT Statement

Using the ROLLBACK Statement

Statement-Level Rollbacks

Using the RELEASE Option

Using the SET TRANSACTION Statement

Overriding Default Locking

Using FOR UPDATE OF

Using LOCK TABLE

Fetching Across COMMITs

Handling Distributed Transactions

Guidelines

Designing Applications

Obtaining Locks

Using PL/SQL

The Need for Error Handling

Error Handling Alternatives

Status Variables

The SQL Communications Area

The SQLSTATE Status Variable

Key Components of Error Reporting Using the SQLCA

Using the SQL Communications Area (SQLCA)

Declaring the SQLCA

What's in the SQLCA?

Structure of the SQLCA

PL/SQL ConsiderationsPL/SQL

Getting the Full Text of Error Messages

Using the WHENEVER Statement

Obtaining the Text of SQL Statements

Restrictions

Sample Program

Using the Oracle Communications Area (ORACA)

Declaring the ORACA

Enabling the ORACA

What's in the ORACA?

Choosing Runtime Options

Structure of the ORACA

An ORACA Example

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

Number of Rows Fetched

Sample Program: Host Arrays

Restrictions

Fetching Nulls

Fetching Truncated Values

Inserting with Arrays

Using the WHERE Clause

Arrays of Structs

Using Arrays of Structs

Restrictions on Arrays of Structs

Declaring an Array of Structs

Using Indicator Variables

Declaring a Pointer to an Array of Structs

Examples

Mimicking CURRENT OF

Using sqlca.sqlerrd[2]

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

Sample Program: Dynamic SQL Method 1

Using Method 2

The USING Clause

Sample Program: Dynamic SQL Method 2

Sample Program: Dynamic SQL Method 3

Using Method 4

Need for the SQLDA

The DESCRIBE Statement

What Is a SQLDA?

Implementing Method 4

Restriction

Using the DECLARE STATEMENT Statement

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

Using the SQLDA Variables

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

Sample Program: Dynamic SQL Method 4

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

Sample Program: A User Exit

Using the GENXTB Utility

Linking a User Exit into SQL*Forms

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

OTT Overview

Using the Object Type Translator

Creating Types in the Database

Invoking OTT

The OTT Command Line

The Intype File

OTT Datatype Mappings

Null Indicator Structs

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

CONFIG

Where OTT Parameters Can Appear

Structure of the Intype File

Nested #include File Generation

Changing Passwords at Runtime

Support for National Character Sets

CHAR_MAP Precompiler Option

New Names for SQLLIB Functions

Object Type Support

Object Type Translator

Migration From Pro*C/C++ Release 2

Oracle Reserved Words and Keywords

Oracle Reserved Namespaces

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

Using the Cursor Management Options

What Is Syntactic and Semantic Checking?

Controlling the Type and Extent of Checking

Specifying SQLCHECK=SEMANTICS

Enabling a Semantic Check

Specifying SQLCHECK=SYNTAX

Entering the SQLCHECK Option

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 Oracle

Linking in an XA Library

Location of the Pro*C/C++ Executable

System Configuration File

INCLUDE Option Syntax

Compiling and Linking

User Exits

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

ALLOCATE (Executable Embedded SQL Extension)

CACHE FREE ALL (Executable Embedded SQL Extension)

CLOSE (Executable Embedded SQL)

COMMIT (Executable Embedded SQL)

CONNECT (Executable Embedded SQL Extension)

CONTEXT ALLOCATE (Executable Embedded SQL Extension)

CONTEXT FREE (Executable Embedded SQL Extension)

CONTEXT USE (Oracle Embedded SQL Directive)

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)

ENABLE THREADS (Executable Embedded SQL Extension)

EXECUTE ... END-EXEC (Executable Embedded SQL Extension)

EXECUTE (Executable Embedded SQL)

EXECUTE IMMEDIATE (Executable Embedded SQL)

FETCH (Executable Embedded SQL)

FREE (Executable Embedded SQL Extension)

INSERT (Executable Embedded SQL)

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)

PREPARE (Executable Embedded SQL)

ROLLBACK (Executable Embedded SQL)

SAVEPOINT (Executable Embedded SQL)

SELECT (Executable Embedded SQL)

TYPE (Oracle Embedded SQL Directive)

UPDATE (Executable Embedded SQL)

VAR (Oracle Embedded SQL Directive)

WHENEVER (Embedded SQL Directive)

Contents

Title and Copyright Information

Preface i

Chapter 1 Introduction 1-1

Chapter 2 Learning the Basics 2-1

Chapter 3 Developing a Pro*C/C++ Application 3-1

Chapter 4 Advanced Pro*C/C++ Applications 4-1

Chapter 5 Using Embedded SQL 5-1

Chapter 6 Using Embedded PL/SQL 6-1

Chapter 7 Using C++ 7-1

Chapter 8 Object Support in Pro*C/C++ 8-1

Chapter 9 Running the Pro*C/C++ Precompiler 9-1

Chapter 10 Defining and Controlling Transactions 10-1

Chapter 11 Handling Runtime Errors 11-1

Chapter 12 Using Host Arrays 12-1

Chapter 13 Using Dynamic SQL 13-1

Chapter 14 Using Dynamic SQL: Advanced Concepts 14-1

Chapter 15 Writing User Exits 15-1

Chapter 16 Using the Object Type Translator 16-1

Appendix A New Features A-1

Appendix B Oracle Reserved Words, Keywords, and Namespaces B-1

Appendix C Performance Tuning C-1

Appendix D Syntactic and Semantic Checking D-1

Appendix E System-Specific References E-1

Appendix F Embedded SQL Commands and Directives F-1