Studying Supporting Tables and Views

Learn how the EBA_DEMO tables and views support Woods HR photo sharing and job referrals.

The tables and views supporting the Woods HR app all use the EBA_DEMO prefix. The DEPT and EMP tables shown below mimic the EMP/DEPT sample dataset. Three related tables support the break room and job referrals pages:

• EMP_BREAKROOM_PHOTOS – stores uploaded pictures employees post.

• JOB_POSITIONS – tracks open job positions

• EMP_JOB_REFERRALS – contains employee-referred job candidates and respective CVs.

While all employees use Break Room and Open Job Referrals, only HR reps manage job positions.

The figure shows the structure of and relationships between the five tables involved.

Description of the illustration woods_hr_photos_and_cvs.png

Since you explore two approaches for storing photos in this section, the views shown below provide two different “interfaces” on the photo storage table. Both join the EMP table to use its ENAME columns as the POSTED_BY employee. Both also use the APEX_UTIL.GET_SINCE function to format the underlying CREATED date in a more useful way for UI display like “3 days ago”. The views differ in the following way:

• EMP_BREAKROOM_PHOTO_V – Has IMAGE BLOB and MIME_TYPE for local photo storage

• EMP_BREAKROOM_PHOTO_B – Has only FILE_NAME for OCI object bucket image.

The figure depicts the structure of these two breakroom photo views and how they related to the two underlying tables EBA_DEMO_EMP and EBA_DEMO_EMP_BREAKROOM_PHOTOS.

Description of the illustration woods_hr_photo_views.png

The EMP_BREAKROOM_PHOTO_V view includes the WHERE clause below to only return rows from the underlying table that have a valid IMAGE BLOB stored.

-- Used for photos stored locally in a BLOB
create view eba_demo_emp_breakroom_photo_v as
  select p.id,
         p.title,
         p.empno,
         p.mime_type,
         e.ename                      as posted_by,
         p.updated,
         p.image,
         apex_util.get_since(created) as posted,
         file_name
from eba_demo_emp_breakroom_photos p
join eba_demo_emp e on ( p.empno = e.empno )
**where      image is not null
       and dbms_lob.getlength(image) > 0**

In contrast, the EMP_BREAKROOM_PHOTO_B view’s WHERE clause returns only rows with no IMAGE BLOB stored. These rows represent images stored in an Oracle Cloud Infrastructure (OCI) object bucket.

-- Used for photos stored in an OCI object bucket
create view eba_demo_emp_breakroom_photo_b as
  select p.id,
         p.title,
         p.empno,
         e.ename                      as posted_by,
         p.updated,
         apex_util.get_since(created) as posted,
         file_name
    from eba_demo_emp_breakroom_photos p
    join eba_demo_emp e on ( p.empno = e.empno )**where    image is null
          or dbms_lob.getlength(image) = 0**