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.
You should open a Service request (SR) with My Oracle Support (MOS). Thanks!
I took a quick look at this, and it seems the export broke in SQL Developer 4.2 in the way described in your post. When I tried your test case with a single row, the query result grid displays as expected, but in a 2 row test I see there is unexpected grid behavior that exists in even earlier versions (I only checked back as far as 4.0.3). Here is the test case...
The table has 3 columns (number, varchar2, clob) and 2 rows of data: one row with ordinary ascii and the other having additional char set values. This is from some other discussion on the forum and I am not sure if different characters are even relevant, which is why I added the ordinary row to the test. When I run the following query, however, I get back only three rows in the grid instead of the expected four rows...
select C_LOB as hlob from helsinki union all select rtrim(xmlagg(xmlelement(E, C_LOB || '; ').extract('//text()')).getclobval(), '; ') as hlob from helsinki;
Not sure how many SQL Developer users have run into this bug, but opening the SR as Jeff suggests is the best way to ensure it gets addressed.
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.
Well - no and yes
No - it does NOT display with the expected data in the grid. It displays as:
That is the column datatype: XMLTYPE. It is NOT VARCHAR2, CLOB, BLOB or other. It is OPAQUE to JDBC meaning JDBC doesn't try to 'interpret' what the contents are. Just like JDBC doesn't try to interpret the contents of a BLOB that might contain a GIF, JPEG or other binary content.
Yes - you can double-click that results value and Sql Dev will search for a 'viewer' that understands the XMTYPE datatype and knows how to display it.
Displaying something in a GUI Graphics viewer is not the same thing as saving something in a text file format.
I'm not sure why using the "xmlelement" function displays correctly but exports incorrectly
Hopefully the above explains what is happening.
but it seems like a bug that should be fixed so that what is displayed is the same as what is exported.
IMHO it would only be a bug if XMLTYPE values were intended to be exported to delimited files. Only the Sql Dev team can answer as to whether that is a design requirement.
Sql Dev also doesn't try to save other LOB datatype values (CLOB, BLOB) as text either.
In 30+ years I haven't run across ANY use cases that tried to use delimited text files to store LOB or XML data values. Those values have always been stored in a file of their own.
So it would make sense to add 'load/save' functionality for the XMLTYPE data displayed in the editor dialog. That would make it function the same way that the dialogs for LOB function.