10 Replies Latest reply: Dec 7, 2009 4:13 PM by 698711 RSS

    On Demand Process - Special Characters

    698711
      I have an on demand process (ODP) that I use to populate items in a page with. I followed the examples from Denis Kubrik and others on the OTN. The problem I have run into is data with ampersands. I know that there a special characters that need to be escaped in XML documents. I have used the replace function in the SQL Query to change '&' into '&'. However, the process still seems to die. I am using the testing capability documented here.

      When I run the ODP that does not return the ampersand all is well, I see the XML document. When I retrieve a row with the ampersand I see:

      "Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

      The operation completed successfully. Error processing resource 'http://test:7734/pls/apex/f?p=128:0:8491..."

      When I run the SQL code from SQL developer using output I see a valid XML document.

      Does anyone have any insight to this?

      Thanks!
        • 1. Re: On Demand Process - Special Characters
          Z?
          Please provide your source...

          Cheers

          Ben
          • 2. Re: On Demand Process - Special Characters
            698711
            DECLARE
            v_first_name VARCHAR2 (50);
            v_last_name VARCHAR2 (50);
            v_title VARCHAR2 (50);
            v_phone VARCHAR2 (25);
            v_email VARCHAR2 (50);
            v_dept VARCHAR2 (200);
            BEGIN

            FOR c IN (select first_name, last_name, nyc_title, desk_phone_number, email_address, kronos_badge_number, employee_id, replace(department_name,'&','&') department_name
            from anc_employees_v
            where employee_id = TO_NUMBER (v('EMPLOYEE_ID')))
            LOOP
            v_first_name := c.first_name;
            v_last_name := c.last_name;
            v_title := c.nyc_title;
            v_phone := c.desk_phone_number;
            v_email := c.email_address;
            v_dept := c.department_name;
            END LOOP;


            OWA_UTIL.mime_header ('text/xml', FALSE);
            HTP.p ('Cache-Control: no-cache');
            HTP.p ('Pragma: no-cache');
            OWA_UTIL.http_header_close;
            HTP.prn ('<body>');
            HTP.prn ('<desc>this xml genericly sets multiple items</desc>');
            HTP.prn ('<item id="P16_FIRST_NAME">' || UPPER(v_first_name) || '</item>');
            HTP.prn ('<item id="P16_LAST_NAME">' || UPPER(v_last_name) || '</item>');
            HTP.prn ('<item id="P16_TITLE">' || v_title || '</item>');
            HTP.prn ('<item id="P16_PHONE">' || v_phone || '</item>');
            HTP.prn ('<item id="P16_EMAIL">' || UPPER(v_email) || '</item>');
            HTP.prn ('<item id="P16_DEPARTMENT_ID">' || UPPER(v_dept) || '</item>');
            HTP.prn ('</body>');
            EXCEPTION
            WHEN OTHERS THEN
            OWA_UTIL.mime_header ('text/xml', FALSE);
            HTP.p ('Cache-Control: no-cache');
            HTP.p ('Pragma: no-cache');
            OWA_UTIL.http_header_close;
            HTP.prn ('<body>');
            HTP.prn ('<desc>GET_EMPLOYEE_DATA</desc>');
            HTP.prn ('<item id="P16_ERROR">' || SQLERRM || '</item>');
            HTP.prn ('</body>');
            END;
            • 3. Re: On Demand Process - Special Characters
              Z?
              Hi

              Have you tried
              replace("&","& amp;");
              And
              replace("&",CHR(26)); --I think???
              Cheers

              Ben
              • 4. Re: On Demand Process - Special Characters
                Sc0tt
                Wrap htf.escape_sc around your output items - this escapes special characters
                • 5. Re: On Demand Process - Special Characters
                  698711
                  Munky wrote:
                  Hi

                  Have you tried
                  replace("&","& amp;");
                  And
                  replace("&",CHR(26)); --I think???
                  Cheers

                  Ben
                  This works:

                  replace("&","& amp;")

                  But why?

                  how is replace("&","& amp;") different than replace("&","&amp;")

                  There is a space in the first one and not in the second...

                  Edited by: nycha_apex on Dec 7, 2009 11:49 AM
                  • 6. Re: On Demand Process - Special Characters
                    Z?
                    Probably because the first is interpreted whereas the second is not.

                    To be honest, I'm not sure but it has worked for me.

                    If it works, I would prefer the CHR option.

                    Cheers

                    Ben

                    Don't forget to mark answers as helpful or correct ;)
                    • 7. Re: On Demand Process - Special Characters
                      698711
                      Sc0tt wrote:
                      Wrap htf.escape_sc around your output items - this escapes special characters
                      This is really the answer that works. Both approaches work - either I do the work or the HTF function does. However, the root of the issue here is possibly a bug in that:

                      htf.escape_sc(upper(v_dept)) works
                      upper(htf.escape_sc(v_dept)) does not work

                      Can anybody else confirm this is a bug?

                      Thanks!
                      • 8. Re: On Demand Process - Special Characters
                        698711
                        Is there a way to mark two people with the answer? And, is this a bug given the updated thread?
                        • 9. Re: On Demand Process - Special Characters
                          Z?
                          Yup, that will work but in PL/SQL, without a first rows hint, you're going to hit every row so calling that function every time could impact performance if you've potentially got a large result set. You may be better concatenating it into a CLOB or VARCHAR(32767) then doing your function/replace after the loop before buffer printing it out via htp.p

                          You can't award double correct points - just shower them with helpfuls!

                          Cheers

                          Ben
                          • 10. Re: On Demand Process - Special Characters
                            698711
                            I understand your point but in this case my list would be less than 50. Also, the root of my problem has to do with my mistake in thinking AMP and amp are equal in XML. It appears they are not. When I called an upper after the replace or htf the XML could not be parsed. When I called it before then all was well. This can be demonstrated in a simple xml file modified on the desktop and run in IE. So, obviously this is not a bug in APEX (how quickly I am to point fingers).

                            I think the benefit for htf.escape_sc is that it takes into account more than just ampersand.

                            Thanks again for the help!