Automic Workload Automation

Greetings.

UC4 stores some object data as some form of tagged text (something that could be XML, not sure) in Oracle BLOBs, chiefly the field OX.OX_CONTENT. These BLOBs are notoriously hard to work with on the command line. For example, if you want run a report over all the hosts used in your CONN objects, or the properties of all your SAP jobs, you're SOL with plain simple SQL select statements.

The following is a case study about processing these silly things. It's full of syntax examples, maybe it'll help someone who has the same task.

A while ago already, I managed to select objects by criteria taken from a text search through that BLOB, like so:

select OH_NAME, OH_IDNR from OH where OH_IDNR in (select OX_OH_IDNR from OX where dbms_lob.instr (OX_CONTENT, utl_raw.cast_to_raw('search_text'),1,1) > 0);

This will find all objects in your UC4 system that have a certain search_text in OX_CONTENT, e.g. for finding all SAP jobs that have certain statements on their SAP card.

Now what if you want to print the content of OX.OX_CONTENT entirely? After a few hours of trial and error, I turned to our fantastic DBA (many thanks, fantastic DBA!), who thought up and installed the following stored procedure:

connect / as sysdba
create or replace function blob_to_clob (l_blob_in in blob) return clob is
  l_clob_result clob := 'X';
  l_dest_offsset number := 1;
  l_src_offsset number := 1;
  l_lang_context number := dbms_lob.default_lang_ctx;
  l_warning number;

begin
  if l_blob_in is not null and dbms_lob.getlength (l_blob_in) > 0 then
    dbms_lob.converttoclob (dest_lob => l_clob_result,
                            src_blob => l_blob_in,
                            amount => dbms_lob.lobmaxsize,
                            dest_offset => l_dest_offsset,
                            src_offset => l_src_offsset,
                            blob_csid => dbms_lob.default_csid,
                            lang_context => l_lang_context,
                            warning => l_warning);

    return (l_clob_result);
  else
    return (null);
  end if;

Once you get your DBA to install this procedure (and if you do, send our fantastic DBA a beer or something), you can then do "simple" SQL queries such as:

select dbms_lob.getlength (ox_content), blob_to_clob (ox_content) from ox;

or, somewhat more practical, to get the contents only for certain object types, e.g. CONNECTION objects, and not get the versioned, deleted objects:

select blob_to_clob (ox_content) from ox where ox_oh_idnr in (select OH_IDNR from oh where OH_OTYPE='CONN' and OH_DELETEFLAG = 0);

You can then proceed to search this output further with e.g. shell script or python or something (unless you're an Oracle deity, then you can probably do this with Oracle functions, too). If you go the external tools route, I strongly suggest you do this with Oracle SQL developer, then export your results as text, because the broken formating of sqlplus will drive anyone insane.

So export it as text, and eventhough it looks like text now, you'll find that for no good reason, it still has some binary content in it:

$ dos2unix conn.txt
dos2unix: Binary symbol found at line 5
dos2unix: Skipping binary file conn.txt

Geez, thanks!

This then, for example, removes all the binary junk that, as far as I can tell, was useless appendixes to tags anyway, miss-handled Umlauts, and it also removes the extra quotation marks at beginning and end:

cat conn.txt | sed 's/$/__LINEBREAK__/g' | tr -d "[:cntrl:]" | sed 's/__LINEBREAK__/\n/g' | sed 's/^"//g' | sed 's/"$//g'

Now you have plain text that somehow resembles XML, but can still not be treated as XML (at least xml2 claims it's invalid, probably because it lacks proper headers and stuff).

But at least now it can be parsed with a scripting language or shell utilities.

I wonder if Automic considers this form of storage as some form of copy protection, given how difficult it is to get to this stuff