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[youtube]http://www.youtube.com/watch?v=sVnUtS1NWOE[/youtube]
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.
SELECT department_name,department_id FROM oehr_departments
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.
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;
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.