APEX User Interface Defaults: A deep dive into Table Dictionary and Attribute Dictionary

Contents

Let’s learn more about APEX User Interface Defaults and its components such as Table Dictionary and Attribute Dictionary. Is this ancient Oracle APEX feature worth anything in 2024?

APEX UI Defaults have been around for a long time. I tried to find which version they appeared in but I couldn’t nail it down exactly. I’m going to guess they predate APEX 4.0. It’s an ancient feature at least 15 years old. However, despite their age, I never really used UI Defaults, so I didn’t really understand their purpose.

I thought it was all about setting Labels and Configurations at the column level so that – when the wizards construct a Form or Report – they use those Labels, etc. As it turns out…. that’s precisely what they’re used for. Phew, so I wasn’t far from the mark. So, should I start using them now I’ve researched them? Or should I continue to ignore them for another 15 years? Let’s find out!

This is part three (and possibly the final entry) of a series of articles entitled Things in APEX I’ve used for years but have not fully understood how they work. Here you can find the previous entries:

Straight into an example

Let’s start by building a table:

CREATE TABLE football_league (
    id           NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- Unique ID for each team
    name         VARCHAR2(100) NOT NULL,                               -- Team name
    pos          NUMBER,                                               -- Position in the league
    p            NUMBER DEFAULT 0,                                     -- Games played
    w            NUMBER DEFAULT 0,                                     -- Wins
    d            NUMBER DEFAULT 0,                                     -- Draws
    l            NUMBER DEFAULT 0,                                     -- Losses
    gf           NUMBER DEFAULT 0,                                     -- Goals For
    ga           NUMBER DEFAULT 0,                                     -- Goals Against
    gd           NUMBER,                                               -- Goal Difference
    pts          NUMBER,                                               -- Points
    created_by   VARCHAR2(50),                                         -- Who created the record
    created      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,                  -- When the record was created
    updated_by   VARCHAR2(50),                                         -- Who last updated the record
    updated      TIMESTAMP DEFAULT CURRENT_TIMESTAMP                   -- When it was last updated
);

Now, let’s populate it with data:

INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Leeds United', 1, 5, 3, 1, 1, 10, 5, 'admin', 'admin');

INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Norwich City', 2, 5, 3, 1, 1, 9, 6, 'admin', 'admin');

INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Sunderland', 3, 5, 2, 2, 1, 8, 4, 'admin', 'admin');

INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Sheffield Wednesday', 4, 5, 2, 1, 2, 7, 7, 'admin', 'admin');

INSERT INTO football_league (name, pos, p, w, d, l, gf, ga, created_by, updated_by)
VALUES ('Coventry City', 5, 5, 1, 3, 1, 6, 6, 'admin', 'admin');

If I create a Report with a Form using the wizard, it looks like I expected it to. It’s a good starting point, I guess. What’s with those floating-point numbers?

The Form looks a bit socially awkward, though. 😏

Usually, I’d get cracking on making the app more usable, adding help texts, choosing format masks, correcting the labels, and the like.

But what if there was a better way? Du du du du du duu 🥁 APEX UI Defaults!

Let’s go back in time to 5 minutes ago – just before we created our app. Let’s go to SQL Workshop \ Utilities \ UI Defaults.

As you can see, we see two options:

Table Dictionary
Attribute Dictionary

Let’s look at them in more detail.

Table Dictionary

We’ll start with Table Dictionary because it’s the more interesting of the two. If we wanted, we could hit that big green Synchronize Defaults button but it will take some time for my 97 tables. I remember doing this a long time ago. It did take a long time, and it crashed, too. This might be best shunted to a Background Job in a future release.

See that text above? Several times while researching information for this article, my brain almost malfunctioned. ⛓️‍💥 This was caused by reading things like “This will … remove columns that no longer exist”. Wait, what? Hands off my DB tables!!! But no, don’t worry – it actually means the UI Default columns, i.e., Rows, not DB columns.

Let’s start by clicking the FOOTBALL_LEAGUE link.

You can create Table Defaults on Tables or Views. We can now click the Create Defaults button.

When done, instead of opening the defaults it just created for you, it throws you back to the previous page, so you must click the FOOTBALL_LEAGUE link again (see below).

However, this time, we can see that Defaults now exist. Click the FOOTBALL_LEAGUE link which brings you to the page below.

Next, click the Edit Table Defaults button, then check the fields and give the table a description. It’s worth doing because we are giving AI valuable information about the nature of this table – though you probably don’t need to provide as much information as I did below.

As you can see, this 15+ year-old feature has now been repurposed to provide additional information to the APEX Generative AI Service, which enables it to generate SQL and build applications. However, be warned that not all fields assist AI – in fact, very few do, so do check the Inline Help Texts.

Now, finally, we get to see our columns.

From here, we can start to give these ridiculous columns proper labels. To do this, we need to click on the columns. Let’s begin with NAME, give it a Label (“Team”) and some Help Text.

The following section concerns the columns’ appearance in a Form, i.e., which Page Item it should use (e.g., Text, Number, Select, Combobox, etc.), what Template to use, whether it is Required, etc.

The Display selector lets you configure whether it should be shown (or not) in the Form.There’s also a % option, which I worked out is a Null Value. I don’t really see the purpose of having Null here. However, in my tests, the item is always displayed – regardless of which of the three settings I had chosen.

The following section is about the Report and Tabular Forms. This is a bit odd as the creation of Tabular Forms was removed two years ago, and UI Defaults do not apply retrospectively to existing components. Some of the Display As entries feel a bit legacy and “Tabular-Formy.”

At the bottom, you can create a LOV (List of Values).

If you create a Static LOV, you cannot see if you have set it up correctly unless you click it again – there are some usability issues here.

I had some real issues with LOVs on this page. I won’t go into the full details in this article, but you can tell I’m not thrilled.

It’s difficult to describe, but this page feels old, though it does useful stuff. In the next step, I’ll add some proper labels to all the fields.

While looking at the Goals column, I click the Create Column Group button.

… and add all my Goals columns (For, Against, Diff) to that group. I do the same for a new Results column group (Played, Win, Draw and Loss).

As mentioned earlier, all this work had no effect on my existing Page components. It only works in the wizards. It does not even apply when creating a new Form Region on a table on an existing page, which is a lost opportunity.

Therefore, I must repeat the steps to create a Report with a Form on new pages. Note: in the wizard, there used to be a switch to use UI Defaults (enabled by default), but this has now been removed, and you can’t opt out.

Now, if I run my app with the new page, it has just been built:

As you can see, the labels have changed. However, the wizard added Format Masks of 999G999G999G999G999G999G999G990D0 to the report Columns (Won, etc.) even though I specifically wanted a Null Format Mask and to be Left Aligned, which APEX basically ignored.

Also, where are my column groups? It specifically said I would get Column Groups in Interactive Reports and Forms…

But there are no Interactive Reports column groupings, and – yup – my Forms also look no different. No groupings either.

Attribute Dictionary

Now, let’s have a quick look at the Attribute Dictionary. I will create five columns I regularly use across all my tables. I’m just going to set the label, and, as you can see, these five are not associated with any table in particular.

Let’s create a new table that features those five columns.

CREATE TABLE office_supplies (
    id        NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  -- Unique identifier for each supply
    supply_name      VARCHAR2(100),                                    -- What's the name of this precious item?
    is_stapler       CHAR(1) DEFAULT 'N',                              -- Y/N, is this item a stapler?
    mysteriously_gone CHAR(1) DEFAULT 'N',                             -- Did it vanish into the office void? Y/N
    complaints       NUMBER DEFAULT 0,                                 -- How many complaints about missing items?
    created_by       VARCHAR2(50),                                     -- Who created the record
    created          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,              -- When the record was created
    updated_by       VARCHAR2(50),                                     -- Who last updated the record
    updated          TIMESTAMP DEFAULT CURRENT_TIMESTAMP               -- When it was last updated
);

Next, we need to add some data:

INSERT INTO office_supplies (supply_name, is_stapler, mysteriously_gone, complaints, created_by, updated_by)
VALUES ('Red Swingline Stapler', 'Y', 'N', 2, 'Milton', 'Milton');

INSERT INTO office_supplies (supply_name, is_stapler, mysteriously_gone, complaints, created_by, updated_by)
VALUES ('Box of Paper Clips', 'N', 'Y', 10, 'Pam', 'Dwight');

INSERT INTO office_supplies (supply_name, is_stapler, mysteriously_gone, complaints, created_by, updated_by)
VALUES ('Fancy Gel Pens', 'N', 'Y', 25, 'Jim', 'Michael');

Now, without creating Table UI Defaults, if I build a Form using the wizard… Hooray, it uses the Attribute Dictionary!

I’ve deliberately not shown you Classic or Interactive Reports because… (sigh) Well, it seems the Attribute Dictionary only works with Forms – at least in APEX 24.1. Even though the documentation tells you otherwise.

If I create Table Defaults from my office_supplies table, it fails to copy the Attribute Dictionary version across and instead tries to come up with a new label for those columns.

In the wizard, Table Defaults supersede the Attribute Dictionary.

So, to be honest, what’s the actual point of the Attribute Dictionary? If one would curate a list of columns in the Attribute Dictionary, that work is completely wasted should someone create Table Defaults.

Summary

Well, that was underwhelming. My experience with this has been mixed. While some things are undoubtedly useful and do ensure standards, there are several surprising settings, some of which just don’t work.

UI Defaults have been around for years, and perhaps, like the Advisor, they need some general improvements. I do see some traction on a three-year-old logged idea, FR-1970, which is now on the road map.

This is not an exhaustive deep dive by any means. It would take me hours to identify all the issues, which I’m not going to do as I want to keep my resting heartbeat low.

My takeaway is that UI Defaults may provide some consistency in Forms for some teams. It may be a good practice to incorporate UI Defaults when creating a new table. Just don’t expect miracles from this feature.

And that’s it for this article. If you’re interested in similar Oracle APEX content, check out some of the other pieces on this blog: