xmltype return empty xml

[WallaceLee]

Something interesting on using PL/SQL Deveoper 7.1.5.1397.
Not sure anyone knows, but it may be only on our oracle database.

try this
select xmltype('') from all_tables;

I have a bunch of rows returned, but at row 7, the xml is empty.

can someone verify this?

Cheers

Wallace

 

[Bo Pedersen]

I have the same behaviour on an 11g database. Furthermore, if I execute the following select:

select xmltype('<Hello></Hello>'),
       xmltype('<Hello></Hello>').getstringval()
  from all_tables;

then the seventh row displays data for the string value but the clob for the XMLtype is empty. When executed in SQL*plus all data is returned. Seems to me to be a problem with the display of the results and not the query in it self. So getstringval or getclobval can be used to see the result.

Bo

 

[Marco Kalter]

Works fine for me. Can you let me know your Oracle Client version?

 

[Bo Pedersen]

My Oracle Client is:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0

I ran the script against a local 11g database.

I also tried it against an Oracle9i Enterprise Edition Release 9.2.0.1.0 database which resulted in PL/SQL Developer crashing. But in previous versions of the Oracle Database and/or PL/SQL Developer I think it was needed to use getclobval or getstringval to avoid an internal Oracle error.

I am using PL/SQL Developer version 7.1.5.1398.

Bo

 

[Worker]

I get the same result. The 7th row shows a non-null "", but clicking into the large data editor shows that it's empty.

Oracle Database 11g Release 11.1.0.7.0
Oracle Client 10.2.0
PL/SQL Developer 7.1.5.1398

In case it matters, the 7th row's info from all_tables is owner='SYS', table_name='COLTYPE$'. I don't think it matters, because if I only select that row, the problem goes away. However, if I add an ORDER BY, the problem goes away as well.

 

[matt_bae]

I get the same problem using the xmlelement() function. i.e.

select xmlelement ("root", xmlattributes( person_id, last_name, first_name)) from person

The 7th row is a but when I open it up, it is empty. Other rows have a error. The empty value also appears on rows 37, 107, 137, 207, etc.

I am using PL/SQL Dev 7.1.1.1339 and oracle client v10.2.

 

[matt_bae]

edit: dup post