OCI*ML: Support for RETURNING

First a quick recap for non-DBAs: in database programming it is a common, nearly universal, pattern to assign a unique identifier to each row of the database independent of the data actually stored in the row – this is called the primary key. These are use partly to identify the row in the case of a duplicate (e.g. two customers named Gaius) and partly to determine relationships between tables in a hierarchical, parent-child structure, also called a master-detail structure. Keys are usually generated using a database feature called a sequence, a function which returns a monotonically increasing integer number – having a single source for new identifiers means there is no danger with duplicate keys.

Consider a table called CUSTOMER, with one row per customer, and another called CUSTOMER_ORDERS, with one row per order that the customer has placed, as follows:

create table customer (id integer primary key, customer_name varchar(80));

create table customer_orders (id integer primary key,        
customer_id integer,
ordered_item varchar2(80));

alter table customer_orders add constraint fk_customer foreign key (customer_id) references customer(id);

create sequence customer_seq;
create sequence customer_order_seq;

To register a customer and place a new order the sequence of events would be:

1. Insert a record into CUSTOMER, optionally COMMIT
2. Find the ID that we have just inserted from customer_seq
3. Do any subsequent processing
4. Insert records into CUSTOMER_ORDERS using an ID from customer_order_seq for its primary key, and the ID from (2) for customer_id
5. Finally COMMIT the entire transaction

Oracle supports this directly with the RETURNING clause to DML statements (INSERT, UPDATE and DELETE), and I have now added support for that to OCI*ML with the orabindout keyword. This takes a placeholder for the type, similar to the AQ keywords.

open Ociml

let () = 
  let lda = oralogon "gaius/abc123" in
  let sth = oraopen lda in 
  
  oraparse sth "insert into customer (id, customer_name) values (customer_seq.nextval, :name) returning id into :id";
  orabind sth (Pos 1) (Varchar "Gaius");
  orabindout sth (Pos 2) (Integer 0);
  oraexec sth;
  
  let customer_id = (orafetch sth).(0) in
  
  oraparse sth "insert into customer_orders (id, customer_id, ordered_item) values (customer_order_seq.nextval, :id, :item)";
  orabind sth (Pos 1) customer_id;
  orabind sth (Pos 2) (Varchar "ML for the Working Programmer");
  oraexec sth;
  
  oracommit lda;

The same technique can be used for PL/SQL stored procedures:

create or replace procedure get_customer(cust_id in integer, cust_name out varchar2) as
begin
    select customer_name into cust_name from customer where id=cust_id;
end;
/

And then, in an interactive ocimlsh session:

connected to gaius@xe > oraparse sth "begin get_customer(:id, :name); end;";;
- : unit = ()
connected to gaius@xe > orabind sth (Pos 1) (Integer 1);;
- : unit = ()
connected to gaius@xe > orabindout sth (Pos 2) (Varchar "");;
- : unit = ()
connected to gaius@xe > oraexec sth;;
- : unit = ()
connected to gaius@xe > orafetchall sth;;
- : Ociml.col_value array list = [[|Varchar "Gaius"|]]

The RETURNING clause is implemented in the underlying OCI as a single instruction, so it requires only a single round trip for an INSERT followed by a SELECT. In the situation in which we wished to COMMIT the parent record anyway (e.g. to register a new customer who has not yet placed any orders) and auto-commit is on, we have reduced three network roundtrips to only one, potentially very significant when running over WAN links. In the case of an UPDATE statement processing many rows, a multi-row result set will be returned. And combining DELETE and RETURNING it is possible to implement a “destructive select”…