Module Occi

module Occi: sig .. end
This OCAML library implements an Oracle client interface using OCCI C++ Oracle library built on top of OCI.

The library was only tested on Linux, but should work on Windows as well. Bug reports should be submitted to the author.

Library features

The following operations with an Oracle database are supported:

This library requires Oracle 10g (at least 10g Release 2) instant client library to be installed on a client machine. OCCI requires only four shared libraries (or dynamic link libraries, as they are called on some operating systems) to be loaded by the dynamic loader of the operating system. Oracle Database 10g Release 2 (10.2) library names are used; the number part of library names will change to remain consistent with future release numbers.

Installing Instant Client

OCCI requires only four shared libraries (or dynamic link libraries, as they are called on some operating systems) to be loaded by the dynamic loader of the operating system. Oracle Database 10g Release 2 (10.2) library names are used; the number part of library names will change to remain consistent with future release numbers.

These shared libraries can be obtained from Oracle Technology Network.

Note: if you are getting a segmentation fault when running the test program or the following compilation warning: /usr/bin/ld: warning:, needed by ...../, may conflict with this means that you have a gcc version 3.4.3 or later, and the Oracle Instant Client was built with gcc 3.2.3. Obtain a patch from: OCCI patch.

If these four libraries are accessible through the directory on the OS Library Path variable (LD_LIBRARY_PATH on Linux and UNIX and PATH on Windows), then OCCI operates in the Instant Client mode. In this mode, there is no dependency on ORACLE_HOME and none of the other code and data files provided in ORACLE_HOME are needed by OCCI.

Also note that when using instant client the TNS_ADMIN variable should point to a directory containing Oracle resolver data (sqlnet.ora and tnsnames.ora). See Oracle documentation on configuring instant client.

Example Usage

See for sample test cases. Below find a simple example illustrating a select statement with bind variables. A more interesting example can be found here.

      open Occi;
      value con  = occi_connect "scott" "tiger" "mydb";
      value stmt = occi_create_statement con "";
      let (_, records) = 
        ora_select stmt "select * from test where name like :1" [| Var_str "Al%" |]
        List.iter print_record records;

Author(s): Serge Aleynikov (serge\
Version: $Rev: 448 $ $Date: 2006-10-30 19:09:00 -0500 (Mon, 30 Oct 2006) $
See also OCCI docs

Types and Exceptions

type ora_env 
type ora_connection 
type ora_statement 
type cursor 

type ora_value =
| Null
| Int of int
| Date of float
| Float of float
| Str of string
This type is for use as a result of a query

type param_val =
| Null_int
| Null_date
| Null_float
| Null_str
| Var_int of int
| Var_date of float
| Var_float of float
| Var_str of string
| Var_out_int
| Var_out_date
| Var_out_float
| Var_out_str of int
This type is for use in parameterized queries

type described_column = {
   name : string;
   ctype : string;
   len : int;
   scale : int;
   not_null : bool;
This type is for use by the occi_describe function
exception ORA_EXCEPTION of (int * string)
Oracle API functions raise this exception containing (err_code * err_message)
exception ORA_BULK_EXCEPTION of (string * (int * int * string) array)
occi_execute_array function raises this exception containing (error_message, failed_rows), where the failed_rows is an array of tuples in the form (row_number, err_code, err_message). The first row in the original array of records has number 0.

Functional API

val decode_occi_type : int -> string
decode_occi_type column used to convert the column type to string or ora_column_type type
?? : int
val occi_connect : string -> string -> string -> ora_connection
occi_connect user password database -> connection creates a connection to an Oracle database. The connection is managed automatically by the CAML garbage collector, or it can be disconnected manually by issuing a call to occi_disconnect.


value con = occi_connect "scott" "tiger" "mydb";

val occi_disconnect : ora_connection -> unit
occi_disconnect connection -> unit disconnects a previously open Oracle connection
val occi_create_statement : ora_connection -> string -> ora_statement
occi_create_statement con sql creates an Oracle statement. The sql string can be left blank, in which case it must be initialized in the call to occi_prepare_sql or any function in the occi_execute* family. The statement is managed automatically by the garbage collector, so it doesn't need to be freed explicitely.


value stmt = occi_create_statement con "";

val occi_free_statement : ora_statement -> unit
occi_free_statement stmt frees an Oracle statement created with ora_create_statement. Note that it's not necessary to call this function, as the statement object is automatically managed by the garbage collector
val occi_prepare_sql : ora_statement -> string -> unit
occi_prepare_sql stmt sql sets an SQL string for a given statement.
val occi_prepare_plsql : ora_statement -> string -> (int * param_val) list -> unit
occi_prepare_plsql stmt sql [ (pos * param_val) ] sets sql text of a PL/SQL statement, and specifies OUT parameters. The parameters are given as an array of tuples, where pos indicates the parameter number, param_val is one of OutInt; OutStr of int; OutFloat; OutDate, and OutStr specifies the max string size in bytes.

This function allows to prepare the SQL statement prior to calling occi_execute or occi_execute_plsql.


occi_prepare_plsql stmt "begin :1 := my_fun(:2); end;" [(1, Var_out_str 30)];
      match (occi_execute_plsql stmt [(2, Var_int 10)]) with
      [ [(1, Var_str s)] -> print_endline s];

val occi_describe : ora_connection -> string -> (string * int * int * int * bool) array
val ora_describe : ora_connection -> string -> described_column array
ora_describe ora_connection table_name descrites a table by returning an array of field descriptors.


        (fun {name=a; ctype=b; len=c; scale=e; not_null=f} -> 
           Printf.printf "  %-30s %-10s %5d %5d %5b\n" a b c e f)
        (occi_describe con "test");

con : ora_connection
table : string
val occi_execute : ora_statement ->
string -> (int * param_val) list -> (int * ora_value) list
occi_execute stmt sql params -> out_values executes a PL/SQL block by preparing an sql text, binding it with a list of params where each element is a tuple (position:int * value:param_val), and returns the out_values result as a list of (position:int * value:ora_value) tuples where each value represents the value of an OUT parameter at a given position.


      let lst = occi_execute stmt "begin :1 = my_fun(:2); end;"
                                  [(1, Var_out_int), (2, Var_float 2.0)]
      in List.iter (fun (i, v) -> Printf.printf "%d, %s\n" i (field_to_string v)) lst;

val occi_fetch : cursor -> ora_value array
occi_fetch rs returns data as an array of fields. This is an internal function, so it's recommended to use the occi_fetch_foreach instead.
Raises End_of_file when there are no more rows in the cursor.
val ora_fetch_foreach : cursor -> (ora_value array -> unit) -> unit
ora_fetch_foreach cursor f applies the function f: array ora_values -> unit to each record in the cursor.
Raises End_of_file when there are no more rows in the cursor.


      let (cursor, _) = occi_execute_query stmt "select * from test" in 
        ora_fetch_foreach cursor (fun [| Int i; Str s |] -> Printf.printf "%d - %s\n" i s)
        [ End_of_file -> () ]; 

rs : cursor
f : ora_value array -> unit
val ora_fetch_foreach_list : ?max_rows:int option ->
cursor -> (ora_value array list -> bool) -> unit
ora_fetch_foreach_list cursor max_rows f fetches up to max_rows of records from an open cursor to list, and applies function f to that list. Function f should return False if there's no need to continue fetching records, or True otherwise. If max_rows is None then all records are retrieved and passed to the f function. If max_rows is Some n then the list passed to the f function will contain at most n elements.


      let (cursor, _) = occi_execute_query stmt "select * from test" in 
      (* Process records 10-elements at a time *)
      ora_fetch_foreach_list cursor (fun data -> do {List.iter process_record data; True}) ~max_rows:10;

max_rows : int option
cur : cursor
f : ora_value array list -> bool
val ora_fetch_list : ?max_rows:int option ->
cursor -> cursor option * ora_value array list
ora_fetch_list cursor max_rows fetches up to max_rows of records from an open cursor to list. If max_rows is None and/or all records are retrieved then result will contain (None, data_list). If max_rows is Some n then after fetching max_rows rows, the function will return (Some cursor, data_list), and the cursor could be used in successive calls to ora_fetch_list.


       let (cursor, _) = occi_execute_query stmt "select * from test" in 
       let (_, data)   = ora_fetch_list cursor None in
         List.iter process_record data;  

max_rows : int option
cur : cursor
val occi_execute_query : ora_statement -> string -> cursor * string array
occi_execute_query stmt sql -> (cursor * field_names) executes an Oracle query and returns an open cursor and an array of field names. The cursor can be fetched using occi_fetch function. sql can be empty, in which case the sql value is taken from the occi_statement con sql call.
Returns an opaque cursor object that can be fetched from using occi_fetch and an array of field names.
val occi_execute_param_query : ora_statement ->
string -> param_val array -> cursor * string array
occi_execute_param_query stmt sql params -> (cursor * field_names) executes an Oracle query. It is the same as occi_execute_query with the exception that it takes an array of parameter values. The query should be a SELECT statement with bind variables in the form: ":1" ... ":N" where each ":i" will represent the i-th parameter from the params array.


      let stmt = occi_create_statement con "" in 
      let (cursor, fields) = occi_execute_param_query stmt
                             "SELECT * FROM TEST WHERE ID < :1 and NAME LIKE :2" 
                             [| Var_int 100; Var_str "S%" |]
      in List.iter do_fetch_record cursor; 

val ora_select : ora_statement ->
string ->
?max_rows:int option ->
param_val array ->
cursor option * string array * ora_value array list
This function selects records given a SELECT SQL command, and returns result in a form of a list. Note that this function doesn't raise End_of_file exception upon reaching the end of a cursor.
Returns (cursor, list_of_records), where the cursor is either None or a value that can be passed to ora_fetch_list function to process remaining records.


        let (_, _, lst) = 
            ora_select stmt "select * from test where id < :1" [| Var_int 10 |])
            List.iter print_record lst;

stmt : ora_statement
sql : string
max_rows : int option
defines the maximum number of rows to return (None means all, which is the default).
params : param_val array
is an array of bind paramerer values. If it's not empty, the query is expected to contain parameters in the form ":1" ... ":N" that will be bound using values from the array at corresponding positions. The bind parameter indexes begin with ":1".
val occi_execute_array : ora_statement -> string -> param_val array list -> int
occi_execute_array stmt executes an SQL INSERT/UPDATE/DELETE bulk operation. The parameter list contains arrays of field values. Each array can be thought of a record in an Oracle table associated with the INSERT/UPDATE/DELETE operation. This function uses a bulk interface, so that only one network roundtrip is performed for the entire list of parameter values.
Raises ORA_BULK_EXCEPTION See example below on how to retrieve the error codes for the failed rows.


         match occi_execute_array stmt 
                 [| Var_int 1, Null_str,      Var_date (encode_date (2006,1,1,0,0,0)) |],
                 [| Var_int 1, Var_str "abc"Null_date |],
         with [n -> Printf.printf "Inserted %d records\n" n]
       with [
          ORA_BULK_EXCEPTION (err_message, failed_rows) -> do {
            Printf.printf "Failed %d rows. Reason: %s\n" (Array.length failed_rows) err_message;
            Array.iter (fun (i, e, m) -> Printf.printf "  Row %d error [%d]: %s\n" i e m) failed_rows

val occi_execute_update : ora_statement -> param_val array -> int
occi_execute_update stmt params -> num_rows executes an INSERT/UPDATE/DELETE operation on a previously bound statement


      occi_prepare_sql stmt "insert into test (id, name) values (:1, :2)";

      match occi_execute_update stmt [| (1, Var_int 1); (2, "test") |] with
      [ n -> Printf.printf "Inserted %d record\n" n];

val occi_execute_plsql : ora_statement ->
(int * param_val) list -> (int * ora_value) list
occi_execute_plsql stmt params -> out_param_values executes a PL/SQL block given a list of bind parameter values and returns the list of OUT parameter tuples. In the params list, and out_param_values list the first element represents the position of the OUT parameter.
See also occi_prepare_plsql for an example
stmt : ora_statement
params : (int * param_val) list
val occi_commit : ora_connection -> unit
occi_commit con commit a transaction
val occi_rollback : ora_connection -> unit
occi_rollback con rollback a transaction
val date_to_tm : float ->
date_to_tm float convert a float date to a record.
Raises Failure for other argument types.
d : float
val decode_date : float -> int * int * int * int * int * int
decode_date d convert a date to a tuple (year:int, mon:int, day:int, hour:int, min:int, sec:int).
Raises Failure for other argument types.
d : float
val encode_date : int * int * int * int * int * int -> float
encode_date date_tuple converts a tuple from (year, mon, day, hour, min, sec) to a float value representing the number of days since epoch and fractional part representing the number of seconds since midnight. year is the calendar year, mon is between 1 and 12, day is between 1 and 31, hour is between 0 and 23, and min and sec are between 0 and 59.
(year,mon,day,hour,min,sec) : int * int * int * int * int * int
val date_to_string : float -> string
date_to_string d convert a date string in the "MM/DD/YYYY HH:MI:SS" format. Failure is raised for other argument types.
d : float
val sysdate : unit -> float
sysdate returns the current local time encoded in the internal date format.
() : unit
val field_to_string : ora_value -> string
field_to_string fld convert an ora_value to string.


 field_to_string(Date d) -> "2006/03/10 15:00:00" 

fld : ora_value
val print_date : float -> unit
Print a date in the format returned by the date_to_string function
d : float
val print_field : int -> ora_value -> unit
Print a field. If n is greater than 0, a comma will be printed in front of the field's value
n : int
fld : ora_value
val print_record : ora_value array -> unit
Print a record containing ora_value fields separated by commas.
r : ora_value array

Object-Oriented API

class statement : ?sql:string -> ora_connection -> object .. end
This class implements DML and DDL Oracle operations on a given connection descriptor.
class connection : string -> string -> string -> object .. end