7 Replies Latest reply: Feb 18, 2013 6:18 PM by xarg-Xrc RSS

    Error when painting page error during AJAX call

    xarg-Xrc
      Hi all

      I am doing a simple AJAX call test with the emp table. In my page I have a search field and a report and a button. You put in a text and click search and you will get the result which all works.

      I have a requirement where if you click the button and there is no matching search you will get redirected to another page, else just reload the report to display the result as usual.

      So I took the AJAX route where I am running a concurrent search when user enters a text and clicks the button. If the search count(*) > 0 then return return count else return 0.

      The code for application process is:
      DECLARE
      begin
      CCOUNT number;
      
      
      
      begin
      Select count(*) into ccount 
      from emp
      WHERE 
      UPPER(:F_SEARCH_RESULT) like '%'||upper(ename)||'%';
      
      exception when no_data_found then
      htp.p(0);
      end;
      
      htp.p(ccount);
      
      
      END;
      The code for my Java script (still working on this) is :
      <script language="JavaScript" type="text/javascript">
      
      
           function search_count()
      
           var get = new htmldb_Get(null,null,'APPLICATION_PROCESS=SEARCH',0);
           get.add('F_SEARCH_RESULT', $v('P1_ENAME'));
           var gReturn = get.get();     
           console.log(gReturn);
      
           var gReturn_sanitised = praseFloat(gReturn);
                if (gReturn_sanitised == 1)
                doSubmit('SUBMIT');
           
                
      </script>
      I am testing this code in Firebug and when I do a console.log(gReturn) on the output of the AJax call I am getting this error which stops me from processing the result of gReturn and finish off the code. The error looks like:
      <html><body>
      <h1>Error occurred while painting error page: ORA-01403: no data found</h1>
      </body></html>
      I have tried adding exeption when no_data_found but with no luck. Still throws the same error. My APEX version is 4.2, any suggestions welcome. Cheers.
        • 1. Re: Error when painting page error during AJAX call
          swesley_perth
          Is this code verbatim? I spot two errors, but I wouldn't think either would return no data found - and select coun(*) should never do so
          1) Variable should be between declare and begin
          DECLARE
          CCOUNT number;
          begin
          2) parseFloat has a typo

          Scott
          • 2. Re: Error when painting page error during AJAX call
            fac586
            xarg-Xrc wrote:
            Hi all

            I am doing a simple AJAX call test with the emp table. In my page I have a search field and a report and a button. You put in a text and click search and you will get the result which all works.

            I have a requirement where if you click the button and there is no matching search you will get redirected to another page, else just reload the report to display the result as usual.

            So I took the AJAX route where I am running a concurrent search when user enters a text and clicks the button. If the search count(*) > 0 then return return count else return 0.

            I am testing this code in Firebug and when I do a console.log(gReturn) on the output of the AJax call I am getting this error which stops me from processing the result of gReturn and finish off the code. The error looks like:
            <html><body>
            <h1>Error occurred while painting error page: ORA-01403: no data found</h1>
            </body></html>
            I have tried adding exeption when no_data_found but with no luck. Still throws the same error. My APEX version is 4.2, any suggestions welcome. Cheers.
            The no data found exception is coming from APEX in response to the syntax error in your code, not because your query doesn't return any rows. A <tt>count(*)</tt> query without a <tt>group by</tt> will always return 1 row: the number of rows or 0 if none are found.
            The code for application process is:
            DECLARE
            begin
            CCOUNT number;
            
            
            
            begin
            Select count(*) into ccount 
            from emp
            WHERE 
            UPPER(:F_SEARCH_RESULT) like '%'||upper(ename)||'%';
            
            exception when no_data_found then
            htp.p(0);
            end;
            
            htp.p(ccount);
            
            
            END;
            This is invalid. The outer <tt>begin</tt> should be before the <tt>declare</tt>. However as explained above, the <tt>no_data_found</tt> exception&mdash;and hence the whole nested block structure&mdash;is unnecessary.
            declare
            
              n number;
            
            begin
            
              select count(*)
              into n
              from emp
              where upper(:f_search_result) like '%' || upper(ename) || '%';
            
              htp.p(n);
             
            exception
            
              when others
              then
                htp.p(sqlerrm);
                raise;
            
            end;
            This is one of the few places where a <tt>when others</tt> exception handler of this type is valid&mdash;at least for testing. For serious production code you would have a proper logging mechanism.

            Always unit test on-demand application processes in an empty browser window before hooking them up to AJAX code:
            http://apex.oracle.com/pls/apex/f?p=TEST_APP:1:14755413778644:APPLICATION_PROCESS=SEARCH:::F_SEARCH_RESULT:KING
            
            http://apex.oracle.com/pls/apex/f?p=TEST_APP:1:14755413778644:APPLICATION_PROCESS=SEARCH:::F_SEARCH_RESULT:
            The code for my Java script (still working on this) is :
            <script language="JavaScript" type="text/javascript">
            
            
                 function search_count()
            
                 var get = new htmldb_Get(null,null,'APPLICATION_PROCESS=SEARCH',0);
                 get.add('F_SEARCH_RESULT', $v('P1_ENAME'));
                 var gReturn = get.get();     
                 console.log(gReturn);
            
                 var gReturn_sanitised = praseFloat(gReturn);
                      if (gReturn_sanitised == 1)
                      doSubmit('SUBMIT');
            </script>
            There's a syntax error there as well: we come to <tt>parseFloat</tt>s, not <tt>prase</tt> them.
            • 3. Re: Error when painting page error during AJAX call
              xarg-Xrc
              Hi thanks for pointing out. I already picked up the parseFloat just picked the wrong code when copy pasted here. Will follow suggestions when near a PC and hopefully will fix it.
              • 4. Re: Error when painting page error during AJAX call
                xarg-Xrc
                Still no luck, even if I do a simple:
                begin
                htp.p('0'); 
                end;
                in the page process, I am getting the same error as before.

                I've done many AJAX call like this before elsewhere and cant for the life of me figure out whats wrong here.

                I have reproduced the issue in apex.oracle.com

                WORKSPACE: AIWORSKSPACE
                USER: TEST
                PASSWORD: test
                APP #: 69721
                Page: 1
                • 5. Re: Error when painting page error during AJAX call
                  fac586
                  xarg-Xrc wrote:
                  Still no luck, even if I do a simple:
                  begin
                  htp.p('0'); 
                  end;
                  in the page process, I am getting the same error as before.

                  I've done many AJAX call like this before elsewhere and cant for the life of me figure out whats wrong here.

                  I have reproduced the issue in apex.oracle.com

                  WORKSPACE: AIWORSKSPACE
                  USER: TEST
                  PASSWORD: test
                  APP #: 69721
                  Page: 1
                  The <tt>F_SEARCH_RESULT</tt> application item had the default Session State Protection security setting of Restricted - May not be set from browser. Change this to Unrestricted.
                  • 6. Re: Error when painting page error during AJAX call
                    Tom Petrus
                    ^ Paul beat me.
                    • 7. Re: Error when painting page error during AJAX call
                      xarg-Xrc
                      Thanks for help. Works now. Cheers.