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
-~----------~----~----~----~------~----~------~--~---
