BI report from Oracle APEX prints out as blank when special characters are encountered

1
votes

I am using Oracle APEX version 5.1 and users are trying to print out BI reports from Oracle APEX. Currently, we are making use of "APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT" (Signature 4) to print the data present in APEX page on to a BI report.

Everything works fine until program encounters "&" or "<" or ">" in the XML tags. When "XML BI report" or "APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT" (I mentioned both because I don't know where the issue is occurring) encounters these characters the report comes out as blank. If the above mentioned characters are manually removed from the XML tags, BI report works fine and it gets printed out.

Steps to reproduce: 1) create XML data as given in the sample below. See the "&" in the "GMRISKMITIGATION" XML tag (you can replace this "&" with "<" or ">" and you will get the same result)

<?xml version="1.0"?>
<ROWSET>
<ROW>
  <ID>1234</ID>
  <ADDITIONAL_COMMENTS>New "test" front test test test test test test.</ADDITIONAL_COMMENTS>
  <GMRISKMITIGATION> test test  test  test &  test.</GMRISKMITIGATION>
</ROW>
</ROWSET>

2) Now you have the data, then using the routine given below from a "Button" in APEX page to download a BI report.

APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (p_file_name => 'myreport',p_content_disposition => 'attachment', p_report_data => l_clob, /* XML data in clob format */ p_report_layout => l_file_as_clob,p_report_layout_type => 'rtf',
  p_document_format => 'rtf');

3) Now if you press the button the BI report will be downloaded successfully but it will be empty.

2
oracleoracle-apexoracle-apex-5oracle-apex-5.1oracle-apex-18.2
just a suggestion, I don't know if solve your problem.... if you can, try to escape the content of each xml element. don't escape all your xml, just the content of each tag docs.oracle.com/database/121/AEAPI/apex_escape.htm#AEAPI29271 – romeuBraga
[When I first implemented the solution the XML that was generated had the equivalent character entities for example the XML data had "&amp;" and Quote was represented as "&quot;" in the XML.] <COMMENTS>New &quot;test&quot; test.</COMMENTS> <GMR> test &amp; test</GMR> Quotes was working fine even if XML had quote symbol in the content, it also worked for apostrophe previously it was &apos; but when I replaced it with the actual apostrophe symbol it worked. What I have observed is when it encounters "&" it can be either &quot; or &amp; its unable to translate back to its symbols. – Ashlesh Kumar
I think its an issue with oracle APEX – Ashlesh Kumar

2 Answers

1
votes

Finally, I was able to resolve this issue. I was able to resolve the character issue, all I did was replace "&" with "%26" and everything is working fine and now I don't have to convert characters from native character set as well, this has improved the performance as well.

l_clob := replace(l_clob,'&','%26');

Please find the URL's below for further reference.

https://community.oracle.com/message/14039814#14039814 https://community.oracle.com/message/3756610#3756610

0
votes

You can use APEX_UTIL.URL_ENCODE to convert '&' to '%26'

DECLARE

    l_clob      CLOB;
    l_varchar   VARCHAR2(32767);
    l_start     PLS_INTEGER := 1;
    l_buffer    PLS_INTEGER := 32767;

BEGIN

    DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);
    FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(l_xml_clob) / l_buffer) LOOP
        l_varchar := DBMS_LOB.SUBSTR(l_xml_clob, l_buffer, l_start);
        l_varchar := APEX_UTIL.URL_ENCODE(l_varchar);    
        DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(l_varchar), l_varchar);
        l_start := l_start + l_buffer;
    END LOOP;

END;