Understand how .NET can talk to OCI

If you want to fine-tune performance, it's useful to understand how .NET can talk directly to the Oracle Call Interface (OCI).

.NET Framework applications are rapidly gaining popularity, especially due to .NET's support of languages such as Visual C++, Visual Basic, and C#, as well as environments such as ASP.NET.

Database access from these languages has traditionally been through several layers of technologies such as ODBC and ADO. The fact that there's a common interface for databases is a positive step toward making applications independent of database implementation; however, it may impact performance and limit some database-specific functionality.

The Oracle Call Interface (OCI) is an interface to the Oracle database, which is very low level for the best available performance and which supports the entire set of public database functionality. However, it tends to get ignored for two reasons: because of its syntax and the idea that OCI is only usable for traditional C programmers.

One advantage of the .NET Framework is the ability to write specifications for calls to a Dynamic Link Library (DLL) and then to call the DLL entry point directly. Oracle for Windows implements OCI as a DLL (oci.dll). Therefore, a .NET application can make an OCI call simply by writing up a specification for the entry point and calling the function. Since version 8, OCI has had a nice, consistent interface based on handles (pointers). These pointers can be treated as simple integers in calls.

Listing A is a C# example, wrapping the initial OCIEnvCreate call. You can wrap this call in a class constructor, as shown in Listing B. Then, you can create an OCI environment handle using the call:

OciEnvironmentHandleenv = new OciEnvironmentHandle();

With .NET Framework 1.1, Microsoft released a library called System.Data.OracleClient, which it said bypassed the ODBC layer to connect to the database and resulted in an approximate 200 percent increase in performance. (It isn't known whether Microsoft used OCI.) The Ximian Mono project (an open source version of .NET that runs on Linux) allows you to look at the source code. As it turns out, they're using the same technique for their implementation of System.Data.OracleClient.

Understanding how .NET can talk directly to OCI is a handy tool if you want to fine-tune performance, add new features that aren't supplied by System.Data.OracleClient (e.g., direct loading of data), and adapt to the next release of Oracle libraries. It also shows that a .NET Framework combined with OCI calls can achieve performance that's similar to native Oracle tools.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.