Friendly URL structure

Oracle has updated apex.oracle.com to APEX 20.1 which includes among other features the new “Friendly URL” option. The legacy URL structure concatenated a string of parameters into a single “p” parameter, which works fine; but it can make it difficult to configure web server rules to match and rewrite URLs. Apart from the application ID or alias (the first part), all the parameters are optional; if all were specified the URL will be something like this (line breaks added for clarity):

f?p=SAMPLE_DB_APP:HOME:16801234560918:myrequest:mydebug
  :myclearcache:myitems:myvalues:myprinterfriendly
  &p_trace=mytrace
  &cs=19A56DBFDXXXE4DF293C96D786
  &c=jk64

In this example, the application alias is used (SAMPLE_DB_APP) and page alias (HOME) followed by the session ID etc. I’ve also specified the workspace (jk64) using the “c” query parameter.

In APEX 20.1 if you edit your Application Properties, you will see the new “Friendly URLs” setting.

With this setting turned on, URLs generated throughout the application will take this form (this is not exhaustive, there are other query parameters supported as well):

/pls/apex/jk64/r/sample_db_app/home
   ?myitems=myvalues
   &request=myrequest
   &clear=myclearcache
   &debug=mydebug
   &printerFriendly=myprinterfriendly
   &session=16801234560918
   &p_trace=mytrace
   &cs=19A56DBFDXXXE4DF293C96D786

When the documentation says the URLs should be “easier to read” I’m pretty sure they mean “by developers”, not end users. I suppose this means the URLs make more sense to developers of other web-based applications, because they conform better to REST URL conventions.

In its simplest form, the URL generated by APEX will include just the workspace path prefix, application, page, and session ID:

 /pls/apex/jk64/r/sample_db_app/home?session=16801234560918 

Calling APEX_PAGE.GET_URL generates the URLs correctly for the application according to whether the Friendly URL setting is on or not.

I noticed the following features of this new format:

• The URL generated by APEX_PAGE.GET_URL now includes a full path (excluding the domain).
• Instead of the “c” workspace query parameter, the application’s Path Prefix (which could be set to something other than the workspace name) is used (jk64 in my example). This is not the workspace name, although in most cases it usually will happen to be the same because it’s defaulted that way. This attribute is set at the workspace level, under Administration / Manage Service / Set Workspace Preferences / SQL Workshop.
• If not specified, the application and page alias will be used rather than the application or page ID, which is nice.
• Even if you specify the application or page alias in uppercase, APEX_PAGE.GET_URL returns them in all lowercase.
• The more important attributes relevant to a user navigating the application are now further towards the start of the URL, such as page and item values, so they will be more likely to be noticed by the end user.
• The “/r/” bit in the URL is just that. It’s just “r” and can’t be anything else, don’t ask me what it means. EDIT: apparently it stands for “router”…

If your users have bookmarked your application using the legacy URL format, you can still safely upgrade your application to use Friendly URLs because both are still supported. This also means that if you have some old code that generates links programmatically they should still work the same (although it is best practice to call APEX_PAGE.GET_URL for this purpose).

In case you’re wondering, it is not possible to change the URL format when calling APEX_PAGE.GET_URL, it will follow your application’s Friendly URL setting. If you call APEX_PAGE.GET_URL for another application, it will return the correct format of URL for the target application. If you call APEX_PAGE.GET_URL for an application that does not exist, it will return the URL in the legacy format.

Existing applications after upgrading, or ones you import from an older version of APEX, will still use the legacy URL syntax. New applications will use the new Friendly URLs by default – but you can revert them to the legacy URLs if you wish.

On a side note, in earlier versions when you create a new application the application alias was set to the application ID by default. In APEX 20.1, a new application will have an application alias generated from the initial application name; when I tried it, it added a number as well for some reason.

For obvious reasons, existing code that parses the URL (e.g. in javascript on the client) will probably break. This is a fairly rare thing but does happen (such as in a plugin of mine which I’ll need to fix).

At this very early stage, the legacy URL format is still fully supported – I imagine it will eventually be deprecated, but not yet.

Finally, I’d like to point out some entries on the APEX Feature Requests site:

Our patience has been rewarded 🙂

Next/Previous Record: how APEX does it

It’s not a common requirement in my experience, but it does come up every now and then: a customer is happy with a simple Report + Form but when they open a record, they want to be able to make their changes and go to the “next” record in one button click, instead of having to go back to the report and select the other record.

In the Sample Database Application, page 29 (Order Details) implements “Next” and “Previous” buttons which allow the user to save and open another record in one action.

These buttons are implemented using a legacy Form Pagination process. This process sets some hidden items (P29_ORDER_ID_NEXT, P29_ORDER_ID_PREV, P29_ORDER_ID_COUNT) based on a query on a specified table (DEMO_ORDERS) with a specified unique identifier (ORDER_ID) associated with a primary key item (P29_ORDER_ID). The process requires a navigation order, specified by one or two columns (ORDER_ID, in this instance) in order to know what would the “next” and “previous” records be. If there is no next or previous record is found, the buttons are hidden.

When the page is submitted, the Next and Previous buttons submit a request (GET_NEXT_ORDER_ID or GET_PREVIOUS_ORDER_ID, respectively). After the ApplyMRU process has run, one of the relevant Branches will redirect the client back to the same page (p29) and set P29_ORDER_ID to either &P29_ORDER_ID_NEXT. or &P29_ORDER_ID_PREV. which causes the page to load the relevant record.

Some things to note with this approach:

1. The legacy Form Pagination process is limited to a maximum 2 columns for the uniqueness constraint, and 2 columns for the navigation order.
2. The navigation order of records will not match any custom sort order or filtering the user might have used on the report; so after the user opens the “first record” in the report, the form will not necessarily navigate to the “next record” that they might expect.
3. The Next / Previous record IDs are queried when the page is initially loaded, so if anything has changed prior to the user clicking “Next” or “Previous”, it’s possible the user will inadvertently be directed to a record that is not actually “next” or “previous” to the record as it is now.
   In the worst case, if someone had deleted a record, the page would show “record not found”.
   In a perhaps less problematic case, if someone else has just inserted a new record with a unique identifier that happens to fall between the user’s previous record and the record they are navigating to, the user will effectively “skip over” the newly inserted record and might be led to believe it doesn’t exist.
4. For large datasets there may be a performance penalty whenever each user loads the form since the page must issue additional queries to find the IDs for the “previous smaller” and “next larger” record, as well as to get the total number of records, and the position in the overall dataset of the current record (so it can set the “count” item to something like “8 of 10”). This involves the execution of a second query (in addition to the original query which gathered the data for the record being viewed).

These are not necessarily insurmountable or showstopping issues but should be kept in mind for forms using this approach.

With the new APEX Form feature, the above approach can still be used in much the same way – the attribute settings are a little different.

The page has a process before header of type Form – Initialization. This process has the following optional settings: Next Primary Key Item(s), Previous Primary Key Item(s), and Current Row/Total Item. To use this feature you must first create items (usually hidden) and then set these attributes to the item names.

Note that the Next / Previous Primary Key Item(s) attributes accept a comma-delimited list of items which allows them to support a compound key; I haven’t tested it but I expect this means it can support more than 2 columns.

The form will automatically populate these hidden items with the Order ID of the next and previous record, and will set the Current Row/Total Item to something like “8 of 10”. You can then use these items how you wish, e.g. as per the legacy pagination scheme, add the “Next” and “Previous” buttons, and create the navigation Branches to open the form with the relevant records.

To control the navigation order, you must set the Order By attribute on the form region. If this is not set, the navigation order is essentially unpredictable (APEX uses null as the sort order).

Same as the legacy pagination process, the form executes two queries: one to load the data for the form, and a second to gather the Next, Previous, Position and Count for the record, using SQL like this (I’ve simplified it a bit):

select i.*
from (
    select ORDER_ID, CUSTOMER_ID, ORDER_TOTAL, ...
           lead(ORDER_ID,1) over (order by ORDER_ID) as APX$NEXT_1,
           lag(ORDER_ID,1) over (order by ORDER_ID) as APX$PREV_1,
           row_number() over (order by ORDER_ID) as APX$ROWN,
           count(*) over () as APX$TOTAL
    from (
        (select /*+ qb_name(apex$inner) */
                d.ORDER_ID, d.CUSTOMER_ID, d.ORDER_TOTAL, ...
         from (select x.* from DEMO_ORDERS x) d
    )) i 
) i where 1=1 
and ORDER_ID=:apex$f1

It should be noted that the comments above about how concurrent record inserts and deletes by other users, and about report filters and sorting, also apply to the new form process.

In the past I built a system where it was important that the “Next” / “Previous” buttons should allow the user to navigate up and down the records exactly as shown in the report, respecting user-entered filters and sort order. The approach I took was to gather the IDs into a collection and pass this to the form when the user opened a record. I described the implementation and limitations of this approach in an older blog post which I expect still works today: Next/Previous buttons from Interactive Report results

User-editable Application Setting

A nice addition to APEX release 18.1 is the Application Settings feature. This allows the developer to define one or more configuration values that are relevant to a particular application. In a recent project this feature came in useful.

I had built a simple questionnaire/calculator application for a client and they wanted a small “FAQ” box on the left-hand side of the page:

I could have built this as an ordinary HTML region, but the Admin users needed to be able to modify the content later, so the content needed to be stored somewhere. I didn’t feel the users’ requirement was mature enough to design another table to store the boilerplate (not yet, at least), so I thought I’d give the Application Settings feature a go.

An Application Setting is a single value that can be set in Component Settings, and retrieved and modified at runtime via the supplied PL/SQL API (APEX_APP_SETTINGS). The feature is most useful for “configuration”-type data relevant to the application’s user interface. In the past I would have created a special table to store this sort of thing – and in some cases I think I still would – but in some cases using Application Settings may result in a simpler design for your applications.

I went to Shared Components, Application Settings and created a new Setting called “FAQ_BOILERPLATE“. Each Application Setting can have the following attributes configured:

• Name – although this can be almost anything, I suggest using a naming standard similar to how you name tables and columns, to reduce ambiguity if you need to refer to the setting in your PL/SQL.
• Value – at first, you would set this to the initial value; if it is changed, it is updated here. Note that the setting can only have one value at any time, and the value is global for all sessions. The value is limited to 4,000 bytes.
• Value Required – if needed you can make the setting mandatory. In my case, I left this set to “No”.
• Valid Values – if needed you can specify a comma-delimited list of valid values that APEX will validate against. In my case, I left this blank.
• On Upgrade Keep Value – if you deploy the application from Dev to Prod, set this to Yes so that if a user has changed the setting your deployment won’t clobber their changes. On the other hand, set this to No if you want the value reset to the default when the application is deployed. In my case, I set this to Yes.
• Build Option – if needed you can associate the setting with a particular build option. If the build option is disabled, an exception will be raised at runtime if the application setting is accessed.

On the page where I wanted to show the content, I added the following:

1. A Static Content region titled “FAQ”.
2. A hidden item in the region named “P10_FAQ_BOILERPLATE“.
3. A Before Header PL/SQL process.

The Text content for the static content region is:

<div class="boilerplate">
&P10_FAQ_BOILERPLATE!RAW.
</div>

Note that the raw value from the application setting is trusted as it may include some embedded HTML; you would need to ensure that only “safe” HTML is stored in the setting.

The Before Header PL/SQL process has this code:

:P10_FAQ_BOILERPLATE := apex_app_setting.get_value('FAQ_BOILERPLATE');

Side note: a simpler, alternative design (that I started with initially) was just a PL/SQL region titled “FAQ”, with the following code:

htp.p(apex_app_setting.get_value('FAQ_BOILERPLATE'));

I later rejected this design because I wanted to hide the region if the FAQ_BOILERPLATE setting was blank.

I put a Server-side Condition on the FAQ region when “Item is NOT NULL” referring to the “P10_FAQ_BOILERPLATE” item.

Editing an Application Setting

The Edit button is assigned the Authorization Scheme “Admin” so that admin users can edit the FAQ. It redirects to another very simple page with the following components:

1. A Rich Text Editor item P50_FAQ_BOILERPLATE, along with Cancel and Save buttons.
2. An After Header PL/SQL process “get value” (code below).
3. An On Processing PL/SQL process “save value” when the Save button is clicked (code below).

After Header PL/SQL process “get value”:

:P50_FAQ_BOILERPLATE := apex_app_setting.get_value('FAQ_BOILERPLATE');

On Processing PL/SQL process “save value”:

apex_app_setting.set_value('FAQ_BOILERPLATE',:P50_FAQ_BOILERPLATE);

The more APEX-savvy of you may have noticed that this design means that if an Admin user clears out the setting (setting it to NULL), since it has the Server-side Condition on it, the FAQ region will disappear from the page (by design). This also includes the Edit button which would no longer be accessible. In the event this happens, I added another button labelled “Edit FAQ” to the Admin page so they can set it again later if they want.

This was a very simple feature that took less than an hour to build, and was suitable for the purpose. Later, if they find it becomes a bit unwieldy (e.g. if they add many more questions and answers, and need to standardise the layout and formatting) I might replace it with a more complex design – but for now this will do just fine.

Related

• APEX Documentation – Managing Application Settings
• APEX Documentation – APEX_APP_SETTING