Home » SQL & PL/SQL » SQL & PL/SQL » Dates integrity (11.2.0.3)
Dates integrity [message #685218] Mon, 08 November 2021 04:23 Go to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Hi Gurus,
drop table id_dates;
create table id_dates
(
	id				number	,
	job				number	,
	dat_start		date	,
	dat_end			date
)
;

alter table id_dates add constraint pk_id_dates primary key (id, job);
alter table id_dates add constraint ck_id_dates check (dat_end > dat_start);

insert into id_dates(id, job, dat_start, dat_end) values (1, 10, to_date('01/01/2021', 'dd/mm/yyyy'), to_date('15/02/2021', 'dd/mm/yyyy'));
insert into id_dates(id, job, dat_start, dat_end) values (1, 20, to_date('15/01/2021', 'dd/mm/yyyy'), to_date('20/01/2021', 'dd/mm/yyyy'));
We have to enforce this rule : Id have at most one job in a period of time.

We can detect this by running this query :
select *
from
(
	with v as
	(
		select id
		, job
		, dat_start
		, dat_end
		, lead(dat_start,1, sysdate) over (partition by id order by dat_start) dat_start_next
		, lead(dat_end,1, sysdate) over (partition by id order by dat_start) dat_end_next
		from id_dates
		where 1 = 1
	)
	select *
	from v
	where 1 = 1
	and (dat_start, dat_end) overlaps (dat_start_next, dat_end_next)
)
;
I know that overlaps is an undocumented feature like said here.

But actually, we want to enforce it at data level.

Thanks Gurus in advance !

Amine
Re: Dates integrity [message #685221 is a reply to message #685218] Mon, 08 November 2021 07:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Amine wrote on Mon, 08 November 2021 05:23

I know that overlaps is an undocumented feature like said here.
But actually, we want to enforce it at data level.
You can use Workspace Manager (run $ORACLE_HOME/rdbms/admin/owminst.plb as SYSDBA connected to pluggable - no license is needed) and use it. For example:

SELECT  WMSYS.WM_OVERLAPS(
                          WMSYS.WM_PERIOD(to_date('01/01/2021','dd/mm/yyyy'),to_date('15/02/2021','dd/mm/yyyy')),
                          WMSYS.WM_PERIOD(to_date('15/01/2021','dd/mm/yyyy'),to_date('20/01/2021','dd/mm/yyyy'))
                         )
  FROM  DUAL
/

WMSYS.WM_OVERLAPS(WMSYS.WM_PERIOD(TO_DATE('01/01/2021','DD/MM/YYYY'),TO_DATE('15
--------------------------------------------------------------------------------
                                                                               1

SQL>
SELECT  WMSYS.WM_OVERLAPS(
                          WMSYS.WM_PERIOD(to_date('01/01/2021','dd/mm/yyyy'),to_date('15/02/2021','dd/mm/yyyy')),
                          WMSYS.WM_PERIOD(to_date('15/03/2021','dd/mm/yyyy'),to_date('20/03/2021','dd/mm/yyyy'))
                         )
  FROM  DUAL
/

WMSYS.WM_OVERLAPS(WMSYS.WM_PERIOD(TO_DATE('01/01/2021','DD/MM/YYYY'),TO_DATE('15
--------------------------------------------------------------------------------
                                                                               0

SQL>
But no matter what we can't use check constraint since we need cross-row validation and unfortunately even though Oracle has some temporal validity (what you have is temporal validity) support it doesn't support (although it days it wil in future versions) temporal validity indexes/constraints.
Anyway, to check for violations we would need to serialize INSERT/UPDATE/DELETE:

1. Lock table in exclusive mode (very restrictive, so I'd partition table by job and lock just corresponding partition)
2. Do INSERT/UPDATE/DELETE
3. If INSERT/UPDATE validate there is no overlap.

SY.
Re: Dates integrity [message #685222 is a reply to message #685221] Mon, 08 November 2021 07:51 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Thanks SY.

Could you please show us how we can serialize DML in this example ?

Thanks in advance.

Amine
Re: Dates integrity [message #685223 is a reply to message #685222] Mon, 08 November 2021 09:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
drop table id_dates purge
/
create table id_dates
(
 id    number ,
 job    number ,
 dat_start  date ,
 dat_end   date
)
partition by range(job)
interval(1)
(
partition p0 values less than(1)
)
/
alter table id_dates add constraint pk_id_dates primary key (id, job)
/
alter table id_dates add constraint ck_id_dates check (dat_end > dat_start)
/
create or replace
  procedure insert_id_dates(
                            p_id        number,
                            p_job       number,
                            p_dat_start date,
                            p_dat_end   date
                           )
    is
    begin
        savepoint save_point;
        execute immediate 'lock table id_dates partition for(' || p_job || ') in exclusive mode';
        insert
          into id_dates
          select  p_id,
                  p_job,
                  p_dat_start,
                  p_dat_end
            from  dual
            where 0 = (
                       select  count(*)
                         from  id_dates
                         where job = p_job
                           and (
                                   p_dat_start between dat_start and dat_end
                                or
                                   p_dat_end between dat_start and dat_end
                               )
                      );
        if sql%rowcount = 0
          then
            raise_application_error(-20500,'Date overlap.');
        end if;
end;
/
Now session 1:

SQL> exec insert_id_dates(1,10,to_date('01/01/2021','dd/mm/yyyy'),to_date('15/02/2021','dd/mm/yyyy'))

PL/SQL procedure successfully completed.

SQL>
Session 2:

SQL> exec insert_id_dates(2,10,to_date('15/01/2021','dd/mm/yyyy'),to_date('20/01/2021','dd/mm/yyyy'))
Session 2 waits (hangs) since we are serialializing via lock partition. Now session 1 commits:

SQL> commit;

Commit complete.

SQL>
And now session 2:

SQL> exec insert_id_dates(2,10,to_date('15/01/2021','dd/mm/yyyy'),to_date('20/01/2021','dd/mm/yyyy'))
BEGIN insert_id_dates(2,10,to_date('15/01/2021','dd/mm/yyyy'),to_date('20/01/2021','dd/mm/yyyy')); END;

*
ERROR at line 1:
ORA-20500: Date overlap.
ORA-06512: at "SCOTT.INSERT_ID_DATES", line 30
ORA-06512: at line 1


SQL>
Similar procedures are needed for UPDATE and for DELETE and all INSERT/UPDATE/DELETE are performed using these procedures. You could create before INSERT/UPDATE/DELETE trigger that checks if INSERT/UPDATE/DELETE was called from procedure.

SY.
Re: Dates integrity [message #685225 is a reply to message #685223] Tue, 09 November 2021 03:09 Go to previous message
Amine
Messages: 371
Registered: March 2010
Senior Member

Very instructive ! Thanks SY
Previous Topic: Sequence number to be entered by a user
Next Topic: Data history
Goto Forum:
  


Current Time: Thu Mar 28 05:36:48 CDT 2024