SQL & PL/SQL

Hi,

I was thinking of replacing an old bulky stored procedure which is creating a 3GB XML file .It is taking more than 10hrs using the UTL_FILE Package,after looping though few cursors and sub cursors ( Existing scenario)
This was written long back.Anyway this is just about the background.

So thought of trying out the latest XML packages & Functions to improve the performance.After logging using scott id, I fired the following query which is there
in oracle documentation.

Oracle Documentation Ref:
------------------------
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96620.pdf
XML Database Developer’s Guide - Oracle XML DB
Chapter 10.5 : XMLElement Function

+++++++++++++++

SELECT XMLElement("Department",dept_t(deptno,dname,
CAST(MULTISET(select empno, ename from emp e
where e.deptno = d.deptno) AS emplist_t)))
AS deptxml FROM dept d;

+++++++++++++++

The Result on my database is

DEPTXML

<Department>
<DEPT_T department_id="10">
<DEPARTMENT_NAME>ACCOUNTING</DEPA

<Department>
<DEPT_T department_id="20">
<DEPARTMENT_NAME>RESEARCH</DEPART

<Department>
<DEPT_T department_id="30">
<DEPARTMENT_NAME>SALES</DEPARTMEN

<Department>
<DEPT_T department_id="40">
<DEPARTMENT_NAME>OPERATIONS</DEPA

4 rows selected.

++++++++++++++++++++++

As You could see on the third line of each dept set, few characters are trimmed. and the expected employee details are also missing.I queried on the database and it has enough values for that join.In the documentaion's sample display everything looks fine ..

Any idea on how to resolve this ?

I am using Oracle version 9.2 on Windows.I tried increasing the "SET LINESIZE 8000" and all but of no use.

The following are the TYPE created before excuting the query , again extactly same in the oracle documentaion

<<
CREATE TYPE emp_t AS OBJECT(
"@employee_id" NUMBER,
last_name VARCHAR2(20)
);
/
CREATE TYPE emplist_t AS TABLE OF emp_t;
/
CREATE TYPE dept_t AS OBJECT(
"@department_id" NUMBER,
department_name VARCHAR2(20),
emplist emplist_t
);
>>

With tonnes of thanks in advance

Ranjeesh
ranjeeshk@infics.com