Return of a blogger (plus a bit of ORA-7445 [LpxMemFree()+200] )

Considering that "conference life" is picking up (two in-person events in June!), it is time to start writing something meaningful (yes, I am back to blogging after years of ignorance)! 

To cover event previews I need a bit more prep time, while today I would like to share a small story (narrow, but very practical!)

At Dulcian we have a couple of systems still on Oracle 11g (yeah, I understand that it is outdated). And today one of my support people started to complain about ORA-07445. To be precise - ORA-7445  [LpxMemFree()+200]. And you know how much fun is to sort out those errors, especially considering my gut feeling ("I bet, XMLtype is misbehaving!")

Obviously, first two steps were obvious: Google and MOS. 

Oook... Google gave me a direct hit - and I indeed was right, it was XML parsing issue - Doc ID 2220757.1 

And in case you don't have MOS access - here is a code snippet to replicate (it should blow up with "end of communication channel" plus corresponding trace file):

 declare
  lv_text varchar2(6000);
  lv_temp_xml xmltype;
  lv_len number := 0;
begin
  lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
  || to_char(sysdate, 'dd-Mon-yy hh24:mi')
  || ' '
  ||lpad('A',4033,'A')
  || '" />';
  lv_len := length(lv_text);
  dbms_output.put_line('Length of the 3rd run is '||lv_len);
  lv_temp_xml := sys.xmltype(lv_text);
 end;

The proposed solution was to enable some kind of "old parser" - you can do it on either session of system level. Considering that everything else in our system worked fine, I added it via Dynamic SQL just before the problematic code, so it will be less overall system impact:

 declare
  lv_text varchar2(6000);
  lv_temp_xml xmltype;
  lv_len number := 0;
begin
  execute immediate 'alter session set events ''31156 trace name context forever, level 1024''';
  lv_text := '<LPCNotes Subject="Comments for LPC " Text="'
  || to_char(sysdate, 'dd-Mon-yy hh24:mi')
  || ' '
  ||lpad('A',4033,'A')
  || '" />';
  lv_len := length(lv_text);
  dbms_output.put_line('Length of the 3rd run is '||lv_len);
  lv_temp_xml := sys.xmltype(lv_text);
 end;

And the whole problem was gone! After a bit more digging I found that this kind of approach could help with a number of other similar ORA-07445 errors if you still on 11.2.0.4 (here is a doc from MOS: Bug 14822091)

Hope, it helps!

P.s. Footprint: in the doc they use hexadecimal level 0x400, but I prefer to use decimals (1024) for readability. What is funny, in the bug description whoever wrote it also used decimal - so, don't worry, it is the same stuff written differently.