Introduction

APEX gives you a lot of power and flexibility with its built in wizards. They are very good at
creating the base needs of a general application. This is true with forms. However, the wizards
create everything in the same way, which may or may not be what you want the end form to look
like. In this tutorial we will look at how to alter and better control the form’s appearance and
layout, as well as how to optimize the form somewhat. This tutorial installs its own sample data,
so the OEHR sample data is not required, but we recommend you have it installed if you don’t
already. Our article on APEX Tutorial Preparation and OEHR Sample Data Install covers it’s
installation and creation of a user account for development purposes.

Part 1 – Loading the Data, Creating and Testing the Base Application

High Level Steps

1) Load the HT_EMP data.(0:40)
2) Create the Base Application. (2:28)
3) Modify Base Application and Test. (3:51)
4) Altering and Reverting Appearance. (7:18)

Times in parenthesis are the approximate start times for those steps

Notes and Resources.

SQL Workshop

This tutorial we touch on and use the SQL workshops’ Script editor to create and run a script
that creates and loads the HT_EMP data. The SQL Workshop is a powerful tool for building, testing
and debugging your SQL calls, scripts and PL/SQL code.

Validation Removal

In this tutorial we delete an unneeded validation (P2_REC_CREATE_DATE not null) that was
automatically generated by the application creation process. Since in actual use this
value is set via a trigger, it is not needed and therefore safe to remove, and was created in part since the column it links
to is a ‘not null’ column.

Appearance Changing

APEX allows you to alter the appearance, color, location, and many other attributes of any
given part of an applications page. While this is powerful, there is no automatic revert, IE an
UNDO button. The system sets things up under the rules of the visual theme you select in the
creation process.

Code and Entered Text.

Below is the script for the HT_EMP load.

CREATE TABLE ht_emp (
emp_id NUMBER PRIMARY KEY,
emp_first_name VARCHAR2(30) NOT NULL,
emp_middle_initial VARCHAR2(1),
emp_last_name VARCHAR2(45) NOT NULL,
emp_part_or_full_time VARCHAR2(1) NOT NULL CHECK (emp_part_or_full_time IN
('P','F')),
emp_salary NUMBER,
emp_dept VARCHAR2(20) CHECK (emp_dept IN
('SALES','ACCOUNTING',
'MANUFACTURING','HR')),
emp_hiredate DATE,
emp_manager NUMBER REFERENCES ht_emp,
emp_special_info VARCHAR2(2000),
emp_telecommute VARCHAR2(1) CHECK (emp_telecommute IN ('Y')),
rec_create_date DATE NOT NULL,
rec_update_date DATE)
/
INSERT INTO ht_emp
(emp_id, emp_first_name, emp_middle_initial, emp_last_name, emp_part_or_
full_time, emp_salary, emp_dept, emp_hiredate, emp_manager, emp_special_info,
emp_telecommute, rec_create_date)
VALUES
(1,'Scott','R','Tiger','F',
100000,'SALES',sysdate,NULL,'cell phone number is xxx.xxx.xxxx
home phone is yyy.yyy.yyyy','Y',
SYSDATE)
/
CREATE SEQUENCE ht_emp_seq
START WITH 2
/
CREATE OR REPLACE TRIGGER bi_ht_emp
BEFORE INSERT ON ht_emp
FOR EACH ROW
BEGIN
SELECT ht_emp_seq.nextval
INTO :NEW.emp_id
FROM DUAL;
:NEW.rec_create_date := SYSDATE;
END;
/
CREATE OR REPLACE TRIGGER bu_ht_emp
BEFORE UPDATE ON ht_emp
FOR EACH ROW
BEGIN
:NEW.rec_update_date := SYSDATE;
END;
/