Low Code - Employee Management System - Create Table, Sequence, Report and Form in Oracle Apex

Introduction

In this blog post, I will show you how to professionally create sequences, define the corresponding tables with unique column names and default values, and set up audit columns. Then, I demonstrate how to quickly and easily create reports and forms using Oracle APEX's low-code features.

Steps to Create Sequences and Tables

1. Sequence and Table for Departments

1.1 Create Sequence for Departments

If you have already created the sequence, you can use the drop statement to drop the old sequence.

 

SQL

xxxxxxxxxx

 

1

-- Drop the old sequence if it exists

2

drop sequence apno_depa_seq;

3

​

4

-- Create a new sequence for departments

5

create sequence apno_depa_seq 

6

start with 100 

7

increment by 1 

8

nocache 

9

nocycle;

1.2 Create Table for Departments

If you have already created the table, then you can drop the table first and then create it again.

 

SQL

xxxxxxxxxx

22

 

1

-- Drop the old department table if it exists

2

drop table apno_department;

3

​

4

-- Create a new department table

5

create table apno_department(

6

    depa_id                number              default apno_depa_seq.nextval primary key,

7

    depa_name              varchar2(4000)      not null,

8

    depa_location          varchar2(4000)      not null,

9

    -- Picture

10

    depa_image             blob                default null,

11

    depa_image_name        varchar2(4000)      default null,

12

    depa_image_mimetype    varchar2(4000)      default null,

13

    -- Audit

14

    depa_remark            clob                default null,

15

    depa_created           timestamp           default sysdate not null,

16

    depa_created_by        varchar2(4000)      default (coalesce(sys_context('apex$session', 'app_user'), sys_context('userenv', 'os_user'), sys_context('userenv', 'session_user')) ) not null,

17

    depa_updated           timestamp           default sysdate not null,

18

    depa_updated_by        varchar2(4000)      default (coalesce(sys_context('apex$session', 'app_user'), sys_context('userenv', 'os_user'), sys_context('userenv', 'session_user')) ) not null,

19

    depa_valid_from        timestamp           default sysdate not null,

20

    depa_valid_to          timestamp           default to_date('31.12.2999', 'dd.mm.yyyy') not null,

21

    depa_deleted_yn        varchar2(4000)      default 'NO'

22

);

2. Sequence and Table for Employees

2.1 Create Sequence for Employees

If the sequence exists, you can drop it and then create a new sequence.

 

SQL

xxxxxxxxxx

 

1

-- Drop the old sequence if it exists

2

drop sequence apno_empl_seq;

3

​

4

-- Create a new sequence for employees

5

create sequence apno_empl_seq 

6

start with 100 

7

increment by 1 

8

nocache 

9

nocycle;

2.2 Create Table for Employees

If you have already created the employee table, you can delete the old table and create a new one.

 

SQL

xxxxxxxxxx

32

 

1

-- Drop the old employee table if it exists

2

drop table apno_employee;

3

​

4

-- Create a new employee table

5

create table apno_employee(

6

    empl_id                 number              default apno_empl_seq.nextval primary key,

7

    empl_name               varchar2(4000)      not null,

8

    empl_job                varchar2(4000)      not null,

9

    empl_hiredate           date                default sysdate,

10

    empl_salary             number              default 45000,

11

    empl_email              varchar2(4000)      default null,

12

    -- Picture

13

    empl_image              blob                default null,

14

    empl_image_name         varchar2(4000)      default null,

15

    empl_image_mimetype     varchar2(4000)      default null,

16

    -- Foreign Key

17

    empl_emplmgr_fk         number              default null,

18

    empl_depa_fk            number              not null,

19

    -- Audit

20

    empl_remark             clob                default null,

21

    empl_created            timestamp           default sysdate not null,

22

    empl_created_by         varchar2(4000)      default (coalesce(sys_context('apex$session', 'app_user'), sys_context('userenv', 'os_user'), sys_context('userenv', 'session_user')) ) not null,

23

    empl_updated            timestamp           default sysdate not null,

24

    empl_updated_by         varchar2(4000)      default (coalesce(sys_context('apex$session', 'app_user'), sys_context('userenv', 'os_user'), sys_context('userenv', 'session_user')) ) not null,

25

    empl_valid_from         timestamp           default sysdate not null,

26

    empl_valid_to           timestamp           default to_date('31.12.2999', 'dd.mm.yyyy') not null,

27

    empl_deleted_yn         varchar2(4)         default 'NO'

28

);

29

​

30

-- Add foreign key constraint

31

alter table apno_employee add constraint "empl_depa_fk_con" foreign key (empl_depa_fk)

32

references apno_department (depa_id) enable;

3. Inserting Data

3.1 Insert Data into Departments Table

After your tables are created, you should insert all department data first.

 

SQL

xxxxxxxxxx

12

 

1

begin

2

    insert into apno_department (depa_id, depa_name, depa_location) values (1, 'Accounting', 'Frankfurt');

3

    insert into apno_department (depa_id, depa_name, depa_location) values (2, 'Research', 'Munich');

4

    insert into apno_department (depa_id, depa_name, depa_location) values (3, 'Sales', 'Hamburg');

5

    insert into apno_department (depa_id, depa_name, depa_location) values (4, 'Operations', 'Berlin');

6

    insert into apno_department (depa_id, depa_name, depa_location) values (5, 'Human Resources', 'Düsseldorf');

7

    insert into apno_department (depa_id, depa_name, depa_location) values (6, 'IT', 'Stuttgart');

8

    insert into apno_department (depa_id, depa_name, depa_location) values (7, 'Customer Service', 'Leipzig');

9

    insert into apno_department (depa_id, depa_name, depa_location) values (8, 'Production', 'Dresden');

10

    insert into apno_department (depa_id, depa_name, depa_location) values (9, 'Legal Department', 'Hannover');

11

    insert into apno_department (depa_id, depa_name, depa_location) values (10, 'Strategy and Development', 'Nuremberg');

12

end;

3.2 Insert Data into Employees Table

Next, you should insert all the employee data.

 

SQL

xxxxxxxxxx

23

 

1

begin

2

    delete from apno_employee;

3

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (1, 'Maximilian Weber', 'Senior Accountant', NULL, 60000, 1, 'Maximilian is known for his precise work ethic and his ability to simplify and optimize complex financial operations. He has an excellent track record in implementing efficient accounting processes that significantly contribute to cost reduction.');

4

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (2, 'Julia Fischer', 'Marketing Manager', NULL, 55000, 2, 'Julia brings a fresh perspective to the marketing team and is known for her creative approaches, which have led to measurable successes in previous positions.');

5

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (3, 'Thomas Schmidt', 'IT Security Officer', NULL, 70000, 4, 'Thomas plays a crucial role in the IT department and is known for his methodical and detail-oriented approach, which protects the company from data loss and security breaches.');

6

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (4, 'Niklas Bauer', 'Research Assistant', 2, 42000, 2, 'Niklas has already accomplished impressive research work early in his career and is known for his quick comprehension and commitment to science. His analytical approach and ability to interpret complex data make him a valuable member of the research team.');

7

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (5, 'Hannah Müller', 'Chief Financial Officer', NULL, 90000, 5, 'Hannah has extensive experience in the finance sector and leads our financial strategies with an innovative approach.');

8

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (6, 'Felix Richter', 'Chief Marketing Officer', NULL, 85000, 6, 'Felix is a visionary leader with a deep understanding of global markets and has taken the company to new marketing dimensions.');

9

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (7, 'Andrea Meier', 'HR Manager', NULL, 58000, 9, 'Andrea has extensive experience in HR and brings innovative approaches to employee development and engagement.');

10

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (8, 'Lukas Klein', 'Production Manager', NULL, 62000, 8, 'Lukas is known for his efficient management of production processes and his commitment to sustainability and quality.');

11

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (9, 'Anna Schmidt', 'Software Developer', 1, 45000, 1, 'Anna has been working in software development for 5 years, specializing in backend systems.');

12

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (10, 'Markus Bauer', 'Project Manager', 2, 50000, 2, 'Markus manages multiple projects in digital marketing.');

13

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (12, 'Tobias Wolf', 'HR Manager', 5, 48000, 4, 'Tobias is responsible for employee development and recruitment.');

14

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (13, 'Emily Sommer', 'IT Support Specialist', 6, 42000, 5, 'Emily supports the company with all IT-related inquiries.');

15

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (14, 'Julian Weber', 'Sales Manager', 7, 60000, 6, 'Julian drives sales through strategic sales initiatives.');

16

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (15, 'Sophie Koch', 'Product Manager', 8, 62000, 7, 'Sophie works closely with the development team to design new products.');

17

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (16, 'Lukas Neumann', 'Network Administrator', 1, 53000, 8, 'Lukas specializes in the security and maintenance of the network infrastructure.');

18

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (17, 'Sara Lang', 'Business Analyst', 2, 47000, 9, 'Sara brings innovative solutions to complex business problems.');

19

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (18, 'Christian Fischer', 'Marketing Specialist', 3, 44000, 10, 'Christian is responsible for the planning and implementation of online marketing campaigns.');

20

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (19, 'Monika Schwarz', 'Data Analyst', 4, 56000, 1, 'Monika supports the team with in-depth analyses of market and customer data to promote data-driven decisions.');

21

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (20, 'Friedrich Bauer', 'Compliance Officer', 5, 61000, 2, 'Friedrich ensures that the company always complies with all legal and internal guidelines.');

22

    insert into apno_employee (empl_id, empl_name, empl_job, empl_emplmgr_fk, empl_salary, empl_depa_fk, empl_remark) values (21, 'Lisa Meyer', 'Head of Customer Service', 6, 54000, 3, 'Lisa leads the customer service team with a focus on excellent customer support and improving customer satisfaction.');

23

end;

4. Low-Code Application Development with Oracle APEX

4.1 Create Report and Form Pages

• Department Pages: One page for the report and one page with a form for departments.
• Employee Pages: One page for the report and one page with a form for employees.

In Oracle APEX Page Designer, create:

• Department Report Page: View all departments.
• Department Form Page: Add, edit, or delete department data.
• Employee Report Page: View all employees.
• Employee Form Page: Add, edit, or delete employee data.

4.2 Create Employee-Manager Hierarchy Tree

To visualize the employee-manager hierarchy, create a new page with a button on the report page that opens a pop-up showing the hierarchy.

Here is the SQL for the view:

 

SQL

xxxxxxxxxx

15

 

1

-- Create a view for the employee-department tree

2

create or replace view apno_empl_depa_tree_v as

3

SELECT 

4

    empl_id                                     AS node_id,      -- Node ID

5

    empl_name || ' (' || depa_location || ')'   AS node_label,   -- Node label with location

6

    empl_emplmgr_fk                             AS parent_id,    -- Parent ID

7

    empl_job                                    AS job,          -- Additional information: job

8

    empl_salary                                 AS salary,       -- Additional information: salary

9

    depa_location                               AS location      -- Additional information: location

10

FROM 

11

    apno_employee

12

JOIN

13

    apno_department

14

ON

15

    depa_id = empl_depa_fk;

Conclusion

By following these steps, you can efficiently create database components and use Oracle APEX for rapid application development. This blog post demonstrates how to set up robust database applications and create interactive applications with minimal coding.

 {fullWidth}