ORACLE Apex validation

Question

I have one table, i have three columns, start_hour, end_hour and date, in ORACLE Apex i have a form page with two items, P00_START_HOUR and P00_END_HOUR, i need to create a validation, to prevent an insert in the same date between start_hour and end_hour from the table. For example, if in the table i already have
| start_hour || end_hour |
| 12:00PM || 08:00PM |
I can't insert a value, who is between these two hours like this
P00_START_HOUR = 08:00AM
P00_END_HOUR = 01:00PM
I have no idea how to do it.

Answer 1

I created a user table for this example

create table users (
    id                             number generated by default on null as identity  
                                   constraint users_id_pk primary key,
    name                           varchar2(50),
    the_user                       varchar2(20),
    email                          varchar2(30),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
);


-- triggers
create or replace trigger users_biu
    before insert or update 
    on users
    for each row
begin
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end users_biu;
/

When the page has been loaded I'm calculating last user created

declare
l_user varchar2(30);
begin

    select the_user into l_user
    from users
    order by id desc
    FETCH FIRST 1 ROWS ONLY;

    return l_user;
end;

Create a validation, I compared with 1 hour but you can use your own rules

declare
    l_created_hour varchar2(50);
    l_next_hour varchar2(50);

begin

    select to_char(CREATED,'DD-MM-YYYY hh:mi:ss'),
           to_char(CREATED + 1/24,'DD-MM-YYYY hh:mi:ss') 
           into l_created_hour, l_next_hour
  from USERS
  where the_user = :P3_LAST_USER;

  if l_next_hour >= l_created_hour then
        return false;
  else
        return true;
  end if;

end;

The result is

Download the app