16.11.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.11.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. 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:
-
Run the Create List Wizard and select the Dynamic list type.
-
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.
Parent topic: Creating Dynamic Lists
16.11.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 |
|
Yes |
Depth level for hierarchical lists, otherwise use NULL |
2 |
Label |
|
Yes |
Text to appear at list entry |
3 |
Target URL |
|
No |
Target URL to branch to when list entry is selected |
4 |
Is Current? |
|
No |
Controls the behavior of the list entry. Valid values include 'YES', 'NO', and NULL |
5 |
Icon Name |
|
No |
The name of icon to be displayed on the list entry |
6 |
Image attributes |
|
No |
Attributes of the image, such as the width or height |
7 |
Image |
|
No |
Value for Image |
8 |
User attribute 1 |
|
No |
N/A |
9 |
User attribute 2 |
|
No |
N/A |
10 |
User attribute 3 |
|
No |
N/A |
11 |
User attribute 4 |
|
No |
N/A |
12 |
User attribute 5 |
|
No |
N/A |
13 |
User attribute 6 |
|
No |
N/A |
14 |
User attribute 7 |
|
No |
N/A |
15 |
User attribute 8 |
|
No |
N/A |
16 |
User attribute 9 |
|
No |
N/A |
17 |
User attribute 10 |
|
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
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.11.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:
See Also:
Parent topic: Creating Dynamic Lists