Oracle APEX Tutorial 2 – Parameterized Report Part 2 – Video Training

kevin.landonTechnical Tips5 Comments


This Tutorial is a guided walkthrough of section 3 of the Oracle APEX Advanced Tutorials. It is recommended 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:

SELECT department_name,department_id FROM oehr_departments

Manager LOV:

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.

     l_dept varchar2(100);
     l_mgr varchar2(100);
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;
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;

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 completely 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.

5 Comments on “Oracle APEX Tutorial 2 – Parameterized Report Part 2 – Video Training”

  1. everything with this works fine except the region title at the end does not assign to the contents of the hidden text box. I made the text box visible nd found that the correct text is goin into the box but it is not copying to the title.

    Any ideas?

  2. It looks like there’s a problem with APEX 4 and matching the list selectors to ‘%null%’. I fixed this by just replacing it with null, no quotes, no percent signs.

  3. Hi, thanks a lot for these very helpful video tutorials!
    Thanks Derple, but even replacing ‘%null%’ by null, no quotes, no percent signs didn’t help me… I did the entire tutorial very keenly and am not understanding why this issue persists.
    Any suggestions/replies will be very helpful.

    1. I also struggled with the title until I found I’ve made two mistakes:
      1. I forgot the DOT (.) at the end of the placeholders – it must be @P1_TEXT. and the dot at the end is crutial.
      2. I assigned the hidden item to the WRONG REGION (Search). It must be in the region where the title is (I think the region name was Employees).

Leave a Reply

Your email address will not be published. Required fields are marked *