XML Problems?

[Sachin]

The following query takes forever in PLD, but runs in seconds in SQLPLUS or SQLDEVELOPER.

SELECT xmlelement("Employee",
xmlattributes('http://www.w3.org/2001/XMLSchema' AS "xmlns:xsi",
'http://www.oracle.com/Employee.xsd' AS
"xsi:nonamespaceSchemaLocation"),
xmlelement("EmployeeNumber", e.empno), xmlelement("EmployeeName", e.ename),
xmlelement("Department", xmlelement("DepartmentName", 'd.dname'),
xmlelement("Location", 'd.loc')))
FROM emp e;

EMP table is:
CREATE TABLE EMP
( EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)

and contains 4 records:
REM INSERTING into SB_EMP
Insert into SB_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH.MI.SSXFF AM'),1100,null,null);
Insert into SB_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),950,null,null);
Insert into SB_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),3000,null,null);
Insert into SB_EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-JAN-82','DD-MON-RR HH.MI.SSXFF AM'),1300,null,null);

Oracle Database:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

I am currently using an Oracle 9i client which is the same one that is used by SQLPLUS, SQLDeveloper and PLD.
Any suggestions?

Thanks,

 

[Marco Kalter]

On a 9i client you should use the getclobval() function to retrieve the XML text:

SELECT xmlelement(...).getclobval()FROM emp e;

On a 10g and later client you can simply retrieve the xmltype value.

 

[Sachin]

Marco,

I understand, but maybe I am missing something here......

As I said in my post, the same query runs fine in SQLPLUS & SQLDeveloper which are using the same Oracle Client as PLD.