Employee Manager Demo Project- -

Concepts used in this project- Forms, Reports, Charts, Validation, Dynamic Actions

If you have been looking for a beginner-friendly, hands-on introduction to Oracle APEX, you have landed in the right place. This guide walks you through building a real, working Employee Manager Application from scratch — covering forms, interactive reports, charts, and a dashboard — all without needing prior APEX experience. By the end, you will have a fully functional web application running entirely inside Oracle Database.

What You Will Learn

What Oracle APEX is and why it matters
How the APEX architecture works under the hood
Setting up your workspace and creating an application
Building database tables and loading sample data
Creating and customising APEX forms with validations
Building interactive reports your users will love
Adding charts and assembling a live dashboard
Finishing touches: navigation, buttons, and responsive testing

Chapter 1 — What Is Oracle APEX?

The Short Answer

Oracle APEX (Application Express) is a low-code web application development platform that lives inside the Oracle Database. You build professional, data-driven web apps using only a browser — no separate web server, no Java EE stack, nothing to install on your machine.

Think of APEX as a smart layer sitting on top of Oracle Database. You write SQL and PL/SQL, and APEX automatically turns that into a fully functional web application — complete with forms, reports, buttons, navigation menus, and charts.

ℹ Good to know APEX is completely free and bundled with every Oracle Database licence — whether you are running on-premises or on Oracle Cloud at apex.oracle.com.

Why Developers and Businesses Love APEX

Runs inside the database — your data never leaves Oracle, so security and performance are excellent.
Genuinely low-code — around 80% of a typical application can be built using point-and-click wizards, without touching JavaScript.
Responsive by default — every APEX theme is mobile-friendly right out of the box.
Rich component library — forms, interactive reports, calendars, maps, charts, kanban boards, and more are all built in.
No licensing cost — included in every Oracle Database licence.

Key APEX Terms You Should Know

Before diving into the build, here is a quick reference for the terminology you will encounter throughout this guide.

Term	Plain-English Meaning
Workspace	Your private area inside APEX — like a tenant in a building. All your apps live here.
Application	A complete web app. Think of it as a folder containing all your pages.
Page	One screen in the browser. An application can have hundreds of pages.
Region	A section on a page — could be a form, a report, a chart, or a container.
Item	A single input field — text box, date picker, select list, and so on.
Process	Server-side logic that runs on page submit — saves data, sends email, calls PL/SQL.
Dynamic Action	Client-side behaviour that fires without reloading the page — show/hide fields, set values, etc.
Shared Components	Reusable parts — navigation menus, lists of values, themes, authentication schemes.
SQL Workshop	A browser-based SQL editor built into APEX — create tables and run queries without leaving the browser.
Page Designer	The drag-and-drop IDE where you build and configure every page.
Universal Theme	The default modern, responsive theme that ships with APEX.

Chapter 2 — Understanding the APEX Architecture

How a Request Flows Through APEX

When a user opens your APEX app in a browser, here is what happens in the background:

The browser sends an HTTP request to the APEX server (URL pattern: /apex/f?p=AppID:PageID).
The APEX engine — a PL/SQL package called APEX_ENGINE — inside the database picks up the request.
APEX executes the page’s SQL queries and PL/SQL logic against your tables.
APEX generates the HTML, CSS, and JavaScript on the fly.
The browser renders the page and the user sees the finished form or report.

ℹ Why this matters There is no separate application server. APEX runs entirely inside Oracle Database using PL/SQL. This is why APEX applications are extremely fast and secure — no data ever has to travel outside the database engine.

The Three-Tier Mental Model

Tier	What It Contains in APEX
Presentation (Browser)	HTML pages generated by APEX. Universal Theme handles CSS and JavaScript automatically.
Application Logic (APEX Engine)	Your pages, regions, items, processes, and validations — all stored as metadata in APEX tables.
Data (Oracle Database)	Your actual tables (such as EMP_DEMO) where all business data lives.

APEX Builder vs. the Runtime App

Throughout this guide you will switch between two modes:

APEX Builder (the IDE) — accessed via /apex, used to design and configure pages. Only developers log in here.
Runtime Application — the actual app your end users will use. Accessed via a URL like /apex/f?p=APP_ID.

💡 Tip Always test your app using the Run button (green triangle) inside Page Designer. This opens the runtime app in a new tab so you can see exactly what users will see.

Chapter 3 — Pre-Session Setup Checklist

Complete every item on this list before starting the build. Running through these checks in advance will save you from scrambling for scripts or hunting down credentials mid-session.

Item	What to Do
APEX Workspace	Log in to your APEX instance and confirm your workspace is active. URL: `https://<your-host>/apex`
Browser	Use Google Chrome or Mozilla Firefox. Avoid Safari for APEX development — some builder features behave differently.
Screen resolution	Set to 1920×1080 or higher. Page Designer needs horizontal space to work comfortably.
SQL scripts	Copy the CREATE TABLE and INSERT scripts from Chapter 5 into a plain .txt file on your desktop.
APEX version	Go to About Oracle APEX (top-right avatar menu) and confirm version 22.x or higher. This guide targets APEX 24/26 but works from version 22 upwards.
Sample data	Run the INSERT script at least once so reports and charts have real data to display.
Backup	Export your workspace (App Builder → Export) after setup so you can restore quickly if something breaks.
Network	Confirm the machine has internet or intranet access to your APEX host.

⚠ Important Never try to type the SQL scripts live. Have them ready to paste. Every extra second of typing risks typos and interrupts your flow.

Chapter 4 — Workspace & Application Setup

Logging Into APEX

When you navigate to the APEX URL, you land on the login page. You need three pieces of information to get in:

Workspace name — for example, HR_DEMO.
Username — your APEX developer account, such as ADMIN.
Password — your account password.

Once logged in, you see the APEX Builder Home with four main panels:

Panel	Purpose
App Builder	Create, edit, and manage your web applications.
SQL Workshop	Run SQL, create tables, manage schema objects — like a browser-based SQL*Plus.
Team Development	Track bugs, features, and to-do items for your project.
App Gallery	Download pre-built sample apps to learn from or use as starting points.

Step-by-Step: Log In

Open your browser and navigate to your APEX URL
For example: https://your-host/apex or https://apex.oracle.com/pls/apex

Enter your workspace credentials
Workspace: HR_DEMO | Username: ADMIN | Password: your password

Confirm you can see the APEX Builder Home page
You should see four coloured panels: App Builder, SQL Workshop, Team Development, and App Gallery.

💡 Tip If you are using the Oracle APEX Free Tier at apex.oracle.com, sign in with your Oracle SSO account and select your workspace from the dropdown.

Understanding What Gets Created Automatically

When you create a new APEX application, the platform sets up several things for you automatically:

A Home page (Page 1) — a blank dashboard page.
A Login page (Page 9999) — handles user authentication.
A Global page (Page 0) — regions and items placed here appear on every single page of the app.
Navigation menus — a side menu and top bar auto-generated from your page list.

Step-by-Step: Create the Employee Manager Application

Click App Builder on the APEX Home page
This opens the App Builder, which lists all applications in your workspace.

Click the Create button (top right, blue)
A dialog appears asking what kind of app to create.

Choose New Application
This opens the Create Application wizard.

Fill in the application details
Name: Employee Manager | Appearance: Redwood Light theme | Leave all other defaults as-is.

Click Create Application
APEX builds the application in seconds. You are taken to the App Builder page showing your new application.

💡 Tip The Redwood theme is Oracle’s latest design language — it looks professional and modern out of the box, and adapts to mobile screens automatically.

Click Run Application (green triangle icon)
This opens the runtime app in a new browser tab. Log in with your APEX credentials. A blank home page confirms everything is working.

Chapter 5 — Database Table & Sample Data

What Is SQL Workshop?

SQL Workshop is a browser-based SQL environment built directly into APEX. Think of it as a lightweight SQL Developer that runs entirely in the browser, requiring zero installation. It has four main tools:

Tool	What You Use It For
Object Browser	View and manage tables, views, sequences, and other schema objects graphically.
SQL Commands	Run any SQL or PL/SQL statement interactively — like an interactive SQL prompt.
SQL Scripts	Store and run saved SQL scripts — great for CREATE TABLE and INSERT batches.
RESTful Services	Expose your data as REST APIs (advanced — not covered in this guide).

Understanding the EMP_DEMO Table Design

Our demo application is built on a single table called EMP_DEMO. Here is what each column stores and why it is designed that way:

Column	Data Type & Purpose
`EMP_ID`	NUMBER — auto-generated primary key using IDENTITY. Oracle assigns a unique number to every new row automatically.
`NAME`	VARCHAR2(100) — full name of the employee. NOT NULL means this field is mandatory.
`DEPT`	VARCHAR2(50) — department name, e.g. Engineering, Sales, Finance.
`ROLE`	VARCHAR2(100) — job title, e.g. Senior Developer, HR Manager.
`SALARY`	NUMBER(10,2) — annual gross salary. Up to 10 digits with 2 decimal places.
`STATUS`	VARCHAR2(20) — employee status. DEFAULT ‘Active’ means new rows get ‘Active’ automatically.
`HIRE_DT`	DATE — date the employee joined. DEFAULT SYSDATE means today’s date if not provided.
`LOCATION`	VARCHAR2(50) — office city, e.g. Mumbai, Bangalore, Delhi.

Step-by-Step: Create the Table

Go to SQL Workshop → SQL Commands
Click the SQL Workshop panel on the APEX Builder Home page, then click SQL Commands.

Clear the editor
Select all (Ctrl+A) and delete any previous content.

Paste and run the CREATE TABLE statement

CREATE TABLE emp_demo (
    emp_id    NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      VARCHAR2(100) NOT NULL,
    dept      VARCHAR2(50),
    role      VARCHAR2(100),
    salary    NUMBER(10,2),
    status    VARCHAR2(20) DEFAULT ‘Active’,
    hire_dt   DATE DEFAULT SYSDATE,
    location  VARCHAR2(50)
);

💡 Tip After running, you should see: Table created. If you see table already exists, it was created in a previous session — that is fine, just proceed to the next step.

Verify the table in Object Browser
Click Object Browser → expand Tables → you should see EMP_DEMO listed. Click it to see its columns and constraints.

Step-by-Step: Insert Sample Data

Back in SQL Commands, paste the INSERT script below
These 10 records give you a realistic set of employees across multiple departments, salary ranges, and statuses.

INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Aarav Mehta’,  ‘Engineering’, ‘Senior Developer’,  142000, ‘Active’,   DATE ‘2019-03-12’, ‘Mumbai’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Priya Sharma’, ‘Engineering’, ‘Tech Lead’,         168000, ‘Active’,   DATE ‘2017-06-01’, ‘Bangalore’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Rajesh Kumar’, ‘Sales’,       ‘Account Executive’,  95000, ‘Active’,   DATE ‘2021-01-15’, ‘Delhi’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Sneha Iyer’,   ‘HR’,          ‘HR Manager’,        112000, ‘Active’,   DATE ‘2018-09-20’, ‘Chennai’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Vikram Singh’, ‘Finance’,     ‘Financial Analyst’, 108000, ‘On Leave’, DATE ‘2020-07-08’, ‘Mumbai’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Divya Nair’,   ‘Engineering’, ‘QA Engineer’,        88000, ‘Active’,   DATE ‘2022-03-30’, ‘Hyderabad’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Arjun Patel’,  ‘Marketing’,   ‘Marketing Head’,   135000, ‘Active’,   DATE ‘2016-11-05’, ‘Pune’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Meera Joshi’,  ‘Sales’,       ‘Sales Manager’,    120000, ‘Inactive’, DATE ‘2019-08-22’, ‘Ahmedabad’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Karan Verma’,  ‘Engineering’, ‘DevOps Engineer’,  130000, ‘Active’,   DATE ‘2020-02-14’, ‘Bangalore’);
INSERT INTO emp_demo (name, dept, role, salary, status, hire_dt, location)
VALUES (‘Ananya Reddy’, ‘Finance’,     ‘CFO’,              210000, ‘Active’,   DATE ‘2015-04-01’, ‘Mumbai’);
COMMIT;

Click Run — confirm 10 rows inserted
You should see: 10 rows inserted. The COMMIT at the end saves the data permanently.

Verify data in Object Browser
Object Browser → EMP_DEMO → click the Data tab. All 10 employee rows should be visible.

🔑 Key point Always end INSERT scripts with COMMIT. Without it, data exists only within your current session and disappears if the session times out.

Chapter 6 — Building Forms

What Is an APEX Form?

A Form in APEX is a web page that lets users view, enter, and edit a single row of data from a table. APEX forms are auto-generated — you point the wizard at a table and APEX creates all the input fields, buttons, and save/delete logic automatically.

Every generated form comes with three standard buttons:

Save — runs an INSERT or UPDATE. APEX detects whether to insert (new record) or update (existing record) based on whether a primary key value is present.
Delete — runs a DELETE with a confirmation dialog.
Cancel — navigates back without saving.

ℹ How it works under the hood APEX Form pages use Automatic Row Processing (ARP) — a built-in process that writes your form data to the database. You do not need to write any DML code yourself.

Understanding Page Designer

Page Designer is the IDE where you build and configure your pages. It has three panels:

Panel	What It Shows
Left panel (Page tree)	A tree listing every component on the page: Regions, Items, Buttons, Processes, Validations, Dynamic Actions.
Centre panel (Canvas)	A visual preview of the page layout. You can drag and resize regions.
Right panel (Properties)	When you click any component, all its configurable properties appear here — Label, Source, Validations, Conditions, and more.

Step-by-Step: Create the Employee Form

Open your application in App Builder
App Builder → click the Employee Manager application → you see the page list.

Click Create Page (blue button, top right)
The Create a Page wizard opens.

Select Form → Form on a Table or View
This auto-generation wizard scans your table and creates all items automatically.

Configure the page settings
Page Number: 2 | Page Name: Employee Form | Navigation: check Include in breadcrumb | Click Next.

Select the table
Data Source: Local Database | Table/View Name: EMP_DEMO | Click Next.

Set the Primary Key column
Primary Key Column 1: EMP_ID | Source: Existing Trigger or Identity Column | Click Next.

Click Create Page
APEX generates the full form in seconds. You just created a complete CRUD interface in under two minutes.

💡 This is what makes APEX special What would take days to code manually is ready instantly. This is one of APEX’s most compelling features.

Customising Form Items

By default, APEX creates every column as a plain text field. You can change item types to make the form smarter and more user-friendly. Here are the most useful options:

Item Type	When to Use It
Text Field	Default. Single-line text input.
Text Area	Multi-line input. Good for notes and comments.
Select List	A dropdown of fixed or dynamic options. Great for STATUS and DEPT fields.
Date Picker	Shows a calendar popup. Always use for DATE columns.
Number Field	Numeric input with optional format mask. Use for SALARY.
Hidden	Stores a value (like a primary key) without displaying it to the user.
Display Only	Shows a value as read-only text — user cannot edit it.

Improve the STATUS Field: Select List

Open Page 2 in Page Designer → find P2_STATUS in the page tree

Change Type to Select List
In Properties → Identification → Type: Select List

Configure the List of Values
Type: Static Values | Values: Active | Active, On Leave | On Leave, Inactive | Inactive

Set Null Display Value to -- Select Status --
This prevents accidental blank selections.

Improve the HIRE_DT Field: Date Picker

Click P2_HIRE_DT → change Type to Date Picker

Set Format Mask to DD-MON-YYYY
The field now shows a calendar icon — clicking it opens a date picker popup.

Format the SALARY Field

Click P2_SALARY → change Type to Number Field

Set Format Mask to 999,999,990.00
This adds comma separators and two decimal places automatically.

Adding Validations

Validations run on the server when the user clicks Save. If a validation fails, APEX shows an error message next to the offending field and no data is written to the database. Here are the most useful validation types:

Validation Type	What It Checks
Not Null	The field must not be empty. The most common validation.
Item is a Valid Number	Ensures the field contains only numeric characters.
Item is a Valid Date	Ensures the field contains a valid date in the specified format.
PL/SQL Expression	Any custom PL/SQL that returns TRUE (valid) or FALSE (error).
PL/SQL Function Returning Error Text	Returns NULL if valid, or an error message string if invalid.

Add a Not Null Validation on NAME

In Page Designer for Page 2, right-click Validations → Create Validation

Configure it
Name: Name is Required | Type: Item is NOT NULL | Item: P2_NAME | Error Message: Employee Name is required. Please enter a name. | Display: Inline with Field

Add a PL/SQL Validation on SALARY

Create another Validation
Name: Salary Must Be Positive | Type: PL/SQL Expression | Expression: :P2_SALARY > 0 | Error Message: Salary must be a positive number.

ℹ APEX item references in PL/SQL In APEX, form item values are referenced in PL/SQL using a colon prefix. :P2_SALARY means: the value the user typed into the P2_SALARY item on page 2.

Dynamic Actions — Interactive Behaviour Without JavaScript

A Dynamic Action (DA) adds interactive behaviour to your form page without a page reload and without writing any JavaScript. Every DA has three parts:

When — what triggers it? (e.g. the Change event on a Select List)
Condition — an optional condition that must be true for the action to fire.
True/False Actions — what happens when the condition is met or not.

Add a Dynamic Action to Show/Hide a Reason Field

Add a REASON text area item to the form
Right-click Items node → Create Page Item | Name: P2_REASON | Type: Text Area | Label: Reason for Leave

Right-click P2_STATUS → Create Dynamic Action
Name: Show Reason When On Leave | Event: Change | Item: P2_STATUS | Condition: Equal to On Leave

Configure the True action → Show → Item: P2_REASON

Add a False action → Hide → Item: P2_REASON

Set P2_REASON initial display to Hidden
Properties → Appearance → check Hidden.

Save and run the page
Change STATUS to “On Leave” — the Reason field appears. Change to “Active” — it disappears. No JavaScript written.

Chapter 7 — Interactive Reports

What Makes Interactive Reports Special?

An Interactive Report (IR) is APEX’s most powerful report component. Unlike a Classic Report which shows a fixed view, an Interactive Report gives end users real-time control over what they see. Without any developer involvement, users can:

Filter, sort, and search data.
Add highlights — colour-code rows based on conditions.
Create and share their own saved views.
Download data as CSV, Excel, HTML, or PDF.
Create pivot tables, group-by aggregations, and charts — directly from the report UI.

All of this is built into APEX. You do not write a single line of JavaScript to enable it.

ℹ Interactive vs Classic Reports Use Interactive Reports when users need to explore data themselves. Use Classic Reports when you need a fixed, consistently formatted output — such as a printable invoice.

Step-by-Step: Create the Interactive Report Page

Create Page → Report → Interactive Report

Configure the page
Page Number: 3 | Page Name: Employee Report | Include in navigation menu | Click Next.

Set the data source
Source Type: Table | Table: EMP_DEMO | Select all columns | Click Create Page.

Run Page 3
You see a table with all 10 employee rows. The Actions button appears at the top right of the report.

What End Users Can Do With the Report

Search and Filter

Type “Engineering” in the search box at the top — the report immediately filters to show only Engineering employees. Click a column header to sort; click again to reverse the sort order.

Row Highlights

Actions menu → Format → Highlight. Name it High Earner, set background colour to yellow, condition: SALARY >= 130000. Rows matching the condition are highlighted and the setting persists across sessions for that user.

Download / Export

Actions → Download → Excel. A .xlsx file downloads with all currently visible columns and rows, including any active filters.

Pivot Tables

Actions → Format → Pivot. Set Pivot Columns: DEPT, Rows: STATUS, Functions: COUNT. APEX generates a cross-tab summary instantly — departments as columns, status as rows, counts in the cells.

Linking Report Rows to the Form

Open Page 3 in Page Designer → find the EMP_ID column
Regions → Employee Report → Columns → click EMP_ID.

Configure a column link
Target: Page in this Application | Page: 2 | Set Items: Name = P2_EMP_ID, Value = #EMP_ID#

Save and run
Click any EMP_ID value in the report — it navigates to the Employee Form pre-populated with that employee’s data.

ℹ APEX substitution syntax #EMP_ID# means: use the EMP_ID value from the current row. This is how APEX passes row context between pages.

Adding a Computed Column

Open Page 3, find the SQL Source of the IR region, and replace the query with the following to add a salary grade column:

SELECT
    emp_id,
    name,
    dept,
    role,
    salary,
    status,
    hire_dt,
    location,
    CASE
        WHEN salary >= 150000 THEN ‘Senior’
        WHEN salary >= 100000 THEN ‘Mid-Level’
        ELSE ‘Junior’
    END AS grade
FROM emp_demo

A new Grade column now appears, automatically computed from salary. No new database column was needed.

Department Summary: Classic Report

Create Page → Report → Classic Report
Page 4, Name: Department Summary | SQL Query:

SELECT
    dept                              AS “Department”,
    COUNT(*)                          AS “Headcount”,
    ROUND(AVG(salary), 0)             AS “Avg Salary”,
    MIN(salary)                       AS “Min Salary”,
    MAX(salary)                       AS “Max Salary”,
    SUM(CASE WHEN status = ‘Active’
             THEN 1 ELSE 0 END)       AS “Active Count”
FROM emp_demo
GROUP BY dept
ORDER BY COUNT(*) DESC

Column aliases in double quotes become the report headers automatically — a clean department summary, no formatting required.

Chapter 8 — Charts & Dashboard

Charts in APEX

APEX uses Oracle JET charts — enterprise-grade, interactive, and responsive out of the box. You configure everything through properties; the SQL query provides the data and APEX handles all the rendering. Every APEX chart requires at minimum:

A Label column — used for axis labels or slice names (e.g. DEPT).
A Value column — the numeric value to plot (e.g. COUNT(*) or SUM(SALARY)).

Chart Type	Best Used For
Bar / Horizontal Bar	Comparing values across categories — e.g. headcount per department.
Line	Showing trends over time — e.g. average salary by hire year.
Pie / Donut	Showing proportions of a whole — e.g. status distribution.
Area	Line chart with fill — good for volume over time.
Gauge	Single KPI value against a target range.

Building the Dashboard on Page 1

KPI Metric Row

Create a Classic Report region on Page 1
Right-click Content Body → Create Region | Title: Key Metrics | Type: Classic Report | SQL:

SELECT
    COUNT(*)                                        AS total_employees,
    ROUND(AVG(salary), 0)                           AS avg_salary,
    SUM(CASE WHEN status=’Active’ THEN 1 ELSE 0 END) AS active_count,
    COUNT(DISTINCT dept)                            AS total_depts
FROM emp_demo

Apply the Cards template
Properties → Appearance → Template: Cards (or Statistics). Each column renders as a metric card.

Bar Chart — Headcount by Department

Create a Chart region → Type: Bar (Vertical)

Series SQL:

SELECT dept   AS label,
       COUNT(*) AS value
FROM   emp_demo
GROUP BY dept
ORDER BY 2 DESC

Map columns: Label = LABEL, Value = VALUE. Save and run.

Donut Chart — Status Distribution

Create a Chart region → Type: Pie (switch to Donut in attributes)
Layout: Start New Row: No, Column: 7, Column Span: 6 — places it beside the bar chart.

Series SQL:

SELECT status  AS label,
       COUNT(*) AS value
FROM   emp_demo
GROUP BY status

Line Chart — Average Salary Trend by Hire Year

Create a Chart region → Type: Line (full width, new row)

Series SQL:

SELECT TO_CHAR(hire_dt, ‘YYYY’)   AS label,
       ROUND(AVG(salary), 0)       AS value
FROM   emp_demo
GROUP BY TO_CHAR(hire_dt, ‘YYYY’)
ORDER BY 1

Enable Markers in Series Properties → Appearance → Markers: Enabled

Horizontal Bar Chart — Average Salary by Department

Create a Chart region → Type: Horizontal Bar

Series SQL:

SELECT dept                     AS label,
       ROUND(AVG(salary), 0)    AS value
FROM   emp_demo
GROUP BY dept
ORDER BY 2 DESC

Run Page 1
You now have a complete dashboard: KPI cards at the top, a bar chart and donut side by side, a salary trend line, and a horizontal bar for department averages. All from pure SQL — no JavaScript.

Chapter 9 — Finishing Touches

Adding Navigation Between Pages

Shared Components → Navigation → Navigation Menu → Desktop Navigation Menu → Edit

Add menu entries
Name: Employees | Target Page: 3 | Icon: fa-table
Repeat for: Add Employee (Page 2), Department Summary (Page 4).

Adding an “Add Employee” Button on the Report

Open Page 3 in Page Designer → right-click Buttons → Create Button
Name: ADD_EMPLOYEE | Label: Add Employee | Position: Right of Search Bar | Action: Redirect to Page 2 | Clear Cache: 2

Setting Clear Cache to 2 clears the form fields so you always get a blank form rather than the last-viewed record.

Testing the Responsive Layout

Press F12 in Chrome → click the mobile icon → choose iPhone 12 Pro
Navigate through the app. The navigation collapses to a hamburger menu. The form stacks vertically. Charts resize. This is all automatic with the Redwood/Universal theme — no extra work required.

Chapter 10 — Quick Reference & Troubleshooting

Page Designer Keyboard Shortcuts

Shortcut	Action
`Ctrl + S`	Save the current page.
`Ctrl + Enter`	Save and Run the page.
`Ctrl + Z`	Undo last change.
`Ctrl + F`	Find component (search the page tree).
`F6`	Toggle focus between Properties panel and canvas.
Right-click any node	Context menu with Create, Copy, Delete, Move options.

APEX Item Naming Convention

APEX item names follow the pattern P[Page Number]_[Column Name]. For example:

P2_NAME — the NAME item on page 2.
P2_EMP_ID — the EMP_ID item on page 2.
P3_DEPT — the DEPT item on page 3.

In PL/SQL and SQL expressions, reference items with a colon prefix: :P2_NAME.

Troubleshooting Common Problems

Problem	Solution
Table EMP_DEMO does not exist	Go to SQL Workshop → SQL Commands and run the CREATE TABLE script. Then run the INSERT script.
Form shows blank after save	Check that the primary key is set correctly in the Automatic Row Processing process. It must reference EMP_ID.
Dynamic action not firing	Make sure you saved the page (Ctrl+S). Also verify the DA event is set to Change (not Click) for a Select List.
Charts show “No Data Found”	Run the chart SQL in SQL Workshop first to confirm it returns rows. Check column aliases match the Label/Value mappings.
Navigation menu missing pages	Shared Components → Navigation Menu → add missing entries with the correct page numbers.
Report column link not working	Verify the Set Items mapping uses the correct P2_EMP_ID item name and #EMP_ID# substitution syntax.
Login page appears when running app	Normal behaviour — log in with your APEX developer credentials. Or set Authentication Scheme to No Authentication for demos.
Page Designer is very slow	Use Chrome or Firefox. Disable browser extensions. Ensure you are on a fast network connection.

Handy SQL Queries for SQL Workshop

Count rows in the table:

SELECT COUNT(*) FROM emp_demo;

View all data sorted by hire date:

SELECT * FROM emp_demo ORDER BY hire_dt DESC;

Salary distribution by department:

SELECT dept, COUNT(*) AS headcount,
       ROUND(AVG(salary),0) AS avg_sal,
       MIN(salary) AS min_sal,
       MAX(salary) AS max_sal
FROM emp_demo
GROUP BY dept
ORDER BY avg_sal DESC;

Delete all rows to start fresh:

DELETE FROM emp_demo;
COMMIT;

Final Pre-Launch Checklist

Check	What to Verify
Table exists with data	`SELECT COUNT(*) FROM emp_demo;` — should return 10.
App runs without errors	Run Page 1 — dashboard should show all charts with data.
Form works end-to-end	Create a new employee on Page 2, save, confirm in the report on Page 3.
Report column link works	Click an EMP_ID in the report — it should open that employee in the form.
Interactive Report actions	Test: sort by salary, add a filter, download CSV, add a highlight.
Charts render on Page 1	All 4 chart regions show data — no “No Data Found” messages.
Navigation menu	All pages are accessible from the top or side navigation.
Browser zoom level	Set to 90–100%. Page Designer needs room to breathe.

🎉 You are ready! You have built a complete Oracle APEX application from scratch — a database-backed Employee Manager with forms, validations, dynamic behaviour, interactive reports, and a live dashboard. Everything you have done here scales directly to real-world APEX projects of any size.

Guide based on Oracle APEX 26 — Employee Manager Demo Project.