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:
Integer
corresponding to OCaml's int
Varchar
corresponding to OCaml's string
Datetime
corresponding to OCaml's Unix.tm
Number
corresponding to OCaml's float
Binary
for use with AQs of type RAW
RefCursor/Statement
for use with orabindout
to 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
ocimlsh
using the included .ocamlinit
configuration, for example:
$ make shell $ ./ocimlsh Objective Caml version 3.12.0 # let lda = oralogon "gaius/abc123";; ...The
Makefile
in 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. username/password@database
where database
is a valid entry in TNSNAMES.ORA
. If the environment variables ORACLE_SID
or 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 commits
and rollbacks
which an application may wish to see, along with a unique identifier (sequence number).
oralogoff logon-handle
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 INSERT
.
The statement handle is a record of type meta_statement
, which is a statement with associated metadata, including counters for parses
, binds
and 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;;
oraclose statement-handle
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 oraparse
, orabind
and 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 SELECT
, INSERT
, UPDATE
and DELETE
statements. Example below.
oraexec statement-handle
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;
The orafetch
and orafetchall
commands work identically in this mode as to SELECT
statements. This is also the method for OUT
parameters in PL/SQL stored procedures.
oracommit logon-handle
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.
oraroll logon-handle
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 ocimlsh
.
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
orabind
/oraexec
calls.
orafetch statement-handle → col-value array
Each call to orafetch
after a SELECT
statement is executed by oraexec
, orabindexec
or 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.map
or 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 NULL
or 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 oracols
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 oraenqueue
or oradequeue
functions.
oradebug [true|false]
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
orastring col-value
Converts any col-value type (Integer, Varchar, Datetime or Number) to a string.
oranullval col-value
Sets the value that will be substituted for NULL
in any results, when using orastring
e.g.:
# 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
oraautocom
does 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
RAW
is also supported for AQ, using the Binary
type, which is a string
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.
ORACLE_HOME
The location of the Oracle software. For example, the sqlplus
binary will be found at $ORACLE_HOME/bin
.
ORACLE_SID or TWO_TASK
The default Oracle server to connect to, as defined in TNSNAMES.ORA
.
LD_LIBRARY_PATH
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
TNS_ADMIN
Alternate location for the file TNSNAMES.ORA
, default is $ORACLE_HOME/network/admin
.ocamlinit
Initialization file setting up the module in the custom toplevel
<ociml@gaius.org.uk>
June 12th 2011