Faster and Friendlier Access to Oracle's OCI API

Why get bogged down in the quicksand of the Oracle Call Interface (OCI) when you can get the same service with less code and fewer headaches with Vincent Rogier's OCILIB?

Add Oracle Access to Your App in the Blink of an Eye

Vincent Rogier's OCILIB is a free, open source, and platform-independent library, written in C, that accesses Oracle Databases for you. OCILIB's design goals include:

Encapsulating the powerful (but complex) OCI API

Hiding unnecessary complexity inherent in OCI

Providing a simple, readable, and reusable API

Offering 350 simple and straightforward APIs

OCILIB is written in pure ISO standard C and will build under any C90 compliant compiler. Certified platforms include Windows, Linux, HP/UX, Solaris, and AIX. Supported compilers are MS Visual Studio, Gnu GCC, MinGW, IBM's XLC, and CC. OCILIB works with Oracle versions 8i, 9i, 10g, and 11g. Because OCILIB is LGPL-licensed, you are free to use it in commercial products without worry.

For those of you who aren't keen on recompiling open source projects, Rogier supplies a nice ZIPped archive with WIN32 and WIN64 binaries that will be enough to get most people started just fine. But if you do need custom DLLs, for whatever reason, there are handy Visual Studio 2005 and Visual Studio 2008 project files right at hand.

An exhaustive list of features would be far longer than I can possibly include in this article, so I'll just give you what I feel are the "top 10" benefits of OCILIB (in no particular order!).

The setup starts in Lines 9-11 where you call OCI_Initialize(). You could have supplied a glogal error handler and an Oracle home path if desired there. You then can set up a connection with OCI_ConnectionCreate() with the usual database, username, and password params. At this point, you're ready to create a Dynamic SQL statement with OCI_StatementCreate() and OCI_ExecuteStmt(). For the example, you are simply querying the system catalogs to get a list of tables; this should work in ANY Oracle database, no matter how humble.

In the second half of the program, Lines 14-18, you establish a Result Set (in other words, make the output of a query available to the app) and then fetch results one by one. The OCI_GetString() and OCI_GetInt() pull the results from the current row in the Result Set, indexed by column number. At the end, OCI_Cleanup() frees all pending connection resources.

Connection Pooling with Threads

Okay, the first example is a bit pedestrian, but you must admit that you sure got a lot done with the 10 or so lines of code in the "meat" of the program. Now, try something more challenging—connection pooling with threads in only 40 lines of code!

Starting off in main(), Lines #17-28 rehash what you did in the first example except that you did add OCI_ENV_THREADED to OCI_Initialize() this time around. Next, you're going to initialize the connection pool that all the threads will draw from. Back in the bad old days, developers had to write their own connection pooling code. In fact, if you are using Oracle 8i, OCILIB will do this for you. For Oracle 9i (and later), OCILIB uses the native connection pooling behind-the-scenes.

The main parameters of interest are the last three: minimum number of connections to open, maximum connections to open, and next increment. For example, you could ask to start with four connections, a maximum of 32 open, and to increment by 8 each time you need more (and you're under the 32 open limit).

You'll skip directly to the thread creating and spawning in Lines #29-32.

OCI_ThreadCreate() just initializes your thread handle, which you are storing in the th[] array for convenience sake. Next, you spawn the new thread (process) with OCI_ThreadRun() passing in the thread handle, pointer to a thread function to execute, and an arbitrary argument. It makes sense to pass in the connection handle (named "pool") that you are going to be sharing amongst all the happy threads.

Now, you must visualize several instances of the worker() function starting up, each with a unique thread handle and a copy of the pool handle. However, before the worker thread can do anything useful, it's got to get its own connection handle via OCI_ConnPoolGetConnection(). Your simple-minded app blithely assumes that it will always have enough resources available to get the job done. A real-world app would probably use OCI_ConnPoolSetNoWait(FALSE) to stall out the thread until a connection was freed up. It's also possible to manage connections by timeout using OCI_ConnPoolSetTimeout() to reap connections that have been idled for a specified duration.

At last, when the thread has completed its work in Lines #11-14, it can free its resources and await termination. Meanwhile, back in the main() program, the original program thread is in a rather simple loop where it is merely waiting on each thread to complete and freeing their resources in the same order in which they were launched. OCI_ThreadJoin() is a blocking call, so the main thread in fact sits and does nothing until each thread finishes and then the function returns. If the thread had already been dead, before OCI_ThreadJoin() was called, I believe it might return FALSE, but in your case you don't care to know how or when the child thread departed. All-in-all, the thread and pooling system strikes me as both simple in conception and sophisticated in operation.

Conclusion

It's not every day that I say this, but I can emphatically say that OCILIB implements an SQL database interface that is exactly the way I would design it myself and I would be hard pressed to come up with a better solution for C programmers. Of course, you have barely scratched the surface of what OCILIB could do for you. It would take probably a dozen articles to adequately cover topics such as working with Internal Large Objects (LOBs) and External Large Objects (FILEs), hash tables, binding arrays, fetching multiple rules, PL/SQL integration, and much much more. If I have piqued your curiosity in any way, my goal here has been achieved.