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 1 - Creating the Application, Page Regions and First level Items

High Level Steps

 1) Apex System Login (0:30)
 2) Create Application (0:55)
 3) Create Regions (3:30)
 4) Create First Level Items (7:01)

Times in parenthesis are the approximate start time for that step

Notes, Code and Resources

Query Region SQL Code

 Below is the code for the Query Region of the tutorial. This query is what actually retreives the requested data from the database. The WHERE clause at the bottom converts the first and last names to all lower case (for both the supplied query and retreived information) in order to make the overall search case insensitive.

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

SELECT
   "OEHR_EMPLOYEES"."EMPLOYEE_ID" "EMPLOYEE_ID",
   "OEHR_EMPLOYEES"."FIRST_NAME" "FIRST_NAME",
   "OEHR_EMPLOYEES"."LAST_NAME" "LAST_NAME",
   "OEHR_EMPLOYEES"."EMAIL" "EMAIL",
   "OEHR_EMPLOYEES"."PHONE_NUMBER" "PHONE_NUMBER",
   "OEHR_EMPLOYEES"."HIRE_DATE" "HIRE_DATE",
   "OEHR_EMPLOYEES"."JOB_ID" "JOB_ID",
   "OEHR_EMPLOYEES"."SALARY" "SALARY",
   "OEHR_EMPLOYEES"."COMMISSION_PCT" "COMMISSION_PCT",
   "OEHR_EMPLOYEES"."MANAGER_ID" "MANAGER_ID",
   "OEHR_EMPLOYEES"."DEPARTMENT_ID" "DEPARTMENT_ID"
FROM
   "#OWNER#"."OEHR_EMPLOYEES" "OEHR_EMPLOYEES"
WHERE
     (lower(first_name) LIKE '%' || lower(:P1_NAME) || '%' OR
      lower(last_name) LIKE '%' || lower(:P1_NAME) || '%')
 AND department_id = decode(:P1_DEPT,'%null%',department_id,:P1_DEPT)
 AND manager_id = decode(:P1_MGR,'%null%',manager_id,:P1_MGR)

P1_ Explained

 In several places in both this tutorial, it’s second part, and subsequent tutorials, you will see entered ID’s that start with a P and a number and then another string. These are unique identifiers (akin to a variable)for items so they can be addressed by the application. The P1 indicates it’s located on Page 1, so anything that refrences it will be working with Page 1. This also enables you to have a P1_TEXT and a P2_Text and they won’t conflict with each other.

Hidden Region

 The hidden region we created in this section is used in conjunction with a process we will create in part 2 to control the displayed region title of the report this application produces. By making it hidden and protected, the end user is unaware of it’s existance, and cannot alter it’s value or state themselves, only the application itself can affect it.

Related Information:

1. Oracle APEX Tutorial 2 - Parameterized Report Part 2 - 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 2 – Video Training APEX gives you a lot of power and flexibility with it's built in wizards. They are very good at creating...
5. 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,...
6. 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...
7. Oracle APEX Tutorial 4 – Form Layout – Part 3 – Video Training APEX gives you a lot of power and flexibility with its built in wizards. They are very good at creating...
8. Oracle APEX Tutorial 10 - Access Control - Part 1 - Video Training Security. We hear about it everywhere. Make this more secure, make this less secure. In the computer industry this is...