Introduction

This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials. It is reccomended that you view our other tutorials on APEX Tutorial Preparation and OEHR Sample Data Install and our tutorial on Oracle APEX Advanced Tutorial 1 - Creating A Tabular Form to familiarize yourself with the APEX interface. This tutorial moves a bit faster than the last, and is more involved.

Part 2 - Creating the LOV’s, Level Two Items, Button, Onload Process, and testing

High Level Steps

1) Create LOV’s (0:30)
2) Create Level 2 Items (1:56)
3) Create Button (4:24)
4) Create Onload Process (5:00)
5) Test Application (6:05)

Times in parenthesis are the approximate start time for that step

Notes, Code and Resources

List Of Values (LOV’s)

Below are the LOV SQL statements for this tutorial.

Department LOV:

1

SELECT department_name,department_id FROM oehr_departments

Manager LOV:

1
2
3

SELECT y.first_name || ' ' || y.last_name d, y.employee_id r
FROM oehr_employees y
WHERE y.employee_id IN ( SELECT x.manager_id FROM oehr_employees x)

If you are unfamiliar with SQL, the Manager LOV reads it’s data into 2 ‘containers’ (x,y) since the SQL itself cannot read from the same source table otherwise. Essentially (in laymans terms) It’s reading each set of data into a temporary virtual table.

Onload Process

Below is the PL/SQL code for the onload process. The process is responsible for looking at our search region and determining if we are searching by a Manager or a Department, and changes the displayed title of the Report Region to match, or leaves it as the default ‘Employees’ if there is nothing selected as of that moment. The code uses the hidden region we created in part 1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

DECLARE
     l_dept varchar2(100);
     l_mgr varchar2(100);
BEGIN
:P1_TEXT := NULL;
 
IF :P1_DEPT != '%null%'
    THEN SELECT department_name
                      INTO l_dept
                      FROM oehr_departments
                  WHERE department_id = :P1_DEPT;
                  :P1_TEXT := :P1_TEXT || ' in Department ' || l_dept;
END IF;
 
IF :P1_MGR != '%null%'
    THEN SELECT first_name || ' ' || last_name
                      INTO l_mgr
                      FROM oehr_employees
               WHERE employee_id = :P1_MGR;
               :P1_TEXT := :P1_TEXT ||' reporting to ' || l_mgr;
END IF;
END;

Typographical Errors

I just want to make a note on this. In both this and the previous Tutorial I have purposely made typographical errors when entering SQL commands to demonstrate that the APEX system will parse and test a query before saving it. Any code examples in the full articles are correct, and have been tested. You should always check your queries over before using them. Grammar, syntax and spelling of commands and table field names will be caught, however it is still possible to make errors that the system will interpret as perfectly correct. IE give it a different field name, it will appear competely valid, but won’t be what you wanted. For this reason we are going to be including the code and queries in the full articles, so you have a reference, and don’t have to worry about typographical errors. Well, unless you choose to type it in manually.

Related Information:

1. Oracle APEX Tutorial 2 - Parameterized Report Part 1 - Video Training

   This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials....

2. Oracle APEX Tutorial 3 - Drilldown Report Part 1- Video Training

   This tutorial covers section 4 of the Oracle APEX Advanced Tutorials, Creating a drilldown report...

3. Oracle APEX Tutorial 3 - Drilldown Report Part 2- Video Training

   This tutorial covers section 4 of the Oracle APEX Advanced Tutorials, Creating a Drilldown Report...

4. Oracle APEX Tutorial 4 – Form Layout – Part 1 – Video Training

   APEX gives you a lot of power and flexibility with its built in wizards. They are very good at creating...

5. Oracle APEX Tutorial 7 - Stacked Bar Chart - Video Training

   Entering and retrieving data is all well and good, but sometimes a visual display of the raw numbers gives more...

6. Oracle APEX Tutorial 8 - Up and Downloading Files - Part 1 - Video Training

   Reports and data entry constitue the bulk of what most of these tutorials cover, and what many businesses need. However,...

7. Oracle APEX Tutorial 9 - JavaScript - Video Training

   JavaScript is everywhere on the Internet today. Even if you don't realize it, this handy little scripting tool has become...

8. Oracle APEX Tutorial 5 – Check Boxes – Part 2 – Video Training

    Within APEX, there are a multitude of things you can use Check Boxes for, and many ways they can...