Consider the following Oracle table:

GAIUS@orcl  17-SEP-10> create table t1 (
  2  c1 number,
  3  c2 number(1));

Table created.

GAIUS@orcl  17-SEP-10> insert into t1 values (1, 1);

1 row created.

GAIUS@orcl  17-SEP-10> commit;

Commit complete.

GAIUS@orcl  17-SEP-10> select count(1) from t1 where c1 = c2;

  COUNT(1)
----------
	 1

Note that both the values inserted were identical. In Python:

>>> from cx_Oracle import connect
>>> lda = connect("gaius/gaius")
>>> sth = lda.cursor()
>>> sth.execute("select c1, c2 from t1")
[<cx_Oracle.NUMBER with value None>, <cx_Oracle.NUMBER with value None>]
>>> for row in sth:
...     print ("OK" if row[0] == row[1] else "FAIL")
...
OK

Same results in SQL and Python. But in OCaml:

open Occi

let _ = 
  let lda = occi_connect "guy" "abc123" "" in
  let sth = occi_create_statement lda "" in
  let (_, _, rs) = ora_select sth "select c1, c2 from t1" [||] in
  List.iter (fun r -> print_endline (match r.(0) = r.(1) with |true -> "OK" |false -> "FAIL")) rs

(* End of file *)

gaius@debian:~$ ./test 
FAIL

What happened there? Well, OCaml is seeing the first column as "1." and the second as "1" (sans quotes), despite them both having obviously been identical integers when inserted. The question is really what didn’t happen – there was no invisible type coercion between float and int (they are actually both Occi.ora_value in the code above).

It’s definitely doing the “right thing” according to the type system of course, unfortunately there has never been any sort of standard relating to this here; the DDL for columns of type NUMBER seems to have been decided on a fairly whimsical basis as a cursory browse of the schemas of some of our main apps shows. And there is loads of pre-existing SQL that I would like to be able to reuse.

This is easy enough to deal with in the SQL: SELECT TRUNC(C1), C2... will let OCaml make the comparison the same as Python. I think I will amend my Occi_overlay module with an ora_select that optionally parses the SQL (looking for a comma seperated list between the keywords SELECT and FROM, and between FROM and WHERE), gets the type of each column and if it’s a number, wrap it in TRUNC. Which is very much a hack, but if I know that the types are always really going to be integers (because I am familiar with the application), it will at least be seamless…

Note that I absolutely do not consider this to be a problem with OCaml – but is is something to be aware of when introducing OCaml (or any strongly-typed languge) into an environment with legacy code.