APEX Tip: Page Items to Submit

One issue that I run into periodically–especially when working with dynamic actions–is that the value of a page item in session state doesn’t match its value in the browser (what I call the DOM state). The simplest way to think about it is to view every page item as part of a form. Logically, when the user is entering data into a form, the HTML page elements have values that are not stored in APEX session state until the form is submitted. This is true whether the user is typing values in, or if the values are being set by a Set Value dynamic action or the $s(…) javascript function. And it’s true regardless of whether or not the page is actually a data entry form.

The issue that I run into is generally when I’m working on a different sort of page–typically, a report or a dashboard–that happens to include page items and dynamic actions. As an example, consider a page with two report regions, where the first region includes links; when the user clicks on a link, we want to set a page item and refresh the second report with details about the clicked-on link. This is a fairly straight-forward scenario, and can provide the user with a lot of relevant information in a compact, usable fashion.

My standard approach to this is to define the link target as a call to $s(…), which sets the value of a hidden page item. Then a dynamic action, set to fire on change of the hidden item, triggers the refresh of the second report region. But it turns out, this isn’t enough–because the $s(…) call sets the DOM state, while the report’s SQL is referencing the session state of the item. So although the report refreshes, it doesn’t reflect the new value of the hidden page item.

The solution, of course, is to set the session state of the hidden page item with the value from the DOM state. My first approach to doing this was to change the dynamic action to use two steps, with the first step being a PL/SQL action that looked like this:

plsql_da

The trick, of course, is the Page Items to Submit field, which is only present on PL/SQL actions; the values from any page items listed here are copied from the DOM state into session state when this action fires. There’s nothing else going on here, hence the null; code body. This solution works, but it’s pretty cumbersome, and isn’t self-documenting–someone looking at the dynamic action who doesn’t understand the trick is likely to be very confused by this action that “does nothing”.

Luckily, there’s a better way. When you edit a report, whether classic or interactive, you’ll note that there’s a Page Items to Submit field there, too! And it turns out that the submission happens whenever the report is refreshed, so by specifying the hidden page item in the list for the report, the original dynamic action–that just refreshed the report region–does exactly what we need it to. Much cleaner.

So, in general: whenever you build a report that depends on page or application items, go ahead and include those in the Page Items to Submit field. That way, if you ever decide to refresh the report via dynamic action, you won’t run into this issue.

APEX How-To: Download a File

One thing I periodically need to do in my applications is to have a link which allows the user to download a file. If you’re dealing with a blob column in a report, that’s fairly straightforward; but what if you want to allow the user to export data from your application in a file that’s generated on the fly? There’s nothing in APEX that allows you to say, “I want to have a link/button which takes this query, turns it into XML/JSON/whatever, and downloads it to the client machine”. But it is possible!

Some background may be necessary; feel free to skip this paragraph. A browser following a link doesn’t “know” what it’s going to receive. It makes an HTTP request, and then waits for the HTTP response, which is broken into a header and everything else. The header tells the browser what it’s getting in the rest of the message, and the browser “does the right thing” from there.

In a typical APEX page, the APEX engine generates the HTTP header, telling the browser that it’s getting an HTML document, which is then rendered to the screen for the user. And that’s exactly what we don’t want to have happen if we’re generating a file to be downloaded, so we need to intervene. The simplest way to do this–there are others–is to have a page which consists solely of a “Before Header” PL/SQL process. This means that it fires before the APEX engine generates the HTTP header, so we can generate our own, serve up our file contents, and then shut down the APEX engine so that it doesn’t do anything else on the page. And here’s the code that does just that!

declare
    l_clob clob;
    l_blob blob;
    l_sql_delimiter varchar2(30);
    l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
    l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
    l_dest_offset   integer := 1;
    l_source_offset integer := 1;
begin
    l_clob := null;
    dbms_lob.createtemporary(l_blob, true);

    -- Generate your file content here in l_clob.

    sys.htp.init;
    sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' );
    sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
    sys.htp.p('Content-Disposition: inline; filename="FILENAME.TXT"' );
    sys.owa_util.http_header_close;

    dbms_lob.converttoblob (
        dest_lob    => l_blob,
        src_clob    => l_clob,
        amount      => DBMS_LOB.LOBMAXSIZE,
        dest_offset => l_dest_offset,
        src_offset  => l_source_offset,
        blob_csid   => DBMS_LOB.DEFAULT_CSID,
        lang_context=> l_lang_context,
        warning     => l_warning
    );

    sys.wpg_docload.download_file(l_blob);
    apex_application.stop_apex_engine;
exception when others then
    sys.htp.prn('error: '||sqlerrm);
    apex_application.stop_apex_engine;
end;

Trust me, it’s less complicated than it looks. Lines 1-8 are standard variable declarations; lines 10 and 11 initialize the CLOB and BLOB used in the process; line 13 is where you should actually generate your file (or else this code will be fairly boring); lines 15-19 generate the HTTP header we mentioned above (remember to provide a useful filename); lines 21-30 convert the generated CLOB into a BLOB, which is then passed to the browser on line 32; and line 33 just tells the APEX engine to stop. Exactly what we want!

With this in place, you can branch to this page using normal APEX objects, and instead of receiving a new page, the browser will download the file. Useful recipe to have in your toolbox for when you need it.

Edit: Thanks to Dan McGhan (@dmcghan) for pointing out that I referred to the HTML header when I should have properly referred to the HTTP header. HTTP is, of course, the network protocol that browsers generally use, while HTML is simply a type of document. Sorry for any confusion!

The APEX Data Dictionary

The APEX data dictionary tree view

One of the APEX features I find myself using frequently is the APEX data dictionary. I'll get to "Why?" in a moment--but I feel like I need to answer the "What?" question first, since I've talked to a fair number of developers who either don't know … [Continue reading]

Opening a new Tab

Every now and then, I need to have a control on my page open a new tab. And for anchors, that's easy--just stick a target="_blank" on the anchor tab and get on with your life. Unfortunately, you very rarely build the anchor tabs in APEX; the engine … [Continue reading]