16.12.3 Creating Dynamic Lists

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

16.12.3.1 About the Process of 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 list by running the Create List Wizard.

  • Step 2: Specify whether to create the list from scratch or by copying an existing list.

  • Step 3: If creating a list from scratch, you are prompted to select a list type. Select Dynamic.

  • Step 4: Enter a SQL query or a PL/SQL function returning a SQL query.

  • Step 5: Add the list to a page by creating a List region.

16.12.3.2 Understanding Dynamic List Syntax

You create a dynamic list using a SQL query or a function returning a SQL Query.

Syntax for a SQL query:

SELECT level, labelValue label, 
       [targetValue]            target, 
       [is_current]             is_current_list_entry,
       [imageValue]             image, 
       [imageAttributeValue]    image_attribute,
       [imageAltValue]          image_alt_attribute,
       [attribute1]             attribute1,
       [attribute2]             attribute2,
       [attribute3]             attribute3,
       [attribute4]             attribute4,
       [attribute5]             attribute5,
       [attribute6]             attribute6,
       [attribute7]             attribute7,
       [attribute8]             attribute8,
       [attribute9]             attribute9,
       [attribute10]            attribute10
FROM ...
WHERE ...
ORDER BY ...

Syntax for a function returning a SQL query:

RETURN
'SELECT level, labelValue label,'||
'       [targetValue]         target,'|| 
'       [is_current]          is_current_list_entry,'||
'       [imageValue]          image,'||
'       [imageAttributeValue] image_attribute, '||
'       [imageAltValue]       image_alt_attribute,'||
'       [attribute1]          attribute1,'||
'       [attribute2]          attribute2,'||
'       [attribute3]          attribute3,'||
'       [attribute4]          attribute4,'||
'       [attribute5]          attribute5,'||
'       [attribute6]          attribute6,'||
'       [attribute7]          attribute7,'||
'       [attribute8]          attribute8,'||
'       [attribute9]          attribute9,'||
'       [attribute10]         attribute10'||
'FROM ...'||
'WHERE ...'||
'ORDER BY ...';

Where:

  • level and labelvalue are required.

  • level - For hierarchical lists, the level parameter should be supplied. For non-hierarchical lists, this parameter can be set to NULL.

  • labelvalue - Text to appear as list entry.

  • targetvalue - Target URL to branch to when list entry is selected.

  • is_current - Controls the behavior of the list entry. Values include:

    • NULL - Currency of target is based upon Target URL.

    • 'YES' - List entry is always current.

    • 'NO' - List entry is not current.

  • imagevalue - The name of image to be display on the list entry

  • imageattributevalue - Attributes of the image, such as the width or height

  • imagealtvalue - Value for Image ALT tag, required for accessibility purposes in templates where the user must click the image.

  • attribute1 to 10: These attributes tie in with the existing ten User Attributes exposed on the Static List Entry page.

16.12.3.3 Creating a Dynamic List from Scratch

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, click Lists.
    5. Click Create.
  2. Click From Scratch and click Next.
  3. For Name and Type:
    1. Name - Enter a numeric or alphanumeric name for the list.
    2. Type - Select Dynamic.
    3. Build Option - If applicable, select a build option for this component. Build options are predefined settings that determine whether components within an application are enabled.
    4. Click Next.
  4. 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.
  5. For Create List, specify whether to create a list region:
    1. If you select Create list region on current page, the following attribute display:
      • Region Position - If creating a new region, select the region position.

      • Region Template - If creating a new region, select a region template.

      • List Template - If creating a new region, select a list template.

    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.