Pro*C is another one of those
tools or features from Oracle that keeps a low profile, quietly
waiting its turn for you to use it when the need arises. With a
little searching on various Oracle-related Web sites, you can read
about how others have used Pro*C to greatly reduce processing time
for some operation or procedure. One of C's features, being a
compiled language, is its processing speed. You may find yourself
in a situation where Oracle lacks the heavy duty processing speed
you need, but at the same time, find that your external
application (written in C or C++) lacks the data processing
capabilities of Oracle.
What is Pro*C? The Pro*C/C++
Precompiler Getting Started for Windows guide found in the Oracle
documentation library starts off with this very question:
The Pro*C/C++ precompiler enables
you to create applications that access your Oracle database
whenever rapid development and compatibility with other systems
are your priorities.
The Pro*C/C++ programming tool
enables you to embed Structured Query Language (SQL) statements in
a C or C++ program. The Pro*C/C++ precompiler translates these
statements into standard Oracle runtime library calls, then
generates a modified source program that you can compile, link,
and run in the usual way.
Another key reference book is the
Pro*C/C++ Precompiler Programmer's Guide, and it lists some reasons
why you want to use Pro*C. Some of the reasons are shown below:
The Oracle Pro*C/C++ Precompiler
lets you use the power and flexibility of SQL in your application
programs. A convenient, easy to use interface lets your
application access Oracle directly.
Unlike many application
development tools, Pro*C/C++ lets you create highly customized
applications. For example, you can create user interfaces that
incorporate the latest windowing and mouse technology. You can
also create applications that run in the background without the
need for user interaction.
Furthermore, Pro*C/C++ helps you
fine-tune your applications. It allows close monitoring of
resource use, SQL statement execution, and various runtime
indicators. With this information, you can change program
parameters for maximum performance.
Where is Pro*C and
How Do You Get It?
I will use the Windows platform as
an example throughout the remainder of this article. After a typical
installation of Oracle software, there will be a directory named
precomp under ORACLE_HOME. Within precomp, you will see several
other subdirectories. If you want to use the sample programs, you
will have to restart Oracle Universal Installer and perform a custom
installation. The place you are looking for is in the development
tools section.
Once you get to the Available
Product Components section, select Oracle Programmer (the option may
show Reinstall; the screen shot shows Installed because I have
already reinstalled this component).
Continue with the installation (it
takes a few minutes) and then exit the installer. Look in the
precomp directory, and under it, in the demo\proc directory. You
should see quite a few folders similar to those shown below.
The getting started for Windows
guide includes a description of each demo program. The demos
generally require the Scott/Tiger schema to be in place, so rebuild
that if you have to (running ORACLE_HOME\rdbms\admin\utlsampl.sql is
one way). Some of the demos require additional scripts to be run, so
check Table 3-1 as needed.
What Else Do You
Need?
The files you use with Pro*C are
"pc" files, and if you are familiar with C or C++, you are probably
looking for .c or .cpp files. They are there, kind of sort of. After
running the precompiler, a .c or .cpp file is created, depending on
what you asked for (we will be using .c). Once the .c file is
created, you can compile the file in a normal fashion to create the
executable. If on UNIX, the make command is used, and on Windows,
you can use, for example, Microsoft Visual C++ 6.0, and that answers
the "what else do you need" question. How do you get Visual C++ (or
Visual Studio)? Many C or C++ textbooks (the Deitel and Deitel
series for one) provide a limited or introductory version of Visual
C++, and for learning purposes, that is all you need for your Pro*C
adventure.
Configuring Your Pro*C
Environment
Configuration is actually a two-part
process. The first step is to configure your Pro*C environment, and
after that is complete, configure Visual C++ so that the generated
.c file will successfully compile, link, and run.
Oracle did a poor job of stating all
of the necessary steps to configure your environment. There are some
fairly obscure references to what is needed, and what is provided
(the project files in the demo area) more than likely needs
adjusting.
The .c files can be generated in one
of two ways: via the command line or by using a GUI tool Oracle
provides. To keep things simple, I will use the command line
interface (using the "proc" command) and the pcscfg.cfg
configuration file found in precomp\admin. With a properly
configured pcscfg.cfg file, all that will be needed to generate the
.c file is a simple "proc iname=input_file_name.pc" at the command
prompt.
Assuming you are using the
Scott/Tiger schema, your ORACLE_HOME is in C:\ora9i, and you have
Visual C++ in C:\Program Files, this is what you need in the
configuration file:
define=(WIN32_LEAN_AND_MEAN)
include=(C:\Program Files\Microsoft
Visual Studio\VC98\Include)
include=(c:\ora9i\precomp\PUBLIC)
include=(C:\ora9i\precomp\lib\msvc)
SQLCHECK=SEMANTICS
USERID=SCOTT/TIGER
Let's use the sample9 project (it
extracts data based on the department number you provide). Change
directory to sample9 in the demo\proc area. To create the sample9.c
file, at the command prompt, enter
You should have a sample9.c file
now. Earlier, I alluded to the fact that the .c file was "kind of
sort of" already present. In a text editor such as TextPad (yes, it
is a shameless plug), open the sample9.pc and sample9.c files and
note the huge difference between them.
If you are familiar with Visual
Studio and jumped ahead, you will have noticed that the sample9.c
file compiles okay, but fails to link. Here are the steps to enable
a successful compile-link-run process:
Go to Tools>Options and select the
Directories tab. Add the path to your ORACLE_HOME\bin directory
(C:\ora9i\bin in my case) under the Executable files menu option for
"Show directories for." Change the menu option to Include files and
enter ORACLE_HOME\precomp\public. Change the menu option to Library
files and enter ORACLE_HOME\precomp\lib\msvc (there should be a file
named oraSQL9.lib there).
Add the oraSQL9.lib file to the
Object/library modules under the Link tab in Project>Settings.
You are now ready to link and run.
What sample9.exe does is shown below.
Okay, the results aren't that
impressive, but if there were millions of rows, how well Pro*C, via
C, performs may make you sit up and pay attention to what the
utility can offer with respect to shaving significant amounts of
time off of a query.
With a little bit of C and a little
bit of Oracle, you now have at your disposal the means to work
outside of Oracle and create an application whose interface with
Oracle is transparent to a user. In the next part of this series, I
will demonstrate how you can build your own version of SQL*Loader.