OCI*ML: Ref Cursors and Raw Messages

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 RAW in AQ (see below)
• oci_version function returns a tuple of (major version, minor version) of the OCI that OCI*ML was built against (equivalent to OraTcl’s orainfo version):

  $ ./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.

• oraldalist and orasthlist logon-handle returning 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 .ocamlinit.

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.

Hello!

Using the following queue structure:

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.

About these ads