NAME

OCI*ML - Oracle database access commands for OCaml.

INTRODUCTION

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:

To use OCI*ML, that module should first be opened:

       open Ociml
       
The 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.

OCI*ML COMMANDS

oralogon connect-strlogon-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-handlestatement-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-handlecol-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-handlecol-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-handlecol-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-namecol-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.

ENVIRONMENT VARIABLES

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

FILES

.ocamlinit

Initialization file setting up the module in the custom toplevel

AUTHOR

Gaius Hammond <ociml@gaius.org.uk> June 12th 2011

ACKNOWLEDGEMENTS

With thanks to the OraTcl project