Topic: Using the Oracle Call Interface

So, OK, the sarcasm is out of my system now. I want to say that once you figure out which parts of all that are actually useful (the OCI: Oracle Call Interface), Oracle is pretty darned neat. This isn't the first time I've worked with Oracle (I've done lots of Oracle work in Perl via the DBI interface) but it was the first time I'd even come close to exploring what it can do. The object storage functionality looks sweet. Not that I used it. Not that I'd feel comfortable using it at all, for $40,000 a pop per Oracle installation. But boy, I'd like to write a comparable API on top of PostgreSQL, that I would.

So the title of the topic is "Using the Oracle Call Interface." That's obviously what I ended up doing. Oracle has all their documentation online at their TechNet, which is free of charge (although you do need to register to use it, which bothers me not one tiny smidgen.) So in about two and a half days I was able to figure out how to translate the wftk taskindex adaptor into Oracle from its original ODBC beginning. (On the way I enhanced it. I love rewrites.)

What I really want to do with this topic is write down some of the gotcha's I discovered while working with OCI. There aren't a whole lot. For some good sample code, you can study my adaptor implementation at the wftk site.

To use OCI (at least in Versions 8 and 9), you include oci.h. You link with oci.lib. The other library is there for version 7 compatibility, so even though it's tempting to use something called "ociw32", you don't need it.

The main set of documentation for the OCI interface at TechNet is at http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96584/toc.htm, or at least it is right now. It's well-written documentation. Besides the list of actual functions, I found Chapters 4 and 5 particularly illuminating and well worth reading a couple of times (after you've failed to code something correctly, these chapters really make sense.)

The sample code was a good initial help, but there were a few things that don't appear in the sample code. Here are a few of my insights about how to make it all make sense. You realize, of course, that this topic is mostly for me, the next time I have to write some Oracle code. I hope it's useful for you, too.

• Handles, handles, handles
  Oracle allocates a whole lot of handles. They're all managed in the environment handle, which is the first thing you allocate. When you free the environment, it frees up everything associated with it, so that's nice, but setting it all up is a chore. Look at the sample code, abstract away the frequent calls to checkerr(), and you can start to see what it all does.
• NULL values are dangerous
  Although the sample code doesn't show it, you need to install an "indicator" variable for each value you fetch from a row. Why? Because if you don't, and the value is NULL, it's an error. Not a warning. Not a blank string. An error. This was quite perturbing until I found a hint to their usage. You can also read the documentation for the OCIDefineByPos() function very carefully.
• Fetching is only into defined variables
  This means if you're dealing with an arbitrary query, you can look forward to maintaining a malloc'd set of buffers.
• Getting column names
  This is actually documented pretty well, but it was so weird I just didn't understand it. When you get the name of a column by doing "description" of an executed query, Oracle doesn't copy the name's value into the variable you give it. It copies a pointer to the name. Then you have to copy it somewhere yourself. And it's not null-terminated, so you really do have to pay attention to that length you pass in. (This will all make sense if you read the documentation for the OCIAttrGet() function.) Got it? Good.
• Error messages for fun and profit
  Unlike (say) Microsoft's error message documentation (you know, like "Error FILE_NOT_FOUND: A file was not found"), Oracle's error message documentation kicks serious ... um ... this isn't a webtoon topic, it's technical, right? Seriously, their documentation is good. I found it to be an excellent resource.

• Oracle's OCI manual
  Read it enough and everything will come clear. The link may well be obsolete by the time you read it. If so, good luck finding your way through Oracle's linking system. One useful hint: start at "Documentation", not at "Product Information" -- the latter is whitepapers.