Creating a Basic APEX Application | Oracle APEX Cookbook : Second Edition

Including different item types

In APEX, it is possible to use many different item types. Some of these we've already seen in other recipes. In this recipe we're going to talk about some of the advanced item types such as shuttles and HTML editors and how to interact with them.

Getting ready

To start with this recipe, create an empty page with ID 15 and put an HTML region with the name IT Employees on it.

How to do it...

The first example of an advanced item type is the shuttle list. We will use this list to add employees to the IT department. The shuttle will show a list of available employees on the left-hand side and a list of employees already in the IT department on the right-hand side.

Create a new item on your page by right-clicking on the IT Employees region and selecting Create Page Item.
Select Shuttle and click on Next.
Name the item P15_IT_SHUTTLE and click on Next until you reach the List of Values page in the wizard.

Enter the following query to populate the left side of the shuttle:

select username display_value
     , id return_value
  from app_employees
 where dept_id <> 3
[9672_01_07.txt]

After clicking on Next, you will be asked to enter a value for Source Value. This will populate the right-hand side of the shuttle. For this, we will use PL/SQL Function Body as the Source Type that will return a list of usernames delimited by colons:
```
declare
  v_list apex_application_global.vc_arr2;
begin

  select username return_value
    bulk collect
    into v_list
    from app_employees
   where dept_id = 3;

  return(apex_util.table_to_string(v_list));
end;
[9672_01_08.txt]
```
Click on Create Item to finish the wizard.

Now the item will be populated with the employees:

The right-hand side of the shuttle item can also be populated by the default value that can be defined on the item's properties.

Another type of item we want to discuss here is the Cascading Select Item. Let's say we want to make a list of all employees. This is potentially a very long list, so before showing the employees, we first want to select the department we are working with.

First, we create the item that shows all departments for our company:

Create a new page item.
Use item type Select List.
Name it P15_DEPARTMENTS.
Give it a label and click on Next until you reach the LOV query, and enter the following SQL:
```
select name display_value
     , id return_value
  from app_departments
[9672_01_09.txt]
```
Now create the item.

The next part is to create the select list for the employees in the department. Again, we create a select list like before and name it P15_EMPLOYEES.

Now when we reach the LOV wizard screen, we enter the following SQL:

select firstname||' '||lastname display_value
     , id return_value
  from app_employees
 where dept_id = :P15_DEPARTMENTS
[9672_01_10.txt]

Also on this screen, change the value of Cascading LOV Parent Item(s) to P15_DEPARTMENTS. Click on Next and then on Create Item.

When we now run the page and select a Department, we can see that the Employees list changes immediately.

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Oracle APEX Cookbook : Second Edition

Oracle APEX Cookbook : Second Edition

Overview of this book

Including different item types

Getting ready

How to do it...

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access