1031-1035 | Oracle Development Unleashed (3rd Edition)

Previous Table of Contents Next

Page 1031

CHAPTER 45

Programmer/2000

IN THIS CHAPTER

Oracle Precompilers 1032
Why Precompile Source Code? 1032
How to Precompile a Program 1035
Embedded SQL Host Programs 1037
Error Handling 1047
Dynamic SQL 1052
User Exits 1057
Performance Tuning 1059

Page 1032

Oracle Programmer/2000 is a suite of programmatic interfaces that allows you to manipulate Oracle7 databases. Some of the products are topics of other chapters in this book ”Oracle Objects for OLE, Chapters 40 through 42, and Open Database Connectivity (ODBC) in Chapter 57. All products in this suite have been separate products in the past and continue to be available separately on server platforms (non-Microsoft Windows and Macintosh) such as UNIX, AIX, and VMS. In addition to Objects for OLE and ODBC the other components of Programmer/2000 are Oracle Call Interface, SQL*Module, and, of course, the precompilers. This chapter focuses on Oracle precompilers. The Oracle Call Interface and SQL*Module products are described at the end of the chapter.

Oracle Precompilers

Extracting and manipulating data from a relational database is very difficult if not for tools such as precompilers. The precompiler allows SQL statements to be embedded within procedural languages. Applications developed with this tool can use the best features of SQL combined with the best features of the procedural language, creating a powerful module. This chapter discusses Oracle precompilers, creating a source program with embedded SQL, new features in version 1.8 of the Oracle precompilers, and some of other products in the Programmer/2000 suite.

The precompiler is a mechanism that allows SQL statements to be embedded within procedural languages. This tool provides the programmer with unlimited capabilities. To fully explain all the benefits of this tool, this chapter focuses on the features of the precompiler, what languages source code can be, how standardized the precompilers are, what options they have, how to precompile source code, and conditional precompiling.

Why Precompile Source Code?

Embedded SQL statements are not understood by high-level languages. The precompiler must therefore take the SQL statements and translate them into something the high-level language understands. Libraries contain data structures that help guide the translation process for consistency. Remembering this general concept will help you understand the precompiler features.

General Precompiler Features

One of the primary reasons you use a precompiler is gaining the capability to use SQL statements in a high-level language. Oracle precompilers offer many benefits and features that can help in this development. These features include the following:

You can write source code in eight different high-level languages, which all have ANSI/ISO precompilers.
Using a precompiler enables a user to take advantage of dynamic SQL, which allows for a more highly customized application.

Page 1033

Precompilers enable a user to process PL/SQL transaction blocks and use host arrays to process large amounts of data quickly; both of these dramatically improve performance.
Precompilers make conversions between Oracle internal data types and language data types and check syntax and semantics.
Precompilers can also include conditional parameters to enable the program to be executed in different environments.
You can make direct interfaces with SQL*Forms using user exits.
Precompilers provide variables to handle warning and error conditions, which are included in the Oracle Communication Area (ORACA).

Each of these features is discussed in further detail later in the chapter.

Precompiler Languages

Oracle has precompilers for eight procedural languages:

Ada
C and C++
COBOL
FORTRAN
Pascal
PL/1
REXX

With Oracle precompilers, you can mix object modules produced in several host languages and link them together into one executable program.

ANSI/IO Standards

Because SQL has become the standard language for relational databases, standardization is an important issue. Oracle precompilers take exceptional efforts to meet standards set by the following organizations:

American National Standards Institute (ANSI)
International Standards Organization (ISO)
U.S. National Institute of Standards and Technology (NIST)

Compliance for these requirements is measured by NIST, which uses over 300 test programs. Oracle precompilers conform to these standards by checking the SQL statements that are embedded for standard syntax and semantics. However, there is much more to precompiler function than syntax checking.

Page 1034

Precompiler Options

To increase flexibility among applications, Oracle precompilers have options that the programmer can manipulate to allow for certain conditions. For example, if a line of the source program exceeds 80 characters, the default, an error occurs. This can be extended to 132 characters or other value. Table 45.1 outlines many of these controls. The * character next to an item indicates that it can be entered inline, meaning it can be changed multiple times in a program.

Table 45.1. Precompiler options.

Syntax	Default	Specifies
COMMON_NAME=block_name		Name of FORTRAN common blocks.
DEFINE=symbol		Symbol used in condi- tional precompilation.
ERRORS=YESNo*	YES	Whether errors are sent to the terminal.
FORMAT=ANSITERMINAL	ANSI	Format of COBOL or FORTRAN input line.
HOLD_CURSOR=YESNO*	NO	How a cursor cache handles SQL statements.
HOST=C, COBOL, COB74, ADA, etc.		Host language.
INAME= path and filename		Name of input file.
INCLUDE=path*		Directory path for the included files.
IRECLEN=integer	80	Record length of input file.
LNAME=path and filename		Name of listing file.
LRECLEN=integer	132	Record length of listing file.
LTYPE=LONGSHORTNONE	LONG	Type of listing.
MAXLITERAL=integer*		Maximum length of string.
MAXOPENCURSORS=integer*	10	Maximum number of cursors cached.
MODE=ORACLEANSIANSI14ISOISO13	ORACLE	Compliance with ANSI/ISO standard.
ONAME=path and filename		Name of output file.
ORACA=YESNO	NO	Whether the ORACA is used.

Page 1035

Syntax	Default	Specifies
ORECLEN=integer	80	Record length of output file.
RELEASE_CURSOR=YESNO*	NO	How cursor cache handles SQL statements.
SELECT_ERROR=YESNO*	YES	How SELECT errors are handled.
SQLCHECK=SEMANTICSFULLSYNTAXLIMITEDNONE*	SYNTAX	Extent of syntax and/or semantic checking.
USERID=username/password		Valid Oracle username and password.
XREF=YESNO*	YES	Cross-reference section in listing.

A precompiler command line might look something like the following:

 proc iname=example.pc include=ora_pcc: include=clib: ireclen=132

The only required argument is iname. This argument tells the precompiler the source filename.

Follow these general guidelines when setting options for precompiling:

When you precompile the program module that connects to Oracle, specify a value for MAXOPENCURSORS that is high enough for any of the program modules that are linked together.
If you want to use just one SQLCA, you must declare it as a global in one of the program modules and as an external in the other modules. In C, for example, this is done by using the external storage class, which tells the precompiler to look for the SQLCA in another program module. Unless you declare the SQLCA as external, each program module uses its own local SQLCA.
You cannot declare a cursor in one file and reference it in another.

You can use all these options in several ways when precompiling your program, but what is the best way to precompile, compile, and link your source program?

How to Precompile a Program

In version 1.8 of the precompilers, executables exist for each of the languages previously mentioned. Each host language has a different command to run the precompiler, with the exception of C++, which uses PROC with the CODE=CPP option.

Previous Table of Contents Next