hidden gems – Page 2

Hidden Gems of APEX

November 5, 2014 by David Leave a Comment

I just finished giving my presentation on “Hidden Gems of APEX” at the East Coast Oracle Users conference. I was pleasantly surprised at attendance–I’d guess that I had about 20 to 25 people. As promised, here is the slide deck for my presentation.

This was not what I would term a “typical” presentation, as it wasn’t focused coherently on a specific part of APEX or how to do something with the tool; it was merely a list of the potentially useful parts of APEX that I have found to be useful, but which I don’t feel are as widely known as they should be.

Conference News

October 8, 2014 by David Leave a Comment

Wow, it’s been a while since I updated this blog. Sorry about that–I moved in the summer, and have been spending time with some of the stuff that comes up whenever you go through a significant life change. Oh, yeah, and I’ve been doing a lot of work–along with the rest of the team–on getting APEX 5.0 cleaned up.

Speaking of which, you’ve probably already heard the news out of the Open World conference–APEX 5 will go through a third early adopter phase. We’ve made some significant progress since EA 2 was released, but we’re not far enough along to go full production at this point. The packaged applications all need to be converted over to the new universal theme, for instance; we’ve started on that work, but there are over 2,000 pages that we’ll need to go through. That’s…a little more than an hour or two’s work, you know?

I did have fun at Open World, and got to meet a lot of people who use APEX. If I met you there and that brought you to this blog, welcome!

In upcoming conference news, I’ll be presenting at the East Coast Oracle Users Conference in early November. If you’re going to be in the area, come on by. My presentation will be on the Hidden Gems of APEX.

APEX Tip: Page Items to Submit

July 1, 2014 by David 14 Comments

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:

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

June 13, 2014 by David 23 Comments

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!

Packaged App Feature: Flex Fields

June 5, 2014 by David 6 Comments

One of the big challenges for us when building the packaged applications is that we’re effectively developing blind–we don’t have a direct customer who is providing us with specific project requirements, so we do our best to come up with the fields that users are likely to want. Naturally, this is…difficult.

One of our main solutions to this problem is the introduction of “flex fields”–extra columns that application administrators can use for data that we didn’t think to build in. Our first implementation was released in the Customer Tracker application in 4.2.4, but we decided that it wasn’t as user-friendly as we’d like, so in the 4.2.5 release, we included flex fields with a much better interface in Customer Tracker and P-Track. We’ll be adding them to more applications as we go forward.

The design is fairly straightforward: any table which supports flex fields has 17 additional columns–8 varchar2(4000), 4 number, 4 date/timestamp, and one CLOB. There’s also a flex registry table that tracks which column(s) have been enabled, what the label should be, if it has a format mask, and so on. We tried to include a lot of options, so you can customize it pretty thoroughly. All of the relevant report pages have columns for the flex fields which display only if the field is active, and all of the relevant form pages include fields for the flex columns (which are, again, conditional).

This last bit is really where the biggest chunk of “magic” comes into play. Because we allow for a variety of different item types (text field or area, select list, date picker, number field), the built-in APEX page items weren’t sufficient. Instead, we built a new item plugin that looks at the registry table to figure out how to correctly render the page item.

If you’re using the Customer Tracker or P-Track packaged apps already, try out the flex fields. If you’re developing your own applications for customers who periodically request that columns be added or removed (wait, project specs never change capriciously, right?), take a look at our implementation and see if you can incorporate it–might just save yourself some hassle!

APEX 4.2.5: Standards Tracker

April 23, 2014 by David 2 Comments

I blogged about the Application Standards Tracker packaged application when it was initially released with APEX 4.2.4. Doing so, however, reminded me of several limitations that I knew existed–I was there from its creation, after all–but had been willing to put up with for the first pass. And, well, we couldn’t let it stand at that, so we took some time and made it better. You’re welcome. ;-)

The core idea of the application is the same as before: “since APEX applications are all built/driven off of the metadata in the database–and since that metadata is exposed in the APEX Data Dictionary views–we can check that metadata for potential issues.” But we weren’t happy about how all of the automated tests were simple “Pass/Fail”–either the application totally met the requirements of the test, or there was something wrong, somewhere, but it was up to you to go find it. So we introduced a new “Report” type of automated test (and made it the default, though you can still use a pass/fail test if that’s what makes sense). The SQL for Report automated tests is mostly flexible, allowing you to include whatever information you feel is necessary/helpful in determining what needs to be fixed, but there are two requirements:

The first column must be a Y/N value to indicate whether the test is passed by that particular component.
The second column should be the ID of the component in the application (and is not displayed to the user).

The reason for this second requirement is to enable the main new functionality of the application: you can now view the report of all of the components that are tested, and if you’ve got an active developer session in the APEX Builder, you’ll be presented with edit links which will take you directly to the relevant component.

Example

A common issue is to have a page item that has help text, but which doesn’t use an item label template that exposes the help to the user. You can find these using the data dictionary, looking at APEX_APPLICATION_PAGE_ITEMS and APEX_APPLICATION_TEMP_LABEL:

select case when upper(tl.theme_class) like '%WITH HELP%' then 'Y' else 'N' end as pass_fail,
 pi.item_id,
 pi.page_id||'. '||pi.page_name page,
 pi.region,
 pi.item_name,
 pi.label,
 pi.display_as item_type,
 pi.item_label_template,
 tl.theme_class
from apex_application_page_items pi,
 apex_application_temp_label tl
where pi.application_id = <APPLICATION_ID>
 and pi.display_as_code != 'NATIVE_HIDDEN'
 and pi.item_help_text is not null
 and pi.item_label_template_id = tl.label_template_id
 and pi.application_id = tl.application_id

This query gives us a list of all (non-hidden) page items with help text for the given application (put what you want in place of the <APPLICATION_ID> placeholder). If the label template doesn’t fall into one of the “with help” templates, it’s flagged as not passing. As noted above, the first column is a simple Y/N; the second is the page item’s ID, so we can use this for the automated test in Standards Tracker, as shown in the screenshot on the right (click for a bigger, easier to read version). Note that the Type is set to Report, and we’re linking to a Page Item. The only other change is that we’re using a bind variable for the application id; I tend to stick with the default :APP_ID.

Once this is set up as a test linked to the appropriate application(s), you should see something like this second screenshot. For convenience, I’ve collapsed the Automated Tests region and toggled the “Show Only” field (another new feature, by the way) to only include the test we’ve been talking about. You’ll note that the Status column shows a variety of progress bars; some applications are at 100%, one is at 0%, and one is somewhere around 80%. Again, we’re not limited to “this application passed or failed as a whole” like we were in the first release. Incidentally, if there are no items in the application which match the test–so, in our case, no page items with help text–then the application shows as 100%, since there’s nothing to fix.

Clicking on the “View Details” button to the right of the status bar opens up a dialog showing the completion percentage for that application, for all of the automated tests in the current standard. This is just another way to view the data, and useful if you’re looking at a standard with multiple automated tests. But the real fun comes next.

Clicking on the “View” button next to the automated test in the dialog takes you to this report page. You can change the Application via the select box, if you want to quickly toggle through all of the relevant applications, and you can toggle whether the report shows only the items that pass, only those that fail, or all items; when I’m working, I tend to prefer the “Only Failures” option (as shown), because those are the ones that need to be fixed. And you’ll notice the yellow pencil icons next to each of the entries–those appear because I logged into the APEX Builder prior to loading the page, and thus have an active session.

Clicking on one of the pencils launches a new window–specifically, an instance of the APEX Application Builder using your current session, loaded directly to the application and component from the report. You can edit the component, hit Apply Changes, and then toggle back to the Standards Tracker window; clicking on the next pencil icon will redirect the new window to the next component. This makes it very easy to quickly update your applications to meet the standards.

Other Builder Integration Features

There are also two new buttons on the Application details page (either click on an application from the Applications tab, or click on an application name from the Standards details page), as seen here. The first, labeled “Edit Application”, launches a new window (or re-uses the one opened above, if it’s still active) loaded onto the main APEX Application Builder page for the application. The second, labeled “APEX Advisor”, directs the window to the APEX Advisor for that application. The Advisor is a nice tool, and will help you identify potential issues in your applications prior to your users being bothered by them, so I highly recommend running it against your applications periodically; the biggest problem is that it can be hard to find–you have to open an application in the APEX Builder, then click on Utilities, and then click on Advisor in the lower left corner. This button is a bit more convenient, at least if you’re already using the Application Standards Tracker.

Packaged Application: Data Reporter

March 7, 2014 by David 6 Comments

I was talking with one of my co-workers yesterday about the Data Reporter packaged application, and it occurred to me that I never actually blogged about it. Which is a real shame, as it is one of our more powerful applications!

One of the really nice features of APEX is the interactive report, which has been around since (I believe) the 3.1 release. It’s especially nice for developers, because we can build a report once, and then allow the users to modify it to meet their needs, within reason. If they want columns re-ordered, or filtered, highlighted, shown/hidden, etc., they can make those changes themselves, without waiting for a developer to make the changes, get it QA’d, and then rolled out to production. They’re very powerful. One of the downsides, however, is that it still requires a developer to build the initial report, or update the query if someone comes up with additional columns that should be included.

Data Reporter, which was originally released in APEX 4.2.3, is our solution to that. Once the application is installed, any user with contributor or administrative access can log in and create a new report, querying whatever data they need. If someone wants to create a report, but doesn’t know SQL–or isn’t very well versed in it–an administrator can create a “data source” (really, just a query) which the users can then use as the basis for their report. As you can imagine, given that we’re giving the users the ability to write SQL against the database, security was a chief concern as we built the application; one chief protection we implemented is the mandatory white-listing of objects. If your query refers to a table or view that’s not on the whitelist, you won’t be able to create it.

You can create a variety of report “types” within the application–Interactive Reports (mentioned above); Calendars; Filter Reports (I blogged about them here); and Dashboards (intended to be quick-glance summarizations of your data). Additionally, if you’re running APEX through Oracle REST Data Services–formerly known as the APEX Listener–you can harness its ability to create PDF reports on the fly.

My co-worker was looking at setting Data Reporter on an instance where we’re trying to eliminate the ability for users to log in and query data directly from the database, without also blocking them from getting the data they need. Through this packaged application, we’ll be able to lock down the database accounts, give the users the data they need in usable formats, and keep a record of how often they look at which reports. Seems like a win all around to me.

Pipelined Functions

January 14, 2013 by David Leave a Comment

Yep, this is another post where I’m going to talk about a feature of Oracle which I didn’t know about until very recently, and which I wish I’d heard of a lot sooner. Today’s topic is pipelined functions.

Now, I feel like I may have heard about pipelined functions once or twice over the years, but I never really understood what they could be used for. Part of the problem may be that when they’re mentioned, it’s often in the specific case of loading large amounts of data into a data warehouse environment, and most of my programming is for querying small amounts of data from ERP-type systems. But pipelined functions are not just for data warehouse situations!

If you’ve been following this blog for a while (or just read through the archives), you’re probably aware of my posts about database views and how they can be very powerful. And that hasn’t changed. But I’ve also found that, if you’re not very careful, referencing views from other views can quickly lead to massive performance problems. WriteTrack ran into this, though it was minor enough that I was able to get around it; I had a series of nested views which produced the calendars for every challenge for a specific user. Originally, the views were designed to produce the calendars for every challenge for every user, but this didn’t work well*, and I had to put v(‘APP_USER’) into the views–which meant that I couldn’t check the views through the SQL workshop, and that I couldn’t easily debug what was happening if a user complained that they were getting the wrong information.

Then I found out about pipelined functions, which were introduced by Oracle way back in 9i. A pipelined function is simply a PL/SQL function which returns a result that can be treated as a table in SQL. One of the first places I used them was in parsing an XML file; I created a view which wrapped the call to the XML parsing function, and was able to treat the data from the XML as if it were a table–all of the ugly calls to DBMS_XML were safely tucked away, making for much cleaner code (and easier APEX development!).

So, last October, I returned to my probelmatic WriteTrack views and re-wrote them as wrappers around a pipelined function. Now, instead of having four or five nested views to do all the necessary calculations (a debugging headache of its own), all the code is in one function. Which can, incidentally, produce all of the data for all users and all challenges in about a second.

* Ok, it didn’t work at all. Once there were multiple users and several different challenges, querying the overall view simply failed to return anything in less than an hour–hardly acceptable for an APEX application!