next up previous contents
Next: The C Interface: OCI Up: Oracle Programming Previous: PL/SQL   Contents

Pro*C: The Oracle SQL Precompiler

Pro*C, with it's kool name and swanky asterisk, is a precompiler for C and C++. You can embed SQL statements in your code and then run the precompiler (the command is proc) on your source and out comes a new source file that you can compile as usual. The interesting thing is that while at first you might think that Pro*C is just for people to lazy to learn the OCI that it's actually using its own library SQLLIB, not the OCI. SQLLIB is unsupported (if used without Pro*C) and apparently changes frequently so they don't advise that you use it directly.

At first glance most sysadmins and developers might be confused by the idea of a SQL preproccessor... but think about it for a second: all those # lines in your C source are directives for the C Preprocessor. If you recall, when you compile your C source one of the first things that happens is the preproccessor (cpp) is run on your code before it's feed to the parser and eventually the assembler and linker. So Pro*C is really just adding another level of preproccessing prior to your calling the compiler.

Lets just play with a really simple example to get the idea.

#include <stdio.h>
#include <sqlca.h>	/* SQL Communications Area */

#define     UNAME_LEN      20	/* Varchar limits */
#define     PWD_LEN        40

VARCHAR	    username[UNAME_LEN];  /* SQL vars for        */
VARCHAR     password[PWD_LEN];    /* username and passwd */

int main(){

	strncpy((char *) username.arr, "SCOTT", UNAME_LEN);
	username.len = strlen((char *) username.arr);

	strncpy((char *) password.arr, "TIGER", PWD_LEN);
	password.len = strlen((char *) password.arr);

	EXEC SQL CONNECT :username IDENTIFIED BY :password;

	printf("Connected to the database as user: %s\n", username.arr);
	
	return(0);
}

Things of interest to note here. SQL is executed by using the "EXEC SQL" statement prior to the SQL command. Notice that for the SQL statement we're using SQL variables using the SQL VARCHAR datatype. Probly the most tricky thing to get used to is the mixing of C and SQL variables in the same source. C variables are refered to as "Host Variables" in the documentation. All SQL variables are prepended with a ":" when deferenced.

It should be noted that you don't need to capitolize everything, just like in SQL*Plus, but it's a good idea to help you keep SQL vs "real" C code seperated in your mind.

If your like me, your probly looking at that example code above and wondering whats up with "username.len". Well, VARCHARs are handled in a wierd way, the Pro*C precompiler is actually replacing those statements with structs. Each VARCHAR (and VARCHAR2) struct has two elements, arr and len. Thus...

VARCHAR   username[20];

becomes....

struct 
{ 
    unsigned short  len; 
    unsigned char   arr[20]; 
} username;

You'll find several, shall we say, interesting little tidbits like this.

To run the precomipiler on your code, use the proc binary with at least 2 arguments, the input source and the output filename.

bash-2.05$ proc connect.c connect-pro.c

Pro*C/C++: Release 10.1.0.2.0 - Production on Thu Oct 7 14:59:54 2004
Copyright (c) 1982, 2004, Oracle.  All rights reserved.

System default option values taken from: 
  /u01/app/oracle/product/10.1.0/db_1/precomp/admin/pcscfg.cfg

bash-2.05$ wc -l connect*.c
     215 connect-pro.c
      24 connect.c
     239 total
bash-2.05$

Once you've successfuly precompiled with Pro*C you can compile your code as usual. Make sure that you link it against the SQLLIB libraries (found in $ORACLE_HOME/precomp/).

Because of all the odd ins and outs of Pro*C, I wouldn't recommend it as a database management interface for common tasts, PL/SQL is much better and easier for that sort of thing. However, if you really want to get down and dirty Pro*C is probly an easier method than using the OCI directly... just make sure you expect to spend more time learning and experiementing than coding.

To get the full scoop on Pro*C, read the Pro*C/C++ Programmer's Guide:

http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/a97269/toc.htm


next up previous contents
Next: The C Interface: OCI Up: Oracle Programming Previous: PL/SQL   Contents
2005-02-10