OCI*ML is an extension to the OCaml language designed to provide access to an Oracle Database server. Its interface is closely based on OraTcl which provides the same functionality for the Tcl/Tk language, although they have no code in common, and not all OraTcl functionality is implemented (yet). The name follows the Oracle convention (e.g. SQL*Plus, Pro*C) as it is implemented using OCI, the Oracle Call Interface. Programmers using OCI*ML should be familiar with basic concepts of database programming, such as connections, cursors and result sets. This manpage documents version 0.3.
At present, 4 major datatypes are supported, and two special types:
Integercorresponding to OCaml's
Varcharcorresponding to OCaml's
Datetimecorresponding to OCaml's
Numbercorresponding to OCaml's
Binaryfor use with AQs of type
RefCursor/Statementfor use with
orabindoutto access ref cursors returned from stored procedures.
To use OCI*ML, that module should first be opened:
open OcimlThe easiest way to explore the module is via the custom toplevel
ocimlshusing the included
.ocamlinitconfiguration, for example:
$ make shell $ ./ocimlsh Objective Caml version 3.12.0 # let lda = oralogon "gaius/abc123";; ...The
Makefilein the distribution can be modified for Makefiles for new applications.
oralogon connect-str → logon-handle
Connects to an Oracle server using the connect string, in the same format as expected by SQL*Plus, e.g.
database is a valid entry in
TNSNAMES.ORA. If the environment variables
TWO_TASK are set, then a connect string of just
username/password can be used. If the password is invalid, an exception of type
Oci_exception will be thrown. This contains an error code (1017 in this case) and an descriptive string. In general, all error handling is in the form of exceptions originating in the OCI layer, using familiar ORA codes, as per SQL*Plus and other standard Oracle tools.
This returns a record encapsulating a connection to Oracle, which should be used in subsequent commands. This can be referred to as a connection, or using old OCI terminology an lda, which means logon data area. Within OCI*ML code it is a
meta_handle, which means a handle with associated metadata, such as counters for
rollbacks which an application may wish to see, along with a unique identifier (sequence number).
Disconnects from the Oracle database and frees any allocated memory in the OCI library. Implicitly commits any outstanding work.
oraopen logon-handle → statement-handle
Opens and returns a statement handle, sometimes referred to as a cursor. This statement handle should be used in subsequent operations that execute queries or DML on the database. Multiple statement handles can be opened allowing different operations to be in flight at the same time (e.g. one doing
SELECT and another
The statement handle is a record of type
meta_statement, which is a statement with associated metadata, including counters for
execs, along with a sequence number (independant of logon handles), and a timer for the last operation. Other attributes of the record are for internal use.
# let sth = oraopen lda;;
Closes an open statement handle/cursor, but the connection to Oracle remains open. Outstanding transactions remain outstanding; committing and rolling back is done at the connection level. If this is not the desired behavior, multiple connections to the same server should be opened.
oraparse statement-handle statement-text
Parses SQL optionally using bind variables using the previously opened statement handle. Bind variables are "placeholders" prefixed by
: which are substituted prior to SQL execution, in a process called binding. This allows a parsed SQL statement to be re-used, saving on the computation of repeated parsing, and defending against SQL injection attacks. A full example of
oraexec is below.
orabind statement-handle position value
Binds values into a parsed SQL statement handle, by name or by position. Once a statement has been parsed once, it can be bound and executed as many times as is necessary. Binding is suitable for
DELETE statements. Example below.
Executes a previously parsed and if necessary bound SQL statement. For example (from
ociml_sample.ml included with the distribution), to bind a row using the position technique, execute it, then bind another row by name:
oraparse sth "insert into ociml_test values (:myint, :mydate, :mystring, :myfloat)"; orabind sth (Pos 1) (Integer 1); orabind sth (Pos 2) (Datetime (localtime (time ()))); orabind sth (Pos 3) (Varchar "PI"); orabind sth (Pos 4) (Number 3.142); oraexec sth; orabind sth (Name "myint") (Integer 2); orabind sth (Name ":mydate") (Datetime (localtime 0.0)); orabind sth (Name "mystring") (Varchar "e"); orabind sth (Name ":myfloat") (Number 2.718); oraexec sth;Missing colons are inserted automagically. This also illustrates the four currently supported datatypes.
orabindout statement-handle position dummyvalue
Binds placeholder values into a parsed SQL statement handle, by position only, for use with the
RETURNING clause. These are so OCI*ML can correctly allocate and convert the types in OCI, e.g.
oraparse sth "insert into datetab (c1) values (sysdate + 10) returning c1 into :date1"; let d = localtime 0.0; orabindout sth (Pos 1) (Datetime d); oraexec sth; orafetchall sth;
orafetchall commands work identically in this mode as to
SELECT statements. This is also the method for
OUT parameters in PL/SQL stored procedures.
Commits any outstanding work on all the statement handles associated with this connection. Finer grained transaction control requires opening more connections to the same database, and creating statement handles from them.
Rolls back any outstanding work on all the statement handles associated with this connection.
orasql statement-handle statement-text
This is a convenience function that does
oraparse on the SQL text then immediately
oraexec. It is not advised to build SQL statements as strings and use this function (use bind variables instead). But it is useful for SQL statements fixed at compile time, or for interactive use in
orabindexec col-value array list
A convenience function to bind an entire row to a previously parsed statement handle and execute it, e.g:
let my_constants = [ [|(Integer 3); (Datetime (localtime (time ()))); (Varchar "Square root 2"); (Number 1.41)|]; [|(Integer 4); (Datetime (localtime (time ()))); (Varchar "Speed of light"); (Number 300000000.)|]; [|(Integer 5); (Datetime (localtime (time ()))); (Varchar "Acceleration of gravity"); (Number 9.8)|]] in orabindexec sth my_constants;The function is implemented as bulk DML and can be over 100× faster than repeated individual
orafetch statement-handle → col-value array
Each call to
orafetch after a
SELECT statement is executed by
orasql will return an array of column value types (Integer, Varchar, Number or Datetime) corresponding to one row of the result set. At the end of the result set, the exception
Not_found is thrown, mimicing the
%NOTFOUND construct in PL/SQL. E.g:
# orasql sth "select * from ociml_test";; # orafetch sth;; ... # orafetch sth;; Exception: Not_found.
orafetchall statement-handle → col-value array list
Convenience function to
orafetch an entire result set and return it as a list of arrays of column values. This can be processed with
List.iter. It is most useful to abstract away the exception handling, for result sets that can easily fit into memory. In the future this will most likely become true bulk DML, however the interface will not change.
oraprefetch statement-handle rows
Sets the number of rows to pre-fetch in
SELECT statements. This has the potential to significantly reduce the number of round-trips required e.g. to
orafetchall. Preliminary tests suggest that a 20× speed up is possible.
oracols statement-handle → col-type array
Returns an array containing the column metadata for the last executed
SELECT statement on a given statement handle, e.g:
# oracols sth;; - : Ociml.stringable array = [|Ociml.Col_type ("CONSTANT_ID", 2, 22, true, false); Ociml.Col_type ("DATE_ENTERED", 12, 7, false, true); Ociml.Col_type ("CONSTANT_NAME", 1, 80, false, true); Ociml.Col_type ("CONST_VALUE", 2, 22, false, true)|]The tuples consist of the column name, its type (as defined in
oci.h, its size, whether it is an integer or not, and whether it accepts
NULLor not. This is is likely to change in a future version.
oradesc logon-handle table-name → col-type array
Describes a table, returning the same format as
oraautocom logon-handle [true|false]
Sets a mode in which DML statements are committed as soon as they are executed. This does not affect the
Enables or disables a verbose logging mode in the OCI*ML module. This is sent to STDERR so it can easily be redirected in an application that has this mode enabled, for example:
$ ./myapp 2>/var/tmp/myapp.log
Converts any col-value type (Integer, Varchar, Datetime or Number) to a string.
Sets the value that will be substituted for
NULL in any results, when using
# oranullval (Integer 0);;or
# oranullval (Varchar "NULL");;
oraenqueue logon-handle queue-name queue-type col-value array
Enqueues a message to the AQ mechanism. The type must match the type of the underlying queue payload type. Must be committed to actually submit the message, e.g.
# open Ociml;; # let lda = oralogon "gaius/abc123";; # oraenqueue lda "message_queue" "message_t" [|Integer 99; Varchar "hello, world!"|];; # oracommit lda;;Note that
oraautocomdoes not affect this command or
oradequeue. Also see the Oracle AQ documentation.
oradequeue logon-handle queue-name queue-type col-value array → col-value array
Dequeues a message from the AQ mechanism. Must be committed to actually remove the message from the queue, e.g.
# oradequeue lda "message_queue" "message_t" [|Integer 0; Varchar ""|];; - : Ociml.col_value array = [|Integer 99; Varchar "hello, world!"|] # oracommit;;The array passed to this command is a dummy with placeholders for the appropriate types so that OCI*ML can do the conversion from the underlying C struct. Type
RAWis also supported for AQ, using the
Binarytype, which is a
oradeqtime logon-handle time
Sets the time in seconds to wait for a message dequeue. Exception
Not_found will be raised if no message was received.
oraldalist unit → logon-handle list
Returns a list of all open logon handles (connections).
orasthlist logon-handle → statement-handle list
Returns a list of all open statement handles (cursors) associated with a logon handle.
oci_version unit → (int, int)
Returns a tuple of (major version, minor version) that this OCI*ML was built with.
The location of the Oracle software. For example, the
sqlplus binary will be found at
ORACLE_SID or TWO_TASK
The default Oracle server to connect to, as defined in
Where the system searches for shared objects to load. It must include
$ORACLE_HOME/lib but may include other directories too. The way to set it if OCI*ML reports
libclntsh.so cannot be found, preserving what it is previously set to (if anything) is:
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
Alternate location for the file
TNSNAMES.ORA, default is
Initialization file setting up the module in the custom toplevel
<email@example.com>June 12th 2011