Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

What's New 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++

1 Introduction

1.1 What is an Oracle Precompiler?

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

1.3 Why Use SQL

1.4 Why Use PL/SQL

1.5 Pro*C/C++ Precompiler Benefits

1.6 Frequently Asked Questions

1.6.1 What is a VARCHAR?

1.6.2 Does Pro*C/C++ Generate Calls to the Oracle Call Interface?

1.6.3 Why Not Code Using SQLLIB Calls and Not Use Pro*C/C++?

1.6.4 Can I Call A PL/SQL Stored Procedure From a Pro*C/C++ Program?

1.6.5 Can I Write C++ Code, and Precompile It Using Pro*C/C++?

1.6.6 Can I Use Bind Variables Anywhere in a SQL Statement?

1.6.7 I Am Confused By Character Handling in Pro*C/C++.

1.6.8 Is There Anything Special About Character Pointers?

1.6.9 Why Does SPOOL Not Work in Pro*C/C++?

1.6.10 Where Can I Find The On-line Versions of the Example Programs?

1.6.11 How Can I Compile and Link My Application?

1.6.12 Does Pro*C/C++ Now Support Using Structures As Host Variables?

1.6.13 Is It Possible to Have Recursive Functions In Pro*C/C++ If I Use Embedded SQL In the Function?

1.6.14 Can I Use Any Release of Pro*C/C++ with Any Version of the Oracle Server?

1.6.15 When My Application Runs Under Oracle9i, I Keep Getting an Ora-1405 Error (Fetched Column Value Is NULL).

1.6.16 Are All SQLLIB Functions Private?

1.6.17 How Does Oracle9i Support The New Object Types?

1.6.18 Compatibility, Upgrading and Migration

2 Precompiler Concepts

2.1 Key Concepts of Embedded SQL Programming

2.1.1 Embedded SQL Statements

2.1.1.1 Executable Statements and Directives

2.1.2 Embedded SQL Syntax

2.1.3 Static Versus Dynamic SQL Statements

2.1.4 Embedded PL/SQL Blocks

2.1.5 Host and Indicator Variables

2.1.6 Oracle Datatypes

2.1.8 Datatype Equivalencing

2.1.9 Private SQL Areas, Cursors, and Active Sets

2.1.10 Transactions

2.1.11 Errors and Warnings

2.2 Steps in Developing an Embedded SQL Application

2.3 Guidelines for Programming

2.3.2 Constants

2.3.3 Declare Section

2.3.4 Delimiters

2.3.5 File Length

2.3.6 Function Prototyping

2.3.7 Host Variable Names

2.3.8 Line Continuation

2.3.9 Line Length

2.3.10 MAXLITERAL Default Value

2.3.11 Operators

2.3.12 Statement Terminator

2.4 Conditional Precompilation

2.4.1 Symbol Definition

2.4.2 Example SELECT Statement

2.5 Precompile Separately

2.5.1 Guidelines

2.5.1.1 Referencing Cursors

2.5.1.2 Specifying MAXOPENCURSORS

2.5.1.3 Use a Single SQLCA

2.6 Compile and Link

2.7 Example Tables

2.7.1 Example Data

2.8 Example Program: A Simple Query

3 Database Concepts

3.1 Connect to the Database

3.1.1 Using the ALTER AUTHORIZATION Clause to Change Passwords

3.1.1.1 Standard CONNECT

3.1.1.2 Change Password on CONNECT

3.1.2 Connecting Using Oracle Net

3.1.3 Automatic Connects

3.1.3.1 The AUTO_CONNECT Precompiler Option

3.1.3.2 SYSDBA or SYSOPER System Privileges

3.2 Advanced Connection Options

3.2.1 Some Preliminaries

3.2.2 Concurrent Logons

3.2.3 Default Databases and Connections

3.2.4 Explicit Connections

3.2.4.1 Single Explicit Connection

3.2.4.2 Multiple Explicit Connections

3.2.4.3 Ensuring Data Integrity

3.2.5 Implicit Connections

3.2.5.1 Single Implicit Connections

3.2.5.2 Multiple Implicit Connections

3.3 Definitions of Transactions Terms

3.4 How Transactions Guard Your Database

3.5 How to Begin and End Transactions

3.6 Using the COMMIT Statement

3.7 Using the SAVEPOINT Statement

3.8 The ROLLBACK Statement

3.8.1 Statement-Level Rollbacks

3.9 The RELEASE Option

3.10 The SET TRANSACTION Statement

3.11 Override Default Locking

3.11.1 Using FOR UPDATE OF

3.11.1.1 Restrictions

3.11.2 Using LOCK TABLE

3.12 Fetch Across COMMITs

3.13 Distributed Transactions Handling

3.14 Guidelines

3.14.1 Designing Applications

3.14.2 Obtaining Locks

3.14.3 Using PL/SQL

4 Datatypes and Host Variables

4.1 Oracle Datatypes

4.1.1 Internal Datatypes

4.1.2 External Datatypes

4.1.2.1 VARCHAR2

4.1.2.3 INTEGER

4.1.2.8 VARCHAR

4.1.2.12 VARRAW

4.1.2.13 LONG RAW

4.1.2.14 UNSIGNED

4.1.2.15 LONG VARCHAR

4.1.2.16 LONG VARRAW

4.1.3 Additional External Datatypes

4.1.3.1 Datetime and Interval Datatypes

4.1.3.2 ANSI DATE

4.1.3.3 TIMESTAMP

4.1.3.4 TIMESTAMP WITH TIME ZONE

4.1.3.5 TIMESTAMP WITH LOCAL TIME ZONE

4.1.3.6 INTERVAL YEAR TO MONTH

4.1.3.7 INTERVAL DAY TO SECOND

4.1.3.8 Avoiding Unexpected Results Using Datetime

4.2 Host Variables

4.2.1 Host Variable Declaration

4.2.1.1 Storage-Class Specifiers

4.2.1.2 Type Qualifiers

4.2.2 Host Variable Referencing

4.2.2.1 Restrictions

4.3 Indicator Variables

4.3.1 The INDICATOR Keyword

4.3.2 Example of INDICATOR Variable Usage

4.3.3 INDICATOR Variable Guidelines

4.3.4 Oracle Restrictions

4.4 VARCHAR Variables

4.4.1 VARCHAR Variable Declaration

4.4.2 VARCHAR Variable Referencing

4.4.3 Return NULLs to a VARCHAR Variable

4.4.4 Insert NULLs Using VARCHAR Variables

4.4.5 Pass VARCHAR Variables to a Function

4.4.6 Find the Length of the VARCHAR Array Component

4.4.7 Example Program: Using sqlvcp()

4.5 Cursor Variables

4.5.1 Declare a Cursor Variable

4.5.2 Allocate a Cursor Variable

4.5.3 Open a Cursor Variable

4.5.3.1 Opening in a Standalone Stored Procedure

4.5.3.2 Return Types

4.5.4 Closing and Freeing a Cursor Variable

4.5.5 Cursor Variables with the OCI (Release 7 Only)

4.5.6 Restrictions

4.5.7 Example: cv_demo.sql and sample11.pc

4.5.7.1 cv_demo.sql

4.5.7.2 sample11.pc

4.6 CONTEXT Variables

4.7 Universal ROWIDs

4.7.1 SQLRowidGet()

4.8 Host Structures

4.8.1 Host Structures and Arrays

4.8.2 PL/SQL Records

4.8.3 Nested Structures and Unions

4.8.4 Host Indicator Structures

4.8.5 Example Program: Cursor and a Host Structure

4.9 Pointer Variables

4.9.1 Pointer Variable Declaration

4.9.2 Pointer Variable Referencing

4.9.3 Structure Pointers

4.10 Globalization Support

4.11 NCHAR Variables

4.11.1 CHARACTER SET [IS] NCHAR_CS

4.11.2 Environment Variable NLS_NCHAR

4.11.3 CONVBUFSZ Clause in VAR

4.11.4 Character Strings in Embedded SQL

4.11.5 Strings Restrictions

4.11.6 Indicator Variables

5 Advanced Topics

5.1 Character Data

5.1.1 Precompiler Option CHAR_MAP

5.1.2 Inline Usage of the CHAR_MAP Option

5.1.3 Effect of the DBMS and CHAR_MAP Options

5.1.3.1 On Input

5.1.3.2 On Input

5.1.3.3 On Output

5.1.4 VARCHAR Variables and Pointers

5.1.4.1 On Input

5.1.4.2 On Output

5.1.5 Unicode Variables

5.1.5.1 Restrictions on Unicode Variable Usage

5.2 Datatype Conversion

5.3 Datatype Equivalencing

5.3.1 Host Variable Equivalencing

5.3.2 User-Defined Type Equivalencing

5.3.2.1 REFERENCE Clause

5.3.3 CHARF External Datatype

5.3.4 The EXEC SQL VAR and TYPE Directives

5.3.5 Example: Datatype Equivalencing (sample4.pc):

5.4 The C Preprocessor

5.4.1 How the Pro*C/C++ Preprocessor Works

5.4.2 Preprocessor Directives

5.4.2.1 Directives Ignored

5.4.3 ORA_PROC Macro

5.4.4 Location of Header File Specification

5.4.5 Some Preprocessor Examples

5.4.5.1 Using #define

5.4.5.2 Other Preprocessor Restrictions

5.4.6 SQL Statements Not Allowed in #include

5.4.7 Include the SQLCA, ORACA, and SQLDA

5.4.8 EXEC SQL INCLUDE and #include Summary

5.4.9 Defined Macros

5.4.10 Include Files

5.5 Precompiled Header Files

5.5.1 Precompiled Header File Creation

5.5.2 Use of the Precompiled Header Files

5.5.3.1 Redundant File Inclusion

5.5.3.2 Multiple Precompiled Header Files

5.5.4 Effects of Options

5.5.4.1 DEFINE and INCLUDE Options

5.5.4.2 CODE and PARSE Options

5.5.5 Usage Notes

5.6 The Oracle Preprocessor

5.6.1 Symbol Definition

5.6.2 An Oracle Preprocessor Example

5.7 Evaluation of Numeric Constants

5.7.1 Numeric Constants in Pro*C/C++

5.7.2 Numeric Constant Rules and Examples

5.8 SQLLIB Extensions for OCI Release 8 Interoperability

5.8.1 Runtime Context in the OCI Release 8 Environment

5.8.2 Parameters in the OCI Release 8 Environment Handle

5.9 Interface to OCI Release 8

5.9.1 SQLEnvGet()

5.9.2 SQLSvcCtxGet()

5.9.3 Embedded OCI Release 8 Calls

5.10 Embedded OCI Release 7 Calls

5.10.1 Set Up the LDA

5.10.2 Remote and Multiple Connections

5.11 New Names for SQLLIB Public Functions

5.12 X/Open Application Development

5.12.1 Oracle-Specific Issues

5.12.1.1 Connecting to Oracle

5.12.1.2 Transaction Control

5.12.1.3 OCI Calls (Release 7 Only)

5.12.1.4 Linking

6 Embedded SQL

6.1 Host Variables

6.1.1 Output versus Input Host Variables

6.2 Indicator Variables

6.2.1 Insert NULLs

6.2.2 Returned NULLs

6.2.3 Fetch NULLs

6.2.4 Test for NULLs

6.2.5 Truncated Values

6.3 The Basic SQL Statements

6.3.1 The SELECT Statement

6.3.1.1 Available Clauses

6.3.2 The INSERT Statement

6.3.2.1 Using Subqueries

6.3.3 The UPDATE Statement

6.3.4 The DELETE Statement

6.3.5 The WHERE Clause

6.4 The DML Returning Clause

6.5.1 The DECLARE CURSOR Statement

6.5.2 The OPEN Statement

6.5.3 The FETCH Statement

6.5.4 The CLOSE Statement

6.6 Scrollable Cursors

6.6.1 Using Scrollable Cursors

6.6.1.1 DECLARE SCROLL CURSOR

6.6.2 The CLOSE_ON_COMMIT Precompiler Option

6.6.3 The PREFETCH Precompiler Option

6.7 Optimizer Hints

6.7.1 Issuing Hints

6.8 The CURRENT OF Clause

6.8.1 Restrictions

6.9 The Cursor Statements

6.10 A Complete Example Using Non-Scrollable Cursor

6.11 A Complete Example Using Scrollable Cursor

6.12 Positioned Update

7 Embedded PL/SQL

7.1 Advantages of PL/SQL

7.1.1 Better Performance

7.1.2 Integration with Oracle

7.1.3 Cursor FOR Loops

7.1.4 Procedures and Functions

7.1.6 PL/SQL Tables

7.1.7 User-Defined Records

7.2 Embedded PL/SQL Blocks

7.3 Host Variables

7.3.1 Example: Using Host Variables with PL/SQL

7.3.2 Complex Example

7.3.3 VARCHAR Pseudotype

7.3.4 Restriction

7.4 Indicator Variables

7.4.1 NULLs Handling

7.4.2 Truncated Values

7.5 Host Arrays

7.5.1 ARRAYLEN Statement

7.5.2 Optional Keyword EXECUTE

7.6 Cursor Usage in Embedded PL/SQL

7.7 Stored PL/SQL and Java Subprograms

7.7.1 Creating Stored Subprograms

7.7.2 Calling a Stored PL/SQL or Java Subprogram

7.7.2.1 Anonymous PL/SQL Block

7.7.2.2 Remote Access

7.7.2.3 The CALL Statement

7.7.2.4 CALL Example

7.7.3 Getting Information about Stored Subprograms

7.8 External Procedures

7.8.1 Restrictions on External Procedures

7.8.2 Creating the External Procedure

7.8.3 SQLExtProcError()

7.9 Using Dynamic SQL

8 Host Arrays

8.1 Why Use Arrays?

8.2 Declaring Host Arrays

8.2.1 Restrictions

8.2.2 Maximum Size of Arrays

8.3 Using Arrays in SQL Statements

8.3.1 Referencing Host Arrays

8.3.2 Using Indicator Arrays

8.3.3 Oracle Restrictions

8.3.4 ANSI Restriction and Requirements

8.4 Selecting into Arrays

8.4.1 Cursor Fetches

8.4.2 Using sqlca.sqlerrd[2]

8.4.3 Number of Rows Fetched

8.4.4 Scrollable Cursor Fetches

8.4.5 Sample Program 3: Host Arrays

8.4.6 Sample Program: Host Arrays Using Scrollable Cursor

8.4.6.1 Scroll Demo2.pc

8.4.7 Host Array Restrictions

8.4.8 Fetching NULLs

8.4.9 Fetching Truncated Values

8.5 Inserting with Arrays

8.5.1 Inserting with Arrays Restrictions

8.6 Updating with Arrays

8.6.1 Updating with Arrays Restrictions

8.7 Deleting with Arrays

8.7.1 Deleting with Arrays Restrictions

8.8 Using the FOR Clause

8.8.1 FOR Clause Restrictions

8.8.1.1 In a SELECT Statement

8.8.1.2 With the CURRENT OF Clause

8.9 Using the WHERE Clause

8.10 Arrays of Structs

8.10.1 Arrays of Structs Usage

8.10.2 Restrictions on Arrays of Structs

8.10.3 Declaring an Array of Structs

8.10.4 Variables Guidelines

8.10.5 Declaring a Pointer to an Array of Structs

8.10.6 Examples

8.10.6.1 Example 1: A Simple Array of Structs of Scalars

8.10.6.2 Example 2: Using Mixed Scalar Arrays with An Array of Structs

8.10.6.3 Example 3: Using Multiple Arrays of Structs with a Cursor

8.10.6.4 Example 4: Individual Array and Struct Member Referencing

8.10.6.5 Example 5: Using Indicator Variables, a Special Case

8.10.6.6 Example 6: Using a Pointer to an Array of Structs

8.11 Mimicking CURRENT OF

9 Handling Runtime Errors

9.1 The Need for Error Handling

9.2 Error Handling Alternatives

9.2.1 Status Variables

9.2.2 The SQL Communications Area

9.3 The SQLSTATE Status Variable

9.3.1 Declaring SQLSTATE

9.3.2 SQLSTATE Values

9.3.3 Using SQLSTATE

9.3.3.1 If You Declare SQLSTATE

9.3.3.2 If You Do not Declare SQLSTATE

9.4 Declaring SQLCODE

9.5 Key Components of Error Reporting Using the SQLCA

9.5.1 Status Codes

9.5.2 Warning Flags

9.5.3 Rows-Processed Count

9.5.4 Parse Error Offsets

9.5.5 Error Message Text

9.6 Using the SQL Communications Area (SQLCA)

9.6.1 Declaring the SQLCA

9.6.2 SQLCA Contents

9.6.3 SQLCA Structure

9.6.3.1 sqlcaid

9.6.3.2 sqlcabc

9.6.3.3 sqlcode

9.6.3.4 sqlerrm

9.6.3.5 sqlerrp

9.6.3.6 sqlerrd

9.6.3.7 sqlwarn

9.6.3.8 sqlext

9.6.4 PL/SQL Considerations

9.7 Getting the Full Text of Error Messages

9.8 Using the WHENEVER Directive

9.8.1 WHENEVER Conditions

9.8.1.1 SQLWARNING

9.8.1.2 SQLERROR

9.8.1.3 NOT FOUND

9.8.2 WHENEVER Actions

9.8.2.1 CONTINUE

9.8.2.3 DO BREAK

9.8.2.4 DO CONTINUE

9.8.2.5 GOTO label_name

9.8.3 WHENEVER Examples

9.8.4 Use of DO BREAK and DO CONTINUE

9.8.5 Scope of WHENEVER

9.8.6 Guidelines for WHENEVER

9.8.6.1 Placing the Statements

9.8.6.2 Handling End-of-Data Conditions

9.8.6.3 Avoiding Infinite Loops

9.8.6.4 Maintaining Addressability

9.8.6.5 Returning After an Error

9.9 Obtaining the Text of SQL Statements

9.9.1 Restrictions

9.9.2 Example Program

9.10 Using the Oracle Communications Area (ORACA)

9.10.1 Declaring the ORACA

9.10.2 Enabling the ORACA

9.10.3 ORACA Contents

9.10.4 Choosing Runtime Options

9.10.5 Structure of the ORACA

9.10.5.1 oracaid

9.10.5.2 oracabc

9.10.5.3 oracchf

9.10.5.4 oradbgf

9.10.5.5 orahchf

9.10.5.6 orastxtf

9.10.5.7 Diagnostics

9.10.5.8 orastxt

9.10.5.9 orasfnm

9.10.5.10 oraslnr

9.10.5.11 Cursor Cache Statistics

9.10.5.12 orahoc

9.10.5.13 oramoc

9.10.5.14 oracoc

9.10.5.15 oranor

9.10.5.16 oranpr

9.10.5.17 oranex

9.10.6 ORACA Example

10 Precompiler Options

10.1 The Precompiler Command

10.1.1 Case Sensitivity

10.2 Precompiler Options

10.2.1 Configuration Files

10.2.2 Precedence of Option Values

10.2.3 Macro and Micro Options

10.2.4 What Occurs During Precompilation?

10.2.5 Scope of Options

10.3 Quick Reference

10.4 Entering Options

10.4.1 On the Command Line

10.4.2.1 Uses for EXEC ORACLE

10.4.2.2 Scope of EXEC ORACLE

10.5 Using the Precompiler Options

10.5.1 AUTO_CONNECT

10.5.2 CHAR_MAP

10.5.3 CLOSE_ON_COMMIT

10.5.5 COMP_CHARSET

10.5.7 CPP_SUFFIX

10.5.9 DEF_SQLCODE

10.5.11 DURATION

10.5.12 DYNAMIC

10.5.14 ERRTYPE

10.5.17 HOLD_CURSOR

10.5.19 INCLUDE

10.5.24 MAXLITERAL

10.5.25 MAXOPENCURSORS

10.5.27 NLS_CHAR

10.5.28 NLS_LOCAL

10.5.29 OBJECTS

10.5.32 PAGELEN

10.5.34 PREFETCH

10.5.35 RELEASE_CURSOR

10.5.36 SELECT_ERROR

10.5.37 SQLCHECK

10.5.38 SYS_INCLUDE

10.5.39 THREADS

10.5.40 TYPE_CODE

10.5.41 UNSAFE_NULL

10.5.43 UTF16_CHARSET

10.5.44 VARCHAR

10.5.45 VERSION

11 Multithreaded Applications

11.1 What are Threads?

11.2 Runtime Contexts in Pro*C/C++

11.3 Runtime Context Usage Models

11.3.1 Multiple Threads Sharing a Single Runtime Context

11.3.2 Multiple Threads Sharing Multiple Runtime Contexts

11.4 User Interface Features for Multithreaded Applications

11.4.1 THREADS Option

11.4.2 Embedded SQL Statements and Directives

11.4.2.1 EXEC SQL ENABLE THREADS

11.4.2.2 EXEC SQL CONTEXT ALLOCATE

11.4.2.3 EXEC SQL CONTEXT USE

11.4.2.4 EXEC SQL CONTEXT FREE

11.4.3 CONTEXT USE Examples

11.4.4 Programming Considerations

11.5 Multithreaded Example

11.6 Connection Pooling

11.6.1 Using the Connection Pooling Feature

11.6.1.1 How to Enable Connection Pooling

11.6.1.2 Command Line Options for Connection Pooling

11.6.1.3 Example

11.6.1.4 Performance Tuning

11.6.2 Demo Program:1

11.6.2.1 Example

11.6.3 Demo Program:2

11.6.3.1 Case 1: By varying CMIN

11.6.3.2 Case 2: By varying CMAX

11.6.3.3 Example

12 C++ Applications

12.1 Understanding C++ Support

12.1.1 No Special Macro Processing

12.2 Precompiling for C++

12.2.1 Code Generation

12.2.2 Parsing Code

12.2.3 Output Filename Extension

12.2.4 System Header Files

12.3 Example Programs

12.3.1 cppdemo1.pc

12.3.2 cppdemo2.pc

12.3.3 cppdemo3.pc

13 Oracle Dynamic SQL

13.1 What is Dynamic SQL?

13.2 Advantages and Disadvantages of Dynamic SQL

13.3 When to Use Dynamic SQL

13.4 Requirements for Dynamic SQL Statements

13.5 How Dynamic SQL Statements are Processed

13.6 Methods for Using Dynamic SQL

13.6.1 Method 1

13.6.2 Method 2

13.6.3 Method 3

13.6.4 Method 4

13.6.5 Guidelines

13.6.5.1 Avoiding Common Errors

13.7 Using Method 1

13.7.1 Example Program: Dynamic SQL Method 1

13.8 Using Method 2

13.8.1 The USING Clause

13.8.2 Example Program: Dynamic SQL Method 2

13.9 Using Method 3

13.9.6 Example Program: Dynamic SQL Method 3

13.10 Using Method 4

13.10.1 Need for the SQLDA

13.10.2 The DESCRIBE Statement

13.10.3 What is a SQLDA?

13.10.4 Implementing Oracle Method 4

13.10.5 Restriction

13.11 Using the DECLARE STATEMENT Statement

13.11.1 Using Host Arrays

13.12 Using PL/SQL

13.12.1 With Method 1

13.12.2 With Method 2

13.12.3 With Method 3

13.12.4 With Oracle Method 4

14 ANSI Dynamic SQL

14.1 Basics of ANSI Dynamic SQL

14.1.1 Precompiler Options

14.2 Overview of ANSI SQL Statements

14.2.1 Example Code

14.3 Oracle Extensions

14.3.1 Reference Semantics

14.3.2 Using Arrays for Bulk Operations

14.3.3 Support for Arrays of Structs

14.3.4 Support for Object Types

14.4 ANSI Dynamic SQL Precompiler Options

14.5 Full Syntax of the Dynamic SQL Statements

14.5.1 ALLOCATE DESCRIPTOR

14.5.2 DEALLOCATE DESCRIPTOR

14.5.3 GET DESCRIPTOR

14.5.4 SET DESCRIPTOR

14.5.5 Use of PREPARE

14.5.6 DESCRIBE INPUT

14.5.7 DESCRIBE OUTPUT

14.5.9 Use of EXECUTE IMMEDIATE

14.5.10 Use of DYNAMIC DECLARE CURSOR

14.5.11 OPEN Cursor

14.5.13 CLOSE a Dynamic Cursor

14.5.14 Differences From Oracle Dynamic Method 4

14.5.15 Restrictions

14.6 Example Programs

14.6.1 ansidyn1.pc

14.6.2 ansidyn2.pc

15 Oracle Dynamic SQL: Method 4

15.1 Meeting the Special Requirements of Method 4

15.1.1 What Makes Method 4 Special?

15.1.2 What Information Does Oracle Need?

15.1.3 Where Is the Information Stored?

15.1.4 How is the SQLDA Referenced?

15.1.5 How is the Information Obtained?

15.2 Understanding the SQLDA

15.2.1 Purpose of the SQLDA

15.2.2 Multiple SQLDAs

15.2.3 Declaring a SQLDA

15.2.4 Allocating a SQLDA

15.3 Using the SQLDA Variables

15.3.1 The N Variable

15.3.2 The V Variable

15.3.3 The L Variable

15.3.4 The T Variable

15.3.5 The I Variable

15.3.6 The F Variable

15.3.7 The S Variable

15.3.8 The M Variable

15.3.9 The C Variable

15.3.10 The X Variable

15.3.11 The Y Variable

15.3.12 The Z Variable

15.4 Some Preliminaries

15.4.1 Converting Data

15.4.1.1 Internal Datatypes

15.4.1.2 External Datatypes

15.4.2 Coercing Datatypes

15.4.2.1 Extracting Precision and Scale

15.4.3 Handling NULL/Not NULL Datatypes

15.5 The Basic Steps

15.6 A Closer Look at Each Step

15.6.1 Declare a Host String

15.6.2 Declare the SQLDAs

15.6.3 Allocate Storage Space for the Descriptors

15.6.4 Set the Maximum Number to DESCRIBE

15.6.5 Put the Query Text in the Host String

15.6.6 PREPARE the Query from the Host String

15.6.7 DECLARE a Cursor

15.6.8 DESCRIBE the Bind Variables

15.6.9 Reset Number of Placeholders

15.6.10 Get Values and Allocate Storage for Bind Variables

15.6.11 OPEN the Cursor

15.6.12 DESCRIBE the Select List

15.6.13 Reset Number of Select-List Items

15.6.14 Reset Length/Datatype of Each Select-list Item

15.6.15 FETCH Rows from the Active Set

15.6.16 Get and Process Select-List Values

15.6.17 Deallocate Storage

15.6.18 CLOSE the Cursor

15.6.19 Using Host Arrays

15.6.20 sample12.pc

15.7 Example Program: Dynamic SQL Method 4

15.8 Sample Program : Dynamic SQL Method 4 using Scrollable Cursors

16 Large Objects (LOBs)

16.1 What are LOBs?

16.1.1 Internal LOBs

16.1.2 External LOBs

16.1.3 Security for BFILEs

16.1.4 LOBs versus LONG and LONG RAW

16.1.5 LOB Locators

16.1.6 Temporary LOBs

16.1.7 LOB Buffering Subsystem

16.2 How to Use LOBs in Your Program

16.2.1 Three Ways to Access LOBs

16.2.2 LOB Locators in Your Application

16.2.3 Initializing a LOB

16.2.3.1 Internal LOBs

16.2.3.2 External LOBs

16.2.3.3 Temporary LOBs

16.2.3.4 Freeing LOBs

16.3 Rules for LOB Statements

16.3.1 For All LOB Statements

16.3.2 For the LOB Buffering Subsystem

16.3.3 For Host Variables

16.4 LOB Statements

16.4.5 CREATE TEMPORARY

16.4.6 DISABLE BUFFERING

16.4.7 ENABLE BUFFERING

16.4.9 FILE CLOSE ALL

16.4.10 FILE SET

16.4.11 FLUSH BUFFER

16.4.12 FREE TEMPORARY

16.4.13 LOAD FROM FILE

16.4.18 DESCRIBE

16.5 LOBs and the Navigational Interface

16.5.1 Transient Objects

16.5.2 Persistent Objects

16.5.3 Navigational Interface Example

16.6 LOB Program Examples

16.6.1 READ a BLOB, Write a File Example

16.6.2 Read a File, WRITE a BLOB Example

16.6.3 lobdemo1.pc

17 Objects

17.1 Introduction to Objects

17.1.1 Object Types

17.1.2 REFs to Object Types

17.1.3 Type Inheritance

17.2 Using Object Types in Pro*C/C++

17.2.1 NULL Indicators

17.3 The Object Cache

17.3.1 Persistent Versus Transient Copies of Objects

17.4 Associative Interface

17.4.1 When to Use the Associative Interface

17.4.2 ALLOCATE

17.4.4 CACHE FREE ALL

17.4.5 Accessing Objects Using the Associative Interface

17.5 Navigational Interface

17.5.1 When to Use the Navigational Interface

17.5.2 Rules Used in the Navigational Statements

17.5.3 OBJECT CREATE

17.5.4 OBJECT DEREF

17.5.5 OBJECT RELEASE

17.5.6 OBJECT DELETE

17.5.7 OBJECT UPDATE

17.5.8 OBJECT FLUSH

17.5.9 Navigational Access to Objects

17.6 Converting Object Attributes and C Types

17.6.1 OBJECT SET

17.6.2 OBJECT GET

17.7 Object Options Set/Get

17.7.1 CONTEXT OBJECT OPTION SET

17.7.2 CONTEXT OBJECT OPTION GET

17.8 New Precompiler Options for Objects

17.8.2 DURATION

17.8.6 SQLCHECK Support for Objects

17.8.7 Type Checking at Runtime

17.9 An Object Example in Pro*C/C++

17.9.1 Associative Access

17.9.2 Navigational Access

17.10 Example Code for Type Inheritance

17.11 Example Code for Navigational Access

17.12 Using C Structures

17.13 Using REFs

17.13.1 Generating a C Structure for a REF

17.13.2 Declaring REFs

17.13.3 Using REFs in Embedded SQL

17.14 Using OCIDate, OCIString, OCINumber, and OCIRaw

17.14.1 Declaring OCIDate, OCIString, OCINumber, OCIRaw

17.14.2 Use of the OCI Types in Embedded SQL

17.14.3 Manipulating the OCI Types

17.15 Summarizing the New Database Types in Pro*C/C++

17.16 Restrictions on Using Oracle Datatypes in Dynamic SQL

18 Collections

18.1 Collections

18.1.1 Nested Tables

18.1.3 C and Collections

18.2 Descriptors for Collections

18.2.1 Declarations for Host and Indicator Variables

18.2.2 Manipulating Collections

18.2.2.1 Autonomous Collection Access

18.2.2.2 Collection Element Access

18.2.3 Rules for Access

18.2.3.1 Autonomous Access

18.2.3.2 Element Access

18.2.4 Indicator Variables

18.2.4.1 Autonomous Bindings

18.2.4.2 Element Bindings

18.3 OBJECT GET and SET

18.4 Collection Statements

18.4.1 COLLECTION GET

18.4.2 COLLECTION SET

18.4.3 COLLECTION RESET

18.4.4 COLLECTION APPEND

18.4.5 COLLECTION TRIM

18.4.6 COLLECTION DESCRIBE

18.4.6.1 Notes on the Table

18.4.7 Rules for the Use of Collections

18.5 Collection Example Code

18.5.1 Type and Table Creation

18.5.2 GET and SET Example

18.5.3 DESCRIBE Example

18.5.4 RESET Example

18.5.5 Example Program:coldemo1.pc

19 The Object Type Translator

19.1 OTT Overview

19.2 What is the Object Type Translator

19.2.1 Creating Types in the Database

19.2.2 Invoking OTT

19.2.2.1 Command Line

19.2.2.2 Configuration File

19.2.2.3 INTYPE File

19.2.3 The OTT Command Line

19.2.3.2 Userid

19.2.3.3 INTYPE

19.2.3.4 OUTTYPE

19.2.3.7 INITFILE

19.2.3.8 INITFUNC

19.2.4 The INTYPE File

19.2.5 OTT Datatype Mappings

19.2.5.1 Mapping Object Datatypes to C

19.2.5.2 OTT Type Mapping Example

19.2.6 NULL Indicator Structs

19.2.7 OTT Support for Type Inheritance

19.2.7.1 Substitutable Object Attributes

19.2.8 The OUTTYPE File

19.3 Using OTT with OCI Applications

19.3.1 Accessing and Manipulating Objects with OCI

19.3.2 Calling the Initialization Function

19.3.3 Tasks of the Initialization Function

19.4 Using OTT with Pro*C/C++ Applications

19.5 OTT Reference

19.5.1 OTT Command Line Syntax

19.5.2 OTT Parameters

19.5.2.1 USERID

19.5.2.2 INTYPE

19.5.2.3 OUTTYPE

19.5.2.5 INITFILE

19.5.2.6 INITFUNC

19.5.2.8 CONFIG

19.5.2.9 ERRTYPE

19.5.2.11 SCHEMA_NAMES

19.5.2.12 TRANSITIVE

19.5.3 Where OTT Parameters Can Appear

19.5.4 Structure of the INTYPE File

19.5.4.1 INTYPE File Type Specifications

19.5.5 Nested #include File Generation

19.5.6 SCHEMA_NAMES Usage

19.5.7 Default Name Mapping

19.5.8 Restriction

19.5.8.1 File Name Comparison

20 User Exits

20.1 What Is a User Exit?

20.2 Why Write a User Exit?

20.3 Developing a User Exit

20.4 Writing a User Exit

20.4.1 Requirements for Variables

20.4.2 The IAF GET Statement

20.4.2.1 Using IAF GET

20.4.3 The IAF PUT Statement

20.4.3.1 Using IAF PUT

20.5 Calling a User Exit

20.6 Passing Parameters to a User Exit

20.7 Returning Values to a Form

20.7.1 The IAP Constants

20.7.2 Using the SQLIEM Function

20.7.3 Using WHENEVER

20.8 An Example

20.9 Precompiling and Compiling a User Exit

20.10 Example Program: A User Exit

20.11 Using the GENXTB Utility

20.12 Linking a User Exit into SQL*Forms

20.13 Guidelines

20.13.1 Naming the Exit

20.13.2 Connecting to Oracle

20.13.3 Issuing I/O Calls

20.13.4 Using Host Variables

20.13.5 Updating Tables

20.13.6 Issuing Commands

20.14 EXEC TOOLS Statements

20.14.1 Writing a Toolset User Exit

20.14.2 EXEC TOOLS SET

20.14.3 EXEC TOOLS GET

20.14.4 EXEC TOOLS SET CONTEXT

20.14.5 EXEC TOOLS GET CONTEXT

20.14.6 EXEC TOOLS MESSAGE

A New Features

A.1 New In This Release

A.1.1 New External Datatypes

A.2 New In Previous Releases

A.2.1 Array of Structs

A.2.2 Precompiled Header Files

A.2.3 CALL Statement

A.2.4 Changing Passwords at Runtime

A.2.5 Support for National Character Sets

A.2.6 CHAR_MAP Precompiler Option

A.2.7 New Names for SQLLIB Functions

A.2.8 New Actions in WHENEVER Statement

A.2.9 Object Type Support

A.2.10 Object Type Translator

A.2.11 LOB Support

A.2.12 ANSI Dynamic SQ

A.2.13 Collections

A.3 Miscellaneous Topics

A.3.1 Unicode Support

A.3.2 UTF16_CHARSET Option

A.3.3 PREFETCH Option

A.3.4 External Procedures

A.3.5 Calling Java from PL/SQL

A.3.6 DML Returning Clause

A.3.7 Universal ROWID

A.3.8 SYSDBA/SYSOPER Privileges in CONNECT Statements

A.3.9 CLOSE_ON_COMMIT Precompiler Option

A.3.10 Character Strings

A.3.11 Error Message Codes

A.3.12 LINES Option

A.4 Migration From Earlier Releases

B Reserved Words, Keywords, and Namespaces

B.1 Reserved Words and Keywords

B.2 Oracle Reserved Namespaces

C Performance Tuning

C.1 What Causes Poor Performance?

C.2 How Can Performance Be Improved?

C.3 Using Host Arrays

C.4 Using Embedded PL/SQL

C.5 Optimizing SQL Statements

C.5.1 Optimizer Hints

C.5.2 Trace Facility

C.6 Using Indexes

C.7 Taking Advantage of Row-Level Locking

C.8 Eliminating Unnecessary Parsing

C.8.1 Handling Explicit Cursors

C.8.1.1 Cursor Control

C.8.2 Using the Cursor Management Options

C.8.2.1 SQL Areas and Cursor Cache

C.8.2.2 Resource Use

C.8.2.3 Infrequent Execution

C.8.2.4 Frequent Execution

C.8.2.5 Embedded PL/SQL Considerations

C.8.2.6 Parameter Interactions

C.9 Avoiding Unnecessary Reparsing

C.10 Using Connection Pooling

D Syntactic and Semantic Checking

D.1 What Is Syntactic and Semantic Checking?

D.2 Controlling the Type and Extent of Checking

D.3 Specifying SQLCHECK=SEMANTICS

D.3.1 Enabling a Semantic Check

D.3.1.1 Connecting to the Oracle server

D.3.1.2 Using DECLARE TABLE

D.3.1.3 Using DECLARE TYPE

D.4 Specifying SQLCHECK=SYNTAX

D.5 Entering the SQLCHECK Option

E System-Specific References

E.1 System-Specific Information

E.1.1 Location of Standard Header Files

E.1.2 Specifying Location of Included Files for the C Compiler

E.1.3 ANSI C Support

E.1.4 Struct Component Alignment

E.1.5 Size of an Integer and ROWID

E.1.6 Byte Ordering

E.1.7 Connecting to the Oracle Server

E.1.8 Linking in an XA Library

E.1.9 Location of the Pro*C/C++ Executable

E.1.10 System Configuration File

E.1.11 INCLUDE Option Syntax

E.1.12 Compiling and Linking

E.1.13 User Exits

F Embedded SQL Statements and Directives

F.1 Summary of Precompiler Directives and Embedded SQL Statements

F.2 About The Statement Descriptions

F.3 How to Read Syntax Diagrams

F.3.1 Required Keywords and Parameters

F.3.2 Optional Keywords and Parameters

F.3.3 Syntax Loops

F.3.4 Multipart Diagrams

F.3.5 Oracle Names

F.3.6 Statement Terminator

F.4 ALLOCATE (Executable Embedded SQL Extension)

F.5 ALLOCATE DESCRIPTOR (Executable Embedded SQL)

F.6 CACHE FREE ALL (Executable Embedded SQL Extension)

F.7 CALL (Executable Embedded SQL)

F.8 CLOSE (Executable Embedded SQL)

F.9 COLLECTION APPEND (Executable Embedded SQL Extension)

F.10 COLLECTION DESCRIBE (Executable Embedded SQL Extension)

F.11 COLLECTION GET (Executable Embedded SQL Extension)

F.12 COLLECTION RESET (Executable Embedded SQL Extension)

F.13 COLLECTION SET (Executable Embedded SQL Extension)

F.14 COLLECTION TRIM (Executable Embedded SQL Extension)

F.15 COMMIT (Executable Embedded SQL)

F.16 CONNECT (Executable Embedded SQL Extension)

F.17 CONTEXT ALLOCATE (Executable Embedded SQL Extension)

F.18 CONTEXT FREE (Executable Embedded SQL Extension)

F.19 CONTEXT OBJECT OPTION GET (Executable Embedded SQL Extension)

F.20 CONTEXT OBJECT OPTION SET (Executable Embedded SQL Ext)

F.21 CONTEXT USE (Oracle Embedded SQL Directive)

F.22 DEALLOCATE DESCRIPTOR (Embedded SQL Statement)

F.23 DECLARE CURSOR (Embedded SQL Directive)

F.24 DECLARE DATABASE (Oracle Embedded SQL Directive)

F.25 DECLARE STATEMENT (Embedded SQL Directive)

F.26 DECLARE TABLE (Oracle Embedded SQL Directive)

F.27 DECLARE TYPE (Oracle Embedded SQL Directive)

F.28 DELETE (Executable Embedded SQL)

F.29 DESCRIBE (Executable Embedded SQL Extension)

F.30 DESCRIBE DESCRIPTOR (Executable Embedded SQL)

F.31 ENABLE THREADS (Executable Embedded SQL Extension)

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

F.33 EXECUTE (Executable Embedded SQL)

F.34 EXECUTE DESCRIPTOR (Executable Embedded SQL)

F.35 EXECUTE IMMEDIATE (Executable Embedded SQL)

F.36 FETCH (Executable Embedded SQL)

F.37 FETCH DESCRIPTOR (Executable Embedded SQL)

F.38 FREE (Executable Embedded SQL Extension)

F.39 GET DESCRIPTOR (Executable Embedded SQL)

F.40 INSERT (Executable Embedded SQL)

F.41 LOB APPEND (Executable Embedded SQL Extension)

F.42 LOB ASSIGN (Executable Embedded SQL Extension)

F.43 LOB CLOSE (Executable Embedded SQL Extension)

F.44 LOB COPY (Executable Embedded SQL Extension)

F.45 LOB CREATE TEMPORARY (Executable Embedded SQL Extension)

F.46 LOB DESCRIBE (Executable Embedded SQL Extension)

F.47 LOB DISABLE BUFFERING (Executable Embedded SQL Extension)

F.48 LOB ENABLE BUFFERING (Executable Embedded SQL Extension)

F.49 LOB ERASE (Executable Embedded SQL Extension)

F.50 LOB FILE CLOSE ALL (Executable Embedded SQL Extension)

F.51 LOB FILE SET (Executable Embedded SQL Extension)

F.52 LOB FLUSH BUFFER (Executable Embedded SQL Extension)

F.53 LOB FREE TEMPORARY (Executable Embedded SQL Extension)

F.54 LOB LOAD (Executable Embedded SQL Extension)

F.55 LOB OPEN (Executable Embedded SQL Extension)

F.56 LOB READ (Executable Embedded SQL Extension)

F.57 LOB TRIM (Executable Embedded SQL Extension)

F.58 LOB WRITE (Executable Embedded SQL Extension)

F.59 OBJECT CREATE (Executable Embedded SQL Extension)

F.60 OBJECT DELETE (Executable Embedded SQL Extension)

F.61 OBJECT DEREF (Executable Embedded SQL Extension)

F.62 OBJECT FLUSH (Executable Embedded SQL Extension)

F.63 OBJECT GET (Executable Embedded SQL Extension)

F.64 OBJECT RELEASE (Executable Embedded SQL Extension)

F.65 OBJECT SET (Executable Embedded SQL Extension)

F.66 OBJECT UPDATE (Executable Embedded SQL Extension)

F.67 OPEN (Executable Embedded SQL)

F.68 OPEN DESCRIPTOR (Executable Embedded SQL)

F.69 PREPARE (Executable Embedded SQL)

F.70 REGISTER CONNECT (Executable Embedded SQL Extension)

F.71 ROLLBACK (Executable Embedded SQL)

F.72 SAVEPOINT (Executable Embedded SQL)

F.73 SELECT (Executable Embedded SQL)

F.74 SET DESCRIPTOR (Executable Embedded SQL)

F.75 TYPE (Oracle Embedded SQL Directive)

F.76 UPDATE (Executable Embedded SQL)

F.77 VAR (Oracle Embedded SQL Directive)

F.78 WHENEVER (Embedded SQL Directive)

Index