Pro*C/C++ Precompiler Programmer's Guide

What This Guide Has to Offer

Who Should Read This Guide?

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

Conventions Used in This Guide

BNF Notation

ANSI/ISO Compliance

Requirements

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

Compliance

Certification

FIPS Flagger

Migrating an Application from Earlier Releases

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?

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

Programming Guidelines

MAXLITERAL Default Value

Operators

Statement Terminator

Conditional Precompilations

Defining Symbols

An Example

Precompiling Separately

Guidelines

Compiling and Linking

Sample Tables

Sample Data

Sample Program: A Simple Query

Connecting to the Database

Using the ALTER AUTHORIZATION Clause to Change Passwords

Connecting Using Net8

Automatic Connects

Advanced Connection Options

Some Preliminaries

Concurrent Logons

Default Databases and Connections

Explicit Connections

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

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

Oracle Datatypes

Host Variables

Declaring Host Variables

Referencing Host Variables

Indicator Variables

Using the Keyword INDICATOR

An Example

Guidelines

Oracle Restrictions

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()

Cursor Variables

Declaring a Cursor Variable

Allocating a Cursor Variable

Opening a Cursor Variable

Closing and Freeing a Cursor Variable

Using Cursor Variables with the OCI (Release 7 Only)

Restrictions

A Sample Program

CONTEXT Variables

Universal ROWIDs

Function SQLRowidGet()

Host Structures

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

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

Unicode Variables

Datatype Conversion

Datatype Equivalencing

Host Variable Equivalencing

User-Defined Type Equivalencing

CHARF External Datatype

Using the EXEC SQL VAR and TYPE Directives

Sample4.pc: Datatype Equivalencing

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

Include Files

Precompiled Header Files

Creating a Precompiled Header File

Using Precompiled Header Files

Examples

Effects of Options

Usage Notes

The Oracle Preprocessor

Defining Symbols

An Oracle Preprocessor Example

Evaluation of Numeric Constants

Using Numeric Constants in Pro*C/C++

Numeric Constant Rules and Examples

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

Embedding (OCI Release 7) Calls

Setting Up the LDA

Remote and Multiple Connections

New Names for SQLLIB Public Functions

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

Using the INSERT Statement

Using the UPDATE Statement

Using the DELETE Statement

Using the WHERE Clause

The DML Returning Clause

Using Cursors

Using the DECLARE CURSOR Statement

Using the OPEN Statement

Using the FETCH Statement

Using the CLOSE Statement

The CLOSE_ON_COMMIT Precompiler Option

The PREFETCH Option

Optimizer Hints

Issuing Hints

Using the CURRENT OF Clause

Restrictions

Using All of 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

An Example

A More Complex Example

VARCHAR Pseudotype

Restriction

Using Indicator Variables

Handling NULLs

Handling Truncated Values

Using 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

Getting Information about Stored Subprograms

External Procedures

Restrictions on External Procedures

Creating the External Procedure

The SQLExtProcError Function

Using Dynamic SQL

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

Sample Program 3: Host Arrays

Restrictions

Fetching NULLs

Fetching Truncated Values

Inserting with Arrays

Restrictions

Updating with Arrays

Restrictions

Deleting with Arrays

Restrictions

Using the FOR Clause

Restrictions

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

The Need for Error Handling

Error Handling Alternatives

Status Variables

The SQL Communications Area

The SQLSTATE Status Variable

Declaring SQLSTATE

SQLSTATE Values

Using SQLSTATE

Declaring SQLCODE

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 Considerations

Getting the Full Text of Error Messages

Using the WHENEVER Statement

Conditions

Actions

Some Examples

Use of DO BREAK and DO CONTINUE

Scope of WHENEVER

Guidelines for WHENEVER

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

The Precompiler Command

Case Sensitivity

Precompiler Options

Precedence of Option Values

Macro and Micro Options

Configuration Files

What Occurs during Precompilation?

Scope of Options

Quick Reference

Entering Options

On the Command Line

Inline

Using the Precompiler Options

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

Examples

Programming Considerations

Multithreaded Example

Understanding C++ Support

No Special Macro Processing

Precompiling for C++

Code Generation

Parsing Code

Output Filename Extension

Sample Programs

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

Using Method 1

Sample Program: Dynamic SQL Method 1

Using Method 2

The USING Clause

Sample Program: Dynamic SQL Method 2

Using Method 3

Sample 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 PL/SQL

Basics of ANSI Dynamic SQL

Precompiler Options

Overview of ANSI SQL Statements

Sample 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

Use of EXECUTE IMMEDIATE

Use of DYNAMIC DECLARE CURSOR

OPEN Cursor

FETCH

CLOSE a Dynamic Cursor

Differences From Oracle Dynamic Method 4

Restrictions

Sample Programs

ansidyn1.pc

ansidyn2.pc

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

Some Preliminaries

Converting Data

Coercing Datatypes

Handling NULL/Not NULL Datatypes

The Basic Steps

A Closer Look at Each Step

Sample Program: Dynamic SQL Method 4

What are LOBs?

Internal LOBs

External LOBs

Security for BFILEs

LOBs vs. 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

Rules for LOB Statements

For All LOB Statements

For the LOB Buffering Subsystem

LOB Statements

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

Introduction to Objects

Object Types

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

Object Options Set/Get

CONTEXT OBJECT OPTION SET

CONTEXT OBJECT OPTION GET

New Precompiler Options for Objects

SQLCHECK Support for Objects

Type Checking at Runtime

An Object Example in Pro*C/C++

Associative Access

Navigational Access

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

Collections

Nested Tables

VARRAYs

C and Collections

Descriptors for Collections

Declarations for Host and Indicator Variables

Manipulating Collections

Rules for Access

Indicator Variables

OBJECT GET and SET

Collection Statements

Rules for the Use of Collections

Collection Sample Code

Type and Table Creation

GET and SET Example

DESCRIBE Example

RESET Example

Sample Program:coldemo1.pc

OTT Overview

What Is 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

Where OTT Parameters Can Appear

Structure of the Intype File

Nested #include File Generation

SCHEMA_NAMES Usage

Default Name Mapping

Restriction

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

Guidelines

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

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 SQL

Collections

Miscellaneous Topics

Unicode Support

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

Migration From Earlier Releases

Character Strings

Error Message Codes

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 Oracle8i

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 Statements

About The Statement Descriptions

How to Read Syntax Diagrams

Required Keywords and Parameters

Optional Keywords and Parameters

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)

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

2 Precompiler Concepts

3 Database Concepts

4 Datatypes and Host Variables

5 Advanced Topics

6 Embedded SQL

7 Embedded PL/SQL

8 Host Arrays

9 Handling Runtime Errors

10 Precompiler Options

11 Multithreaded Applications

12 C++ Applications

13 Oracle Dynamic SQL

14 ANSI Dynamic SQL

15 Oracle Dynamic SQL: Method 4

16 Large Objects (LOBs)

17 Objects

18 Collections

19 The Object Type Translator

20 User Exits

A New Features

B Reserved Words, Keywords, and Namespaces

C Performance Tuning

D Syntactic and Semantic Checking

E System-Specific References

F Embedded SQL Statements and Directives