Workaround for ORA-24347: Warning of a NULL column in an aggregate function

Oracle 10g R2
ORA-24347: Warning of a NULL column in anaggregate function

I encountered this error while using DBMS_XMLGEN to generate someXML output. And if you're reading this, it's most likely because you encountering the same problem.

Well, it turns out there is an Oracle bug (#5911073,consult metalink) that throws this error when an aggregate function (such as SUM, COUNT or AVG) receives a NULL input value and is used in conjunction with DBMS_XMLGEN.

The following script demonstrates the problem:

dayneo@SANDBOX> create table test_tbl(
 2    group_id  number,
 3    num_value number
 4  )
 5  /
Table created.
dayneo@SANDBOX> begin
 2    insert into test_tbl values(0, 1);
 3    insert into test_tbl values(0, 2);
 4    insert into test_tbl values(1, null);
 5    insert into test_tbl values(1, 3);
 6    insert into test_tbl values(2, null);
 7    insert into test_tbl values(2, null);
 8    insert into test_tbl values(3, null);
 9    commit;
10  end;
11  /
PL/SQL procedure successfully completed.
dayneo@SANDBOX> select group_id,
 2       sum(num_value)   sum_value,
 3       count(num_value) cnt_value,
 4       avg(num_value)   avg_value
 5   from test_tbl
 6  group by group_id
 7  order by group_id
 8  /
 GROUP_ID  SUM_VALUE  CNT_VALUE  AVG_VALUE
---------- ---------- ---------- ----------
        0          3          2        1.5
        1          3          1          3
        2                     0
        3                     0
4 rows selected.
dayneo@SANDBOX> declare
 2    l_ctx dbms_xmlgen.ctxhandle;
 3    l_xml clob;
 4    l_cur sys_refcursor;
 5    l_cnt pls_integer;
 6  begin
 7 
 8    open l_cur for
 9      select group_id,
10             sum(num_value)   sum_value,
11             count(num_value) cnt_value,
12             avg(num_value)   avg_value
13        from test_tbl
14      group by group_id
15      order by group_id;
16 
17    l_ctx := dbms_xmlgen.newcontext(l_cur);
18    begin
19      dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
20      l_xml := dbms_xmlgen.getxml(l_ctx);
21      l_cnt := dbms_xmlgen.getNumRowsProcessed(l_ctx);
22      dbms_xmlgen.closecontext(l_ctx);
23      close l_cur;
24      dbms_output.put_line(l_xml);
25    exception
26      when OTHERS then
27        dbms_xmlgen.closecontext(l_ctx);
28        close l_cur;
29        raise;
30    end;
31 
32  end;
33  /
declare
*
ERROR at line 1:
ORA-19202: Error occurred in XML processing
ORA-24347: Warning of a NULL column in an aggregate function
ORA-06512: at line 29

As can be seen by this script, the SQL statement executes just fine by itself, but when used with DBMS_XMLGEN, the error occurs.

The workaround depends on the aggregatefunction being used. Fortunately, they are not wildly complex and follow the same pattern. The COUNT function is the easiest one to deal with. I willtackle the SUM, COUNT and AVG aggregate functions here.

COUNT
So this one is the easiest to deal with since COUNT is simply the sum of the number of items that are NOT NULL. Using the NVL2 function, we can return 1's for NOT NULL values and 0's for NULL values. The sum of these 1's and 0's will be equal to the COUNT of the values.

The CNT_VALUE column from the problem script above will now change to:

sum(nvl2(num_value, 1, 0)) cnt_value,

SUM
The SUM aggregate function is a little more tricky. For this function we need to check if all of the values in the group are NULL. If they are, then we must return NULL, otherwise we must run the sum and substitute NULLs with 0's using the NVL function.

The SUM_VALUE column from the problem script above will now change to:

decode(
 sum(nvl2(num_value, 1, 0)), -- count the NOT NULL values
 0, null,                    -- if 0 NOT NULL values are found, return NULL
 sum(nvl(num_value, 0))      -- else return SUM, replacing NULLS with 0's
) sum_value,

AVG
The AVG aggregate function seems like the most complex, but it is just a combination of the COUNT and SUM workarounds above. An average is simply the SUM/COUNT.

The AVG_VALUE column from the problem script above will now change to:

decode(
 sum(nvl2(num_value, 1, 0)), -- count the NOT NULL values
 0, null,                    -- if 0 NOT NULL values are found, return NULL
 sum(nvl(num_value, 0))/sum(nvl2(num_value, 1, 0)) -- else return SUM/COUNT
) avg_value

PUTTING IT TOGETHER
The followining script output demonstrates the final solution:

dayneo@SANDBOX> declare
 2    l_ctx dbms_xmlgen.ctxhandle;
 3    l_xml clob;
 4    l_cur sys_refcursor;
 5    l_cnt pls_integer;
 6  begin
 7 
 8    open l_cur for
 9      select group_id,
10             decode(
11                    sum(nvl2(num_value, 1, 0)),
12                    0, null,       
13                    sum(nvl(num_value, 0))   
14             ) sum_value,
15             sum(nvl2(num_value, 1, 0)) cnt_value,
16             decode(sum(nvl2(num_value, 1, 0)),
17                    0, null,      
18                    sum(nvl(num_value, 0))/sum(nvl2(num_value, 1, 0))
19             ) avg_value
20        from test_tbl
21       group by group_id
22       order by group_id;
23 
24    l_ctx := dbms_xmlgen.newcontext(l_cur);
25    begin
26      dbms_xmlgen.setNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);
27      l_xml := dbms_xmlgen.getxml(l_ctx);
28      l_cnt := dbms_xmlgen.getNumRowsProcessed(l_ctx);
29      dbms_xmlgen.closecontext(l_ctx);
30      close l_cur;
31      dbms_output.put_line(l_xml);
32    exception
33      when OTHERS then
34        dbms_xmlgen.closecontext(l_ctx);
35        close l_cur;
36        raise;
37    end;
38 
39  end;
40  /
<?xml version="1.0"?>
<ROWSET>
<ROW>
 <GROUP_ID>0</GROUP_ID>
 <SUM_VALUE>3</SUM_VALUE>
 <CNT_VALUE>2</CNT_VALUE>
 <AVG_VALUE>1.5</AVG_VALUE>
</ROW>
<ROW>
 <GROUP_ID>1</GROUP_ID>
 <SUM_VALUE>3</SUM_VALUE>
 <CNT_VALUE>1</CNT_VALUE>
 <AVG_VALUE>3</AVG_VALUE>
</ROW>
<ROW>
 <GROUP_ID>2</GROUP_ID>
 <SUM_VALUE/>
 <CNT_VALUE>0</CNT_VALUE>
 <AVG_VALUE/>
</ROW>
<ROW>
 <GROUP_ID>3</GROUP_ID>
 <SUM_VALUE/>
 <CNT_VALUE>0</CNT_VALUE>
 <AVG_VALUE/>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.