I think you missed my point. The point is that DBMS_XMLGEN.getxml should
only "see" the result of the outer select statement [select count(a)]. In
this case the result of that select statement is "0". The fact that the
inner select statement [select '' from dual] returns a NULL result set should
be irrelevant.
I'm just wondering if there is some justification for
the behavior of DBMS_XMLGEN.getxml or if it's a bug.
Thanks for response.
Regards,
Mike
On Tue, Sep 23, 2008 at 7:17 PM, Bud Manz <[EMAIL PROTECTED]> wrote:
>
> Hi Mike,
>
> It appears that the DBMS_XMLGEN package doesn't like null values.
> *This works...
> SELECT TO_NUMBER(EXTRACTVALUE (
> xmltype(DBMS_XMLGEN.getxml (
> 'select count(a) c from (select
> nvl('''',''x'') a from
> dual)'
> ))
> , '/ROWSET/ROW/C'
> )) rst
> FROM DUAL;
>
> HTH,
> Bud
>
>
> On Aug 25, 6:45 pm, "Michael Moore" <[EMAIL PROTECTED]> wrote:
> > *this works:*
> >
> > select count(a) c from (select 'x' a from dual);
> >
> > *and this works: *
> >
> > SELECT TO_NUMBER(EXTRACTVALUE (
> > xmltype(DBMS_XMLGEN.getxml (
> > 'select count(a) c from (select ''x'' a
> > from dual)'
> > ))
> > , '/ROWSET/ROW/C'
> > )) rst
> > FROM DUAL;
> >
> > *and this works:*
> > select count(a) c from (select '' a from dual);
> >
> > *But this ...*
> > SELECT TO_NUMBER(EXTRACTVALUE (
> > xmltype(DBMS_XMLGEN.getxml (
> > 'select count(a) c from (select '''' a
> from
> > dual)'
> > ))
> > , '/ROWSET/ROW/C'
> > )) rst
> > FROM DUAL;
> >
> > results in :
> > ORA-19202: Error occurred in XML processing
> > ORA-24347: Warning of a NULL column in an aggregate function
> > ORA-06512: at "SYS.DBMS_XMLGEN", line 176
> > ORA-06512: at line 1
> >
> > It would seem to me that DBMS_XMLGEN would see only the result set of the
> > select statement which is it's argument. In this case, the result set
> would
> > be a tuple with the value of 0 (zero). The fact that that 0 was derived
> via
> > a 'count()' function should be (according to me) irrelevant! Obviously,
> it's
> > not.
> >
> > So the questions are:
> > 1. Is this a bug?
> > 2. Is there a work-around?
> >
> > Mike
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
[EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
