When executing a query, it seems like columns/fields that use the "xmlelement" function will correctly display with the expected data in the on-screen grid results, but when the results are exported (to at least CSV and Excel) files the values are incorrect or blank.
This mock query:
select rtrim(xmlagg(xmlelement(E, someclobfield || '; ').extract('//text()')).getclobval(), '; ') as something from sometable
will display the expected value on-screen in the results grid when executed in SQL Developer 4.2.0, 17.3.0, and 17.3.1. When the results are exported to Excel by right-clicking on the result grid and selecting "Export...", the result rows for the "something" field are all empty. If exported to a CSV, they all have a value similar to "oracle.sql.CLOB@7ce54717". The number at the end the field for each row is different and I assume is internally referring to a memory address.
I did some further testing by stripping things pieces from the SQL statement in 17.3.1:
- I removed the "xmlagg" and selected "xmlelement(E, someclobfield || '; ').extract('//text()'))". It displayed correctly in the on-screen grid results, but exported as "OPAQUE" for each row in the CSV.
- I then removed the "extract" and selected "xmlelement(E, someclobfield || '; ')". It also displayed correctly in the on-screen grid results, but exported as "OPAQUE" for each row in the CSV.
- Lastly, I removed "xmlelement" and simply selected "someclobfield" by itself. It displayed correctly in the on-screen grid results and also exported correctly.
It seems the cause of the export problem is the xmlelement function?
I'm not sure why using the "xmlelement" function displays correctly but exports incorrectly, but it seems like a bug that should be fixed so that what is displayed is the same as what is exported. I'm not sure how to report this as a bug in a way that will make it to the developers to fix. In my searching, it seems like this forum is the way to do that? Please let me know if I need to report this via some other means.