Skip Headers

Pro*C/C++ Programmer's Guide
Release 9.2

Part Number A97269-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to next page
Next
View PDF

Contents

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience
Documentation Accessibility
Structure
Related Documents
Conventions

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.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.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.2 NUMBER
4.1.2.3 INTEGER
4.1.2.4 FLOAT
4.1.2.5 STRING
4.1.2.6 VARNUM
4.1.2.7 LONG
4.1.2.8 VARCHAR
4.1.2.9 ROWID
4.1.2.10 DATE
4.1.2.11 RAW
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.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 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 Examples
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 Cursors
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.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
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.5 Packages
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.2 DO
9.8.2.3 DO BREAK
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 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 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
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_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.42 USERID
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.1 PREPARE
13.9.2 DECLARE
13.9.3 OPEN
13.9.4 FETCH
13.9.5 CLOSE
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.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 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.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 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.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
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.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
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.2 Varrays
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.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 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.4 CODE
19.5.2.5 INITFILE
19.5.2.6 INITFUNC
19.5.2.7 HFILE
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 #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