SQL data extracts works in Oracle 11g but not Oracle 12c

Question

When I execute the following SQL using TOAD against an Oracle 11g database, the fully formed XML is returned successfully:

With T As (SELECT dbms_xmlgen.getxml('SELECT m.trans_message FROM xml_nodes_ams_in a, message m WHERE a.id = m.msg_id AND a.UPN IN(''A30971016528VE8K'',''A30971016529VE84'') ORDER BY a.upn ASC'
  ) As output_xml from dual
) select dbms_xmlgen.Convert(output_xml,1) from T

However, when I execute the exact same SQL against our newly installed Oracle 12c database, some of the XML data appears to be missing (around 5000 characters).

I have discussed this with the DBA who reckons its a client issue rather than a database issue as he says there is no setting against the database that would cause this.

Has anyone got any advise on how I can progress this issue?

probably there is incomplete data in the 12c Database. check if both databases contain the same count of data in the table xml_nodes_ams_in — Migs Isip
Both databases contain the same data. When I manually extract the data using TOADs filter capabilities the XML is well formed and complete. — Luke_Skywalker007
Is the final CLOB being truncated, or is there data missing from within the XML? How much data is shown? Is there a setting in Toad similar to the SQL*Plus set long that determines how much of the CLOB is shown - maybe at connection level if you're using the same client to access both DBs? — Alex Poole
@AlexPoole - The XML is missing from within the XML (not being truncated). Also, most of the data is shown, its only a small snippet of the XML that is missing. Ive tried to look at the settings within TOAD however its quite an old version and doesnt support the 12c client software therefore that might be contributing to the issue. I currently connect to both the 11g and 12c database using a 10g client. Im in the process of downloading the new SQL developer using a 12c client and ill report back my findings. — Luke_Skywalker007

Luke_Skywalker007 Luke_Skywalker007 · Accepted Answer · 2017-04-25T11:57:37

I raised a service request with Oracle and they came back to me and advised that there is a bug with the dbms_xmlgen.Convert function within Oracle 12.1 that was fixed in Oracle 12.2. Basically the function fails with XML greater than 120 KB.

SQL data extracts works in Oracle 11g but not Oracle 12c

1 Answers