Previous Next JavaScript must be enabled to correctly display this content

## 16.8.3 Creating Dynamic Lists

A Dynamic List is based on a SQL query or a PL/SQL function executed at runtime.

- About Creating a Dynamic List
  Learn about the steps involved in creating a dynamic list.
- Understanding Dynamic List Syntax
  Learn about dynamic list syntax.
- Creating a Dynamic List from Scratch
  Create a dynamic list from scratch by running the Create List Wizard.

Parent topic: Creating Lists

### 16.8.3.1 About Creating a Dynamic List

Learn about the steps involved in creating a dynamic list.

A dynamic list enables you to create a List component based on items from a SQL query or a PL/SQL function returning a SQL query. In a dynamic list, the number of list entries depends on the list query output. When you delete a list, then corresponding list regions are deleted as well. These dynamic lists can then be rendered on a page using any List Template from your theme.

The process of creating a dynamic list involves the following steps:

- Step 1: Create the dynamic list by either:

  - Creating a list from scratch by running the Create List Wizard. When prompted enter the list name and select the list type, Dynamic.

  - Copying an existing dynamic list by running the Copy List Wizard.

- Step 2: Define the List Query:

  - Select a Query Source Type. Options include:

    - SQL Query

    - Function Returning a SQL Query

  - Enter a SQL query.

- Step 3: Add the list to a page by creating a List region.

See Also:

- Adding a List to a Page

- Editing a Dynamic List

- Accessing List Reports

Parent topic: Creating Dynamic Lists

### 16.8.3.2 Understanding Dynamic List Syntax

Learn about dynamic list syntax.

You create a dynamic list using a SQL query or a function returning a SQL query. Oracle APEX interprets your query's results by using the `SELECT` list column position as indicated in the table below. Since column aliases are ignored, choose any names that best document the information your query returns. Your SQL statement must include a minimum of two columns in its `SELECT` list, but can return up to 17 columns depending on your needs. All optional columns accept a `NULL` value as a placeholder to allow you to specify a non-null value for a later column position. For example, if you select Badge list for your list region's Appearance, List Template attribute, then the value of column 8 corresponding to User Attribute 1 determines the text that will appear inside the badge.

Tip:

The documentation for the List region explains which user attribute values are used by different Appearance template options.

Column | Purpose | Datatype | Required? | Notes
--- | --- | --- | --- | ---
1 | Level | `NUMBER` | Yes | Depth level for hierarchical lists, otherwise use NULL. Note that the root parent level must have a value of 1 or higher. When using 0, that level is skipped for creating the hierarchy.
2 | Label | `VARCHAR2` | Yes | Text to appear at list entry
3 | Target URL | `VARCHAR2` | No | Target URL to branch to when list entry is selected
4 | Is Current? | `VARCHAR2` | No | Controls the behavior of the list entry. Valid values include 'YES', 'NO', and NULL
5 | Icon Name | `VARCHAR2` | No | The name of icon to be displayed on the list entry
6 | Image attributes | `VARCHAR2` | No | Attributes of the image, such as the width or height
7 | Image `ALT` Text | `VARCHAR2` | No | Value for Image `ALT` tag, required for accessibility purposes in templates where the user must click the image.
8 | User attribute 1 | `VARCHAR2` | No | N/A
9 | User attribute 2 | `VARCHAR2` | No | N/A
10 | User attribute 3 | `VARCHAR2` | No | N/A
11 | User attribute 4 | `VARCHAR2` | No | N/A
12 | User attribute 5 | `VARCHAR2` | No | N/A
13 | User attribute 6 | `VARCHAR2` | No | N/A
14 | User attribute 7 | `VARCHAR2` | No | N/A
15 | User attribute 8 | `VARCHAR2` | No | N/A
16 | User attribute 9 | `VARCHAR2` | No | N/A
17 | User attribute 10 | `VARCHAR2` | No | N/A

For example, a simple query for a dynamic list of employee names might look like this:

    select null  c1_level,
           ename c2_name_for_label
    from emp
    order by ename

To include a person icon next to each employee's name in the list, select `NULL` for the intervening columns and return the name of an icon like `fa-user` in the fifth column position like this:

    select null      c1_level,
           ename     c2_name_for_label,
           null      c3_target_url,
           null      c4_is_current,
           'fa-user' c5_icon_name
    from emp
    order by ename

To have the list entries link to page 10, passing the value of `EMPNO` for the `P10_EMPNO` page item, adjust the query to provide a value for the target URL in the third column like this:

    select null      c1_level,
           ename     c2_name_for_label,
           apex_page.get_url(p_page   => 10,
                             p_items  => 'P10_EMPNO',
                             p_values => EMPNO) c3_target_url,
           null      c4_is_current,
           'fa-user' c5_icon_name
    from emp
    order by ename

The following query returns a value for a user attribute. It expands on the previous query to return each employee's salary (`sal`) in the column 8 slot as User Attribute 1. The List region's Appearance, List Template option, Badge List, uses this User Attribute 1 value as the text to display on the badge.

    select null      c1_level,
           ename     c2_name_for_label,
           apex_page.get_url(p_page   => 10,
                             p_items  => 'P10_EMPNO',
                             p_values => EMPNO) c3_target_url,
           null      c4_is_current,
           'fa-user' c5_icon_name,
           null      c6_icon_attrs,
           null      c7_icon_alt_text,
           sal       c8_user_attr1_badge_text
    from emp
    order by ename

To create a hierarchical list, return a non-null value for the first column that indicates the depth level of the hierarchy. This example returns a hierarchical list of employees starting with the employee(s) having no manager at the top of the tree.

    select level                                     c1_level,
           ename                                     c2_name_for_label
           apex_page.get_url(p_page   => 10,
                             p_items  => 'P10_EMPNO',
                             p_values => EMPNO)      c3_target_url,
           null                                      c4_is_current,
           'fa-user'                                 c5_icon_name
    from emp
    connect by prior empno = mgr
    start with mgr is null

If you need to return a SQL query defined by a function, the same rules apply. You may find it useful to use PL/SQL's multi-line string literal syntax `q'[ ... ]'` as shown in the following example to more easily return query text that spans multiple lines. Note that any syntax that returns a correctly-formatted SQL statement is allowed.

    return q'[
       select null      c1_level,
              ename     c2_name_for_label,
              apex_page.get_url(p_page   => 10,
                                p_items  => 'P10_EMPNO',
                                p_values => EMPNO) c3_target_url,
              null      c4_is_current,
              'fa-user' c5_icon_name
       from emp
       order by ename
    ]';

Parent topic: Creating Dynamic Lists

### 16.8.3.3 Creating a Dynamic List from Scratch

Create a dynamic list from scratch by running the Create List Wizard.

To create a dynamic list:

1. Access the Create/Edit Lists Wizard:
   1. On the Workspace home page, click the App Builder icon.
   2. Select an application.
   3. On the Application home page, click Shared Components.
   4. Under Navigation and Search, click Lists.
   5. Click Create.
   The Create List Wizard appears.
2. For Name and Type:
   1. Name - Enter a numeric or alphanumeric name for the list.
   2. Type - Select Dynamic.
   3. Click Next.
3. For Query or Static Values:
   1. Query Source Type - Select SQL Query or Function Returning SQL Query.
   2. Query - Enter a SQL query or function returning a SQL query. To view SQL Query examples, expand the Examples region.

      Build Query - Click Build Query to use a wizard to build a query on the table or view you select. Follow the on-screen instructions.

   3. Click Next.
4. For Confirm:
   1. Create List Regions - Select whether to create a list region. Options include:

      - Do not create list region(s)

      - Create list region on current page

   2. Region Position - If creating a new region, select the region position.
   3. Region Template - If creating a new region, select a region template.
   4. Click Create.

Related Topics

- Understanding Dynamic List Syntax

Parent topic: Creating Dynamic Lists