4 Replies Latest reply on Oct 27, 2017 12:44 AM by Gary Graham-Oracle

    Bug report: SQL Developer exports incorrectly when "xmlelement" function is used

    user9221399

      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.

        • 1. Re: Bug report: SQL Developer exports incorrectly when "xmlelement" function is used
          thatJeffSmith-Oracle

          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!

          • 2. Re: Bug report: SQL Developer exports incorrectly when "xmlelement" function is used
            Gary Graham-Oracle

            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...

            XmlElementExportBugData.jpg

            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;
            

             

            XmlElementExportBugResult.jpg

            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.

            • 3. Re: Bug report: SQL Developer exports incorrectly when "xmlelement" function is used

              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:

              (XMLTYPE)

              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.

              • 4. Re: Bug report: SQL Developer exports incorrectly when "xmlelement" function is used
                Gary Graham-Oracle

                Also, it occurs to me that there are some preferences that may affect the grid display, but they do not seem to impact the results in this particular test case...

                DisplayXMLValueInGridPref.jpg