I was faced with a catch-all logging database table. It was used to save off some transactions with other systems that are passed via XML, saved to a directory with some other files for processing. The database saved time-stamps and some other metadata, but I needed to search it by looking for some specific values saved within the XML to do some bug-hunting during some specific transactions. As I got to looking at the different ways I could do this, I discovered that oracle has some great options for XML parsing, exposing the power of Xpath for use within queries.
The problem was, I kept running into some errors. Specifically this one:
ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00230: invalid character 0 (U+0000) found in a Name or Nmtoken Error at line 2 ORA-06512: at "SYS.XMLTYPE", line 254 ORA-06512: at line 1
Doesn’t look too complicated, it’s finding a NULL character somewhere in the XML where I attempt to create XMLType from the CLOB to in order to use my xpath query. So, I tried replace(myxmlcolumn,CHR(0),”) to simply remove the NULL. I still got the error– strange indeed! So, I tried replacing it with an ‘H’, just in case Oracle was being non-ANSI by treating (in my replace) the empty string as a NULL.
At this point, I’m lost. Research reveals a few vague hints at what could be going wrong, but interestingly there are a few hints out there about this being a bug. I gave up and turned to trusty Stack Overflow and posted my situation. After a few days, and no answer– I posted a bounty on the question to up the incentive factor. These guys would help me out for sure.
But no. The bounty expired, and the closest I got was instructions to go search Oracle Support for a bug. No here’s where I have a problem– you must FIRST PAY to use the support site. I understand and respect this business model if I required resources from Oracle. However, I’m simply looking for verification that I’m dealing with a _known bug_ in the software, and if I am I should be able get some more details what exactly the bug is, so that I can be sure to avoid it. I’m sure the fortune 50 company I work for has the 5-figure support contract, but I’m also sure it would take me weeks to go through the bureaucracy to get what I need.
So let’s get this straight, the software has been paid for, the license has been purchased, and my company even has a support contract, all to the tune of millions of dolars. But for a simple contractor I still cannot use the product as the available documentation says I can because of a known bug. I cannot even work around the bug with any certainty without first paying. Worse yet, nobody can even tell me the details for fear of retribution from the company.
I hope someone will look at the business strategy here, because I’m at a bit of a loss. As a developer just starting to experiment with Oracle databases, I have been given roadblocks. I respect the model of paid support, but when there is a know bug, or a configuration that should be avoided, that crosses the line from support to documentation.
To the problem at hand, I simply gave up. CLOBs offer some nice text proccessing, but it is exposed via the Oracle supplied package DBMS_LOB. The answer for me was to avoid any chance of the bug and sacrifice the gains from using a true xml language tool and just treat it as unstructured text. This is a pretty fragile fix that Oracle has forced me into, but I have work to do. I got lucky and the XML doesn’t change too much, and I can grab the first of the elements with the value I’m looking to use later in my query:
Select dbms_lob.substr(myxmlcolumn, 10, dbms_lob.instr(myxmlcolumn,'<data>') + 6)
Ended up looking something like this (I’m taking out the \n to make it look better in a report)
Select * from(
select dbms_lob.substr(message, 10, dbms_lob.instr(message,'<ASDFNumber>') + 12) AS ASDF_NUM,
PROCESSIND,CREATEDT,LASTUPDATEDT,
replace(replace(ERRORDES,CHR(10),'--'), CHR(13), '') as Error_Description,
replace(replace(MESSAGE,CHR(10),'--'), CHR(13), '') as Message
from myschema.mytable
WHERE internalserviceid= 'some stuff I wanted to query for'
and CREATEDT > TO_DATE( sysdate -3) -- to_date('4/25/2012 04:30:00 pM', 'mm/dd/yyyy HH:MI:SS AM')
) WHERE ASDF_NUM in(
'1234123410',
'1234123411',
'1234123412')
order by ASDF_NUM,CreateDT;

Recent Comments