|Pro*C/C++ Precompiler Programmer's Guide
Part Number A76942-01
This manual is a comprehensive user's guide and reference to the Pro*C/C++ Precompiler. It shows you how to use the database language SQL and Oracle's procedural extension, PL/SQL, in conjunction with Pro*C/C++ to manipulate data in an Oracle8i database. It explores a full range of topics, from underlying concepts to advanced programming techniques, and uses code examples.
Topics in this preface are:
This guide shows you how the Oracle Pro*C/C++ precompiler and embedded SQL can benefit your entire applications development process. It gives you the lessons in how to design and develop applications that harness the power of Oracle. And, as quickly as possible, it helps you become proficient in writing embedded SQL programs.
An important feature of this guide is its emphasis on getting the most out of Pro*C/C++ and embedded SQL. To help you master these tools, this guide shows you all the "tricks of the trade" including ways to improve program performance. It also includes many program examples to better your understanding and demonstrate the usefulness of embedded SQL and embedded PL/SQL.
Note: You will not find installation instructions or other system-specific information in this guide; refer to your system-specific Oracle documentation.
Anyone developing new applications or converting existing applications to run in the Oracle environment will benefit from reading this guide. Written especially for programmers, this comprehensive treatment of Pro*C/C++ will also be of value to systems analysts, project managers, and others interested in embedded SQL applications. To use this guide effectively, you need a working knowledge of applications programming in C or C++. It would be helpful to have some familiarity with the SQL database language, although this book will guide you through most of the complexities of embedded SQL programming.
Volume 1 presents the basics of Pro*C/C++ programming. For many Pro*C/C++ developers, these chapters are sufficient to enable them to write useful and powerful Pro*C/C++ applications.
Later chapters offer more detailed descriptions of Pro*C/C++. They describe some of the more complicated things that you can do with the product, such as writing Pro*C/C++ programs that use dynamic SQL, multithreading, user-defined objects, collections and Large Objects.
This chapter introduces you to Pro*C/C++. You look at its role in developing application programs that manipulate Oracle data. There is a Frequently Asked Questions section which is important.
This chapter explains how embedded SQL programs work. Programming guidelines follow. Then the sample tables that we work with are shown, along with a sample query Pro*C/C++ application
This chapter describes transaction processing. You learn the basic techniques that safeguard the consistency of your database and how to connect to the database server.
You learn about the Oracle datatypes, host variables, indicator variables, data conversion, Unicode character strings.
This chapter presents advanced topics, such as how to take advantage of datatype equivalencing, C preprocessor support, new names for SQLLIB functions, and interfacing to OCI.
This chapter teaches you the essentials of embedded SQL programming. You learn how to use host variables, indicator variables, cursors, cursor variables, and the fundamental SQL commands that insert, update, select, and delete Oracle data.
This chapter shows you how to improve performance by embedding PL/SQL transaction processing blocks in your program. You learn how to use PL/SQL with host variables, indicator variables, cursors, stored procedures, host arrays, and dynamic SQL.
This chapter looks at using arrays to improve program performance. You learn how to manipulate Oracle data using arrays, how to operate on all the elements of an array with a single SQL statement, and how to limit the number of array elements processed.
This chapter discusses error reporting and recovery. It shows you how to use the SQLSTATE and SQLCODE status variables with the WHENEVER statement to detect errors and status changes. It also shows you how to use the SQLCA and ORACA to detect error conditions and diagnose problems.
This chapter details the requirements for running the Oracle Pro*C/C++ Precompiler. You learn what happens during precompilation, how to issue the precompiler command, and how to specify the many useful precompiler options.
Writing multithreaded applications is discussed in this chapter. Your compiler must also support multithreading.
This chapter describes how to precompile your C++ application, and lists three sample C++ programs.
This chapter shows you how to take advantage of dynamic SQL. You are taught three methods--from simple to complex--for writing flexible programs that, among other things, let users build SQL statements interactively at run time.
The new ANSI dynamic SQL should be used for all new method 4 applications (where your program accepts or builds SQL statements with a varying number of variables). ANSI dynamic SQL must be used for applications involving complex types such as object types, collections, cursor variables, arrays of structs, and LOBs.
This chapter gives you an in-depth explanation of Dynamic SQL Method 4--dynamic SQL using descriptors. This will tell you how to modify existing applications that were developed before Oracle release 8.1.
This chapter presents large object datatypes (BLOBs, CLOBs, NCLOBs, and BFILEs). The embedded SQL commands that provide functionality comparable to OCI and PL/SQl are presented and used in sample code.
This chapter describes object support features: associative and navigational interfaces (embedded SQL commands), precompiler options for objects, and restrictions on the use of datatypes in Oracle dynamic SQL.
Collection types (VARRAYS and nested tables) are described in this chapter. Embedded SQL statements for using collections are presented, with examples.
This chapter discusses the Object Type Translator (OTT) which maps object types to C structures that are used in Pro*C/C++ applications. Then it describes the OTT options, how to use OTT, and the results.
This chapter focuses on writing user exits for Oracle Tools applications. You learn about the commands that are used to interface between a forms application and a Pro*C/C++ user exit, and how to write and link a forms user exit.
This appendix highlights the improvements and new features introduced with the 8.0 and 8.1 releases of Pro*C/C++, and where to find them in this guide.
This appendix points you to where to find reserved words and keywords that have a special meaning to Oracle and namespaces that are reserved for Oracle libraries.
This appendix shows you some simple, easy-to-apply methods for improving the performance of your applications.
This appendix shows you how to use the SQLCHECK option to control the type and extent of syntactic and semantic checking done on embedded SQL statements and PL/SQL blocks.
This appendix documents the aspects of Pro*C/C++ that can be system-specific.
This appendix contains descriptions (syntax diagrams, keyword and parameter definitions, etc.) of precompiler directives, embedded SQL statements, and Oracle's embedded SQL extensions.
Important terms being defined for the first time are italicized. In discussions, UPPERCASE is used for database objects and SQL keywords, and italicized lowercase is used for the names of C variables and SQL parameters. C keywords are in bold face type.
The following notation is used in this guide for BNF syntax:
Square brackets enclose optional items in syntax descriptions.
Angle brackets enclose the name of a syntactic element in syntax descriptions. Sometimes italics are used instead.
Braces enclose items, only one of which is required.
A vertical bar separates options within brackets or braces.
Two dots separate the lowest and highest values in a range.
. . .
An ellipsis shows that the preceding parameter can be repeated or that statements or clauses irrelevant to the discussion were left out of code examples.
The Pro*C/C++ Precompiler complies fully with the ANSI/ISO SQL standards. Compliance with these standards was certified by the National Institute of Standards and Technology (NIST). To flag extensions to ANSI/ISO SQL, a FIPS Flagger is provided.
ANSI standard X3.135-1992 (known informally as SQL92) provides three levels of compliance:
ANSI standard X3.168-1992 specifies the syntax and semantics for embedding SQL statements in application programs written in a standard programming language such as C.
A conforming SQL implementation must support at least Entry SQL. The Oracle Pro*C/C++ Precompiler conforms to Entry SQL92.
NIST standard FIPS PUB 127-1, which applies to RDBMS software acquired for federal use, also adopts the ANSI standards. In addition, it specifies minimum sizing parameters for database constructs and requires a "FIPS Flagger" to identify ANSI extensions.
For copies of the ANSI standards, write to:
SQL has become the standard language for relational database management systems. This section describes how the Pro*C/C++ Precompiler conforms to SQL standards established by the following organizations:
These organizations have adopted SQL as defined in the following publications:
For a copy of the ISO standard, write to the national standards office of any ISO participant. For a copy of the ISO standard, write to the national standards office of any ISO participant. For a copy of the NIST standard, write to:
The Pro*C/C++ Precompiler complies 100% with current ANSI/ISO standards.
The Pro*C/C++ Precompiler also complies 100% with the NIST standard. It provides a FIPS Flagger and an option named FIPS, which enables the FIPS Flagger. For more information, see "FIPS Flagger".
NIST tested the Pro*C/C++ Precompiler for ANSI SQL92 compliance using the SQL Test Suite, which consists of nearly 300 test programs. Specifically, the programs tested for conformance to the C embedded SQL standard. The result: the Oracle Pro*C/C++ Precompiler was certified 100% ANSI-compliant for Entry SQL92.
For more information about the tests, write to
According to FIPS PUB 127-1, "an implementation that provides additional facilities not specified by this standard shall also provide an option to flag nonconforming SQL language or conforming SQL language that may be processed in a nonconforming manner." To meet this requirement, Pro*C/C++ provides the FIPS Flagger, which flags ANSI extensions. An extension is any SQL element that violates ANSI format or syntax rules, except privilege enforcement rules. For a list of Oracle extensions to standard SQL, see the Oracle8i SQL Reference.
You can use the FIPS Flagger to identify
Thus, the FIPS Flagger helps you develop portable applications.
The FIPS precompiler option governs the FIPS Flagger. To enable the FIPS Flagger, specify FIPS=YES inline or on the command line. For more information about the FIPS option, see the section "Using the Precompiler Options".
There are several semantic changes in database operations between Oracle7 and Oracle8. For information on how this affects Pro*C/C++ applications, see Oracle8 Migration.
The Oracle Corporation technical staff values your comments. As we write and revise, your opinions are the most important input we receive. Please use the Reader's Comment Form in this manual to tell us what you like and dislike about this Oracle publication.