Probably a stupid question, but being too tired, would appreciate a bit of help to get around the following problem.
This one alway confuses me due to the Oracle OO nature of the error. How can I get a multiple row output/returned while doing the UNION?
The following is a simplistic example of my issue.
SQL> select xmlelement("Root", xmlforest(dummy "A", dummy "B")) from dual;
XMLELEMENT("ROOT",XMLFOREST(DUMMY"A",DUMMY"B"))
--------------------------------------------------------------------------------
<Root><A>X</A><B>X</B></Root>
SQL> select xmlelement("Root", xmlforest(dummy "A", dummy "B")) from dual
2 union
3 select xmlelement("Root", xmlforest(dummy "A", dummy "B")) from dual
4 ;
select xmlelement("Root", xmlforest(dummy "A", dummy "B")) from dual
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method
My real union statement would need to return, as while only using SQL, the SQL output, the following in XML
<Root><A>X</A><B>X</B></Root>
<Root><A>1</A><B>2</B></Root>