The Oracle Pro*C Precompiler is a popular choice for developing Oracle applications in the C/C++ languages. The primary advantage of using the precompiler is that it allows you to embed SQL (and PL/SQL) directly into your application. Used in combination with Oracle Instant Client packages you can easily begin to develop applications using the Pro*C Precompiler capabilities. For information on getting started using Pro*C on Windows, please see my previous post entitled "Oracle Pro*C on Windows with Express Edition Products".
Many of the components used in the previous post are also used here and the steps are similar (if not the same). For this post, all components are installed on a single host (oel02) running Oracle Enterprise Linux. The components used are (valid at time of posting):
- Oracle Database 10g Express Edition (available here)
- Oracle Enterprise Linux (available here)
- Oracle Instant Client 11.2.0.1 Packages for Linux x86 (available here)
- Instant Client Package - Basic Lite
- Instant Client Package - SDK
- Instant Client Package - Precompiler
- Instant Client Package - SQL*Plus
NOTE: The precompiler uses the "standard" Oracle Technology Network license rather than the Instant Client license. Be sure to review the license!
You can, of course, allow for some variation in the above; however, you may then need to make adjustments to the steps that follow. For example, the database can be on another host and/or platform. In order to keep things as simple as possible I have elected to use a single host for everything. In order for the sample code to work unaltered you should have access to the HR sample schema user (included in Express Edition). If you require character set or language support not provided by the "Basic Lite" Instant Client package you should use the "Basic" package. In addition, version 11.1.0.7 of the components should work as well (though I have not tested it).
Installing the Instant Client Packages
I downloaded the following Instant Client packages using the above provided link to my home directory on oel02:
- instantclient-basiclite-linux32-11.2.0.1.zip
- instantclient-precomp-linux32-11.2.0.1.zip
- instantclient-sdk-linux32-11.2.0.1.zip
- instantclient-sqlplus-linux32-11.2.0.1.zip
Installing each one is simply a matter of unzipping each file:
[markwill@oel02 ~]$ unzip instantclient-basiclite-linux32-11.2.0.1.zip
[markwill@oel02 ~]$ unzip instantclient-precomp-linux32-11.2.0.1.zip
[markwill@oel02 ~]$ unzip instantclient-sdk-linux32-11.2.0.1.zip
[markwill@oel02 ~]$ unzip instantclient-sqlplus-linux32-11.2.0.1.zip
The act of unzipping the files will create an "instantclient_11_2" directory in the directory where the files are unzipped (/home/markwill in my case). The complete list of files installed after unzipping each file is as follows:
[markwill@oel02 ~]$ cd instantclient_11_2/
[markwill@oel02 instantclient_11_2]$ pwd
/home/markwill/instantclient_11_2
[markwill@oel02 instantclient_11_2]$ find .
.
./libsqlplusic.so
./libclntsh.so.11.1
./cobsqlintf.o
./ojdbc6.jar
./sqlplus
./adrci
./libnnz11.so
./SQLPLUS_README
./PRECOMP_README
./libociicus.so
./ojdbc5.jar
./sdk
./sdk/demo
./sdk/demo/demo_procob_ic.mk
./sdk/demo/occiobj.cpp
./sdk/demo/occidemo.sql
./sdk/demo/occiobj.typ
./sdk/demo/occidemod.sql
./sdk/demo/demo_proc_ic.mk
./sdk/demo/demo.mk
./sdk/demo/occidml.cpp
./sdk/demo/procdemo.pc
./sdk/demo/procobdemo.pco
./sdk/demo/cdemo81.c
./sdk/procob
./sdk/rtsora
./sdk/SDK_README
./sdk/ott
./sdk/ottclasses.zip
./sdk/include
./sdk/include/orid.h
./sdk/include/occiAQ.h
./sdk/include/oratypes.h
./sdk/include/nzerror.h
./sdk/include/sqlkpr.h
./sdk/include/oci1.h
./sdk/include/sql2oci.h
./sdk/include/ocikpr.h
./sdk/include/ocidef.h
./sdk/include/oci.h
./sdk/include/ldap.h
./sdk/include/ort.h
./sdk/include/ocixmldb.h
./sdk/include/ocidfn.h
./sdk/include/ocixstream.h
./sdk/include/oraca.h
./sdk/include/sqlcpr.h
./sdk/include/odci.h
./sdk/include/ori.h
./sdk/include/occiCommon.h
./sdk/include/occi.h
./sdk/include/ociapr.h
./sdk/include/sqlca.h
./sdk/include/occiObjects.h
./sdk/include/sqlucs2.h
./sdk/include/occiControl.h
./sdk/include/sqlda.h
./sdk/include/xa.h
./sdk/include/ocidem.h
./sdk/include/oci8dp.h
./sdk/include/ociextp.h
./sdk/include/occiData.h
./sdk/include/ociap.h
./sdk/include/nzt.h
./sdk/include/orl.h
./sdk/include/oro.h
./sdk/include/sqlapr.h
./sdk/proc
./libocci.so.11.1
./libocijdbc11.so
./genezi
./BASIC_LITE_README
./xstreams.jar
./libsqlplus.so
./glogin.sql
./precomp
./precomp/admin
./precomp/admin/pcscfg.cfg
./precomp/admin/pcbcfg.cfg
[markwill@oel02 instantclient_11_2]$
Configure the Environment
One of the nice things about using Pro*C on Linux (and UNIX) is that we do not normally have to configure the Pro*C configuration file (pcscfg.cfg) as would generally be necessary in Windows. As a convenience for setting environment variables (such as the PATH), in my home directory I have created a file I use to do this (I'm using the bash shell here):
[markwill@oel02 ~]$ cat oic11.env
export ORACLE_BASE=/home/markwill
export ORACLE_HOME=$ORACLE_BASE/instantclient_11_2
export LD_LIBRARY_PATH=$ORACLE_HOME
export PATH=$ORACLE_HOME:$ORACLE_HOME/sdk:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/X11R6/bin:~/bin
[markwill@oel02 ~]$
I can then set my environment to use the newly installed Oracle components as follows:
[markwill@oel02 ~]$ . ./oic11.env
Test the Oracle Installation
Since SQL*Plus was installed as part of the above packages, it can be used to quickly and easily test the installation. Here I connect to the Express Edition database as the HR sample schema user using the EZConnect syntax (host:port/Service_Name):
[markwill@oel02 ~]$ sqlplus hr/hr@oel02:1521/XE
SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 26 13:27:59 2009
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select user from dual;
USER
------------------------------
HR
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
[markwill@oel02 ~]$
Create a Test Application
Now that I have tested that the Oracle software is working correctly, I create a simple test application. I call the test application "proctest" and create a new directory to hold the files:
[markwill@oel02 ~]$ mkdir -p Projects/proc/proctest
[markwill@oel02 ~]$ cd Projects/proc/proctest
Here's the content of the proctest.pc source file:
[markwill@oel02 proctest]$ cat proctest.pc
/*
** standard include files for Pro*C application
*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>
#define NAME_LEN 30
#define ERR_LEN 512
VARCHAR username[NAME_LEN];
VARCHAR password[NAME_LEN];
VARCHAR database[NAME_LEN];
/*
** host variables to hold results of query
*/
int dept;
VARCHAR fname[NAME_LEN];
VARCHAR lname[NAME_LEN];
int sal;
int rn;
/*
** indicator variables used to determine null-ness
*/
short dept_ind;
short fname_ind;
short lname_ind;
short sal_ind;
short rn_ind;
/*
** standard Pro*C error handler function
*/
void sql_error(char *msg)
{
char err_msg[ERR_LEN];
size_t buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("\n");
if (msg)
{
printf("%s\n", msg);
}
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s", msg_len, err_msg);
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}
int main(int argc, char *argv[])
{
/*
** setup username, password and database (ezconnect format)
*/
strncpy((char *) username.arr, "hr", NAME_LEN);
username.len = (unsigned short) strlen((char *) username.arr);
strncpy((char *) password.arr, "hr", NAME_LEN);
password.len = (unsigned short) strlen((char *) password.arr);
strncpy((char *) database.arr, "oel02:1521/XE", NAME_LEN);
database.len = (unsigned short) strlen((char *) database.arr);
/*
** register the error handler function
*/
EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error: \n");
/*
** attempt to connect to the database
*/
EXEC SQL CONNECT :username IDENTIFIED BY :password USING :database;
/*
** indicate we want to break out of the loop when we get a no data
** found message (i.e. at end of fetch)
*/
EXEC SQL WHENEVER NOT FOUND DO break;
/*
** declare and open cursor using analytic function
** to get "top 3" in department by highest salary
*/
EXEC SQL DECLARE emp_cursor CURSOR FOR
select *
from
(
select department_id,
first_name,
last_name,
salary,
row_number() over (partition by department_id
order by salary desc) rn
from employees
where department_id is not null
)
where rn <= 3;
EXEC SQL OPEN emp_cursor;
/*
** loop through cursor fetching results
** and printing as we go
*/
for (;;)
{
EXEC SQL FETCH emp_cursor
INTO :dept:dept_ind,
:fname:fname_ind,
:lname:lname_ind,
:sal:sal_ind,
:rn:rn_ind;
fname.arr[fname.len] = '\0';
lname.arr[lname.len] = '\0';
printf("%d, %s %s, %d, %d\n", dept, fname.arr, lname.arr, sal, rn);
}
/*
** clean-up and exit
*/
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT RELEASE;
return EXIT_SUCCESS;
}
[markwill@oel02 proctest]$
The sample application is pretty bland - the only real point of interest is the use of an analytic function to get the "top 3" ranking of salaries by department. The use of the analytic function will allow us to see how options can be passed to the Pro*C precompiler.
Building the Sample
Rather than craft a custom Makefile or enter the various build/compiler commands by hand, the demonstration Makefile that is provided as part of the precompiler package installed earlier can be used with only small modification. To use this Makefile, copy it into the directory with the source file. On my system this was done as follows:
[markwill@oel02 proctest]$ cp $ORACLE_HOME/sdk/demo/demo_proc_ic.mk .
I use VIM to edit the file. Of course you can use whatever editor you wish.
[markwill@oel02 proctest]$ vi demo_proc_ic.mk
The important part is listed in the "NOTES" section in the Makefile:
# NOTES:
# 1. Please change "cc/CC" and the "InstantClient directories" to point to
# appropiate locations on your machine before using this makefile.
Because the CC and cc entries are already correct, I did not alter them. I did, however, change the Instant Client directory entries as follows:
# InstantClient Directories.
ICSDKHOME=$(ORACLE_HOME)/sdk/
ICLIBHOME=$(ORACLE_HOME)/
By using the ORACLE_HOME environment variable (which is set in my oic11.env file) in the Makefile I do not need to hard-code the actual path.
Before building the sample, take a minute or two to review the Makefile comments. I build the sample using the following command-line:
[markwill@oel02 proctest]$ make -f demo_proc_ic.mk build PROCFLAGS="common_parser=yes" \
> EXE=proctest OBJS="proctest.o"
Notice how PROCFLAGS is used to pass the "common_parser=yes" to the proc binary (i.e the Pro*C program itself). The EXE option determines the name of the binary executable produced and the OBJS option determines what object files are needed. In this case the options are simple, but larger and more complex projects likely use more than a single object file and possibly other Pro*C options.
Once the build has completed test the application:
[markwill@oel02 proctest]$ ./proctest
10, Jennifer Whalen, 4400, 1
20, Michael Hartstein, 13000, 1
20, Pat Fay, 6000, 2
[ snip ]
100, Nancy Greenberg, 12000, 1
100, Daniel Faviet, 9000, 2
100, John Chen, 8200, 3
110, Shelley Higgins, 12000, 1
110, William Gietz, 8300, 2
[markwill@oel02 proctest]$
One final addition I make to the Makefile is creating a new target called "dust". A Makefile traditionally has a "clean" target which can be used to remove various (non-source!) files. These files are generally used during the build process, but are not needed by the final executable. The "clean" target also removes the executable, however. Since I like the idea of being able to clean the no longer necessary files, but want to keep the executable, I add a target called "dust". Here's the "end" of the Makefile with the "dust" target added:
# Clean up all executables, *.o and generated *.c files
clean: $(CLNCACHE)
$(REMOVE) $(PROCDEMO) $(PROCDEMO).o $(PROCDEMO).c $(PROCDEMO).lis
dust: $(CLNCACHE)
$(REMOVE) $(PROCDEMO).o $(PROCDEMO).c $(PROCDEMO).lis
cleancache:
$(REMOVE) $(CACHEDIR)
$(REMOVE) $(ICLIBHOME)libclntsh$(SO_EXT)
Of course, adding this target is purely optional; however, after building the sample, I then "dust" the directory:
[markwill@oel02 proctest]$ make -f demo_proc_ic.mk dust PROCDEMO=proctest
rm -rf SunWS_cachea
rm -rf /home/markwill/instantclient_11_2/libclntsh.so
rm -rf proctest.o proctest.c proctest.lis
[markwill@oel02 proctest]$
This then leaves the directory a bit cleaner with only the Makefile, source file, and executable:
[markwill@oel02 proctest]$ ls -al
total 28
drwxrwxr-x 2 markwill markwill 4096 Sep 26 14:31 .
drwxrwxr-x 3 markwill markwill 4096 Sep 26 13:40 ..
-rw-rw-r-- 1 markwill markwill 3617 Sep 26 14:31 demo_proc_ic.mk
-rwxrwxr-x 1 markwill markwill 9798 Sep 26 14:29 proctest
-rw-rw-r-- 1 markwill markwill 2900 Sep 26 14:14 proctest.pc
[markwill@oel02 proctest]$
