Ensure Oracle row represents a unique timespan

Question

I have to make a process in Oracle/PLSQL. I have to verify that the interval of time between start_date and end_date from a new row that I create must not intersect other start_dates and end_dates from other rows.

Now I need to check each row for that condition and if it doesn't correspond the repetitive instruction should stop and after that to display a message such as "The interval of time given is not correct".

I don't know how to make repetitive instructions in Oracle/PLSQL and I would appreciate if you would help me.

I need a loop or smth like that to verify each row in my table that the interval of time given by the date_hour_i and date_hour_e does not intersect the other intervals of time given by the rest of the rows. One more specification....the dates from each row correspond to a client and a employee that performs a haircut to the client in the given interval of time....and i want somehow not to let to introduce a new row if for the same client(or other client) and employee, the new interval of time intersects the other intervals of time with the same/other client and employee....i hope i made myself clear...

Answer 1

why check each row? just query for the start and end times. if the result > 0, output the error message, else, insert.

Answer 2

i assume this will be during the BEFORE INSERT OR UPDATE trigger.

you will want to query the existing table for overlaps in the dates - but this will give a mutating trigger error.

You can get around this by using PRAGMA AUTONOMOUS_TRANSACTION to spawn a new thread.

alternately - you could save each date range in a secondary table, and use that to query against on each insert... something like the following (uncompiled)

CREATE OR REPLACE TRIGGER mytrigger
BEFORE INSERT OR UPDATE ON mytable FOR EACH ROW
DECLARE 
    cnt number;
BEGIN 
    SELECT count(*) into cnt
    FROM reserved_date_range                
    WHERE :new.begin_date BETWEEN begin_dt and end_dt

    if ( cnt > 0 ) then
        raise_application_error(-20000,'Overlapping date ranges');
    else
        insert into reserved_date_range( begin_dt, end_dt ) 
        values ( :new.begin_date, :new.end_date );
    end if;
End;
/

Answer 3

Say your table is tab1 and the start date is stdate and end date is endate also let new start date and new end date be in PLSQL variables v_stdate and v_endate.

so your insert can be something like

insert into tab1 (stdate,endate)
select v_stdate,v_endate  from dual
where not exists(
select 'overlap' from tab1 t1
where v_stdate between(t1.stdate and nvl(t1.endate,v_endate) 
or   v_endate between(t1.stdate and nvl(t1.endate,v_endate)

)

The solution to this problem is a bit complicated because of concurrency issues. In your case you are scheduling an event (or a resource).So I suppose you have a table that holds resource (say client). Before you add another schedule (or event) for a client you should lock the particular client record like.

select client_id from Clients where client_id=p_client_id for update;

Then you can verify there are no overlaps and insert the new schedule and commit.At this point the lock will be released.Any solution that does not use a serialization object is bound to be flawed due to concurrency issues.You can do it in your PLSQL or in a After Insert trigger.But it is an absolute must to lock the actual resource record.

Answer 4

Two links for your reading pleasure:-

Time intervals with no overlaps

and

Avoiding overlap values...