Pro C/C++ Precompiler. Programmer's Guide

966 Pages·2001·5.594 MB·English

by  Melnick J., Smith T., Gregolre J.

Pro*C/C++ Precompiler
Programmer’s Guide
Release 9.0.1
June 2001
Part No. A89861-01
Pro*C/C++ Precompiler Programmer’s Guide, Release 9.0.1
Part No. A89861-01
Copyright © 1996, 2001, Oracle Corporation. All rights reserved.
Primary Authors: Jack Melnick, Tim Smith, Joan Gregoire
Contributing Authors: Ruth Baylis, Paul Lane, James W. Rawles
Contributors: Beethoven Cheng, Maura Joglekar, Shiao-Yen Lin, Ajay Popat
Graphic Designer: Valarie Moore
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
Theinformationcontainedinthisdocumentissubjecttochangewithoutnotice.Ifyoufindanyproblems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and SQL*Forms, SQL*Net, and SQL*Plus, Oracle Net, Oracle Call
Interface, Oracle7, Oracle7 Server, Oracle8, Oracle8 Server, Oracle8i, Oracle9i, Oracle Forms, PL/SQL,
Pro*C, and Pro*C/C++ are trademarks or registered trademarks of Oracle Corporation. Other names
may be trademarks of their respective owners.
Contents
Send Us Your Comments............................................................................................................... xxix
Preface........................................................................................................................................................ xxxi
1 Introduction
What is an Oracle Precompiler?....................................................................................................... 1-2
Why Use the Oracle Pro*C/C++ Precompiler................................................................................ 1-3
Why Use SQL....................................................................................................................................... 1-3
Why Use PL/SQL................................................................................................................................ 1-4
Pro*C/C++ Precompiler Benefits...................................................................................................... 1-4
Frequently Asked Questions............................................................................................................ 1-7
What is a VARCHAR?................................................................................................................. 1-7
Does Pro*C/C++ Generate Calls to the Oracle Call Interface?.............................................. 1-7
Why Not Code Using SQLLIB Calls and Not Use Pro*C/C++?........................................... 1-7
Can I Call A PL/SQL Stored Procedure From a Pro*C/C++ Program?.............................. 1-8
Can I Write C++ Code, and Precompile It Using Pro*C/C++? ............................................ 1-8
Can I Use Bind Variables Anywhere in a SQL Statement?.................................................... 1-8
I Am Confused By Character Handling in Pro*C/C++.......................................................... 1-8
What About Character Pointers? Is There Anything Special About Them?........................ 1-9
Why SPOOL Does Not Work in Pro*C/C++?.......................................................................... 1-9
Where Can I Find The On-line Versions of the Example Programs?.................................... 1-9
How Can I Compile and Link My Application?...................................................................... 1-9
Does Pro*C/C++ Now Support Using Structures As Host Variables?.............................. 1-10
Is It Possible to Have Recursive Functions In Pro*C/C++ If I Use Embedded SQL In the
Function? 1-10
iii
Can I Use Any Release of Pro*C/C++ with Any Version of the Oracle Server?............... 1-10
When My Application Runs Under Oracle9i, I Keep Getting an Ora-1405 Error (Fetched
Column Value Is NULL). 1-10
Are All SQLLIB Functions Private?......................................................................................... 1-11
How Does Oracle9i Support The New Object Types?.......................................................... 1-12
2 Precompiler Concepts
Key Concepts of Embedded SQL Programming ......................................................................... 2-2
Embedded SQL Statements ........................................................................................................ 2-2
Embedded SQL Syntax................................................................................................................ 2-4
Static Versus Dynamic SQL Statements.................................................................................... 2-5
Embedded PL/SQL Blocks ........................................................................................................ 2-5
Host and Indicator Variables...................................................................................................... 2-5
Oracle Datatypes........................................................................................................................... 2-6
Arrays............................................................................................................................................. 2-7
Datatype Equivalencing............................................................................................................... 2-7
Private SQL Areas, Cursors, and Active Sets........................................................................... 2-7
Transactions................................................................................................................................... 2-7
Errors and Warnings.................................................................................................................... 2-8
Steps in Developing an Embedded SQL Application................................................................. 2-8
Guidelines for Programming.......................................................................................................... 2-10
Comments ................................................................................................................................... 2-10
Constants...................................................................................................................................... 2-10
Declare Section............................................................................................................................ 2-10
Delimiters..................................................................................................................................... 2-11
File Length................................................................................................................................... 2-12
Function Prototyping................................................................................................................. 2-12
Host Variable Names................................................................................................................. 2-13
Line Continuation....................................................................................................................... 2-13
Line Length.................................................................................................................................. 2-13
MAXLITERAL Default Value................................................................................................... 2-14
Operators..................................................................................................................................... 2-14
Statement Terminator................................................................................................................. 2-15
Conditional Precompilation............................................................................................................ 2-15
Symbol Definition....................................................................................................................... 2-15
iv
Example SELECT Statement..................................................................................................... 2-16
Precompile Separately..................................................................................................................... 2-16
Guidelines.................................................................................................................................... 2-16
Compile and Link............................................................................................................................. 2-17
Example Tables.................................................................................................................................. 2-18
Example Data.............................................................................................................................. 2-18
Example Program: A Simple Query.............................................................................................. 2-19
3 Database Concepts
Connect to the Database.................................................................................................................... 3-2
Using the ALTER AUTHORIZATION Clause to Change Passwords................................. 3-3
Connecting Using Oracle Net .................................................................................................... 3-4
Automatic Connects..................................................................................................................... 3-4
Advanced Connection Options........................................................................................................ 3-6
Some Preliminaries....................................................................................................................... 3-6
Concurrent Logons....................................................................................................................... 3-6
Default Databases and Connections.......................................................................................... 3-7
Explicit Connections..................................................................................................................... 3-8
Implicit Connections.................................................................................................................. 3-14
Definitions of Transactions Terms................................................................................................. 3-15
How Transactions Guard Your Database..................................................................................... 3-16
How to Begin and End Transactions............................................................................................. 3-17
Using the COMMIT Statement...................................................................................................... 3-18
Using the SAVEPOINT Statement................................................................................................ 3-19
The ROLLBACK Statement............................................................................................................ 3-20
Statement-Level Rollbacks........................................................................................................ 3-22
The RELEASE Option...................................................................................................................... 3-23
The SET TRANSACTION Statement........................................................................................... 3-23
Override Default Locking............................................................................................................... 3-24
Using FOR UPDATE OF............................................................................................................ 3-24
Using LOCK TABLE.................................................................................................................. 3-25
Fetch Across COMMITs................................................................................................................... 3-26
Distributed Transactions Handling............................................................................................... 3-26
Guidelines.......................................................................................................................................... 3-27
Designing Applications............................................................................................................. 3-28
v
Obtaining Locks.......................................................................................................................... 3-28
Using PL/SQL............................................................................................................................. 3-28
4 Datatypes and Host Variables
Oracle Datatypes................................................................................................................................. 4-2
Internal Datatypes........................................................................................................................ 4-2
External Datatypes........................................................................................................................ 4-3
Additional External Datatypes................................................................................................. 4-12
Host Variables.................................................................................................................................... 4-14
Host Variable Declaration......................................................................................................... 4-15
Host Variable Referencing......................................................................................................... 4-18
Indicator Variables ......................................................................................................................... 4-19
The INDICATOR Keyword...................................................................................................... 4-19
Example of INDICATOR Variable Usage............................................................................... 4-20
INDICATOR Variable Guidelines............................................................................................ 4-21
Oracle Restrictions...................................................................................................................... 4-21
VARCHAR Variables........................................................................................................................ 4-21
VARCHAR Variable Declaration............................................................................................. 4-22
VARCHAR Variable Referencing............................................................................................. 4-23
Return NULLs to a VARCHAR Variable................................................................................ 4-24
Insert NULLs Using VARCHAR Variables............................................................................ 4-24
Pass VARCHAR Variables to a Function................................................................................ 4-24
Find the Length of the VARCHAR Array Component......................................................... 4-25
Example Program: Using sqlvcp()............................................................................................ 4-26
Cursor Variables ............................................................................................................................... 4-29
Declare a Cursor Variable.......................................................................................................... 4-30
Allocate a Cursor Variable........................................................................................................ 4-31
Open a Cursor Variable............................................................................................................. 4-31
Closing and Freeing a Cursor Variable................................................................................... 4-34
Cursor Variables with the OCI (Release 7 Only).................................................................... 4-34
Restrictions................................................................................................................................... 4-36
Example: cv_demo.sql and sample11.pc................................................................................. 4-37
CONTEXT Variables........................................................................................................................ 4-40
Universal ROWIDs........................................................................................................................... 4-42
SQLRowidGet()........................................................................................................................... 4-44
vi
Host Structures ................................................................................................................................. 4-44
Host Structures and Arrays....................................................................................................... 4-45
PL/SQL Records......................................................................................................................... 4-46
Nested Structures and Unions.................................................................................................. 4-46
Host Indicator Structures.......................................................................................................... 4-47
Example Program: Cursor and a Host Structure................................................................... 4-48
Pointer Variables............................................................................................................................... 4-51
Pointer Variable Declaration..................................................................................................... 4-51
Pointer Variable Referencing.................................................................................................... 4-51
Structure Pointers ...................................................................................................................... 4-52
Globalization Support..................................................................................................................... 4-53
NCHAR Variables............................................................................................................................. 4-55
CHARACTER SET [IS] NCHAR_CS....................................................................................... 4-55
Environment Variable NLS_NCHAR...................................................................................... 4-56
CONVBUFSZ Clause in VAR................................................................................................... 4-56
Character Strings in Embedded SQL ...................................................................................... 4-57
Strings Restrictions..................................................................................................................... 4-57
Indicator Variables .................................................................................................................... 4-57
5 Advanced Topics
Character Data..................................................................................................................................... 5-2
Precompiler Option CHAR_MAP.............................................................................................. 5-2
Inline Usage of the CHAR_MAP Option.................................................................................. 5-3
Effect of the DBMS and CHAR_MAP Options........................................................................ 5-3
VARCHAR Variables and Pointers............................................................................................ 5-8
Unicode Variables......................................................................................................................... 5-9
Datatype Conversion....................................................................................................................... 5-12
Datatype Equivalencing ................................................................................................................. 5-12
Host Variable Equivalencing.................................................................................................... 5-13
User-Defined Type Equivalencing........................................................................................... 5-14
CHARF External Datatype........................................................................................................ 5-15
The EXEC SQL VAR and TYPE Directives............................................................................. 5-15
Example: Datatype Equivalencing (sample4.pc):.................................................................. 5-16
The C Preprocessor........................................................................................................................... 5-29
How the Pro*C/C++ Preprocessor Works............................................................................. 5-29
vii
Preprocessor Directives.............................................................................................................. 5-30
ORA_PROC Macro..................................................................................................................... 5-31
Location of Header File Specification...................................................................................... 5-32
Some Preprocessor Examples................................................................................................... 5-32
SQL Statements Not Allowed in #include.............................................................................. 5-34
Include the SQLCA, ORACA, and SQLDA............................................................................ 5-34
EXEC SQL INCLUDE and #include Summary...................................................................... 5-36
Defined Macros........................................................................................................................... 5-36
Include Files................................................................................................................................. 5-36
Precompiled Header Files............................................................................................................... 5-37
Precompiled Header File Creation........................................................................................... 5-37
Use of the Precompiled Header Files....................................................................................... 5-38
Examples...................................................................................................................................... 5-39
Effects of Options........................................................................................................................ 5-41
Usage Notes................................................................................................................................. 5-43
The Oracle Preprocessor.................................................................................................................. 5-44
Symbol Definition....................................................................................................................... 5-44
An Oracle Preprocessor Example............................................................................................. 5-45
Evaluation of Numeric Constants.................................................................................................. 5-45
Numeric Constants in Pro*C/C++........................................................................................... 5-46
Numeric Constant Rules and Examples.................................................................................. 5-47
SQLLIB Extensions for OCI Release 8 Interoperability............................................................ 5-47
Runtime Context in the OCI Release 8 Environment............................................................ 5-47
Parameters in the OCI Release 8 Environment Handle........................................................ 5-48
Interface to OCI Release 8............................................................................................................... 5-48
SQLEnvGet()................................................................................................................................ 5-49
SQLSvcCtxGet().......................................................................................................................... 5-50
Embedded OCI Release 8 Calls................................................................................................ 5-50
Embedded OCI Release 7 Calls...................................................................................................... 5-52
Set Up the LDA........................................................................................................................... 5-53
Remote and Multiple Connections........................................................................................... 5-53
New Names for SQLLIB Public Functions.................................................................................. 5-54
X/Open Application Development................................................................................................ 5-56
Oracle-Specific Issues................................................................................................................. 5-58
viii
6 Embedded SQL
Host Variables...................................................................................................................................... 6-2
Output versus Input Host Variables.......................................................................................... 6-2
Indicator Variables.............................................................................................................................. 6-3
Insert NULLs................................................................................................................................. 6-4
Returned NULLs........................................................................................................................... 6-5
Fetch NULLs.................................................................................................................................. 6-5
Test for NULLs.............................................................................................................................. 6-6
Truncated Values.......................................................................................................................... 6-6
The Basic SQL Statements................................................................................................................ 6-6
The SELECT Statement................................................................................................................ 6-8
The INSERT Statement................................................................................................................ 6-9
The UPDATE Statement............................................................................................................ 6-10
The DELETE Statement............................................................................................................. 6-11
The WHERE Clause................................................................................................................... 6-11
The DML Returning Clause........................................................................................................... 6-12
Cursors................................................................................................................................................ 6-12
The DECLARE CURSOR Statement........................................................................................ 6-13
The OPEN Statement................................................................................................................. 6-14
The FETCH Statement............................................................................................................... 6-15
The CLOSE Statement................................................................................................................ 6-16
The CLOSE_ON_COMMIT Precompiler Option.................................................................. 6-16
The PREFETCH Precompiler Option...................................................................................... 6-17
Optimizer Hints................................................................................................................................ 6-19
Issuing Hints .............................................................................................................................. 6-19
The CURRENT OF Clause.............................................................................................................. 6-19
Restrictions.................................................................................................................................. 6-20
The Cursor Statements..................................................................................................................... 6-21
A Complete Example........................................................................................................................ 6-21
Positioned Update............................................................................................................................ 6-23
7 Embedded PL/SQL
Advantages of PL/SQL....................................................................................................................... 7-2
Better Performance....................................................................................................................... 7-2
Integration with Oracle................................................................................................................ 7-2
ix
Cursor FOR Loops........................................................................................................................ 7-2
Procedures and Functions........................................................................................................... 7-3
Packages......................................................................................................................................... 7-4
PL/SQL Tables.............................................................................................................................. 7-4
User-Defined Records.................................................................................................................. 7-5
Embedded PL/SQL Blocks................................................................................................................ 7-6
Host Variables...................................................................................................................................... 7-7
Example: Using Host Variables with PL/SQL......................................................................... 7-7
Complex Example......................................................................................................................... 7-9
VARCHAR Pseudotype............................................................................................................. 7-11
Restriction.................................................................................................................................... 7-12
Indicator Variables............................................................................................................................ 7-12
NULLs Handling........................................................................................................................ 7-13
Truncated Values........................................................................................................................ 7-14
Host Arrays......................................................................................................................................... 7-14
ARRAYLEN Statement.............................................................................................................. 7-17
Optional Keyword EXECUTE................................................................................................... 7-19
Cursor Usage in Embedded PL/SQL............................................................................................. 7-20
Stored PL/SQL and Java Subprograms......................................................................................... 7-21
Creating Stored Subprograms.................................................................................................. 7-22
Calling a Stored PL/SQL or Java Subprogram...................................................................... 7-24
Getting Information about Stored Subprograms................................................................... 7-30
External Procedures.......................................................................................................................... 7-30
Restrictions on External Procedures........................................................................................ 7-31
Creating the External Procedure.............................................................................................. 7-32
SQLExtProcError()...................................................................................................................... 7-33
Using Dynamic SQL......................................................................................................................... 7-34
8 Host Arrays
Why Use Arrays?................................................................................................................................. 8-2
Declaring Host Arrays........................................................................................................................ 8-2
Restrictions..................................................................................................................................... 8-2
Maximum Size of Arrays............................................................................................................. 8-2
Using Arrays in SQL Statements..................................................................................................... 8-3
Referencing Host Arrays............................................................................................................. 8-3
x