Using Built-in Substitution Strings Tutorial – Part 1

One of the most common pieces of functionality we use as Oracle Forms developers is substitution strings. Everything from user and sysdate in triggers to system variables such as :system.current_item in Forms. Substitution strings are also used in APEX and this tutorial aims to show how these substitution strings can be referenced and used. It is assumed that you have a workspace with the Sample Application installed, as we will be building upon this application and its data structure.

The purpose of this tutorial is not to provide a list of substitution strings with their definitions, these 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 1 – Recording the user logged on in a trigger

A common use of the USER substitution string in database triggers is to set the value of a created_by or modified_by column on the table. However, APEX usually accesses the database as APEX_PUBLIC_USER so using the USER substitution string would not give you the name of the user logged into the APEX application.

In the Object Browser:

  1. Add new column varchar2(50) called modified_by on the EMP table.
  2. Add new before row trigger to the EMP table called emp_btrg with the following code:
  3. IF inserting or updating THEN
    :NEW.modified_by := apex_application.g_user;
    END IF;

In the Application Builder:

  1. Click the Create button to create a very simple application to fire the code above.
  2. Set the name to Tutorials and click Next.
  3. Click Tabular Form in the Add Page section and select the EMP table. Click Add Page.
  4. Click Next to navigate through the wizard pages accepting the default settings and selecting a theme of your choice.
  5. Click Create to create your new application.
  6. Now run the application logging in as an Application Express user.
  7. A screen displaying the data in the EMP table should be displayed.
  8. On this screen change one of the Ename values to Jones and click Submit.
  9. For this record you should see that the modified_by value is updated to the username you have logged in as.

You can try this logging in as different users to show that the modified_by field is always updated to the user you have logged in as. This is achieved by referencing the apex_application substitution string g_user. The apex_application API has a number of useful substitution strings that can be used in a similar way.

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.

4 Responses to “Using Built-in Substitution Strings Tutorial – Part 1”

  • Patrick Wolf says:

    Hi,

    you should use

    :NEW.modified_by := NVL(apex_application.g_user, USER);

    so that it also works when the table is modified outside of an Oracle APEX session context.

    Regards
    Patrick

  • Sara says:

    Hi Patrick

    This is exactly what we’ve done in our application. However, for the purposes of this tutorial I figured it wasn’t necessary as we’d only be updating the information through the APEX screens.

    Regards
    Sara

  • Dan says:

    Sara,

    I created an open source project called tapiGen (http://sourceforge.net/projects/tapigen/) to kind of assist with this kind of thing… When I wrote it I used the v(‘APP_USER’) syntax. However, your method would seem to be a little more efficient. I’ll update the code ASAP. Thanks!

    Regards,
    Dan

  • Peter Odekerken says:

    Sara,

    this is the third tiem this week that i’m glad i’ve found you. Your solutions are a great help for me just starting APEX development.

    kind regards, peter

Comments can no longer be submitted.