Database Software

My select statement with XMLAGG and ORDER BY is working fine in SQL
but when I put it in PL/SQL it throws error
I am using ORACLE version "9.2.0.6.0"

DECLARE ...
BEGIN...
<select_statement>.
END

It throws an error abt XMLAGG function. "wrong number or types of arguments in call to 'XMLAGG'"

THE PL/SQL works fine when I remove the "ORDER BY" from XMLAGG !!

my working sql looks like this

SELECT XMLELEMENT( "Department", XMLATTRIBUTES ( d.dname AS "name" ),
(SELECT XMLAGG(XMLELEMENT ("emp", XMLATTRIBUTES (e.ename AS name),
( SELECT XMLAGG(XMLELEMENT( "dependent",
XMLATTRIBUTES(de.name AS "name")
order by de.name
)
)
FROM dependents de
WHERE de.empno = e.empno ) ))
FROM emp e
WHERE e.deptno = d.deptno) ) AS "dept_list"
FROM dept d ;


Any suggestions?
Sanju

Message was edited by:
user451439