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

A58233-01

Library

Product

Index

Next

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
Notation
ANSI/ISO Compliance
Requirements
Migrating an Application
Your Comments Are Welcome.

1 Introduction

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?
Requirements
Compliance
Certification
FIPS Flagger
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
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

3 Developing a Pro*C/C++ Application

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
Error Message Codes
Include Files
Output Files
Indicator Variables
Programming Guidelines
C++ Support
Comments
Constants
Delimiters
File Length
Function Prototyping
Host Variable Names
Line Continuation
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
Oracle Datatypes
Internal Datatypes
External Datatypes
Host Variables
Declaring Host Variables
Referencing Host Variables
Indicator Variables
Using the Keyword INDICATOR
An Example
Guidelines
Oracle Restrictions
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
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
Declaring LOBs
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)
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

5 Using Embedded SQL

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

6 Using 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
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
Using Host Arrays
ARRAYLEN Statement
Optional Keyword EXECUTE
Using Cursors
An Alternative
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++
Code Emission
Parsing Code
Output Filename Extension
System Header Files
Sample Programs
cppdemo1.pc
cppdemo2.pc
cppdemo3.pc

8 Object Support in Pro*C/C++

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

9 Running the Pro*C/C++ Precompiler

The Precompiler Command
Precompiler Options
Default Values
Case Sensitivity
Configuration Files
What Occurs during Precompilation?
Scope of Options
Entering Options
On the Command Line
Inline
Using the Precompiler Options
AUTO_CONNECT
CHAR_MAP
CODE
COMP_CHARSET
CONFIG
CPP_SUFFIX
DBMS
DEF_SQLCODE
DEFINE
DURATION
ERRORS
ERRTYPE
FIPS
HOLD_CURSOR
INAME
INCLUDE
INTYPE
LINES
LNAME
LTYPE
MAXLITERAL
MAXOPENCURSORS
MODE
NLS_CHAR
NLS_LOCAL
OBJECTS
ONAME
ORACA
PARSE
RELEASE_CURSOR
SELECT_ERROR
SQLCHECK
SYS_INCLUDE
THREADS
UNSAFE_NULL
USERID
VARCHAR
VERSION
Conditional Precompilations
Defining Symbols
An Example
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
Guidelines
Designing Applications
Obtaining Locks
Using PL/SQL

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
Using SQLSTATE
Declaring SQLCODE
Key Components of Error Reporting Using the SQLCA
Status Codes
Warning Flags
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
Conditions
Actions
Some Examples
Use of DO BREAK and DO CONTINUE
Scope of WHENEVER
Guidelines
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

12 Using 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
Number of Rows Fetched
Sample Program: 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
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
Method 1
Method 2
Method 3
Method 4
Guidelines
Using Method 1
Sample Program: Dynamic SQL Method 1
Using Method 2
The USING Clause
Sample Program: Dynamic SQL Method 2
Using Method 3
PREPARE
DECLARE
OPEN
FETCH
CLOSE
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
Using Host Arrays
Using PL/SQL
With Method 1
With Method 2
With Method 3
With Method 4
Caution

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
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
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
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
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
EXEC TOOLS GET
EXEC TOOLS SET CONTEXT
EXEC TOOLS GET CONTEXT
EXEC TOOLS MESSAGE

16 Using the Object Type Translator

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
CONFIG
Where OTT Parameters Can Appear
Structure of the Intype File
Nested #include File Generation
SCHEMA_NAMES Usage
Default Name Mapping
Restrictions

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
Trace Facility
Using Indexes
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
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

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
Syntax Loops
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)


Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Index