A few more additions to OCI*ML:
- Support for ref cursors and binding OUT variables by name as well as by position (see below)
- Support for type
RAWin AQ (see below)
oci_versionfunction returns a tuple of (major version, minor version) of the OCI that OCI*ML was built against (equivalent to OraTcl’s
$ ./ocimlsh Objective Caml version 3.11.2 not connected > oci_version ();; - : int * int = (11, 2)
This is an enabler for eventually incorporating 11g-specific features, such as session pools, while remaining compatible with 10g, which I will need to do anyway for my own work for the foreseeable future.
orasthlist logon-handlereturning a list of all open connections, and all open cursors (statement handles) on a given connection. This enables applications that wish iterate over many different databases, e.g. for monitoring purposes. This is another feature from OraTcl.
- Tidied up
A ref cursor is a technique for returning a multi-row result set from a stored procedure. If a view is a stored
SELECT statement, a ref cursor can be thought of as a view based on PL/SQL, with the
WHERE predicates determined programmatically at runtime. Using the following very simple stored procedure just to illustrate the feature:
create or replace package pkg_ref_cursor as type t_cursor is ref cursor; procedure pr_ref_cursor (p_refcur out t_cursor); end pkg_ref_cursor; / create or replace package body pkg_ref_cursor as procedure pr_ref_cursor (p_refcur out t_cursor) is v_cursor t_cursor; begin open v_cursor for select * from ociml_test; p_refcur := v_cursor; end pr_ref_cursor; end pkg_ref_cursor; /
The code to access this cursor and display it using a modified qq is as follows:
let lda = oralogon "gaius/abc123" in let sth = oraopen lda in oraparse sth "begin pkg_ref_cursor.pr_ref_cursor(:cur); end;"; orabindout sth (Name "cur") RefCursor; oraexec sth; let row = orafetch sth in let rc = row.(0) in match rc with |Statement s -> let r = new report (Array.map orastring (oracols s)) in begin try while true do let row = Array.map orastring (orafetch s) in r#add_row row; done; with |Not_found -> (); end; r#print_report () |_ -> ()
Note that the placeholder is of type
RefCursor, but when the statement is executed, the return type is a
Statement, the same as
sth once extracted from its type wrapper, and
orafetch works identically. This should be straightforward enough to modify to handle nested tables as well. But I work with 500+ line PL/SQL stored procedures returning ref cursors, and I don’t think I’ve ever actually used a nested table myself…
Another feature of Oracle AQ is the ability to handle raw payloads for unstructured data, or if you prefer, data whose structure is opaque to the database, e.g. serialized objects. I can think of nothing more unstructured than my friend Abby’s stupid cat, Lynx.
begin dbms_aqadm.create_queue_table (queue_table => 'tbl_image_queue', queue_payload_type => 'RAW'); dbms_aqadm.create_queue (queue_name => 'image_queue', queue_table => 'tbl_image_queue'); dbms_aqadm.start_queue (queue_name => 'image_queue'); end; /
Long-suffering Lynx can now be picked up by the scruff of his neck, stuffed into the queue, then extracted again:
(* code from PLEAC http://pleac.sourceforge.net/pleac_ocaml/filecontents.html *) let slurp_channel channel = let buffer_size = 4096 in let buffer = Buffer.create buffer_size in let string = String.create buffer_size in let chars_read = ref 1 in while !chars_read <> 0 do chars_read := input channel string 0 buffer_size; Buffer.add_substring buffer string 0 !chars_read done; Buffer.contents buffer let slurp_file filename = let channel = open_in_bin filename in let result = try slurp_channel channel with e -> close_in channel; raise e in close_in channel; result let cat = slurp_file "lynx.jpg";; (* OCI*ML code *) let lda = oralogon "gaius/abc123";; oraenqueue lda "image_queue" "RAW" [|Binary cat|];; oracommit lda;; (* a 1-element array of Col_value *) let newcat = oradequeue lda "image_queue" "RAW" [|Binary ""|];; oracommit lda;; let f = open_out_bin "lynx2.jpg" in match newcat.(0) with |Binary b -> output_string f b |_ -> prerr_endline "Cat-astrophe!"
Checking that his DNA is alright:
$ md5sum lynx* 588b7cf16ad98905b17ba27def23c0a7 lynx2.jpg 588b7cf16ad98905b17ba27def23c0a7 lynx.jpg
Now we have two Lynxes… Oh no! Unlike C, OCaml strings can handle the full 8-bits, so for convenience in OCaml, that is what the binary data is stored in. This may be the first time in history that a cat has been successfully teleported via an enterprise message bus†, and if works on him it will work on any kind of data…
With these I am advancing the version to 0.3. The next features will probably be BLOBs and an Oracaml compatibility layer, not least so I can run my own “legacy” code! And also, more advanced AQ features such as accessing and manipulating the message metadata.
† no cats were harmed in the development of this code.