APEX Mystery Solved

I have used Oracle APEX in various capacities since version 3. Since Oracle introduced their packaged applications in version 4 I have played around with most of them from time to time and currently use several of them in my daily work. P-Track, ORAchk, WebSheets and others make my job a lot easier, and the new design interface in APEX 5 is fantastic. I am currently using APEX 5.0.2, ORDS 3.0.2, Tomcat 8, and Oracle Database

One of the apps that has always seemed to have a lot of potential – but never quite lived up to it in my opinion – is the Survey Builder. While the user interface is not perfect, it has a lot of potential. The app offers functionality generally only seen in commercial products like Survey Monkey. Complicated surveys can be generated, distributed, and tracked with ease. It has one really major flaw however, that always prevented me from using it in any real sense: the page that displays the survey to the user randomly displays a blank white screen.

When I looked at the survey page’s source HTML code using Internet Explorer (Firefox showed no source code at all), I saw a truly blank document, with nothing between the <BODY> tags:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> 
 <META content="text/html; charset=windows-1252" http-equiv=Content-Type></HEAD> 

The problem was maddeningly inconsistent. Sometimes the survey would display with no problems, other times it would display correctly in Internet Explorer but not Firefox, or vice versa. Sometimes it wouldn’t work for any browser. The only thing that seemed to be completely predictable was that once a particular browser failed, the survey would not load correctly again until Tomcat was restarted.

People have noticed this and commented about it for years, posting in various online forums with threads like this one, offering advice on tuning Tomcat and such to minimize (but not eliminate) the problem. I tried their recommendations but never had any real success; adjusting Tomcat parameters didn’t really seem to make much difference other than to delay (by only a few minutes) the inevitable, and I couldn’t deploy surveys to my client base without being confident that they would be able to actually see them. Recently however a customer had a real need for this application’s capabilities, and I took another look at what was really going on in the application code in an effort to finally get it working the way it should.

This time I started with the code because because I had already spent a lot of time troubleshooting Tomcat and various browser configurations to no avail. Since no other part of APEX was having issues – only the delivery of this single, essentially static page – I had no reason to think that Tomcat was the culprit other than the various forum posts I had already found. If it had been I would have expected to see many more widespread problems with APEX and that just wasn’t happening.

What I did find after unlocking the application code and taking a look under the hood is that the Survey Builder application does things a little differently than other APEX apps in an attempt to improve performance and the end-user experience. The process is described in detail in this blog post from one of the original Survey Builder developers, but the gist of it is that when the survey is published by the author its HTML is pre-rendered and stored in a database column rather than being built dynamically at run time like all other APEX pages. This decreases the amount of processing time required to deliver the page to the user. When the user connects to the survey, the pre-rendered, effectively static content of the survey page is then delivered to the browser through a PL/SQL call.

After examining the content of the pre-rendered survey page in the EBS_SB_PAGES table, it appeared that the survey generation was working correctly. The HTML code stored in the database was complete and correct in every way. The fact that the survey was actually delivered correctly on some occasions supported this. How then, if the survey was being rendered correctly, was it not being delivered consistently?

The answer turned out to be in the application code itself. The same APEX page – page 100 of the Survey Builder application – is used to deliver the survey whether in testing mode or actual deployment. There is almost no code on the page, except a “static content” process which begins with a simple query to retrieve the static survey content and an exception to return a “not found” page if the query returns no results:

    select apex_escape.html(q.lang) lang,
        to_char(systimestamp, 'TZR')) at time zone 'GMT',
    into l_lang, l_last_updated_ts, l_page_content
    from eba_sb_pages p, eba_sb_questionnaires q
    where p.questionnaire_id = q.id and p.key = l_key;
  exception when NO_DATA_FOUND then
    -- The web listener doesn't have the ability to return app specific 404 messages
    -- so return 200 with a message saying the survey is not found
      nstatus => 200,
      creason => 'Not Found',
      bclose_header => false);
    sys.owa_util.mime_header('text/html', false);
    sys.htp.p('<html><head><title>Not Found</title></head><body>Survey not found</body></html>');
    wwv_flow.g_page_text_generated := true;

I wasn’t seeing “not found” and I could confirm that the survey code was correctly stored in the database, so it seemed logical to assume that we were making it past this part of the code without error. The next part of the procedure checked the HTTP_IF_MODIFIED_SINCE header to see if the survey had been previously downloaded, and if so return an HTTP 304 code instructing the browser to load it from its own cache – further improving response time. If the header value indicated that the page had not previously been downloaded the the code should return an HTTP 200 code and download the page normally.

  -- Get Last Modified from the HTTP header and compare. If equal, emit an HTTP 304 to the browser
  -- and return, indicating that the file should be used from the browser cache
  l_modified_since := sys.owa_util.get_cgi_env('HTTP_IF_MODIFIED_SINCE');
  if l_modified_since is not null then
    -- Account for the rare case when HTTP_ID_MODIFIED_SINCE contains a proprietary Netscape extension, a semicolon separated value
    if instr(l_modified_since,';') > 0 then
      l_modified_since := substr(l_modified_since,1,instr(l_modified_since,';')-1);
    end if;
    l_modified_since_ts := to_timestamp_tz(l_modified_since, 'Dy, DD Mon YYYY HH24:MI:SS TZR', 'NLS_DATE_LANGUAGE = AMERICAN');
    if l_last_updated_ts = l_modified_since_ts then
        nstatus => 304,
        creason => 'Not Modified',
        bclose_header => true);
      wwv_flow.g_page_text_generated := true;
    end if;
  end if;

The end of the procedure actually transmits a number of HTTP headers as well as the actual survey code:

  l_content_length := sys.dbms_lob.getlength(l_page_content);
  sys.owa_util.mime_header('text/html', false, 'utf-8');
  sys.htp.p('Content-Length: ' || l_content_length);
  sys.htp.p('Content-Language: ' || l_lang);
  sys.htp.p('Last-Modified: ' || to_char(l_last_updated_ts, 'Dy, DD Mon YYYY HH24:MI:SS', 'NLS_DATE_LANGUAGE = AMERICAN') || ' GMT' );
  sys.htp.p('Cache-Control: must-revalidate, max-age=0');
  sys.htp.p('Expires: 0');
  -- set page generated to true in order to stop rendering after completion of this PL/SQL block
  wwv_flow.g_page_text_generated := true;

None of these headers were showing up in my “blank” page, so it also seemed reasonable to believe that we were never reaching this part of the procedure. It appeared that the application code was incorrectly identifying the survey as being in the browser cache, when it was actually not. The browsers then responded by showing a blank page.

I presented my findings to Oracle Support and they were able to duplicate my test results and essentially confirmed what I was seeing:

In house assessment is basically correct except the code never returned an HTTP 304 status. It never got that far.

Was able to duplicate the issue you described below. Added debug to the opening of the questionnaire page (100) and found that when the page rendered blank, the call to sys.owa_util.get_cgi_env(‘HTTP_IF_MODIFIED_SINCE’) (to populate the l_modified_since variable) was returning a very strange string not at all resembling a timestamp.

Since the l_modified_since variable was not null, the rest of the PL/SQL was trying to convert it into a timestamp (and failing)…

Also added a call to apex_debug.message() to show the value of the l_modified_since variable and found that whether the variable was null or populated correctly, the page rendered as expected. It only rendered blank when the timestamp conversion failed.

The final solution turned out to be quite elegant in its simplicity – simply to comment out the one line of code that reads the HTTP_IF_MODIFIED_SINCE header and force the l_modified_since variable to always be “null”:

--l_modified_since := sys.owa_util.get_cgi_env('HTTP_IF_MODIFIED_SINCE'); 
l_modified_since := null;

I made the change, which essentially forces the survey page to download every time and never use the browser cache, and immediately the survey page loaded correctly. I have now been testing it for a couple of months and it continues to load correctly every time.

One thought on “APEX Mystery Solved

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.