To use OCaml for real work, I have to be able to talk to a variety of databases. SQLite seems reasonably well supported, judging by PLEAC. Don’t underestimate SQLite – parsing a few Gb of logs or CSVs and loading them into SQLite for analysis using SQL is a very powerful technique. The bulk of our data however is in Oracle, and there would appear to be three options:

• Oracaml: a native binding to OCCI. Last updated in May 2006.
• OCamlODBC : uses ODBC. I don’t think we use this apart from the rare cases in which we need a Unix box to talk to MS SQL Server or Informix. Would require at a guess UnixODBC and an appropriate driver.
• OCaml DBI : OCaml has the interesting facility to use Perl libraries; this is Perl’s DBI/DBD mechanism. Of course would require DBI and DBD::Oracle installed. We do have this as well, Perl scripts using DBD::ODBC (on top of UnixODBC and FreeTDS… the sort of extensive prerequisites that I am trying to avoid).

Given my strategy of minimizing external dependencies, Oracaml seems to be the logical choice. I have Oracle XE for Debian already installed in my development environment, with ORACLE_HOME, ORACLE_SID and LD_LIBRARY_PATH set, and $ORACLE_HOME/bin in my PATH. First, download and unpack the Oracaml distribution:

gaius@debian:~$ tar zxf oracaml-0.1.tar.gz 
gaius@debian:~$ cd oracaml-0.1/
gaius@debian:~/oracaml-0.1$ ls
doc  Makefile  occi.c  occi.ml  occi_test.ml

Next as per the release notes, edit the Makefile to set the variables OCCIHOME and CAMLHOME to where they are installed in your environment.

OCCIHOME = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server
CAMLHOME = /usr

And then it should be straightforward…

gaius@debian:~/oracaml-0.1$ make
ocamlc -g -c -pp camlp4r -I /usr/lib/ocaml/camlp4 occi.ml
File "occi.ml", line 122, characters 26-28:
Error: Unbound type parameter 'a
make: *** [occi.cmo] Error 2

… only it isn’t. What happened there? Well, the syntax of OCaml has changed in the last 4 years it seems, between versions 3.8 and 3.10. In the file occi.ml:

type ora_env            = 'a;
type ora_connection     = 'a;
type ora_statement      = 'a;
type cursor             = 'a;

Remove ='a, leaving the ;. Next problem:

gaius@debian:~/oracaml-0.1$ make
ocamlc -g -custom -o occi_test unix.cma -cclib -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -cclib -locci -cclib -lclntsh  occi.o occi.cmo occi_test.cmo -cclib -lunix
/usr/bin/ld: warning: libstdc++.so.5, needed by /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libocci.so, not found (try using -rpath or -rpath-link)

So quickly grab that in another window:

debian:~# apt-get install libstdc++5

And now:

gaius@debian:~/oracaml-0.1$ make 
ocamlc -g -custom -o occi_test unix.cma -cclib -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -cclib -locci -cclib -lclntsh  occi.o occi.cmo occi_test.cmo -cclib -lunix
ocamlmktop -g -custom camlp4r.cma -o ocamlora unix.cma -cclib -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -cclib -locci -cclib -lclntsh  occi.o occi.cmo -pp camlp4r -I /usr/lib/ocaml/camlp4
File "_none_", line 1, characters 0-1:
Error: Error while linking /usr/lib/ocaml/camlp4/camlp4r.cma(Camlp4):
Reference to undefined global `Dynlink'
make: *** [ocamlora] Error 2

Nearly there… Another change in OCaml has been between 3.10 and 3.11, now Dynlink needs to be run before Camlp4, so that command can actually be executed as:

gaius@debian:~/oracaml-0.1$ ocamlmktop -g dynlink.cma -custom camlp4r.cma -o ocamlora unix.cma -cclib -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -cclib -locci -cclib -lclntsh  occi.o occi.cmo -pp camlp4r -I /usr/lib/ocaml/camlp4 

Note that I have inserted dynlink.cma before camlp4r.cma. Finally we can test it:

gaius@debian:~/oracaml-0.1$ ./occi_test -u gaius -p gaius
===Testing Functional features===
Test table dropped.
Test table created.
*** glibc detected *** ./occi_test: munmap_chunk(): invalid pointer: 0x08da4f48 ***

That doesn’t look good! This is a well known sign of heap corruption. The irony being that OCaml is supposed to protect you from this kind of runtime error! But it can’t when the error originates in the C++.

======= Backtrace: =========
/lib/i686/cmov/libc.so.6(+0x6b321)[0xb6891321]
/lib/i686/cmov/libc.so.6(+0x6c59e)[0xb689259e]
/usr/lib/libstdc++.so.5(_ZdlPv+0x23)[0xb67fca03]
./occi_test(_ZN9__gnu_cxx13new_allocatorIN6oracle4occi8MetaDataEE10deallocateEPS3_j+0x11)[0x8061eb5]
./occi_test(_ZNSt12_Vector_baseIN6oracle4occi8MetaDataESaIS2_EE13_M_deallocateEPS2_j+0x25)[0x8061b61]
./occi_test(_ZNSt12_Vector_baseIN6oracle4occi8MetaDataESaIS2_EED2Ev+0x3e)[0x80614e2]
./occi_test(_ZNSt6vectorIN6oracle4occi8MetaDataESaIS2_EED1Ev+0x56)[0x8060f1c]
./occi_test(occi_describe+0x40b)[0x805f1f3]
./occi_test(caml_interprete+0x13a4)[0x8073f9e]
./occi_test(caml_main+0x386)[0x8062ec2]
./occi_test(main+0x14)[0x8072bd8]
/lib/i686/cmov/libc.so.6(__libc_start_main+0xe6)[0xb683cc76]
./occi_test[0x80576c1]

Could it be the result of mixing libstdc++.so.5 with libc.so.6? I don’t see a way around that; Oracle 10g expects that to link against, and name mangling means you can’t mix and match with C++. It seems I’m not the only one to experience this issue (but the only one with OCaml!) The last version of GCC to use libstdc++.so.5 was 3.3 which I cannot apt-get. I am using 4.4; the oldest “officially” available is 3.4. This is mentioned in the documentation but the patch is no longer available.

Despite what Oracle Corporation would perhaps prefer, there’s still an awful lot of 10g around, including all our most important systems, such as main OLTP and the DW. Perhaps I’ll have more luck with 11g. Modifying my Makefile:

OCCIHOME = /opt/oracle/product/11.2.0_db

Then:

gaius@debian:~/oracaml-0.1$ make clean && make
gaius@debian:~/oracaml-0.1$ ./occi_test -u gaius -p gaius
===Testing Functional features===
...
12. Execute DDL test
  Table dropped
  Session disconnected

Success at last! And if we examine the ldd output for both occi_test and $ORACLE_HOME/lib/libocci.so.11.1 we find that they both link against the same C and C++ libraries – version 6. Now the acid test: will the same binary run on Redhat 64?

redhat64$ ./occi_test -u gaius -p gaius
./occi_test: error while loading shared libraries: libocci.so.11.1: wrong ELF class: ELFCLASS64
redhat64$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib32
redhat64$ ./occi_test -u gaius -p gaius
===Testing Functional features===

So it works… Kinda. It’s easy enough to wrap it in a little shell script launcher to set the environment correctly, far easier than building and maintaining the Perl4Caml → DBI → DBD::ODBC → UnixODBC → Oracle ODBC stack. However given that we already have the native Oracle drivers there’s no way we’d be willing to pay EasySoft for their driver just to do this!

Conclusion: While there are very serious commercial users of OCaml, it is also important to remember that its primary developers are not really interested in commercial RDBMS applications, which is fair enough, they’re CS researchers. And I don’t want to seem ungrateful to the elusive Serge Aleynikov, author of Oracaml, after all he did something that I cannot – as yet – do for myself, then gave it away for free. But based on my own experience of how trivial it is to plumb Python and Tcl/Tk into the world’s #1 RDBMS, I have to wonder how many people less bloody-minded than me have simply given up on OCaml (as I did the first time ’round) Having said that, many of these issues are more the fault of Linux being such an immature platform.

Of course the effort of learning and using OCaml is not going to be wasted. There’s always F#, if I ever need to code on the Windows platform, and it has all of the .NET libraries on-hand.