OCaml client interface for Oracle Code

(*-------------------------------------------------------------------------*
 * File: $Id: occi.ml 448 2006-10-31 00:09:00Z serge $
 *-------------------------------------------------------------------------* 
 * Copyright 2006 Serge Aleynikov <serge@hq.idt.net>. All Rights Reserved.
 * Created: 1-Oct-2006
 *-------------------------------------------------------------------------*
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  
 * USA
 *-------------------------------------------------------------------------*)
(** 
  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 
  {{:mailto:Serge Aleynikov <serge\@corp.idt.net>}author}.

  {b Library features}

  The following operations with an Oracle database are supported:

  - Simple DML operations including SELECT / INSERT / UPDATE / DELETE
  - Parameterized DML operations including SELECT / INSERT / UPDATE / DELETE
  - Execution of PL/SQL stored procedures
  - Execution of bulk DML array INSERT / DELETE / UPDATE operations
  - Oracle exception handling
  - Oracle bulk exception handling with specifying failed rows and errors for each row
  - Transaction control (commit and rollback)
  - Automatic garbage control of Oracle resources (connections, statements
    and cursors)
  - Functional and Object-Oriented API included
  
  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.

  {b 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.

    - OCI Instant Client Data Shared Library ([libociei.so] on Linux and UNIX 
      and [oraociei10.dll] on Windows); correct installation of this file 
      determines if you are operating in Instant Client mode
    
    - Client Code Library ([libclntsh.so.10.1] on Linux and UNIX and 
      [oci.dll] on Windows)
      
    - Security Library ([libnnz10.so] on Linux and UNIX and 
      [orannzsbb10.dll] on Windows)

    - OCCI Library ([libocci.so.10.1] on Linux and UNIX and [oraocci10.dll] on Windows)
                                        
  These shared libraries can be obtained from 
  {{:http://www.oracle.com/technology/tech/oci/instantclient/}Oracle Technology Network}.

  Note: if you are getting the following compilation warning:
  [/usr/bin/ld: warning: libstdc++.so.5, needed by ...../libocci.so, may conflict with libstdc++.so.6]
  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:
  {{:http://www.oracle.com/technology/tech/oci/occi/occidownloads.html}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.
  
  {b Example Usage}

  See [occi_test.ml] for sample test cases.  Below find a simple example 
  illustrating a select statement with bind variables.  A more interesting 
  example can be found {{:#VALocci_execute_array}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%" |]
      in 
        List.iter print_record records;
  ]}
 
  @author  Serge Aleynikov (serge\@corp.idt.net)
  @version $Rev: 448 $ $Date: 2006-10-30 19:09:00 -0500 (Mon, 30 Oct 2006) $
  @see <http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14294/toc.htm> OCCI docs

*)

open Unix;

(*-------------------------------------------------------------------------*)
(** {3 Types and Exceptions}                                               *)
(*-------------------------------------------------------------------------*)

type ora_env            = 'a;
type ora_connection     = 'a;
type ora_statement      = 'a;
type cursor             = 'a;
type ora_value          = [ Null                 (* 0 *)
                          | Int   of int         (* 0 *)
                          | Date  of float       (* 1 *)
                          | Float of float       (* 2 *)  
                          | Str   of string      (* 3 *)
                          ];
(** This type is for use as a result of a query *)

type param_val          = [ (* For getting/passing a value of IN and IN/OUT parameter types *)
                            Null_int             (* 0 *)
                          | Null_date            (* 1 *)
                          | Null_float           (* 2 *)
                          | Null_str             (* 3 *)
                          | Var_int   of int     (* 0 *)
                          | Var_date  of float   (* 1 *)
                          | Var_float of float   (* 2 *)
                          | Var_str   of string  (* 3 *)
                            (* Parameter declaration types *)
                          | Var_out_int          (* 4 *)
                          | Var_out_date         (* 5 *)
                          | Var_out_float        (* 6 *)
                          | Var_out_str of int   (* 4 *)
                          ]; 
(** 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 DEFINITIONS                                              *)
(*-------------------------------------------------------------------------*)
 
(** Oracle API functions raise this exception containing 
    [(err_code * err_message)] *)
exception ORA_EXCEPTION of (int * string);
value _ = Callback.register_exception "ORA_EXCEPTION" (ORA_EXCEPTION (0, "")); 

(** [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. *)
exception ORA_BULK_EXCEPTION of (string * array (int * int * string));
value _ = Callback.register_exception "ORA_BULK_EXCEPTION" (ORA_BULK_EXCEPTION ("", [| |])); 

(*-------------------------------------------------------------------------*)
(** {3 Functional API}                                                     *)
(*-------------------------------------------------------------------------*)

(** [decode_occi_type column] used to convert the column type to string or 
    [ora_column_type] type *)
value decode_occi_type = fun
    [ 1   -> "VARCHAR2"                      (* (ORANET TYPE) character string *)
    | 2   -> "NUMBER"                          (* (ORANET TYPE) oracle numeric *)
    | 3   -> "INTEGER"                                (* (ORANET TYPE) integer *)
    | 4   -> "FLOAT"                    (* (ORANET TYPE) Floating point number *)
    | 5   -> "STRING"                                (* zero terminated string *)
    | 6   -> "VARNUM"                        (* NUM with preceding length byte *)
    | 8   -> "LONG"                                                    (* long *)
    | 9   -> "VARCHAR"                            (* Variable character string *)
    | 10  -> "SQLT_NON"                     (* Null/empty PCC Descriptor entry *)
    | 11  -> "ROWID"                                                  (* rowid *)
    | 12  -> "DATE"                                   (* date in oracle format *)
    | 15  -> "VARRAW"                                  (* binary in VCS format *)
    | 23  -> "RAW"                                      (* binary data(DTYBIN) *)
    | 24  -> "LONG RAW"                                         (* long binary *)
    | 94  -> "LONG VARCHAR"                             (* Longer longs (char) *)
    | 95  -> "LONG VARRAW"                               (* Longer long binary *)
    | 96  -> "CHAR"                                         (* Ansi fixed char *)
    | 104 -> "CHARZ"                                       (* rowid descriptor *)
    | 108 -> "NAMED DATA TYPE"                            (* named object type *)
    | 110 -> "REF"                                                 (* ref type *)
    | 112 -> "CLOB"                                           (* character lob *)
    | 113 -> "BLOB"                                              (* binary lob *)
    | 114 -> "BFILE"                                        (* binary file lob *)
    | 115 -> "CFILE"                                     (* character file lob *)
    | 187 -> "TIMESTAMP"                                          (* TIMESTAMP *)
    | 188 -> "TIMESTAMP WITH TIME ZONE"            (* TIMESTAMP WITH TIME ZONE *)
    | 189 -> "INTERVAL YEAR TO MONTH"                (* INTERVAL YEAR TO MONTH *)
    | 190 -> "INTERVAL DAY TO SECOND"                (* INTERVAL DAY TO SECOND *)
    | 232 -> "TIMESTAMP WITH LOCAL TIME ZONE"       (* TIMESTAMP WITH LOCAL TZ *)
    | n   -> raise (Failure (Printf.sprintf "Unsupported field type %d" n))
];

(*-------------------------------------------------------------------------*)
(** [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].

    {b Example:}
    {[value con = occi_connect "scott" "tiger" "mydb";]}
*)
external occi_connect: string -> string -> string -> ora_connection
      = "occi_connect";

(*-------------------------------------------------------------------------*)
(** [occi_disconnect connection -> unit] disconnects a previously open Oracle
    connection *)
external occi_disconnect: ora_connection -> unit
      = "occi_disconnect";

(*-------------------------------------------------------------------------*)
(** [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.

    {b Example:}
    {[value stmt = occi_create_statement con "";]}
*)
external occi_create_statement: ora_connection -> string -> ora_statement
      = "occi_create_statement";

(*-------------------------------------------------------------------------*)
(** [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 
*)
external occi_free_statement: ora_statement  -> unit
      = "occi_free_statement";

(*-------------------------------------------------------------------------*)
(** [occi_prepare_sql stmt sql] sets an SQL string for a given statement. *)
external occi_prepare_sql: ora_statement  -> string -> unit
      = "occi_prepare_sql";
      
(*-------------------------------------------------------------------------*)
(** [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].
    
    {b Example:}
    
    {[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];
    ]}
*)
external occi_prepare_plsql: ora_statement  -> string -> list (int * param_val) -> unit
      = "occi_prepare_plsql";
      
external occi_describe: ora_connection -> string -> array (string * int * int * int * bool) 
      = "occi_describe";

(*-------------------------------------------------------------------------*)
(** [ora_describe ora_connection table_name] descrites a table by returning an array of
    field descriptors.

    {b Example:}
    {[
      Array.iter 
        (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");
    ]}
*)
value ora_describe: ora_connection -> string -> array described_column = 
fun con table ->
    let arr = occi_describe con table in 
    Array.map
        (fun (n, t, w, s, u) -> 
            let c = decode_occi_type t in
            {name=n; ctype=c; len=w; scale=s; not_null=u})
        arr;
      
(*-------------------------------------------------------------------------*)
(** [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.

    {b Example:}  {[
      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;
    ]}
*)
external occi_execute: ora_statement  -> string -> list (int * param_val) -> list (int * ora_value)
      = "occi_execute";
      
(*-------------------------------------------------------------------------*)
(** [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.
    @raise End_of_file when there are no more rows in the cursor.
*)
external occi_fetch: cursor -> array ora_value
      = "occi_fetch";

(*-------------------------------------------------------------------------*)
(** [ora_fetch_foreach cursor f] applies the function [f: array ora_values -> unit] to 
    each record in the [cursor].
    @raise End_of_file when there are no more rows in the cursor.

    {b Example:}
    {[
      let (cursor, _) = occi_execute_query stmt "select * from test" in 
      try
        ora_fetch_foreach cursor (fun [| Int i; Str s |] -> Printf.printf "%d - %s\n" i s)
      with
        [ End_of_file -> () ]; ]}
*)
value ora_fetch_foreach: cursor -> (array ora_value -> unit) -> unit = 
fun rs f -> 
    let rec fetch = fun rs f -> do {
        let r = (occi_fetch rs) in f (r);
        fetch rs f
    } in
    fetch rs f;

(*-------------------------------------------------------------------------*)
(** [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.

    {b Example:} {[
      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;
    ]}
*)
value ora_fetch_foreach_list: 
    ?max_rows:option int -> cursor -> (list (array ora_value) -> bool) -> unit =
fun ?(max_rows = None) cur f -> 
    let rec fetch = fun cur recs n ->
        match max_rows with
        [ Some n -> if (f (List.rev recs)) then (fetch cur [] 0) else ()
        | None   -> try 
                        let r = occi_fetch cur in fetch cur [r :: recs] (n+1)
                    with
                        [End_of_file -> ignore (f (List.rev recs))]
        ]
    in 
        fetch cur [] 0;

(*-------------------------------------------------------------------------*)
(** [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].

    {b Example:} {[
       let (cursor, _) = occi_execute_query stmt "select * from test" in 
       let (_, data)   = ora_fetch_list cursor None in
         List.iter process_record data;  ]}
*)
value ora_fetch_list: ?max_rows:option int -> cursor ->
                         (option cursor * list (array ora_value)) = 
fun ?(max_rows = None) cur ->
    let rec fetch = fun cur recs n -> 
        match max_rows with
        [ Some n -> (Some cur, (List.rev recs))
        | None   -> try 
                        let r = occi_fetch cur in fetch cur [r :: recs] (n+1)
                    with
                        [End_of_file -> (None, (List.rev recs))]
        ]
    in 
        fetch cur [] 0;

(*-------------------------------------------------------------------------*)
(** [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.
    
    @return an opaque cursor object that can be fetched from using occi_fetch and an
            array of field names.
    @param  stmt Oracle statement obtained from occi_create_statement call.
    @param  sql  SQL select query.

    {b Example:}
    {[let (cursor, fields) = occi_execute_query "select * from test" in ...]}
*)
external occi_execute_query: ora_statement  -> string -> (cursor * array string)
      = "occi_execute_query";

(*-------------------------------------------------------------------------*)
(** [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. 
    
    {b Example:}
    {[
      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; ]}
*)
external occi_execute_param_query: ora_statement -> string -> array param_val -> 
         (cursor * array string)
      = "occi_execute_param_query";

(*-------------------------------------------------------------------------*)
(* Why didn't we put ?max_rows parameter after params array?
 * Since it's optional it can be omited, in which case the spec should 
 * include an additional 'unit' parameter at the end, which is not very 
 * convenient. This issue is commented here:
 * http://www.ocaml-tutorial.org/common_error_messages 
 * [Warning: This optional argument cannot be erased]
 *
 * Summary: avoid putting optional parameters at the end of parameter list 
 * in a function declaration.  Since they are named, they are position 
 * independent anyway. 
 *)

(** 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.
    
    @param max_rows defines the maximum number of rows to return ([None] means all, 
           which is the default).
    @param params 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".
    @return [(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.

    {b Example:} {[
        let (_, _, lst) = 
            ora_select stmt "select * from test where id < :1" [| Var_int 10 |])
        in
            List.iter print_record lst;
    ]}
*)
value ora_select: ora_statement -> string -> ?max_rows:option int -> array param_val -> 
                  (option cursor * array string * list (array ora_value)) = 
fun stmt sql ?(max_rows = None) params -> 
    let (cursor, fields) = occi_execute_param_query stmt sql params in
    let (cur,    lst)    = ora_fetch_list ~max_rows:max_rows cursor in
    (cur, fields, lst);

(*-------------------------------------------------------------------------*)
(** [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.

    @raise ORA_BULK_EXCEPTION  See example below on how to 
    retrieve the error codes for the failed rows.

    {b Example:} {[ 
       try
         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
          }
       ];
    ]}
*)
external occi_execute_array: ora_statement  -> string -> list (array param_val) -> int
      = "occi_execute_array";

(*-------------------------------------------------------------------------*)
(** [occi_execute_update stmt params -> num_rows] executes an INSERT/UPDATE/DELETE 
    operation on a previously bound statement

    {b Example:}

    {[
      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];
    ]}
*)
external occi_execute_update: ora_statement  -> array param_val -> int
      = "occi_execute_update";

(*-------------------------------------------------------------------------*)
(** [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 <#VALocci_prepare_plsql> occi_prepare_plsql for an example
*)
value occi_execute_plsql: ora_statement -> list (int * param_val) -> list (int * ora_value) =
fun stmt params ->
    occi_execute stmt "" params;

(*-------------------------------------------------------------------------*)
(** [occi_commit con] commit a transaction *)
external occi_commit: ora_connection -> unit
      = "occi_commit";

(*-------------------------------------------------------------------------*)
(** [occi_rollback con] rollback a transaction *)
external occi_rollback: ora_connection -> unit
      = "occi_rollback";

(*-------------------------------------------------------------------------*)
(** [date_to_tm float] convert a float date to a [Unix.tm] record.  
    @raise Failure for other argument types. 
*)
value date_to_tm = fun d -> Unix.localtime (d *. 86400.0 +. 0.5);

(*-------------------------------------------------------------------------*)
(** [decode_date d] convert a date to a tuple 
    [(year:int, mon:int, day:int, hour:int, min:int, sec:int)].
    @raise Failure for other argument types. 
*)
value decode_date =
    fun d -> let tm = date_to_tm d in 
             ((tm.tm_year+1900), (tm.tm_mon+1), tm.tm_mday, tm.tm_hour, tm.tm_min, tm.tm_sec);

(*-------------------------------------------------------------------------*)
(** [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].
*)
value encode_date = fun (year, mon, day, hour, min, sec) ->
    let (tm, _) = mktime {tm_sec=sec; tm_min=min; tm_hour=hour; 
                          tm_mday=day; tm_mon=mon-1; tm_year=year-1900; 
                          tm_wday=0; tm_yday=0; tm_isdst=False} 
    in tm /. 86400.0;
    
(*-------------------------------------------------------------------------*)
(** [date_to_string d] convert a date string in the "MM/DD/YYYY HH:MI:SS" format. 
    Failure is raised for other argument types. *)
value date_to_string = 
    fun d -> let (y, mon, day, h, min, s) = decode_date d in
             Printf.sprintf "%4d/%02d/%02d %02d:%02d:%02d" y mon day h min s;

(*-------------------------------------------------------------------------*)
(** [sysdate] returns the current local time encoded in the internal date format. *)
value sysdate: unit -> float = fun () -> Unix.time () /. 86400.;

(*-------------------------------------------------------------------------*)
(** [field_to_string fld] convert an ora_value to string.

    {b Example:}   {[ field_to_string(Date d) -> "2006/03/10 15:00:00" ]}
*)
value field_to_string fld = 
    match fld with
    [ Int   i -> string_of_int i
    | Str   s -> ("\"" ^ s ^ "\"")
    | Date  d -> ("\"" ^ (date_to_string d) ^ "\"")
    | Float f -> string_of_float f
    | _       -> "NULL"];

(*-------------------------------------------------------------------------*)
(** Print a date in the format returned by the [date_to_string] function   *)
value print_date d = Printf.printf "\"%s\"" (date_to_string d);
            
(*-------------------------------------------------------------------------*)
(** Print a field.  If [n] is greater than 0, a comma will be printed in 
    front of the field's value *)
value print_field n fld = do {
    if n > 0 then print_string ", " else print_string "  ";
    print_string (field_to_string fld);
    ()
};

(*-------------------------------------------------------------------------*)
(** Print a record containing [ora_value] fields separated by commas.      *)
value print_record = fun r -> do {
    Array.iteri print_field r;
    print_newline ()
};


(*-------------------------------------------------------------------------*)
(** {3 Object-Oriented API}                                                *)
(*-------------------------------------------------------------------------*)

(** This class implements DML and DDL Oracle operations on a given 
    connection descriptor.  It should not be instantiated using it's 
    own constructor, but rather via a [connection#stmt] call.  
*)
class statement ?(sql="") con =
object (self)
    value stmt = occi_create_statement con sql;
    value connection = con;
    
    (** Get a connection object that this statement is bound to. *)
    method con = connection;

    (** Execute a SELECT query that can contain bind variables.
        @see <Occi.html#VALora_select> [ora_select] for more info. *)
    method select ?(sql = "") ?(max_rows = None) params = 
        ora_select stmt sql params ~max_rows:max_rows;

    (** Execute a SELECT query that can contain bind variables.
        @return a list of records (up to [max_rows]). 
        @see <Occi.html#VALora_fetch_list> [ora_fetch_list] 
        for more info. *)
    method select_list ?(sql = "") ?(max_rows = None) params = 
        let (cursor, _) = occi_execute_param_query stmt sql params in
            ora_fetch_list ~max_rows:max_rows cursor;

    (** This method is used in combination with [select] when [max_rows]
        parameter is specified.  In this case the [cursor] parameter returned 
        by the [select] method can be fetched again to get the next [max_rows]
        rows as a list of records represented as arrays of fields.
        
        {b Example:} {[
          value stmt = con#stmt ~sql:"select * from test";
          let n = 10 in
          let (cursor, lst) = stmt#select ~max_rows:n in
            match cur with
            [ None     -> ()
            | Some cur -> (let rec f -> fun (c, l)  (ora_fetch_list cur ~max_rows:n)]
        ]}
    *)
    method fetch ?(max_rows = None) cursor =
        ora_fetch_list cursor ~max_rows:max_rows;

    (** Apply function [f] foreach record fetched from the given SQL select query 
        @see <Occi.html#VALora_fetch_foreach> [ora_fetch_foreach] for details.
    *)
    method foreach ?(params = [| |]) ?(sql = "") f = 
        let (cursor, _) = occi_execute_param_query stmt sql params in 
            try  ora_fetch_foreach cursor f
            with [End_of_file -> ()];

    (** Execute a PL/SQL block.
        @see <#Occi.html#VALocci_execute> [occi_execute] for details.
    *)
    method execute ?(sql = "") params = occi_execute stmt sql params;

    (** Execute a bulk DML INSERT / UPDATE / DELETE
        @see <Occi.html#VALocci_execute_array> [occi_execute_array] for details.
    *)
    method execute_array ?(sql = "") params = occi_execute_array stmt sql params;
    
    (** Assign an SQL string to the statement *)
    method prepare sql                = occi_prepare_sql stmt sql;
    (** Assign an PL/SQL block to the statement.  The block may contain bind variables *)
    method prepare_plsql ?(params = []) sql = occi_prepare_plsql stmt sql params;
    (** Use this method to run an SQL INSERT / UPDATE / DELETE statement *)
    method update ?(sql="")  params   = do {
        if sql != "" then self#prepare sql else ();
        occi_execute_update stmt params
    };
end;

class connection user pwd db = 
object
    value  con               = occi_connect user pwd db;
    method stmt ?(sql="") () = new statement con ~sql:sql;
    method commit            = occi_commit con;
    method rollback          = occi_rollback con;
    method describe tab      = ora_describe con tab;
    method disconnect        = occi_disconnect con;
end;