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.

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

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
]';

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.

See Also:

Understanding Dynamic List Syntax