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

Posted on Jun 17, 2009 in Technical Tips | 1 comment

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

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

   This tutorial covers section 4...

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

   This tutorial covers section 4...

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

   APEX gives you a lot...

5. Oracle APEX Tutorial 8 – Up and Downloading Files – Part 1 – Video Training

   Reports and data entry constitue...

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

    Within APEX, there are...

7. Oracle APEX Tutorial 4 – Form Layout – Part 3 – Video Training

   APEX gives you a lot...

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

   Within APEX, there are a...