Home » SQL & PL/SQL » SQL & PL/SQL » Problem in generating XML (oracle 10g )
Problem in generating XML [message #409657] Tue, 23 June 2009 07:24 Go to next message
Developer
Messages: 19
Registered: January 2004
Junior Member
Hi,
I am using XMLSequence function to generate XML.
current query is fine but when I add With Clause, it gives error. Is there any workaround to fix this?
SELECT VALUE (tr) rule_row
FROM   TABLE
    (XMLSEQUENCE
        (CURSOR( with q as (select 10 as dept from dual)
                 select empno, ename
                 from emp e, q
                 where e.deptno = q.dept))) tr


This is just sample query. I am using WITH clause to improve performance of my query. One thing could be put one more select above WITH clause. Confused
Re: Problem in generating XML [message #409658 is a reply to message #409657] Tue, 23 June 2009 07:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see no error.....

Can you tell us the error you got?
Re: Problem in generating XML [message #409661 is a reply to message #409657] Tue, 23 June 2009 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT VALUE (tr) rule_row
  2  FROM   TABLE
  3      (XMLSEQUENCE
  4          (CURSOR( with q as (select 10 as dept from dual)
  5                   select empno, ename
  6                   from emp e, q
  7                   where e.deptno = q.dept))) tr
  8  /
        (CURSOR( with q as (select 10 as dept from dual)
                 *
ERROR at line 4:
ORA-00936: missing expression


SQL> with q as (select 10 as dept from dual)
  2                   select empno, ename
  3                   from emp e, q
  4                   where e.deptno = q.dept
  5  /
     EMPNO ENAME
---------- ----------
      7839 KING

1 row selected.

SQL> SELECT VALUE (tr) rule_row
  2  FROM   TABLE
  3      (XMLSEQUENCE
  4          (CURSOR(
  5  select empno, ename
  6  from emp e, (select 10 as dept from dual) q
  7  where e.deptno = q.dept))) tr
  8  /
RULE_ROW
----------------------------------------------------------------
 <ROW>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
 </ROW>

1 row selected.

Seems CURSOR does not like WITH.

Regards
Michel
Re: Problem in generating XML [message #409662 is a reply to message #409657] Tue, 23 June 2009 07:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I took pity on you and dug out the script for Emp and Dept...

You need to move the WITH to the start of the query:
with q as (select 10 as dept from dual)
SELECT VALUE (tr) rule_row
FROM   TABLE
    (XMLSEQUENCE
        (CURSOR( select empno, ename
                 from scott.emp e,  q
                 where e.deptno = q.dept))) tr;


I can see nothing in the Documentation for SELECT or CURSOR that indicates why this restriction is required.
icon14.gif  Re: Problem in generating XML [message #409680 is a reply to message #409662] Tue, 23 June 2009 08:15 Go to previous message
Developer
Messages: 19
Registered: January 2004
Junior Member
Thanks great!!!
Previous Topic: external table
Next Topic: finding default constraint
Goto Forum:
  


Current Time: Sat Dec 03 22:16:56 CST 2016

Total time taken to generate the page: 0.21617 seconds