Pro*C/C++ Precompiler Programmer's Guide
Release 8.0
A58233-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

What This Guide Has to Offer

Who Should Read This Guide?

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

Conventions Used in This Guide

ANSI/ISO Compliance

Migrating an Application

Your Comments Are Welcome.

1 Introduction

What Is an Oracle Precompiler?

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

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

2 Learning the Basics

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

Datatype Equivalencing

Private SQL Areas, Cursors, and Active Sets

Errors and Warnings

Steps in Developing an Embedded SQL Application

Sample Program: A Simple Query

3 Developing a Pro*C/C++ Application

Support for the C Preprocessor

How the Pro*C/C++ Preprocessor Works

Preprocessor Directives

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

Migrating From Earlier Pro*C/C++ Releases

Character Strings

Deprecated Precompiler Option

Error Message Codes

Indicator Variables

Programming Guidelines

Function Prototyping

Host Variable Names

Line Continuation

MAXLITERAL Default Value

Statement Labels

Statement Terminator

Precompile-time Evaluation of Numeric Constants

Using Numeric Constants in Pro*C/C++

Numeric Constant Rules and Examples

Oracle Datatypes

Internal Datatypes

External Datatypes

Declaring Host Variables

Referencing Host Variables

Indicator Variables

Using the Keyword INDICATOR

Oracle Restrictions

Host Structures

Host Structures and Arrays

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

4 Advanced Pro*C/C++ Applications

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

Sample Program for LOB Datatypes

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)

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

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

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

Embedding OCI Calls

Developing X/Open Applications

Oracle-Specific Issues

5 Using Embedded SQL

Using Host Variables

Output versus Input Host Variables

Using Indicator Variables

Inserting Nulls

Handling Returned 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 the DECLARE CURSOR Statement

Using the OPEN Statement

Using the FETCH Statement

Using the CLOSE Statement

Optimizer Hints

Using the CURRENT OF Clause

Using All the Cursor Statements

A Complete Example

6 Using Embedded PL/SQL

Advantages of PL/SQL

Better Performance

Integration with Oracle

Cursor FOR Loops

Procedures and Functions

User-Defined Records

Embedding PL/SQL Blocks

Using Host Variables

Restrictions on Host Variables

A More Complex Example

VARCHAR Pseudotype

Using Indicator Variables

Handling Truncated Values

Using Host Arrays

ARRAYLEN Statement

Optional Keyword EXECUTE

Stored Subprograms

Creating Stored Subprograms

Calling a Stored Subprogram

Getting Information about Stored Subprograms

Using Dynamic SQL

7 Using C++

Understanding C++ Support

No Special Macro Processing

Precompiling for C++

Output Filename Extension

System Header Files

Sample Programs

8 Object Support in Pro*C/C++

Introduction to Objects

Varying Length Arrays

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

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

Structures for Collection Object Types

Declarations for Host and Indicator Variables

Handling Collection Object Types

Generating a C Structure for a REF

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

9 Running the Pro*C/C++ Precompiler

The Precompiler Command

Precompiler Options

Case Sensitivity

Configuration Files

What Occurs during Precompilation?

Scope of Options

Entering Options

On the Command Line

Using the Precompiler Options

Conditional Precompilations

Defining Symbols

Guidelines for Precompiling Separately

Compiling and Linking

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

Designing Applications

Obtaining Locks

11 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

Declaring SQLCODE

Key Components of Error Reporting Using the SQLCA

Rows-Processed Count

Parse Error Offset

Error Message Text

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

Use of DO BREAK and DO CONTINUE

Scope of WHENEVER

Obtaining the Text of SQL Statements

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

12 Using Host Arrays

Why Use Arrays?

Declaring Host Arrays

Maximum Size of Arrays

Using Arrays in SQL Statements

Referencing Host Arrays

Using Indicator Arrays

Oracle Restrictions

ANSI Restriction and Requirements

Selecting into Arrays

Number of Rows Fetched

Sample Program: Host Arrays

Fetching Truncated Values

Inserting with Arrays

Updating with Arrays

Deleting with Arrays

Using the FOR Clause

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

Mimicking CURRENT OF

Using sqlca.sqlerrd[2]

13 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

Sample Program: Dynamic SQL Method 1

The USING Clause

Sample Program: Dynamic SQL Method 2

Sample Program: Dynamic SQL Method 3

Need for the SQLDA

The DESCRIBE Statement

What Is a SQLDA?

Implementing Method 4

Using the DECLARE STATEMENT Statement

Using Host Arrays

14 Using Dynamic SQL: Advanced Concepts

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

Some Preliminaries

Converting Data

Coercing Datatypes

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

15 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

Precompiling and Compiling a User Exit

Sample Program: A User Exit

Using the GENXTB Utility

Linking a User Exit into SQL*Forms

Naming the Exit

Connecting to Oracle

Issuing I/O Calls

Using Host Variables

Updating Tables

Issuing Commands

EXEC TOOLS Statements

Writing a Toolset User Exit

EXEC TOOLS SET CONTEXT

EXEC TOOLS GET CONTEXT

EXEC TOOLS MESSAGE

16 Using the Object Type Translator

A New Features

Array of Structs

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

Migration From Pro*C/C++ Release 2

B Oracle Reserved Words, Keywords, and Namespaces

Oracle 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

Taking Advantage of Row-Level Locking

Eliminating Unnecessary Parsing

Handling Explicit Cursors

Using the Cursor Management Options

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

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

Struct Component Alignment

Size of an Integer and ROWID

Connecting to Oracle

Linking in an XA Library

Location of the Pro*C/C++ Executable

System Configuration File

INCLUDE Option Syntax

Compiling and Linking

F 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

Multi-part Diagrams

Database Objects

Statement Terminator

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 OBJECT OPTION GET (Executable Embedded SQL Extension)

CONTEXT OBJECT OPTION SET (Executable Embedded SQL Extension)T

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)

Copyright © 1997 Oracle Corporation.
All Rights Reserved.