DBMS_XMLGEN and bug

Last 2 weeks I had to deal with a post-upgrade problem (9.2.0.5 to 10.2.0.4) on a system which is not well tested before upgrade.  The issue was error stack below

ORA-06502: PL/SQL: numeric or value error:invalid LOB locator specified: ORA-22275

or for some records

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

Developer was insisting that this is a character set issue because of the first line of the error stack and I was saying it is impossible because both servers were UTF8 and only difference is NLS_NCHAR_CHARACTERSET which is never used anyway.  Because developer already gave the last decision, it was my turn to prove that this wasnt a characterset issue.

It took 2 weeks to understand the real problem (My development skills are not as good as my DBA skills and I did not get any proper help from development that’s why it took longer than it needs:) ) .  Basically application gets data from database converts it to XML (by DBMS_XMLGen) and writes it to a file (by DBMS_LOB).   We tried every possible solution we found from google and metaling for the error stacks and finally we understand that XML generation was the problem.  DBMS_XMLGEN.GETXML was not generating anything for some records but it was generating in 9i.  To be honest The error stack (was using  DBMS_UTILITY.FORMAT_ERROR_STACK and  DBMS_UTILITY.format_error_backtrace)   was not very clear  to me thats why I spent too much time with other things.
According to the bug numbers below DBMS_XMLGEN.GETXML is not working same in 10R2  as it is working on 9i. The way it handles nulls and special characters is buggy. Workaround it using DBMS_XMLQUERY instead.

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR

Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2

Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR

Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2

Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

This is the demo to show the different behaviour (taken from metalink ).

in 10G

SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

in 9i

SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-24347: Warning of a NULL column in an aggregate function

no rows selected

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
--------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

Lessons Learned.

1-Do not trust “we tested its ok”  and Try to understand the application and try to ask developers what packages they are using so you can search for possible bugs before you upgrade the system.

2-After 1 day of struggling, Search metalink first  for a possible bug.  Stop messing around google