Contents

Oracle9i Release 1 (9.0.1) New Fe atures in Pro*C/C++

Oracle8i Release 8.1.5 New Features in Pro*C/C++
< dd>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
< dd>1.4 Why Use PL/SQL
1.5 Pro*C/C++ Precompiler Benefits

1.6 Frequently Asked Questions

1.6.1 What is a VARCHA R?

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

1.6.3 Why Not Code Using SQLLIB Calls and Not Use P ro*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 Precompil e It Using Pro*C/C++?

1.6.6 Can I Use Bind Variables Anywher e in a SQL Statement?

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

1.6.8 Is There Anything Special About Char acter Pointers?

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

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?< /a>

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

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 C an 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 Variab les

2.1.6 Oracle Datatypes

2.1.7 Arrays

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 .1 Comments

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.6.1 ANSI_C

2.3.6.2 KR_C

2.3.6.3 CPP

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.1 2 Statement Terminator

2.4 Conditional Pre compilation

2.4.1 Symbol Definition

2.4.2 Example SELECT Statement

2.5 Precompile Separately

2.5.1 Guidelines

2.5.1.1 Refe rencing 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 D ata

2.8 Example Program: A Simple Query
< /dl>
3 Database Concepts

3.1 Connect to the Database

< a href="pc_03dbc.htm#sthref268">3.1.1 Using the ALTER AUTHORIZATION Clause to Change Passwords
< dd>

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_CONNEC T Precompiler Option

3.1.3.2 SYSDBA or SYSOPER System Privi leges

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

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 Gu ard 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 Optio n

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 LOC K TABLE

3.12 Fetch Across COMMITs

3.13 Distributed Transactions Handling

3.14 Guidelines

3 .14.1 Designing Applications

3.14.2 Obtaining Locks< /a>

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

4.1.2.2 NUMBER

4.1.2.3 INTEGER

4.1.2.4 F LOAT

4.1.2.5 STRING

4.1.2.6 VARNUM

4.1.2.7 L ONG

4.1.2.8 VARCHAR

4.1.2.9 ROWID

4.1.2.10 D ATE

4.1.2.11 RAW

4.1.2.12 VARRAW

4.1.2.13 LO NG RAW

4.1.2.14 UNSIGNED

4.1.2.15 LONG VARCHAR

4.1. 2.16 LONG VARRAW

4.1.2.17 CHAR

4.1.2.18 CHARZ

4.1.2.19 CHARF

4.1.3 Additional External Datatypes

4.1.3.1 Datetime and Interval Datatype s

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 TIM E 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 St orage-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 IN DICATOR Variable Usage

4.3.3 INDICATOR Variable Guidelines< /a>

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 Allocat e a Cursor Variable

4.5.3 Open a Cursor Variable
< dd>

4.5.3.1 Opening in a Standalone Stored Procedure
< dd>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.sq l 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 Nest ed 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 Suppor t

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 Data type Equivalencing

5.3.1 Host Variable Equivalenc ing

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++ P reprocessor 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 #def ine

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 Examples

5.5.3.1 Redundant File Inclusion

5.5.3.2 Multiple Precompile d 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 Defini tion

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 Applica tion 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 Va riables

6.1.1 Output versus Input Host Variables< /a>

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 S ELECT 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 Cursors

6.5.1 The DECLARE CURSOR Statement

6.5.2 The O PEN 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.1.2 OPEN

6.6.1.3 FETCH

6.6.1.4 CLOSE

6.6.2 The CLOSE_ON_COMMIT Precompiler Option

6.6.3 The PREFETCH Precompiler Option< /a>

6.7 Optimizer Hints

6.7.1 Issuing Hints

6.8 The CURRENT OF Clause

6.8.1 Restrictions

6.9 The Cursor Stateme nts

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 Integra tion with Oracle

7.1.3 Cursor FOR Loops

7.1.4 Procedures and Functions

7.1.5 Packages

7.1.6 PL/SQL Tables

7.1.7 User-Defined Records

< a href="pc_07pls.htm#sthref1135">7.2 Embedded PL/SQL Blocks

7.3 Host Variables

7.3.1 Example: Using Host Variables with PL/SQL

7.3.2 Com plex 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 Subpr ograms

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

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 Us ing Arrays in SQL Statements

8.3.1 Referencing H ost 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 Pr ogram: 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 Up dating with Arrays

8.6.1 Updating with Arrays Re strictions

8.7 Deleting with Arrays

8.7.1 Deleting with Arrays Restrictions
< dd>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 Usi ng the WHERE Clause

8.10 Arrays of Structs

< dl>
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 Pointe r 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 Str ucts

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

8.10.6.4 Example 4: Individual Array and Str uct Member Referencing

8.10.6.5 Example 5: Using Indicator Variables, a Special Case

8.10.6.6 Example 6: Using a Poi nter to an Array of Structs

8.11 Mimic king CURRENT OF

9 Handling Runtime Errors

9.1 The Need for Err or 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< /dd>
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 Err or 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< /dd>
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 t he Full Text of Error Messages

9.8 Using the WHENEVER Dire ctive

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.2 DO~~

9.8.2.3 DO BREAK< /dd>
9.8.2.4 DO CONTINUE

9.8.2.5 GOTO label_name

9.8.2.6 STOP

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 P lacing the Statements

9.8.6.2 Handling End-of-Data Conditi ons

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

9.10.1 Declaring the ORACA

9.10.2 Enabling the ORACA

9.10.3 ORACA Contents

9.10.4 Choo sing 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 oradb gf

9.10.5.5 orahchf

9.10.5.6 orastxtf

9.10.5.7 Diagnostics

9.10.5.8 oras txt

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

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 Op tion 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 Opt ions

10.4.1 On the Command Line

10.4.2 Inline

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< /dd>
10.5.2 CHAR_MAP

10.5.3 CLOSE_ON_COMMIT

10.5.4 CODE

10.5.5 COMP_CHARSET

10.5.6 CONFIG

10.5.7 CPP_SUFFIX

10.5.8 DBMS

10.5.9 DEF_SQLCODE

10.5.10 DEFINE

10.5.11 DURATION

10.5.12 DYNAMIC

10.5.13 ERRORS

10.5.14 ERRTYPE

10.5.15 FIPS

10.5.16 HEADER

10.5.17 HOLD_CURSOR
10.5.18 INAME

10.5.19 INCLUDE

10.5.20 INTYPE

10.5.21 LINES

10.5.22 LNAME

10.5.23 LTYPE

10.5.24 MAXLITERAL

10.5.25 MAXOPENCURSORS

10.5.26 MODE

10.5.27 NLS_CHAR

10.5.28 NLS_LOCAL

10.5.29 OBJECTS

10.5.30 ONAME

10.5.31 ORACA

10.5.32 PAGELEN

10.5.33 PARSE

10.5.34 PREFETCH

10.5.35 RELEASE_CURSOR

10.5.36 SELECT_ERRO R

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.42 USERID

10.5.43 UTF16_CHARSET
< dd>10.5.44 VARCHAR

10.5.45 VERSION

11 Multithreaded Applications

11.1 What are Threads?

11.2 Runtime Con texts in Pro*C/C++

11.3 Runtime Context Usage Models

11.3.1 Multiple Threads Sharing a Single Runtime Cont ext

11.3.2 Multiple Threads Sharing Multiple Runtime Conte xts

11.4 User Interface Features for Multithread ed Applications

11.4.1 THREADS Option
< dd>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< /dd>
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 A dvantages and Disadvantages of Dynamic SQL

13.3 When to Us e 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 Avoid ing Common Errors

13.7 Using Method 1< /a>

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.1 PREP ARE

13.9.2 DECLARE

13.9.3 OPEN

13.9.4 FET CH

13.9.5 CLOSE

13.9.6 Example Program: Dynamic SQL Method 3

13.10 Using Method 4

13.1 0.1 Need for the SQLDA

13.10.2 The DESCRIBE Stateme nt

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 ANS I Dynamic SQL

14.1 Basics of ANSI Dynamic SQL< /a>

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

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.8 EXECUTE

14.5.9 Use of EXECUTE IMMEDIATE

14.5.10 Use of DYNAMIC DECLARE CURSOR

14.5.11 OPEN Cursor

14.5.12 FETCH

14.5.13 CLOSE a Dynamic Cursor

14.5.14 Differences From Oracle Dynamic Method 4

14.5.15 Restricti ons

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 W here Is the Information Stored?

15.1.4 How is the SQLDA Re ferenced?

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
< /dd>
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 Varia ble

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

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 Converti ng 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< /a>

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 PREPAR E 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 Selec t-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 BFILE s

16.1.4 LOBs versus LONG and LONG RAW

16.1.5 LOB Locators

16.1.6 Temporary LOBs

16.1.7 LOB Bufferi ng Subsystem

16.2 How to Use LOBs in Your Progra m

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 Exte rnal 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< /dd>

16.4 LOB Statements

16.4.1 APPEND

16.4.2 ASSIGN

16.4.3 CLOSE

16.4.4 COPY

16.4.5 CREATE TEMPORARY

16.4.6 DISABLE BUFFERING

16.4.7 ENABLE BUFFERING

16.4.8 ERASE

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.14 OPEN

16.4.15 READ

16.4.16 TRIM

16.4.17 WRITE

16.4.18 DESCRIBE

16.5 LOBs and the Navigational Interface

16.5.1 T ransient 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 Objec ts

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

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.3 FREE

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

1 7.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.1 VERSION

17.8.2 DURATION

17.8.3 OBJECTS

17.8.4 INTYPE

17.8 .5 ERRTYPE

17.8.6 SQLCHECK Support for Objects< /dd>
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 Exam ple Code for Type Inheritance

17.11 Example Code for Navig ational 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 Em bedded SQL

17.14 Using OCIDate, OCIString, OCINu mber, and OCIRaw

17.14.1 Declaring OCIDate, OCIS tring, OCINumber, OCIRaw

17.14.2 Use of the OCI Types in E mbedded 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.2 Varrays

18.1.3 C and Collections< /a>

18.2 Descriptors for Collections

< dl>
18.2.1 Declarations for Host and Indicator Variables
< dd>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 AP PEND

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

18.5.2 GET and SET Example

18.5.3 DESCRIBE Example

18.5.4 RESET Example

18.5.5 Example Prog ram:coldemo1.pc

19 The Object Type Translator

19.1 OTT Overview

19.2 What is the Object Type Translator< /dd>

19.2.1 Creating Types in the Database

< a href="pc_19ott.htm#sthref2519">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.1 OTT

19.2.3.2 Userid

19.2.3.3 INTYPE

19.2.3.4 OUTTYPE

19.2.3.5 CODE

19.2.3.6 HFILE

19.2.3.7 INITFILE

19.2.3.8 INITFUNC

19.2.4 T he 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 Appli cations

19.3.1 Accessing and Manipulating Object s with OCI

19.3.2 Calling the Initialization Function< /dd>
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

< a href="pc_19ott.htm#sthref2575">19.5.2.2 INTYPE

19.5.2.3 OUTTYPE

19.5.2.4 CODE

19.5.2.5 INITFILE

19.5.2.6 INITFUNC

19.5.2.7 HFIL E

19.5.2.8 CONFIG

19.5.2.9 ERRTYPE

19.5.2.10 CASE

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 #i nclude 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 E xit?

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

2 0.7 Returning Values to a Form

20.7.1 The IAP Constants

20.7.2 Using the SQLIEM Function
< dd>20.7.3 Using WHENEVER

20.8 An Example

20.9 Precompiling and Compiling a User Exit

20.10 Example Pro gram: 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 Connecti ng 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

< a href="pc_20exi.htm#sthref2706">20.14.2 EXEC TOOLS SET

~~20.14.3 EXEC TOOLS GET~~

20.14.4 E XEC 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< /dd>
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 F unctions

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
< dd>A.2.13 Collections

A.3 Miscellaneous Topics

A.3.1 Unicode Support

A.3.2 UTF16_CHARSET Opt ion

A.3.3 PREFETCH Option

A.3.4 External Procedures

A.3.5 Calling Java from PL/SQL

A.3.6 DML Return ing 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 Namespace s

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 Usin g 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 Resou rce 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 Pool ing

D Syntacti c 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 Op tion

E System- Specific References

E.1 System-Specific Inform ation

E.1.1 Location of Standard Header Files

E.1.2 Specifying Location of Included Files for the C Compil er

E.1.3 ANSI C Support

E.1.4 Struct Component Alignment

E.1.5 Size of an Integer and ROWID

E.1.6 B yte 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 Embedde d SQL Statements

F.2 About The Statement Descriptions< /dd>
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 Diagra ms

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 Emb edded SQL)

F.9 COLLECTION APPEND (Executable Embedded SQL Extension)

F.10 COLLECTION DESCRIBE (Executable Embedded S QL Extension)

F.11 COLLECTION GET (Executable Embedded SQL Extension)

F.12 COLLECTION RESET (Executable Embedded SQL Extension)

F.13 COLLECTION SET (Executable Embedded SQL E xtension)

F.14 COLLECTION TRIM (Executable Embedded SQL Ex tension)

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 DESCRIPT OR (Executable Embedded SQL)

F.31 ENABLE THREADS (Executab le Embedded SQL Extension)

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

F.33 EXECUTE (Executable Em bedded SQL)

F.34 EXECUTE DESCRIPTOR (Executable Embedded S QL)

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 AS SIGN (Executable Embedded SQL Extension)

F.43 LOB CLOSE (E xecutable Embedded SQL Extension)

F.44 LOB COPY (Executabl e Embedded SQL Extension)

F.45 LOB CREATE TEMPORARY (Execu table Embedded SQL Extension)

F.46 LOB DESCRIBE (Executabl e Embedded SQL Extension)

F.47 LOB DISABLE BUFFERING (Exec utable Embedded SQL Extension)

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

F.49 LOB ERASE (Executa ble Embedded SQL Extension)

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

F.51 LOB FILE SET (Executabl e Embedded SQL Extension)

F.52 LOB FLUSH BUFFER (Executabl e Embedded SQL Extension)

F.53 LOB FREE TEMPORARY (Executa ble Embedded SQL Extension)

F.54 LOB LOAD (Executable Embe dded SQL Extension)

F.55 LOB OPEN (Executable Embedded SQL Extension)

F.56 LOB READ (Executable Embedded SQL Extensi on)

F.57 LOB TRIM (Executable Embedded SQL Extension)< /dd>
F.58 LOB WRITE (Executable Embedded SQL Extension)
F.59 OBJECT CREATE (Executable Embedded SQL Extension)
< a href="pc_afemb.htm#sthref3212">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 CONNE CT (Executable Embedded SQL Extension)

F.71 ROLLBACK (Exec utable Embedded SQL)

F.72 SAVEPOINT (Executable Embedded S QL)

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 W HENEVER (Embedded SQL Directive)

Index

< font face="arial, helvetica, sans-serif">Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

What's New in Pro*C/C++?

1 Introduction

2 Precompiler Concepts

3 Database Concepts

5 Advanced Topics

6 Embedded SQL

7 Embedded PL/SQL

8 Host Arrays

9 Handling Runtime Errors

11 Multithreaded Applications

12 C++ Applications

13 Oracle Dynamic SQL

14 ANS I 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 Namespace s

C Performance Tuning

D Syntacti c and Semantic Checking

E System- Specific References

F Embedded SQL Statements and Directives

Index