Using Built-in Substitution Strings Tutorial – Part 3

In this series of tutorials we have looked at using the apex_application API and learned how to create dynamic branches to return to previous pages. In Part 2 we created a blank About page, which we will now build on using different substitution strings to add extra fields and information.

It is assumed that you have a workspace with the Sample Application installed, as we will be building on this application, and that you have worked through Exercise 2, as we will be adding to the page we created then.

The purpose of this tutorial is not to provide a list of substitution strings with their definitions, this can be found in the Oracle APEX help pages. These provide an easy reference to the different built-in substitution strings and the different ways they can be accessed.

Exercise 3 – Adding Detail to the About Page

As a first step to diagnosing problems in an application it is often useful to get the user to provide a standard set of information. This can help to highlight obvious problems like being logged in as the wrong user or using the wrong APEX version. To facilitate this, an About page can be used to display all this information in one place. Substitution strings allow us to quickly and easily build up this information. We will now add items to the About page we previously created.

In the Application Builder:

  1. Navigate to the About page you previously created.
  2. Click to add a new item to this page and click Next to confirm you want to add a Text item.
  3. Select the second option down, Text Field (Disabled, does not save state), and click Next.
  4. Change the item name to be “P22_APP_USER” (assuming your About page is page 22) and click Next twice.
  5. Now change the source to be PL/SQL Expression or Function and in the Item Source Value field enter “:APP_USER”.
  6. On the final page, change the setting to “From source each time item is displayed” and click Create Item.
  7. Repeat steps 2 – 6 above, this time the item name should be “P22_APP_SESSION” and the Item Source Value should be “:APP_SESSION”.
  8. Again repeat steps 2 – 6, the item name should be “P22_APEX_VERSION” and the source type should be SQL Query and the Item Source Value should be:
    select distinct substr(flow_version,-12)
    from #FLOW_OWNER#.wwv_flows
    where security_group_id=10;
  9. Now add an item similar to that in step 8, the name should be “P22_APEX_INSTALLED” and the Item Source Value should be:
    select distinct trunc(last_updated_on)
    from #FLOW_OWNER#.wwv_flows
    where security_group_id=10;

Now, if you run the page you should have four new fields showing the username you logged into APEX as, the session id, the version of APEX you are running and the date this was installed. Although some of this information is available from other sources (the session id is in the url and the user is often displayed in the page template), this exercise shows how easy it is to access this information. The APP_SESSION substitution string can be used with the APP_ID substitution string to provide links to different pages in the application.

This exercise also demonstrates how to display the version of APEX that is running. When dealing with end users, it is often important to make such information as accessible as possible. Running the wrong version of APEX for an application is an easy mistake to make, but could be difficult to diagnose if the user you are dealing with is non-technical.

Sara

Sara Blair is a freelance Oracle developer with over 10 years Oracle experience. She has been working with Oracle APEX for nearly 4 years since htmldb 1.5, and is available to hire through Silvercore Solutions.

1 Response to “Using Built-in Substitution Strings Tutorial – Part 3”

  • renhart says:

    #FLOW_OWNER#.wwv_flows does not appear to be granted to the schema so the application chokes with

    >>
    ORA–00942: table or view does not exist
    Error ERR-1019 Error computing item default value: page=22 name=P22_APEX_INSTALLED.
    OK
    >>

    I can bypass the problem for the P22_APEX_VERSION by using the source method

    >>
    select version_no from apex_release;
    which returns

    VERSION_NO
    3.1.1.00.09
    >>

    I am surmising that the problem is probably due to permissions on wwv_flows which do not appear to be automatically made in

    the latest release to public.

Comments can no longer be submitted.