Introduction

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 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 retrieves 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 retrieved 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 references 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 existence, and cannot alter it’s value or state themselves, only the application itself can affect it.