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:

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.

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 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: 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: 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 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 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(s): Serge Aleynikov (serge\@corp.idt.net)
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

Parameters:

?? : 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.

Example:

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.

Example:

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.

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];

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.

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");

Parameters:

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.

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;

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.

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 -> () ];

Parameters:

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.

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;

Parameters:

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.

Example:

       let (cursor, _) = occi_execute_query stmt "select * from test" in 

       let (_, data)   = ora_fetch_list cursor None in

         List.iter process_record data;

Parameters:

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.

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;

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.

Example:

        let (_, _, lst) = 

            ora_select stmt "select * from test where id < :1" [| Var_int 10 |])

        in

            List.iter print_record lst;

Parameters:

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.

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

          }

       ];

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

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];

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

Parameters:

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 -> Unix.tm

date_to_tm float convert a float date to a Unix.tm record.
Raises Failure for other argument types.

Parameters:

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.

Parameters:

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.

Parameters:

(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.

Parameters:

d : float

val sysdate : unit -> float

sysdate returns the current local time encoded in the internal date format.

Parameters:

() : unit

val field_to_string : ora_value -> string

field_to_string fld convert an ora_value to string.

Example:

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

Parameters:

fld : ora_value

val print_date : float -> unit

Print a date in the format returned by the date_to_string function

Parameters:

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

Parameters:

n	:	`int`
fld	:	`ora_value`

val print_record : ora_value array -> unit

Print a record containing ora_value fields separated by commas.

Parameters:

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