16.7.3 Creating a Dynamic List of Values at the Application-Level

Run the Create List of Values Wizard to define a named dynamic list of values at the application-level.

Oracle APEX stores named (or shared) list of values in the List of Values repository. A dynamic list of values retrieves data from a local data source, a remote connection defined using REST Enabled SQL, or from a RESTful web service defined using REST Data Sources.

The following procedure creates dynamic list of values for the form item, Task Name and then displays it as a Select List. This following example is built on the EBA_DEMO_IR_PROJECTS table which available in the sample dataset, Project Data.

To create a named dynamic list of values:

1. Navigate to the Shared Components page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.
      The Shared Components page appears.

   Tip:

   You can also access Shared Components from Page Designer by clicking Shared Components () on the Page Designer toolbar.
2. Under Other Components, select Lists of Values.
3. Run the Create List of Values Wizard. On the Lists of Values page, click Create.
4. In the Create List of Values Wizard:
   1. Source:

      • Create List of Values - Specify how to create the list of values. For this example, select From Scratch.

      • Click Next.

   2. Name and Type:

      • Name - Enter a name for the list of values. For example:

        Task

      • Type - Select Dynamic.

      • Click Next.

   3. List of Values Source:

      • Data Source - Select Local Database.

      • Source Type - Lists using a Local Database can be based on a Table, SQL Query or PL/SQL Function Returning SQL.

        For this example, select Table.

      • Table / View Owner - Select the owner of the table on which you are building the list of values.

      • Table / View Name - Select the table or view on which the list of values will be based.

        Select EBA_DEMO_IR_PROJECTS.

      • Click Next.

   4. Column Mappings:

      • Return Column - Defines the column used as the return value for your list of values. Accept the default, ID.

      • Display Column - Define the column that used as the display value for your list of values. Accept the default, TASK_NAME.

        You can define additional display columns after creating a list of values for item types that support displaying multiple columns (such as the Popup LOV).

   5. Click Create.
      The new list of values appears on the Lists of Values page.
5. In Page Designer, return to the form page and update the form item to reference the list of values:
   1. Return to Page Designer. Click Edit Page X on the Page Designer toolbar where X is the page number.
      Page Designer appears.
   2. View the form page (for example, Update Projects) in Page Designer.
   3. In the Rendering tab, select the form page item. For example, select P3_TASK_NAME.
   4. In the Property Editor, edit the following attributes:

      • Identification, Type - Select Select List.

      • List of Values, Type - Select Shared Component.

      • List of Values, List of Values - Select TASK.

      • List of Values, Display Extra Values - An item may have a session state value which does not occur in the given list of values definition. Select whether this list of values displays this extra session state value. Accept the default.

      • List of Values, Display Null Value - When enabled, a NULL value displays at the top of the list. Accept the default.

      • List of Values, Null Display Value - Enter the text to be displayed within the list NULL option at the top of this list. Enter:

        - Select Task -

        This value also displays when rendering the item if the value is null. If you do not enter any text, it is displayed as a blank line in the list and a blank item when rendered.

      
      Description of the illustration dynamic_lov_page_designer.png
   5. Click Save and then run the page.

   The form item, Task Name, now displays as select list.

   
   Description of the illustration dynamic_lov_select_list.png