DROP TABLE T PURGE
/
DROP TABLE T_EXPANDED PURGE
/
  CREATE TABLE t 
   (	id integer NOT NULL,
	DATE_BEGIN DATE,
	DATE_END DATE,
	PRIMARY KEY (id),
	check (DATE_BEGIN < DATE_END) 
   );
  CREATE TABLE t_expanded 
   (	DATE_TAKEN DATE UNIQUE
   );


  CREATE OR REPLACE TRIGGER CHK_CONSISTENCY
   before insert
       or delete
       or update
          of DATE_BEGIN,
             DATE_END
   on t
   for each row
   begin
       delete  t_expanded
         where date_taken between :old.date_begin and :old.date_end;
       insert
        into t_expanded
          select  :new.date_begin + (level - 1) / 24 / 3600
            from  dual
            connect by :new.date_begin + (level - 1) / 24 / 3600 <= :new.date_end;
end;
/

SQL> INSERT INTO t
  2  (
  3   id,
  4   DATE_BEGIN,
  5   DATE_END
  6  )
  7  select 1, to_date('2001-11-01 10:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2001-11-01 11:30:00','YYYY-MM-DD HH24:MI:SS') from dual
  8  union all
  9  select 2, to_date('2001-11-01 15:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2001-11-01 16:30:00','YYYY-MM-DD HH24:MI:SS') from dual
 10  union all
 11  select 3, to_date('2001-11-01 11:45:00','YYYY-MM-DD HH24:MI:SS'), to_date('2001-11-01 13:15:00','YYYY-MM-DD HH24:MI:SS') from dual;

3 rows created.

SQL> 
SQL> INSERT INTO t
  2  (
  3   id,
  4   DATE_BEGIN,
  5   DATE_END
  6  )
  7  select 4, to_date('2001-11-01 16:00:00','YYYY-MM-DD HH24:MI:SS'), to_date('2001-11-01 17:30:00','YYYY-MM-DD HH24:MI:SS') from dual;
INSERT INTO t
            *
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0014001) violated
ORA-06512: at "SCOTT.CHK_CONSISTENCY", line 4
ORA-04088: error during execution of trigger 'SCOTT.CHK_CONSISTENCY'


SQL> 

SQL> select (date '2100-01-01' - date '1900-01-01' + 1) * 24 n from dual;

                       N
------------------------
                 1753200

SQL>