Add ANYDATA and XMLTYPE overloads to LOG and APPEND_PARAM procedures

[jborgesbarreto]

I believe this development is relevant to anyone that makes heavy usage of sql-types (or XMLTYPE).

The XMLTYPE overload would help mask the usage of .getClobVal or getStringVal, no need for the developer to be concerned with that just call LOGGER with the XMLTYPE variable you have in your program unit.

The ANYDATA overload is a little bit more tricky but with some very interesting uses like logging of an object type:

CREATE TYPE my_object AS OBJECT (my_field VARCHAR2 (20))
/
CREATE TYPE my_collection IS TABLE OF my_object
/
DECLARE
   o   my_object := my_object ('My object value');
BEGIN
   logger.LOG (p_text => 'This object''s contents can be logged with the XMLTYPE overload',
               p_extra => xmltype(o));

   logger.LOG (p_text => 'OR with the anydata overload',
               p_extra => anydata.convertobject (o));
END;
/

Or logging a collection in one single line without the need of a for loop:

DECLARE
   l   my_collection := NEW my_collection (my_object ('Collections'), my_object ('Rock'));
BEGIN
   logger.LOG (p_text => 'This collection''s rows can be logged easily with the ANYDATA overload',
               p_extra => ANYDATA.convertCollection (l));
   -- you could, of course, given that you have the XMLTYPE overload do this:
   -- logger.LOG (p_text => 'Chain a lot to be able to use the XMLTYPE overload',
   --             p_extra => XMLTYPE (ANYDATA.convertCollection (l)).getClobVal);
   -- but you don't want to be converting here (see below)
END;
/

The ANYDATA overload would also allow objects to be logged with a single call using ANYDATA.convertObject. You could argue that having the XMLTYPE overload would be enough except that:

CLEAR SCREEN
SET serveroutput ON
DECLARE
  o my_object := my_object ('My values');
  it INTEGER  := 100000;
BEGIN
  logger.time_start ('Converting object to XMLTYPE');
  FOR i IN 1 .. it
  LOOP
    DECLARE
      x xmltype;
    BEGIN
      x := xmltype (o);
    END;
  END LOOP;
  logger.time_stop ('Converting object to XMLTYPE' );
  ----------------------------------------------------------
  logger.time_start ('Converting object to ANYDATA');
  FOR i IN 1 .. it
  LOOP
    DECLARE
      a anydata;
    BEGIN
      a := anydata.convertobject (o);
    END;
  END LOOP;
  logger.time_stop ('Converting object to ANYDATA' );
END;
/

Converting object to XMLTYPE - 00:00:23.052524 Converting object to ANYDATA - 00:00:03.928571

In this case using the XMLTYPE overloads would bring performance to the ground because conversion would be always done before invoking LOGGER so even if NO_OP is on or that particular level is not being logged the conversion would happen.

[shawnweeks]

I just discovered this project and I've been working on a similar capability to use in our in house logging system. Currently we require the developers to implement logging stubs in their types. I'm little concerned that your test may make the conversions look a little worse than they are. You'd never want to call the convert individually on every element of a collection as your probably doing a context switch between PL/SQL and C every time as both the XML and AnyData libraries are not written in PL/SQL. As far as flexibility AnyData rules out though because you can natively convert collections.

create or replace type cl_strings as table of varchar2(4000 char);
/

create or replace type cl_strings_obj as object( strings cl_strings );
/

declare  
    v_values        cl_strings;
    v_values_obj    cl_strings_obj;
    v_xml           xmltype;
    v_anydata       anydata;
    v_start_time    timestamp;
    v_end_time      timestamp;
begin
    select 'TEST_VALUE' bulk collect into v_values from dual connect by level <= 1000000;    
    v_values_obj := cl_strings_obj(v_values);
    --
    v_start_time := systimestamp;
    v_xml := xmltype(v_values_obj);   
    v_end_time := systimestamp;
    dbms_output.put_line( 'XML Conversion Time          ' || to_char( v_end_time - v_start_time ) );
    ----------------------------------------------------------
    v_start_time := systimestamp;
    v_anydata := anydata.convertobject(v_values_obj);
    v_end_time := systimestamp;
    dbms_output.put_line( 'AnyData Conversion Time      ' || to_char( v_end_time - v_start_time ) );
end;
/

XML Conversion Time          +000000000 00:00:02.578250000
AnyData Conversion Time      +000000000 00:00:00.536647000

In my test of converting a million element collection I got pretty decent results with AnyData winning hands down. If your particularly worried about performance I'd start questioning how big of an object your passing around.