9 Replies Latest reply: Aug 12, 2010 6:19 AM by Alistair Laing RSS

    XML returned from on-demand process

    sky123
      Hi,

      I have been trying to return XML from on-demand application process. No error has been thrown. However, no XML object is returned to the local variable gReturn in javascript. What's wrong with the following?

      Thanks.
      Andy

      <pre>
      BEGIN
      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 ( '<rows>' );

      htp.prn ( '<row id="' || '1' || '">' );
      htp.prn ( '<cell>' || 'column1' || '</cell>' );
      htp.prn ( '<cell>' || 'column2' || '</cell>' );
      htp.prn ( '<cell>' || 'column3' || '</cell>' );
      htp.prn ( '<cell>' || 'column4' || '</cell>' );
      htp.prn ( '<cell>' || 'column5' || '</cell>' );
      htp.prn ( '<cell>' || 'column6' || '</cell>' );
      htp.prn ( '<cell>' || 'column7' || '</cell>' );
      htp.prn ( '<cell>' || 'column8' || '</cell>' );
      htp.prn ( '<cell>' || 'column9' || '</cell>' );
      htp.prn ( '<cell>' || 'column10' || '</cell>' );
      htp.prn ( '<cell>' || 'column11' || '</cell>' );
      htp.prn ( '</row>' );
      END LOOP;

      htp.prn ( '</rows>' );
      exception
      htp.prn('error');
      END;
      </pre>
        • 1. Re: XML returned from on-demand process
          sky123
          Sorry, here is the code that I meant. Do you know the reason why the process didn't return anything?

          <pre>
          BEGIN
          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 ( '<rows>' );

          htp.prn ( '<row id="' || '1' || '">' );
          htp.prn ( '<cell>' || 'column1' || '</cell>' );
          htp.prn ( '<cell>' || 'column2' || '</cell>' );
          htp.prn ( '<cell>' || 'column3' || '</cell>' );
          htp.prn ( '<cell>' || 'column4' || '</cell>' );
          htp.prn ( '<cell>' || 'column5' || '</cell>' );
          htp.prn ( '<cell>' || 'column6' || '</cell>' );
          htp.prn ( '<cell>' || 'column7' || '</cell>' );
          htp.prn ( '<cell>' || 'column8' || '</cell>' );
          htp.prn ( '<cell>' || 'column9' || '</cell>' );
          htp.prn ( '<cell>' || 'column10' || '</cell>' );
          htp.prn ( '<cell>' || 'column11' || '</cell>' );
          htp.prn ( '</row>' );

          htp.prn ( '</rows>' );
          exception
          htp.prn('error');
          END;
          </pre>

          <pre>
          <script>
          var mygrid;
          function fgetClientSearch(){
          var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
          'APPLICATION_PROCESS=GET_CLIENT_TEST',0);

          gReturn = get.get('XML');

          if (gReturn) {
          alert (gReturn);
          } else { alert('no data'); }
          get = null;
          }
          </script>
          </pre>
          • 2. Re: XML returned from on-demand process
            Denes Kubicek
            Because there is a syntax error in your code - exception...

            This should work.
            ´BEGIN
               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 ('<rows>');
               HTP.prn ('<row id="' || '1' || '">');
               HTP.prn ('<cell>' || 'column1' || '</cell>');
               HTP.prn ('<cell>' || 'column2' || '</cell>');
               HTP.prn ('<cell>' || 'column3' || '</cell>');
               HTP.prn ('<cell>' || 'column4' || '</cell>');
               HTP.prn ('<cell>' || 'column5' || '</cell>');
               HTP.prn ('<cell>' || 'column6' || '</cell>');
               HTP.prn ('<cell>' || 'column7' || '</cell>');
               HTP.prn ('<cell>' || 'column8' || '</cell>');
               HTP.prn ('<cell>' || 'column9' || '</cell>');
               HTP.prn ('<cell>' || 'column10' || '</cell>');
               HTP.prn ('<cell>' || 'column11' || '</cell>');
               HTP.prn ('</row>');
               HTP.prn ('</rows>');
            EXCEPTION
               WHEN OTHERS
               THEN
                  HTP.prn ('error');
            END;
            Try using Toad and the formater. It will inform you about the errors as soon as you try formating your code.

            Denes Kubicek
            -------------------------------------------------------------------
            http://deneskubicek.blogspot.com/
            http://www.opal-consulting.de/training
            http://apex.oracle.com/pls/otn/f?p=31517:1
            -------------------------------------------------------------------
            • 3. Re: XML returned from on-demand process
              sky123
              Denes,

              That was just something I came up to isolate individual causes and for testing purpose. The following is the actual code that I am using. It actually didn't throw any error and the local variable gReturn contains the object. However, when I try to load the XML "object" into the DHTMLX grid, the browser shows this message:

              <pre>
              Not an XML, probably incorrect content type specified ( must be text/xml ), or some text output was started before XML data

              Loading: [object XMLDocument]
              Status: 404
              Response: <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
              <HTML><HEAD>
              <TITLE>404 Not Found</TITLE>
              </HEAD><BODY>
              <H1>Not Found</H1>
              The requested URL /pls/case_htmldb/[object XMLDocument] was not found on this server.<P>
              <HR>
              <ADDRESS>Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at <my domain and port></ADDRESS>
              </BODY></HTML>
              </pre>


              Here is the actual on-demand process:
              <pre>
              DECLARE
              v_count number := 0;
              BEGIN
              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 ( '<rows>' );

              FOR c IN ( select a.mcm_pers_pk,
              '1' dah_client,
              ltrim(rtrim(a.pers_last_name)) pers_last_name,
              ltrim(rtrim(a.pers_first_name)) pers_first_name,
              ltrim(rtrim(a.pers_mi)) pers_mi,
              a.dob,
              a.pers_aka,
              decode(a.ssn,null,null,substr(a.ssn,1,3) || '-' || substr(a.ssn,4,2) || '-' || substr(a.ssn,6)) ssn,
              b.race_desc,
              c.lang_desc,
              d.sex_desc,
              e.sexual_orientation
              from mcm_pers_rev a, mcm_race b, mcm_lang c, mcm_sex d, code_sexual_orientation e
              where a.ethnicity = b.race_code
              and a.primary_lang = c.lang_code
              and a.sex = d.sex_code
              and a.sexual_orientation = e.sexual_orientation_code (+)
              and a.pers_last_name like upper( v('F118_LAST_NAME_SEARCH') ) || '%'
              and a.pers_first_name like upper( v('F118_FIRST_NAME_SEARCH') ) || '%'
              and nvl( a.ssn,'0' ) like replace( v('F118_SSN_SEARCH'),'-',NULL) || '%'
              and nvl( a.dob,sysdate ) like to_date( v('F118_DOB_SEARCH'),'MM/DD/RRRR' ) || '%'
              order by a.pers_last_name, a.pers_first_name )
              LOOP
              v_count := v_count + 1;

              HTP.prn ( '<row id="' || v_count || '">' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.mcm_pers_pk) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.dah_client) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.pers_last_name) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.pers_first_name) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.pers_mi) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.pers_aka) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.ssn) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.dob) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.sex_desc) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.lang_desc) || '</cell>' );
              HTP.prn ( '<cell>' || htf.escape_sc(c.race_desc) || '</cell>' );
              HTP.prn ( '</row>' );
              END LOOP;

              HTP.prn ( '</rows>' );
              EXCEPTION
              WHEN OTHERS THEN
              HTP.prn ( 'error' );
              END;
              </pre>


              Here is the actual javascript call:
              <pre>
              <script>
              var mygrid;
              function fgetClientSearch(){
              var get = new htmldb_Get(null,html_GetElement('pFlowId').value,
              'APPLICATION_PROCESS=GET_CLIENT_SEARCH',0);
              get.add('F118_LAST_NAME_SEARCH','P1_LAST_NAME_SEARCH');
              get.add('F118_FIRST_NAME_SEARCH','P1_FIRST_NAME_SEARCH');
              get.add('F118_SSN_SEARCH','P1_SSN_SEARCH');
              get.add('F118_DOB_SEARCH','P1_DOB_SEARCH');

              gReturn = get.get('XML');

              if (gReturn) {

              // load grid
              mygrid = new dhtmlXGridObject('mygrid_container');
              mygrid.setImagePath("/i/themes/custom/dhtmlx/dhtmlxGrid/dhtmlxGrid/codebase/imgs/");

              mygrid.setHeader("ID,In DAH,Last Name,First Name,MI,Aliases,SSN,DOB,Gender,Language,Ethnicity");
              mygrid.setInitWidths("*,*,*,*,*,*,*,*,*,*,*");

              mygrid.setSkin("xp");
              mygrid.init();
              mygrid.load('"' + gReturn + '"' + ',"xml"');

              // dhtmlxError.catchError("LoadXML", myErrorHandler);

              } else { alert('no data'); }
              get = null;

              }
              function myErrorHandler(type, desc, erData){
              alert('problem loading XML');
              return false;
              }
              </script>
              </pre>
              • 4. Re: XML returned from on-demand process
                Denes Kubicek
                Have you seen my latest blog posting:

                http://deneskubicek.blogspot.com/

                ?

                Denes Kubicek
                -------------------------------------------------------------------
                http://deneskubicek.blogspot.com/
                http://www.opal-consulting.de/training
                http://apex.oracle.com/pls/otn/f?p=31517:1
                -------------------------------------------------------------------
                • 5. Re: XML returned from on-demand process
                  sky123
                  Denes,

                  Thanks for the information. It is very useful. Btw, I finally got the problem fixed. Here are the causes:

                  1. The following condition in the query (inside the on-demand process) contains some problem. I took it out from the process.
                  <pre>
                  :
                  and a.pers_last_name like upper( v('F118_LAST_NAME_SEARCH') ) || '%'
                  and a.pers_first_name like upper( v('F118_FIRST_NAME_SEARCH') ) || '%'
                  and nvl( a.ssn,'0' ) like replace( v('F118_SSN_SEARCH'),'-',NULL) || '%'
                  and nvl( a.dob,sysdate ) like to_date( v('F118_DOB_SEARCH'),'MM/DD/RRRR' ) || '%'
                  :
                  </pre>

                  2. According to the support at DHTMLX, I should use mygrid.parse(gReturn,"xml") instead of mygrid.load(gReturn) because the local variable gReturn contains an object.

                  It does take a few second for the on-demand process to spool all the XML data to the calling process and to load the datagrid. I will try to see if the vendor's SmartRending add-on (with pagination) can improve the performance.

                  Finally, I would like to say the DHTMLX grid is the most easiest product (with support from company) to be integrated in Oracle APEX. I tried other open-source UI components but failed to make them working. Most of those open-source components like extjs, jqgrid, flexigrid, omnigrid, etc. are mostly designed for PHP pages.

                  Thanks.

                  Andy
                  • 6. Re: XML returned from on-demand process
                    fac586
                    It does take a few second for the on-demand process to spool all the XML data to the calling process
                    Theoretically better performance might be achievable using SQL/XML to generate the XML, as this eliminates the context switching associated with the cursor loop and the overhead of repeatedly calling htf.escape_sc() (at the expense of using more memory), although the gains appear to be insignificant for the number of rows likely involved here: only became measurable at about > 500 rows, e.g.
                      declare
                    
                        xml xmltype;
                    
                      begin
                    
                        select
                                  xmlelement(
                                      "rows"
                                    , xmlagg(
                                        xmlelement(
                                            "row"
                                          , xmlattributes(row_number() over (order by ename) as "id")
                                          , xmlforest(
                                                empno as "cell"
                                              , ename as "cell"
                                              , job as "cell"
                                              , mgr as "cell"
                                              , hiredate as "cell"
                                              , sal as "cell"
                                              , comm as "cell"
                                              , deptno as "cell"))
                                        order by ename asc)) xml
                        into
                                  xml
                        from
                                  emp;
                    
                        owa_util.mime_header('text/xml', false);
                        htp.p('Cache-Control: no-cache');
                        htp.p('Pragma: no-cache');
                        owa_util.http_header_close();
                        htp.p(xml.getstringval());
                        
                      exception
                    
                        when others
                        then
                         htp.p('error');
                    
                      end;
                    There are other benefits: the XML is always well-formed; if using a schema, it can be validated on creation; the code will be more compact and easier to maintain than rolling your own with lots of manually entered tags.

                    In a production scenario, probably an idea to perform buffered output using getclobval() to avoid hitting problems with the 32K varchar limit with getstringval() and htp.p().
                    • 7. Re: XML returned from on-demand process
                      sky123
                      Hi,

                      Thanks for your information above. However, there were two major problems when using your method:

                      1. xmlforest will not return anything if the data value is null.
                      2. getclobval() couldn't take care of the 32K varchar limit in Apex. I still encountered the 32K varchar limit.

                      I still don't see a better way to return a large set of XML tags from pl/sql. The htp.p() works perfectly as long as the number of records returned are small. However, if there are a lot of records to be returned, it will take a long time to spool all those records.

                      Any better idea?

                      Thanks.
                      Andy
                      • 8. Re: XML returned from on-demand process
                        Alistair Laing
                        I had the same problem this week turned out to being 32k limits

                        Adding more xml attributes (help with xmlElement and XMLForest)

                        This should work
                        declare 
                          l_clob        clob;
                          l_limit       NUMBER := 32767;
                          v_text_amt    BINARY_INTEGER := l_limit;
                          v_text_buffer varchar2(32767);
                          v_text_pos    NUMBER := 1;
                        begin
                        
                        --Create a tempory LOB to place our xml in
                          dbms_lob.CREATETEMPORARY(lob_loc => l_clob
                            , cache   => false
                            , dur     => dbms_lob.session
                          );
                          
                        --Generate some xml whihc in our case is an html table
                        --We place this in a clob in case its over 32k 
                        --in which case we would hit a limit
                          
                          select
                                      xmlelement(
                                          "rows"
                                        , xmlagg(
                                            xmlelement(
                                                "row"
                                              , xmlattributes(row_number() over (order by ename) as "id")
                                              , xmlforest(
                                                    empno as "cell"
                                                  , ename as "cell"
                                                  , job as "cell"
                                                  , mgr as "cell"
                                                  , hiredate as "cell"
                                                  , sal as "cell"
                                                  , comm as "cell"
                                                  , deptno as "cell"))
                                            order by ename asc)) xml
                            into
                                      l_clob
                            from
                                      emp;
                         
                            owa_util.mime_header('text/xml', false);
                            htp.p('Cache-Control: no-cache');
                            htp.p('Pragma: no-cache');
                            owa_util.http_header_close();
                            
                        --Now to loop through the CLOB in 32k intervals 
                        --so we can htp.p the string of data back on the page
                        
                          LOOP
                            v_text_buffer := DBMS_LOB.SUBSTR(l_clob, v_text_amt, v_text_pos);
                            EXIT WHEN v_text_buffer IS NULL;
                        -- process the text and prepare to read again 
                              htp.p(v_text_buffer);
                              v_text_pos := v_text_pos + v_text_amt;
                           END LOOP;
                        
                        --Kill the temporary LOB   
                           DBMS_LOB.FREETEMPORARY(lob_loc => l_clob);
                           
                        end ;
                        • 9. Re: XML returned from on-demand process
                          Alistair Laing
                          This time with getclobval and xmlelement rather than xmlforests
                          declare 
                            l_clob        clob;
                            l_limit       NUMBER := 32767;
                            v_text_amt    BINARY_INTEGER := l_limit;
                            v_text_buffer varchar2(32767);
                            v_text_pos    NUMBER := 1;
                          begin
                          
                          --Create a tempory LOB to place our xml in
                            dbms_lob.CREATETEMPORARY(lob_loc => l_clob
                              , cache   => false
                              , dur     => dbms_lob.session
                            );
                            
                          --Generate some xml whihc in our case is an html table
                          --We place this in a clob in case its over 32k 
                          --in which case we would hit a limit
                            
                            select
                                        xmlelement(
                                            "rows"
                                          , xmlagg(
                                              xmlelement(
                                                  "row"
                                                , xmlattributes(row_number() over (order by ename) as "id")
                                                , xmlelement("cell", empno)
                                                , xmlelement("cell", ename)
                                                , xmlelement("cell", job)
                                                , xmlelement("cell", mgr)
                                                , xmlelement("cell", hiredate)
                                                , xmlelement("cell", sal)
                                                , xmlelement("cell", comm)
                                                , xmlelement("cell", deptno)
                                              )
                                              order by ename asc)).getclobval()
                              into
                                        l_clob
                              from
                                        emp;
                           
                              owa_util.mime_header('text/xml', false);
                              htp.p('Cache-Control: no-cache');
                              htp.p('Pragma: no-cache');
                              owa_util.http_header_close();
                              
                          --Now to loop through the CLOB in 32k intervals 
                          --so we can htp.p the string of data back on the page
                          
                            LOOP
                              v_text_buffer := DBMS_LOB.SUBSTR(l_clob, v_text_amt, v_text_pos);
                              EXIT WHEN v_text_buffer IS NULL;
                          -- process the text and prepare to read again 
                                htp.p(v_text_buffer);
                                v_text_pos := v_text_pos + v_text_amt;
                             END LOOP;
                          
                          --Kill the temporary LOB   
                             DBMS_LOB.FREETEMPORARY(lob_loc => l_clob);
                             
                          end ;