Oracle APEX Tutorial 2 - Parameterized Report Part 1 - Video Training
Filed Under Technical Tips // Tags: oracle apex, video training
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:
- 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....
- 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...
- 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...
- 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...
- 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,...
- 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...
- 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...
- 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...
