Faster and Friendlier Access to Oracle's OCI API

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!).

1. Integrated smart define and fetch mechanism
2. Full Unicode support
3. Multi-row fetching
4. Binding array Interface for fast and massive bulk operations
5. Connection pooling (saves on resources)
6. PL/SQL blocks
7. LOBs and FILEs support
8. Provides Hash tables, portable threads, and mutexes API
9. Can load OCI libs at runtime (no Oracle libraries required at compile time)
10. Can be compiled as a static lib or a shared (import) library

Getting Started

Take a look at the "Hello World" type example for OCILIB so you can see what it takes to make a simple connection to a database and then get some kind of SQL Select statement working:

 1 #include "ocilib.h"
 2
 3 int main(int argc, char *argv[])
 4 {
 5    OCI_Connection* cn;
 6    OCI_Statement* st;
 7    OCI_Resultset* rs;
 8
 9    OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT);
10    cn = OCI_ConnectionCreate("db", "user", "pwd",
                                OCI_SESSION_DEFAULT);
11    st = OCI_StatementCreate(cn);
12
13    OCI_ExecuteStmt(st, "select table_name,
                      num_rows from user_tables order by 1");
14    rs = OCI_GetResultset(st);
15
16    while (OCI_FetchNext(rs)) {
17       printf("table %-30s : %10i rows\n", OCI_GetString(rs,1),
                OCI_GetInt(s, 2));
18    }
19
20    OCI_Cleanup();
21    return EXIT_SUCCESS;
22 }

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!

 1 #include "ocilib.h"
 2
 3 #define MAX_THREADS 50
 4 #define MAX_CONN    10
 5
 6 void worker(OCI_Thread *thread, void *data)
 7 {
 8    OCI_Connection *cn =
         OCI_ConnPoolGetConnection((OCI_ConnPool *) data);
 9    char buf[100];
10
11    OCI_Immediate(cn, "select sysdate from dual",
                    OCI_ARG_TEXT, buf);
12    printf("Thread ID : %6d - Date : %s\n",
             OCI_HandleGetThreadID(thread), buf);
13
14    OCI_ConnectionFree(cn);
15 }
16
17 int main(void)
18 {
19    OCI_Thread *th[MAX_THREADS];
20    OCI_ConnPool *pool;
21    int i;
22
23    if (!OCI_Initialize(NULL, NULL, OCI_ENV_DEFAULT |
                          OCI_ENV_THREADED))
24    return EXIT_FAILURE;
25
26    pool = OCI_ConnPoolCreate("db", "user", "pwd",
27       OCI_SESSION_DEFAULT, 0, MAX_CONN, 1);
28
29    for (i = 0; i < MAX_THREADS; i++) {
30       th[i] = OCI_ThreadCreate();
31       OCI_ThreadRun(th[i], worker, pool);
32    }
33
34    for (i = 0; i < MAX_THREADS; i++) {
35       OCI_ThreadJoin(th[i]);
36       OCI_ThreadFree(th[i]);
37    }
38
39    OCI_Cleanup();
40    return EXIT_SUCCESS;
41 }