Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: August 01, 2000 - 1:58 am UTC

Last updated: May 04, 2021 - 9:47 am UTC

Version: 8.0

Viewed 10K+ times! This question is

You Asked

Hai Mr Tam,

U said U have a 'trick' for the following problem. It couldbe nice if u tell me that..thanks.

A form(5.0) with tabular style is displayed like below..

to from discount
-- --- -----
10 40 1.5
50 65 2.5
70 90 1.2
. .
. .
60 99 ----> should not be allowed.
65 80 ----> should not be allowed. Is there a way
. .
. .

But I would like to stop OVERLAPPING range like above shown with
arrow marks. How can I do it.


Thanks once again

rgs
priya

and we said...

Here it is. We use dbms_lock to serialize access to the table (necessary for a multi-user environment to avoid overlaps by 2 people concurrently in your case above -- IF there are other columns in the table, we might be able to serialize on a much lower level of granularity). We also must defer checking the rule until AFTER the insert has taken place. In order to do that, we follow the logic we would for a mutating table (see
</code> http://asktom.oracle.com/~tkyte/Mutate/index.html <code>
for details on that).

The AFTER INSERT trigger does all of the work -- you'll have to tune that query for your environment. It catches all 4 cases we need to worry about with overlaps. There are other ways to code that query, depending on your indexes and such you might explore the others for performance reasons. This should be enough to show you the technique however:


ops$tkyte@DEV8I.WORLD> create table discount_info
2 (xto number(2), frm number(2) );

Table created.

ops$tkyte@DEV8I.WORLD> create or replace package state_pkg
2 as
3 type myArray is table of rowid
4 index by binary_integer;
5
6 newones myArray;
7 empty myArray;
8
9 end;
10 /

Package created.

ops$tkyte@DEV8I.WORLD> create or replace trigger discount_info_bi
2 before insert on discount_info
3 begin
4 state_pkg.newones := state_pkg.empty;
5 end;
6 /

Trigger created.

ops$tkyte@DEV8I.WORLD> create or replace trigger discount_info_aifer
2 after insert on discount_info for each row
3 begin
4 state_pkg.newones(state_pkg.newones.count+1) :=
5 :new.rowid;
6 end;
7 /

Trigger created.

REM the owner of this trigger MUST have EXECUTE on
REM DBMS_LOCK granted to them (not a role) by SYS!!
REM the choice of 123 was totally arbitrary here in the
REM dbms_lock call....

ops$tkyte@DEV8I.WORLD>
create or replace trigger discount_info_ai
2 after insert on discount_info
3 declare
4 status int;
5 l_rec discount_info%rowtype;
6 l_cnt number;
7 begin
8 status := dbms_lock.request
9 ( id => 123,
10 lockmode => dbms_lock.x_mode,
11 release_on_commit => TRUE );
12
13 for i in 1 .. state_pkg.newones.count
14 loop
15 select * into l_rec
16 from discount_info
17 where rowid = state_pkg.newones(i);
18
19 select count(*)
20 into l_cnt
21 from discount_info
22 where rowid <> state_pkg.newones(i)
23 and ( xto between l_rec.xto and l_rec.frm
24 OR frm between l_rec.xto and l_rec.frm
25 OR ( xto < l_rec.xto and l_rec.xto < frm ) );
26
27 if ( l_cnt > 0 ) then
28 raise_application_error( -20001,
29 'overlap -- ' || l_rec.xto || ', ' ||
30 l_rec.frm || ' rejected' );
31 end if;
32 end loop;
33 end;
34 /

Trigger created.

SQL> insert into discount_info values ( 5, 10 );

1 row created.

SQL> insert into discount_info values ( 1, 11 );
insert into discount_info values ( 1, 11 )
*
ERROR at line 1:
ORA-20001: overlap -- 1, 11 rejected
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 26
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'


SQL> insert into discount_info values ( 1, 7 );
insert into discount_info values ( 1, 7 )
*
ERROR at line 1:
ORA-20001: overlap -- 1, 7 rejected
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 26
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'


SQL> insert into discount_info values ( 7, 8 );
insert into discount_info values ( 7, 8 )
*
ERROR at line 1:
ORA-20001: overlap -- 7, 8 rejected
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 26
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'

SQL> insert into discount_info values ( 7, 11 );
insert into discount_info values ( 7, 11 )
*
ERROR at line 1:
ORA-20001: overlap -- 7, 11 rejected
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 26
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'

SQL> insert into discount_info values ( 11, 20 );

1 row created.



Rating

  (81 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How about Avoiding Overlapping Dates

Pascal, April 08, 2002 - 6:35 am UTC

Hi Tom ,

I have been reading this and i would like to write a Tigger to avoid Overlapping DATE values in my Table...

for Example :

date_from date_to
01.04.2002 30.04.2002
04.04.2002 01.05.2002 Not allowed here..because it's overlapping ..


I have tried the above logic but it doesn't work for Date values .


Thanks


Pascal



Tom Kyte
April 08, 2002 - 9:04 am UTC

Sure it does -- numbers, dates, strings -- between works on all of them. 

You'll have to be a little more "exact" in your question.  "doesn't work" is about as vague as you can get.  

All I did (really ALL I DID) was change the datatype on the discount info table from number(2) to a date and:

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into discount_info values ( '01-apr-2002', '30-apr-2002' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into discount_info values ( '04-apr-2002', '01-may-2002' );
insert into discount_info values ( '04-apr-2002', '01-may-2002' )
            *
ERROR at line 1:
ORA-20001: overlap -- 04-APR-02, 01-MAY-02 rejected
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 26
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'

 

Could you please try this ....

Pascal, April 08, 2002 - 10:20 am UTC

Hi Tom

Thanks very much for your quick response..

I tried this and it didn't refuse the values:

Suppose i already have :

date_from 01.04.2002
date_to 30.04.2002

Now i want to insert

date_from 05.04.2002
date_to 20.04.2002

The second set of Dates should not be allowed , but they were allowed when i tested on my Computer..

Could you please test these values to see if it's something to do with my triggers..

Thanks in advance











Tom Kyte
April 08, 2002 - 3:46 pm UTC

I did -- 


ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into discount_info values ( '01-apr-2002', '30-apr-2002' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into discount_info values ( '05-apr-2002', '20-apr-2002' );
insert into discount_info values ( '05-apr-2002', '20-apr-2002' )
            *
ERROR at line 1:
ORA-20001: overlap -- 05-APR-02, 20-APR-02 rejected
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 26
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'

 

All i did -- all i did -- was change the datatype from number(2) to DATE.

Try again -- you missed something -- just run this script AS IS and you'll see:

drop table discount_info;

create table discount_info
(xto date, frm date );

create or replace package state_pkg
as
    type myArray is table of rowid
          index by binary_integer;

   newones  myArray;
   empty    myArray;

end;
/

create or replace trigger discount_info_bi
before insert on discount_info
begin
    state_pkg.newones := state_pkg.empty;
end;
/

create or replace trigger discount_info_aifer
after insert on discount_info for each row
begin
    state_pkg.newones(state_pkg.newones.count+1) :=
         :new.rowid;
end;
/

create or replace trigger discount_info_ai
after insert on discount_info
declare
    status int;
    l_rec  discount_info%rowtype;
    l_cnt  number;
begin
    status := dbms_lock.request
             ( id                => 123,
               lockmode          => dbms_lock.x_mode,
               release_on_commit => TRUE );

    for i in 1 .. state_pkg.newones.count
    loop
        select * into l_rec
          from discount_info
         where rowid = state_pkg.newones(i);

        select count(*)
          into l_cnt
          from discount_info
         where rowid <> state_pkg.newones(i)
           and ( xto between l_rec.xto and l_rec.frm
              OR frm between l_rec.xto and l_rec.frm
              OR ( xto < l_rec.xto and l_rec.xto < frm ) );

        if ( l_cnt > 0 ) then
            raise_application_error( -20001,
              'overlap -- ' || l_rec.xto || ', ' ||
              l_rec.frm || ' rejected' );
        end if;
    end loop;
end;
/

insert into discount_info values ( '01-apr-2002', '30-apr-2002' );
insert into discount_info values ( '05-apr-2002', '20-apr-2002' );

 

Thanks...i was mixing between Arguments

Pascal, April 09, 2002 - 7:26 am UTC

Hi Tom
Thanks for your Response..
I was actually interchanging/mixing the Between Arguments...

I was actually coding :

where rowid <> state_pkg.newones(i)
and ( xto between l_rec.frm and l_rec.xto
OR frm between l_rec.frm and l_rec.xto
OR ( xto < l_rec.xto and l_rec.xto < frm ) );


instead of :

where rowid <> state_pkg.newones(i)
and ( xto between l_rec.xto and l_rec.frm
OR frm between l_rec.xto and l_rec.frm
OR ( xto < l_rec.xto and l_rec.xto < frm ) );



Does it really matter which argument comes first in the Between construct ?

Thanks , now it works perfectly








Tom Kyte
April 09, 2002 - 8:07 am UTC

Amazing -- you change the code -- you don't mention "i changed the code" -- and expected it to work. hmmmm

Yes, between is very sensitive to the order of the operands. It is

between LO_VAL and HI_VAL


if you say between 10 and 5 you'll NEVER get a result, between 5 and 10 -- you might get one.

a@ORA817DEV.US.ORACLE.COM> select * from dual where 6 between 10 and 5;

no rows selected

a@ORA817DEV.US.ORACLE.COM> select * from dual where 6 between 5 and 10;

D
-
X

a@ORA817DEV.US.ORACLE.COM>



If you are going to take my samples, modify them and then say "they don't work" -- make sure to POST your changes. It would have taken about 2 seconds to diagnose that one.

How to serialize trigger at lower level

Chuck Erickson, May 01, 2003 - 8:44 pm UTC

How would you serialize on a lower level? Say you had product, to, from, discount in the table and wanted to assure no overlapping ranges for each product--but not lock the whole table.

I thought I could use dbms_utility.get_hash_value to get a unique lock id for the product value--as shown in Appendix A/DBMS_LOCK in Expert One on One (best Oracle book ever written by the way!)

We'd have to create/check the lock for each row as you process it in the after trigger--but actually we only want to create/check the lock for each "product" which may be a set of rows. Do we modify the trigger to process rows in product order and break on product to check the lock?

Am I missing a simpler approach?

Thanks.

Tom Kyte
May 02, 2003 - 7:54 am UTC

That is how you would do it -- to get a semi-unique (hashing doesn't guarantee uniqueness) lock id to serialize access by product id.


you create the lock once -- each subsequent "get" of the lock after you got it won't create a new lock -- you can get that lock over and over.



Yogesh Chaudhari, August 21, 2003 - 1:45 pm UTC

Hi Tom,

I would like to split time ranges to find availability to schedule meeting for user defined time period.

e.g.
Resource_Id Start_Time End_Time
----------- ---------- --------
1 9.00 17.00
2 14.00 16.00
3 12.00 16.00

if user enters 30 mins. Program should return following result

Start_Time End_Time
---------- --------
14.00 14.30


If we can do this in SQL using analytical functions it would be great, if not a procedure or function would also be fine. I am using oracle 9iR2.

Thanks,

Tom Kyte
August 21, 2003 - 7:10 pm UTC

well, i looked at this and looked at it, and I haven't any idea why the answer is 14.00 to 14.30?

to me, it looks like all 3 resources are already booked?

i don't get your logic

Find earliest available time slot

Yogesh Chaudhari, August 22, 2003 - 10:31 am UTC

Hi Tom,

Thanks for the prompt reply. Sorry I did not explain properly.

Table : Resource_availability

Resource_id Date Start_time End_time
----------------------------------------------
1 9/1/2003 9.00 17.00
2 9/1/2003 14.00 16.00
3 9/1/2003 12.00 16.00


Listed above are the availability of resources for a particular day.( This availability has already factored in the already scheduled meetings of all resources )

Given this, how can we come up with a earliest common available time to schedule a meeting


for ex : If Meeting organizer wants the meeting to be held for 30 minutes on 9/1/2003 to be attended by resource 1 ,2 and 3 . The earliest available time slot of 30 mins would be 14.00 - 14.30.

Tom Kyte
August 22, 2003 - 2:07 pm UTC

well, that is just max(start_time)

select max(start_time) from resource_availability where start_time between to_date( '9/1/2003', 'mm/dd/yyyy' ) and to_date( '9/1/2002 23:59:59', 'mm/dd/yyyy hh24:mi:ss' )

but it is probably more complex then that -- you probably have multiple records for each resource and such. if so, give a real example.

A reader, August 22, 2003 - 3:16 pm UTC

I like the name referenced by original poster "Mr Tam"
by the way who is Mr.Tam??


Find Resource Avaibility

Yogesh Chaudhari, August 22, 2003 - 3:33 pm UTC

Hi Tom,

Thanks for prompt reply, here is my table defination where we are storing availibity of
external resources, who will be attending the meeting. Resources could be persons and rooms .
The example shown below explains that we can have more then 1 record
for a resource for a given day in this table . One thing is sure though , that they wont have
overlapping availability for a given day for a resource , in other words they will always be
mutually exclusive



CREATE TABLE AVAILABILITY
(
RESOURCE_ID NUMBER(13) NOT NULL,
AVAILABILITY_ID NUMBER(13) NOT NULL, ( Primary key )
START_TIME DATE NOT NULL,
END_TIME DATE NOT NULL,
CREATED_BY VARCHAR2(30 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY VARCHAR2(30 BYTE) NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
RESCHEDULED VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL
)



Resource_id Date Start_time End_time
----------------------------------------------
1 9/1/2003 9.00 10.00
1 9/1/2003 12.00 15.00
1 9/1/2003 16.00 17.00

2 9/1/2003 9.30 11.00
2 9/1/2003 14.00 16.00
2 9/1/2003 16.30 17.00

3 9/1/2003 12.00 15.00

For this scenario if user is looking for 30 min slot (meeting time is user defined).
System should return 14:00 – 14:30.

Tom Kyte
August 23, 2003 - 9:50 am UTC

well, rdbms's really hate to make up data -- and that is what we have to do.  Not that we cannot do it, but we'll have to.

I'll take the long and winding explanation here -- it is a deceptively simple looking query:

ops$tkyte@ORA920> select distinct real_date, cnt_dates, cnt_resources
  2    from (
  3  select real_date,
  4         count(*) over (partition by real_date) cnt_dates,
  5             count(distinct resource_id) over () cnt_resources
  6    from (
  7  select resource_id, start_time, end_time, real_date
  8    from availability,
  9         (select to_date(:DAY) + (rownum-1) * :LENGTH * (1/24/60) real_date
 10                from all_objects
 11                   where rownum <= 24*60 / :LENGTH ) B
 12   where start_time between to_date( :DAY ) and to_date( :DAY ) + 1-1/24/60/60
 13     and real_date between start_time and end_time
 14     and real_date + :LENGTH * (1/24/60) <= end_time
 15         )
 16             )
 17   where cnt_dates = cnt_resources
 18   order by real_date
 19  /

REAL_DATE           CNT_DATES CNT_RESOURCES
------------------ ---------- -------------
09/01/2003 02:00pm          3             3
09/01/2003 02:30pm          3             3

ops$tkyte@ORA920>
ops$tkyte@ORA920> 

that generates the set of all possible meeting times for all three resources...

What I did was start by generating a set of all N minute intervals:


ops$tkyte@ORA920> variable DAY varchar2(20)
ops$tkyte@ORA920> variable LENGTH number
ops$tkyte@ORA920> exec :DAY := '9/1/2003'

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec :LENGTH := 30

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> break on resource_id skip 1
ops$tkyte@ORA920> break on start_time skip 1
ops$tkyte@ORA920>
ops$tkyte@ORA920> select to_date(:DAY) + (rownum-1) * :LENGTH * (1/24/60) real_date
  2    from all_objects
  3   where rownum <= 24*60 / :LENGTH
  4  /

REAL_DATE
------------------
09/01/2003 12:00am
09/01/2003 12:30am
09/01/2003 01:00am
09/01/2003 01:30am
......


in the day.  We were looking for 1/2 hour periods.  I took the simplification that everything started on N minute boundaries as well (eg: this will find 30 minute windows that start on the top or the bottom of the hour only -- you would have to adjust for accomodate say 15 minute windows -- so that you could have meetings starting at 12 or 12:15 or 12:45.  whatever you generated in this set will dictate when meetings can in fact start).....


Now, we do a non-equi join of this set with our set of resources:

ops$tkyte@ORA920> select resource_id, start_time, end_time, real_date
  2    from availability,
  3         (select to_date(:DAY) + (rownum-1) * :LENGTH * (1/24/60) real_date
  4                from all_objects
  5                   where rownum <= 24*60 / :LENGTH ) B
  6   where start_time between to_date( :DAY ) and to_date( :DAY ) + 1-1/24/60/60
  7     and real_date between start_time and end_time
  8     and real_date + :LENGTH * (1/24/60) <= end_time
  9   order by resource_id, real_date
 10  /

RESOURCE_ID START_TIME         END_TIME           REAL_DATE
----------- ------------------ ------------------ ------------------
          1 09/01/2003 09:00am 09/01/2003 10:00am 09/01/2003 09:00am
          1                    09/01/2003 10:00am 09/01/2003 09:30am

          1 09/01/2003 12:00pm 09/01/2003 03:00pm 09/01/2003 12:00pm
          1                    09/01/2003 03:00pm 09/01/2003 12:30pm
          1                    09/01/2003 03:00pm 09/01/2003 01:00pm
          1                    09/01/2003 03:00pm 09/01/2003 01:30pm
          1                    09/01/2003 03:00pm 09/01/2003 02:00pm
          1                    09/01/2003 03:00pm 09/01/2003 02:30pm

          1 09/01/2003 04:00pm 09/01/2003 05:00pm 09/01/2003 04:00pm
          1                    09/01/2003 05:00pm 09/01/2003 04:30pm

          2 09/01/2003 09:30am 09/01/2003 11:00am 09/01/2003 09:30am
          2                    09/01/2003 11:00am 09/01/2003 10:00am
          2                    09/01/2003 11:00am 09/01/2003 10:30am

          2 09/01/2003 02:00pm 09/01/2003 04:00pm 09/01/2003 02:00pm
          2                    09/01/2003 04:00pm 09/01/2003 02:30pm
          2                    09/01/2003 04:00pm 09/01/2003 03:00pm
          2                    09/01/2003 04:00pm 09/01/2003 03:30pm

          2 09/01/2003 04:30pm 09/01/2003 05:00pm 09/01/2003 04:30pm

          3 09/01/2003 12:00pm 09/01/2003 03:00pm 09/01/2003 12:00pm
          3                    09/01/2003 03:00pm 09/01/2003 12:30pm
          3                    09/01/2003 03:00pm 09/01/2003 01:00pm
          3                    09/01/2003 03:00pm 09/01/2003 01:30pm
          3                    09/01/2003 03:00pm 09/01/2003 02:00pm
          3                    09/01/2003 03:00pm 09/01/2003 02:30pm


24 rows selected.


That gives us every 30 minute window they each have available.


Using the analytic function:

count(*) over (partition by real_date) cnt_dates,


we are able to count how many people have the same start time available for the required duration and then using:

 count(distinct resource_id) over () cnt_resources

we are able to see how many resources we are in fact coordinating...  

Once we have that, the predicate:

where cnt_dates = cnt_resources

just keeps the times that we have as many people free as people.  Order by that and take the first row (or any of the rows, they are all equally OK at that point).....


btw: this was my setup for this example:

drop TABLE AVAILABILITY;
CREATE TABLE AVAILABILITY
(
  RESOURCE_ID       NUMBER(13)                  NOT NULL,
  START_TIME        DATE                        NOT NULL,
  END_TIME          DATE                        NOT NULL
)
/

alter session set nls_date_format = 'mm/dd/yyyy hh:miam';

insert into availability values ( 1, '9/1/2003  9:00am', '9/1/2003 10:00am' );
insert into availability values ( 1, '9/1/2003 12:00pm', '9/1/2003  3:00pm' );
insert into availability values ( 1, '9/1/2003  4:00pm', '9/1/2003  5:00pm' );

insert into availability values ( 2, '9/1/2003  9:30am', '9/1/2003 11:00am' );
insert into availability values ( 2, '9/1/2003  2:00pm', '9/1/2003  4:00pm' );
insert into availability values ( 2, '9/1/2003  4:30pm', '9/1/2003  5:00pm' );

insert into availability values ( 3, '9/1/2003 12:00pm', '9/1/2003  3:00pm' );


 

Vipin, August 22, 2003 - 4:06 pm UTC

Hi Tom,

In the query which you mentioned :-


select count(*)
into l_cnt
from discount_info
where rowid <> state_pkg.newones(i)
and ( xto between l_rec.xto and l_rec.frm
OR frm between l_rec.xto and l_rec.frm
OR ( xto < l_rec.xto and l_rec.xto < frm ) );


Can't the overlapping be checked with following predicate:-
select count(*)
into l_cnt
from discount_info
where rowid <> state_pkg.newones(i)
and (xto between l_rec.xto and l_rec.frm
OR
l_rec.xto between xto and frm )

Please correct me if I am wrong.

Tom Kyte
August 23, 2003 - 9:54 am UTC

there are a possibly infinite number of ways to express the predicate. if you want, feel free to prove yours out. could be. I just used the first one that popped into my head that I knew would work.

Please, need help again...

A reader, October 15, 2003 - 9:04 am UTC

Tom,

How can I achive following:
I have a following table:

DROP TABLE test_trigger
/

CREATE TABLE test_trigger
(userid NUMBER(10,0),
datecompleted VARCHAR2(10),
timein VARCHAR2(250),
timeout VARCHAR2(250),
projectid NUMBER(10,0) NOT NULL,
outletid NUMBER(10,0) NOT NULL,
exception NUMBER)
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
TABLESPACE msp_rpt
STORAGE (
INITIAL 40960
NEXT 5242880
PCTINCREASE 50
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

Following are the values to populate the table:

INSERT INTO &&table_name
VALUES
(6,'09/15/2003','10:03:20','13:25:20',39,48936,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/15/2003','14:00:12','15:00:52',16,48936,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/15/2003','14:52:51','14:52:51',39,24519,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/15/2003','15:20:42','16:39:42',39,26100,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/15/2003','17:02:01','17:42:41',16,24574,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/15/2003','17:43:11','17:43:11',39,24574,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/16/2003','08:49:32','10:13:32',39,26099,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/16/2003','10:33:39','12:33:02',16,24202,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/16/2003','14:29:21','17:02:21',39,12085,0)
/
INSERT INTO &&table_name
VALUES
(6,'09/16/2003','17:07:03','17:51:21',23,23556,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/15/2003','10:00:05','12:21:48',17,31205,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/15/2003','14:00:01','16:20:22',17,38544,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/15/2003','16:42:02','17:54:23',17,24446,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/16/2003','08:30:17','09:30:17',60,1,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/16/2003','10:31:45','12:43:45',39,26150,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/16/2003','13:22:04','14:12:20',23,26149,0)
/
INSERT INTO &&table_name
VALUES
(9,'09/16/2003','14:23:07','16:35:04',16,24612,0)
/


I need to populate exception value with 1 in case there are overlapping TimeIn and TimeOut on the immediate subsequent calls (group by userid,datecompleted).
For example, the timeIn in the third record is less then timeOut in the second record, which should not happend, because user cannot start next call earlier then he finished his prior call.

Please help

Tom Kyte
October 15, 2003 - 6:22 pm UTC

you cannot apply the logic above to your problem? it is a straightforward application of the above technique? all you have to do is figure out "what do I need to look for in order to flag an error"

Resource Time allocation

Yogesh Chaudhari, October 16, 2003 - 11:52 am UTC

Hi Tom,

Thanks for prompt reply to my earlier question, I am using your query in my application to derive the following result set of the resources and the time slots they can be scheduled at.

Resource available Time slot#
-------------------------------
c1 1
c1 2
c1 3
c1 4
c2 1
c2 2
c2 4
c3 1
c3 3
c3 4
c4 2
c4 3
c4 4




We need to allocate consecutive time slot# to all 4 resources . The # of resources and timelots could vary .

so result will look like

c1 2
c2 1
c3 3
c4 4

to achieve this I am using following query in loop to select least available & allocate time slot to it and update that record with sch = 'Y' and then requery the next set of remaing slots with the remaining resources

Could you please let us know if this could be achieved without having to go for looping and do it in single query set.

select distinct caseid, count(time_slot) over (partition by caseid), first_value(time_slot) over(partition by caseid)
from temp
where caseid not in (select caseid from temp where sch is not null) and time_slot not in (select time_slot from temp where sch is not null)
order by 2

Table definition is:

CREATE TABLE TEMP
(
CASEID VARCHAR2(13),
TIME_SLOT NUMBER,
SCH VARCHAR2(1)
)


thanks,
Yogesh





Tom Kyte
October 16, 2003 - 5:12 pm UTC

well, you could cartesian product the set with itself to find all possible ones -- using rownum = 1 to get just one if that is what you want.

ops$tkyte@ORA920> with V as (select * from t)
  2  select *
  3    from v t1, v t2, v t3, v t4
  4   where t1.ts = 1
  5     and t2.ts = 2
  6     and t3.ts = 3
  7     and t4.ts = 4
  8     and t1.res <> t2.res and t1.res <> t3.res and t1.res <> t4.res
  9     and t2.res <> t3.res and t2.res <> t4.res
 10     and t3.res <> t4.res
 11  /
 
RE         TS RE         TS RE         TS RE         TS
-- ---------- -- ---------- -- ---------- -- ----------
c2          1 c4          2 c3          3 c1          4
c3          1 c2          2 c4          3 c1          4
c3          1 c4          2 c1          3 c2          4
c1          1 c4          2 c3          3 c2          4
c3          1 c1          2 c4          3 c2          4
c2          1 c4          2 c1          3 c3          4
c2          1 c1          2 c4          3 c3          4
c1          1 c2          2 c4          3 c3          4
c3          1 c2          2 c1          3 c4          4
c2          1 c1          2 c3          3 c4          4
c1          1 c2          2 c3          3 c4          4
 
11 rows selected.
 

How about on a view?

Doug C, November 24, 2003 - 3:00 pm UTC

Tom - your implementation requires a row level and a table leve trigger. I was wondering, if I use Workspace Manager which when versioning, turns my table into a view, can I still validate overlapping dates in other columns that createtime and retiretime even though I am limited to INSTEAD OF triggers exclusively?

Tom Kyte
November 24, 2003 - 3:26 pm UTC

give it a try!

OK - Tried it

Doug, November 24, 2003 - 6:39 pm UTC

Ok - no reason Tom should do everything :-) but I am confused - here's the setup - 

create table salary (
   name varchar2(10),
   salary number(10),
   title varchar2(20),
    dateofbirth date,
    starttime date,
   stoptime date);


SQL> alter table salary add constraint aprimarykey primary key
   (name, dateofbirth);


SQL> begin
            DBMS_WM.EnableVersioning ('salary',  'VIEW_WO_OVERWRITE');
end;  2    3
  4  /


insert into salary values ('Bob',6000,'Assist Provost',to_date('09-APR-45','DD-MON-RR'),to_date('01-JAN-95','DD-MON-RR'),to_date('01-JUN-95','DD-MON-RR'))

update salary set salary=7000,
              title = 'Assist Provost',
              starttime=to_date('01-JUN-95','DD-MON-RR'),
              stoptime=to_date('01-OCT-95','DD-MON-RR')
              where name='Bob'
              and dateofbirth=to_date('09-APR-45','DD-MON-RR')
/
Commit complete.

SQL> select * from salary;

NAME           SALARY TITLE                DATEOFBIR STARTTIME STOPTIME
---------- ---------- -------------------- --------- --------- ---------
Bob              7000 Assist Provost       09-APR-45 01-JUN-95 01-OCT-95

SQL>


Now get the trigger - 

SQL> get tom.sql
  1  create or replace trigger salary_ai
  2      INSTEAD OF update on salary
  3      declare
  4          status int;
  5          l_rec  salary%rowtype;
  6          l_cnt  number;
  7      begin
  8             select count(1) into l_cnt
  9               from salary
 10           where
 11           :new.stoptime between :old.stoptime and :old.starttime
 12           OR :new.starttime between :old.stoptime and :old.starttime
 13           OR :new.stoptime < :old.stoptime and :old.stoptime < :new.starttim
e ;
 14             if ( l_cnt > 0 ) then
 15                 raise_application_error( -20001,
 16                   'overlap -- ' || l_rec.stoptime  || ', ' ||
 17                   l_rec.starttime || ' rejected' );
 18             end if;
 19*    end;
SQL>

Trigger created.

SQL>

SQL>  update salary set starttime=to_date('01-JUL-95','DD-MON-RR');

1 row updated.

SQL> select * from salary;

NAME           SALARY TITLE                DATEOFBIR STARTTIME STOPTIME
---------- ---------- -------------------- --------- --------- ---------
Bob              7000 Assist Provost       09-APR-45 01-JUN-95 01-OCT-95

SQL>

It doesn't appear to be working. just tossing the update.
This may be a goofy question.. not sure.:-)  I am finding workspace manager a lot of fun.  
 

Tom Kyte
November 24, 2003 - 6:56 pm UTC

hmmm -- ok - hope you don't mind, but I'm going to indulge my favorite rant!

it takes at most and at least 4 characters to represent a year!!! always!! (i know, its a demo -- but your asst provost is yet to be born! he is from the year 2045!!!)

but look at your predicate. it only references :new and :old??

and it has between STOP and START, where STOP > START (where between 2 and 1 is patently "false")

You just rushed the example :)

A similar requirement...

Matt, February 05, 2004 - 5:43 am UTC

I need to load lots of data very quickly from an external table the data looks like:

id,value,from_date,to_date

I need to load this data subject to the following requirements:

1) There can be no overlaps for a particular id (so one records to_date is equal to the next records from_date.
2) There must be a full set of data from a given historic date date (roughly three years in the past).
3) All the data for each id must span the period from the historic date as close to the current date as possible (given the data available).

For example:

If historic date is 01/01/2003 (DD/MM/YYYY), this is the data that I want to load (I need to discard data that does not marry the previous records to_date to the next records from date:

id value from_date to_date
1 10 01/01/2003 01/02/2003
1 12 01/02/2003 21/05/2003
1 19 21/05/2003 01/06/2003
1 25 01/06/2003 01/12/2003
1 25 01/12/2003 01/02/2004

How would one generate a select to pull out data that marries like this?

Also, I need to constrain the schema so that only the data that satifies these rules will be accepted (similar to the original post). Does the method described in this post still apply to 9.2 or is there a better way?

Best Regards,

Tom Kyte
February 05, 2004 - 7:47 am UTC

tell me, what if the inputs do have overlaps. what logic decides what rows are actually kept (and why)

what does "there must be a full set of data" mean actually?

Additional info....

Matt, February 05, 2004 - 6:12 pm UTC

(I'll answer your Q's in a sec).

I'm working at this problem from two ends.

1) a query to pull out the valid data from the external table subject to my requirements in order to create a table

2) a loader to go through the data that was not retreived by the query. The loader will bulk load these values and constraints will identify what the problem was with the data (so it can be investigated and fixed)

So, I think I need

a) A suitable query to gather the data according to the requirements (more detail will follow).

b) A mechanism to constrain the target table so that 'bad' data cannot get in (and a constraint or trigger will raise an error that may be caught and logged)

The logic for keeping records is this:

a) The record with the earliest from_date for a particular id must have a from_date of 01/01/2003 (DD/MM/YYYY) (the historic date in the example above).
b) The set of records for a particular id must completely span the period (with no gaps - ie: it fishtails so that current from_date=previous to_date) that starts on 01/01/2003 (the historic date) and continues up to the present. However;
provided that the data fishtails throughout the period it is allowable for the most recent record to finish on a date before the current date.
d) there can be no duplicate data (even if the rows are the same eg: two records with the same from and to dates.)
e) no to_date can be strictly greater than the current date.

So, you can see (I hope) that the logic requires me to valiodate "sets" of rows (defined by the id) such that the following are "sets" are accepted:

HistDate Now
| |
ID: 1 |-----|
ID: 1 |-----------|
ID: 1 |------|
ID: 1 |----------|
ID: 2 |---------|
ID: 2 |---|
ID: 2 |-----|
ID: 2 |---------|
ID: 3 |---------------------------------|
ID: 4 |-----------------------------------|

and the following sets would be rejected:

HistDate Now
| |
ID: 5 |-----| <- before HistDate
ID: 6 |-----------| <- a gap
ID: 6 |------|
ID: 6 |------------|
ID: 7 |---------|
ID: 7 |-----| <- overlap
ID: 7 |-----|
ID: 7 |---------|
ID: 8 |-------------------------------------| <- greater than current date
ID: 9 |---------|
ID: 9 |--------------------|
ID: 9 |--------------------| <- duplicate
ID: 9 |----|

A "full set of data" means a consistent set of data for the id.

When loading the data I could order the loads by id and from_date to simplify the validation. In that the data would be loaded in a (from_date) time ordered fashion, which would satify the logic described above.


Best Regards,

I've posted this as a separate question...

Matt, February 06, 2004 - 6:02 pm UTC

...lots of info in here and I know the rules :o)

Tom Kyte
February 07, 2004 - 2:19 pm UTC

this looks like something you might have to do procedurally -- as I could easily envision multiple covering sets for a given "id" are possible -- even probable and SQL isn't going to be the easiest thing to deal with that..

Sorry but this looks like "a program", not a simple query. especiall since a single row might be used in more than one "set" and SQL 'hates' that, since it would only return the row 'once'

or, is it that all of the rows for a given ID are to be used to create the covering set?? that we could probably do.

Matt, February 08, 2004 - 12:40 am UTC

I guess it is possible that there may be multiple covering sets for an id (but the original post provides a means of avoiding overlaps for an id so we could catch this). So I think that at least part of this can be done.

"or, is it that all of the rows for a given ID are to be used to create the covering set?? that we could probably do."

I recieve the "clean" data and I am expecting that this will be possible (your statement above). I need to capture the data that satisfies the conditions described and if there is more data than could be loaded flag this as problem data. In reality I will initially load the 'good' data for an id and identify the problem in the data that could not be loaded for the same id (and then the whole data for the id will be fixed ready for the next load run)



Locking strategy in "discount_info_ai" above

Matt, March 03, 2004 - 6:33 pm UTC

In the trigger discount_info_ai you use

status := dbms_lock.request
( id => 123,
lockmode => dbms_lock.x_mode,
release_on_commit => TRUE );

to serialise access to the table. So each time the table is accessed and the trigger fires the lock with user defined id 123 is requested.

Isn't there some additional logic required examine the status value and act accordingly? ie:

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock2.htm#1001077 <code>

says the possible return values are:

0 - Success
1 - Timeout
2 - Deadlock
3 - Parameter error
4 - Already own lock specified by id or lockhandle
5 - Illegal lock handle

Shouldn't the trigger only proceed on a status of 0 and raise and exception otherwise?

Cheers



Tom Kyte
March 04, 2004 - 8:09 am UTC

good point, we would not catch #2 (only possible problem).

we wait forever (never timeout).
parameters are AOK.
if we already own the lock thats ok.
the lock handle is ok (cause we are not using it)

So, there should be a check for a status of 2 afterwards, just in case. thanks

Muhammad Ibrahim, April 07, 2004 - 5:41 am UTC

Dear Tom,

CREATE TABLE TTTTT (
START_DATE DATE,
END_DATE DATE);
/

INSERT INTO TTTTT ( START_DATE, END_DATE ) VALUES (
TO_Date( '01/01/2003'), TO_Date( '01/01/2003'));
INSERT INTO TTTTT ( START_DATE, END_DATE ) VALUES (
TO_Date( '01/01/2003'), TO_Date( '31/12/2003'));
INSERT INTO TTTTT ( START_DATE, END_DATE ) VALUES (
TO_Date( '01/03/2003'), TO_Date( '31/07/2003'));
INSERT INTO TTTTT ( START_DATE, END_DATE ) VALUES (
TO_Date( '31/05/2003'), TO_Date( '31/05/2003'));
INSERT INTO TTTTT ( START_DATE, END_DATE ) VALUES (
TO_Date( '01/06/2003'), TO_Date( '31/08/2003'));
INSERT INTO TTTTT ( START_DATE, END_DATE ) VALUES (
TO_Date( '01/06/2003'), TO_Date( '31/05/2004'));
COMMIT;

The question is i want to split the date intervals from the above table. First take the first small start date then compare with all other start and end date eg:

the overlapping dates like

|
01/01/2003 - Start date
01/01/2003 - End date

|-------------------------------------|
01/01/2003 - St 31/12/2003 - End

|---------------|
01/03/2003 31/07/2003

|
31/05/2003 - Start date
31/05/2003 - End date

|-------------|
01/06/2003 31/08/2003


|---------------------------------------------|
01/06/2003 31/05/2004

How can i acheive the below result:
------------------------------------
Start Date End Date

01/01/2003 01/01/2003 -- There is one record which starts and end date on the same day.
02/01/2003 28/02/2003 -- There is one start date on 01/03/2003 so this should end one day before that
01/03/2003 30/05/2003 -- here before its own end date there is another start date so that start date -1
-- is the end date of this current record
31/05/2003 31/05/2003 -- this starts and ends in the same day
01/06/2003 31/07/2003 -- this end date is end date of previous record
01/08/2003 31/08/2003 --
01/09/2003 31/12/2003 --
01/01/2004 31/05/2004


ie:

i need to split the above overlapping dates like below figure:

|
01/01/2003 - Start date
01/01/2003 - End date

|------------|
02/01/2003 28/02/2003

|-------------|
01/03/2003 30/05/2003

|
31/05/2003( Starts )
31/05/2003( Ends )

|-----------|
01/06/2003 31/07/2003

|-------------|
01/08/2003 31/08/2003

|----------------|
01/09/2003 31/12/2003

|-------------------|
01/01/2004 31/05/2004


I tried with Analytic function "Lead" but i couldnt acehive! Is it possible with a single SQL statement.


Regards,
Ibrahim.



Marius Theron, June 07, 2004 - 4:29 am UTC

I would like to know if you can have a multi user entry written on a trigger in SQL server. Meaning if you have more then one user insert data into table and a other user also entry data on the same time.

Tom Kyte
June 07, 2004 - 8:31 am UTC

er?

Avoiding overlap values...with serializable isolation level

Brian Camire, June 25, 2004 - 10:39 am UTC

The technique you described in your original followup may allow overlaps when the isolation level is serializable.

For example, using your original CREATE statements, consider the following sequence of statements under two sessions (A and B):

REM SESSION A
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/
REM SESSION B
INSERT INTO DISCOUNT_INFO VALUES(5, 10)
/
REM SESSION B
COMMIT
/
REM SESSION A
INSERT INTO DISCOUNT_INFO VALUES(1, 11)
/
REM SESSION A
COMMIT
/

These statements execute without error, and result in two overlapping rows in table.

I understand why this is happening. By the time session A inserts, session B has already committed and therefore released the lock, but because session A's isolation level is serializable, it doesn't "see" the row inserted by session B.

By using an autonomous transaction to do the test for overlaps in discount_info_ai, we could "see" the row inserted by session B. However, an autonomous transaction would not see any uncommitted changes from session A, including changes that cause rows that overlap another row (for example, if session A had inserted a second row with values 0 and 3). Of course, we could do the test twice, once using an autonomous transaction, and once not. However, we would only need to do this if the isolation level is serializable. Is there any way for the trigger to "know" what the isolation level is?

Still, though, the autonomous transaction might give us some "false positives". For example, suppose there is no session B, but the table already contains one row with the values 5 and 10. If session A deletes this row, and then inserts a row with the values 1 and 11, the autonomous transaction will not "see" the uncommitted delete, and so will falsely detect that there's an overlap.

I suppose we could use delete triggers in the same fashion as the insert triggers to track the deleted rows, and then exclude these from the test in the autonomous transaction. This starts to get messy, though, particularly considering we have the same issue with updates (for example, if session A updates the values in the existing row from 5 and 10 to 15 and 20 prior to the insert).

What are your thoughts?

Tom Kyte
June 25, 2004 - 4:09 pm UTC

oh wow.  didn't even think about that....

that is a problem isn't it.

serialization all dml on the table and using an a-trans to query it would work (no false positives that way).  scalable, no.  doable, yes.

another way is to trick out serializable -- "flash forward" if you will.


create or replace trigger discount_info_ai
after insert on discount_info
declare
    status int;
    l_rec  discount_info%rowtype;
    l_cnt  number;
    n      number;
begin
    status := dbms_lock.request
             ( id                => 123,
               lockmode          => dbms_lock.x_mode,
               release_on_commit => TRUE );
                                                                                
    for i in 1 .. state_pkg.newones.count
    loop
        select * into l_rec
          from discount_info
         where rowid = state_pkg.newones(i);
<b>                                                                                
        select dbms_flashback.get_system_change_number into n from dual;
  </b>                                                                              
        for x in (
        select *
          from discount_info <b>as of scn n</b>
         where rowid <> state_pkg.newones(i)
           and ( xto between l_rec.xto and l_rec.frm
              OR frm between l_rec.xto and l_rec.frm
              OR ( xto < l_rec.xto and l_rec.xto < frm ) )
        )
        loop
            raise_application_error( -20001,
              'overlap -- ' || l_rec.xto || ', ' ||
              l_rec.frm || ' rejected' );
        end loop;
    end loop;
end;
/

that causes the transaction to fail with:


ops$tkyte@ORA9IR2> INSERT INTO DISCOUNT_INFO VALUES(1, 11);
INSERT INTO DISCOUNT_INFO VALUES(1, 11)
            *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
ORA-06512: at "OPS$TKYTE.DISCOUNT_INFO_AI", line 20
ORA-04088: error during execution of trigger 'OPS$TKYTE.DISCOUNT_INFO_AI'


which is an interesting error I was not totally expecting, but in hindsight makes sense.  You can get false positives with this because of the nature of 8177's 

really amazing (5+ stars for both of you - brian and tom)

A reader, June 26, 2004 - 2:25 pm UTC

tom, before 9i the only way to go is the one brian described, isn't it?

Tom Kyte
June 26, 2004 - 7:04 pm UTC

yes.... as far as I can think of.

another point

Matthias Rogel, June 28, 2004 - 5:37 am UTC

when I read the original question and your (Tom's) first answer, I wondered why you only consider inserts.

SQL> insert into discount_info values ( 5, 10 );

1 Zeile wurde erstellt.

SQL> insert into discount_info values ( 1, 11 );
insert into discount_info values ( 1, 11 )
            *
FEHLER in Zeile 1:
ORA-20001: overlap -- 1, 11 rejected
ORA-06512: in "DP.DISCOUNT_INFO_AI", Zeile 23
ORA-04088: Fehler bei der Ausf³hrung von Trigger 'DP.DISCOUNT_INFO_AI'


SQL> REM BUT

SQL> insert into discount_info values ( 11, 21 );

1 Zeile wurde erstellt.

SQL> update discount_info set xto=xto-10, frm=frm-10 where xto=11;

1 Zeile wurde aktualisiert.

SQL> select * from discount_info;

       XTO        FRM
---------- ----------
         5         10
         1         11


Is it that you can do only inserts with
a form(5.0) with tabular style ?


Another and probably easier solution to avoid overlap gaps 
- only in the case where the possible range of values is not
too large, for example when you cover days (dates which are equal to their trunced value) or integer-numbers from which you know upperbound and lowerbound (and these bounds are not
"too" far away from each other) -
and which has the advantage that problems like the one Brian 
pointed out are automatically handled by the Kernel, might be to populate a shadow table with all the values between 
xto and frm and put a unique index on this shadow table.

SQL> drop table discount_info;

Tabelle wurde gel÷scht.

SQL> drop package state_pkg;

Paket wurde gel÷scht.

SQL> create table discount_info
  2  (xto integer, frm integer);

Tabelle wurde angelegt.

SQL> create table discount_info_no_overlap(i integer);

Tabelle wurde angelegt.

SQL> create unique index discount_info_no_overlap_uidx on discount_info_no_overlap(i);

Index wurde angelegt.

SQL> create trigger discount_info_shadow
  2  before insert or update or delete on discount_info
  3  for each row
  4  begin
  5  if inserting then
  6     declare vi integer;
  7     begin
  8        for vi in :new.xto .. :new.frm loop
  9            insert into discount_info_no_overlap values(vi);
 10        end loop;
 11     end;
 12  elsif deleting then
 13     delete discount_info_no_overlap where i between :old.xto and :old.frm;
 14  elsif updating then
 15     delete discount_info_no_overlap where i between :old.xto and :old.frm;
 16     declare vi integer;
 17     begin
 18        for vi in :new.xto .. :new.frm loop
 19            insert into discount_info_no_overlap values(vi);
 20        end loop;
 21     end;
 22  end if;
 23  end;
 24  /

Trigger wurde erstellt.


SQL> insert into discount_info values ( 5, 10 );

1 Zeile wurde erstellt.

SQL> insert into discount_info values ( 1, 11 );
insert into discount_info values ( 1, 11 )
            *
FEHLER in Zeile 1:
ORA-00001: Versto&#9600; gegen Eindeutigkeit, Regel
(DP.DISCOUNT_INFO_NO_OVERLAP_UIDX)
ORA-06512: in "DP.DISCOUNT_INFO_SHADOW", Zeile 6
ORA-04088: Fehler bei der Ausf³hrung von Trigger 'DP.DISCOUNT_INFO_SHADOW'

SQL> insert into discount_info values ( 11, 20 );

1 Zeile wurde erstellt.


apparantly the problem "avoid doubles" is much easier
than the problem "avoid overlaps" - even guru Tom
forgot Brian's point -.

of course, in reality the problem "avoid doubles" occurs
much more often than the problem "avoid overlaps", but
since software grows and tries to meet more and more requirements
I'm quite sure we sometimes will have a real simple mechanism
in Oracle (as simple as creation of a unique index)
to avoid overlaps
 

and even worse ...

A reader, June 30, 2004 - 4:11 pm UTC

the "flash forward" trick fails to detect:

insert into discount_info values ( 5, 10) ;
insert into discount_info values ( 1, 11) ;

Tom Kyte
June 30, 2004 - 7:57 pm UTC

ahh, of course not (and neither would atrans!).

as of that scn, those rows did not exist -- you cannot see your own changes. You would need to check both the flashback(atrans) and current version (eg: dup code, no as of second time)

Trigger on nested table

Branka, August 10, 2004 - 3:41 pm UTC

I want to check if number of records in the nested table is <= some value (I have function that calculate that value)

Can I create trigger on the nested table?
Is trigger best solution for enforcing number of records that can be inserted in the nested table?

  1     create or replace type
  2          B as object
  3         ( x int,
  4           y number
  5*        )
SQL> /

Type created.

SQL>  create or replace type
  2        BType as table of B
  3  /

Type created.

SQL>      create table aa
  2         ( pk       int primary key,
  3           bb BType
  4         )
  5        nested table bb store as bb_tab
  6  /

Table created.

SQL>      insert into aa values
  2         ( 1, BType( B(1,1 ),
  3                           B(2,2) ,
  4                           B(3,3)
  5                         )
  6      )
  7  /

1 row created.


  1       insert into aa values
  2         ( 2, BType( B(2,1 ),
  3                           B(2,2) ,
  4                           B(2,3),
  5                     b(2,4)
  6                         )
  7*     )
SQL> /

1 row created.


For each value pk in aa table, I know how many records in nested table I can have.
I have to stop user who is trying to insert record in nested table, after max for that pk is reached.
 

Tom Kyte
August 10, 2004 - 4:09 pm UTC


You create the trigger on the parent.

ops$tkyte@ORA9IR2> create table t2
  2  ( x int,
  3    y myType
  4  )
  5  nested table y store as y_tab
  6  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace trigger t2
  2  after insert or update on t2 for each row
  3  begin
  4          dbms_output.put_line( 'we seem to have ' || :new.y.count );
  5  end;
  6  /
 
Trigger created.
 
ops$tkyte@ORA9IR2> insert into t2 values ( 1, mytype(1,2,3) );
we seem to have 3
 
1 row created.
 
ops$tkyte@ORA9IR2> update t2 set x = 2;
we seem to have 3
 
1 row updated.



(i'd personally avoid nested tables.... as a storage mechanism anyway) 

trigger on nested table

Branka, August 10, 2004 - 4:37 pm UTC

Trigger work for insert and update table, but if I insert into nested table for existing record in the table, it does not work.

insert into table (select bb from aa where pk=2)
values (2,5)

Nested table is not my choice, but now I have to solve some problems. It is in production already.
Do I have to think about mutating records too?

Tom Kyte
August 10, 2004 - 7:24 pm UTC

mutating table -- multi-user issues (boat loads of those - you do know that you cannot read the rows someone else is inserting until after they commit, but nor can you block and wait to read them either right....)

you'd have to use an instead of trigger on a view (which means you could ditch the nested table, use real tables and slip an OR-view in place, best of both worlds)

since this is production, and you are trying to implement "new rules", this will be version 2 anyway so this should be something to "seriously consider"

view on nested table

Branka, August 11, 2004 - 12:00 am UTC

You told somebody that creating view on nested table is problem and that oracle have bug (bug 3666744 ), I tried to find that bug on metalink, but did not find.
Is it resolved.
I have to stay with existing configuration, for now, and to solve problem.
If I can not create view, is it any other option?


Tom Kyte
August 11, 2004 - 9:42 am UTC

it only affects select for updates against the view (did not permit a select for update).

do you actually select for update, if not, not a problem here.

you can use that number to contact support and ask for an update if you need. they can see it. it was filed against 10.1.0.2 (eg: against the current stuff)

A reader, September 22, 2004 - 3:12 am UTC

HI TOM

PL TELL IF I DONT GIVE THIS CLAUSE

status := dbms_lock.request
( id => 123,
lockmode => dbms_lock.x_mode,
release_on_commit => TRUE );
WHAT COULD HAPPEN ..

REGDS

Tom Kyte
September 22, 2004 - 7:51 am UTC

did you read the original answer?

do you understand the intent of the logic:

<quote>
Here it is. We use dbms_lock to serialize access to the table (necessary for a
multi-user environment to avoid overlaps by 2 people concurrently in your case
above -- IF there are other columns in the table, we might be able to
serialize on a much lower level of granularity).
We also must defer checking
the rule until AFTER the insert has taken place. In order to do that, we follow
the logic we would for a mutating table (see
</quote>

OVERLAPPING

Orit, January 26, 2005 - 11:25 am UTC

I am working on handling overlapping caused by insert,update,delete number of records at the same time and then - save those changes. Client don't like to make changes record by record.

YOUR EXAMPLE:
to from discount
-- --- -----
10 40 1.5
50 65 2.5
70 90 1.2
. .
. .
60 99 ----> not allowed
65 80 ----> not allowed

WHAT I NEED:

to from discount
-- --- -----
10 40 1.5
50 65 2.5
70 90 1.2
. .
. .
CHANGES AT THE SAME TIME USING CLIENT FORM:
80 89 -- new record
90 99 -- new record
70 79 -- update of old record
SAVE
----> should be allowed.

Tom Kyte
January 26, 2005 - 1:16 pm UTC

so? i don't see the difference -- you'll try to insert 60,99, it fails. you try 65,80, it fails. you try 80,89, it succeeds and so on..

?

Displaying overlapping within multiple tables

Pawan Negi, July 29, 2005 - 6:19 am UTC

Hi Tom,

Posting this question here since I could see this thread remotely related to my query. Here goes:

I have a Phone_Details table which contains phone numbers for an employee for different date ranges, like:

EMP_ID DATE_FROM DATE_TO PHONE_NUMBER
1111 01/15/2004 01/20/2004 111-1111

Another table Person_Details would contain (along with other static demography information) the effective start date and end date of this employee, like:

EMP_ID START_DATE END_DATE
1111 01/01/2004 01/31/2004

Now what I am trying to write is a query that would give me this person against valid date ranges, and corresponding numbers, like for the above case,:

EMP_ID START_DATE END_DATE PHONE_NUMBER
1111 01/01/2004 01/14/2004 NULL
1111 01/15/2004 01/20/2004 111-1111
1111 01/21/2004 01/31/2004 NULL

Another case could be
Phone_Details:
EMP_ID DATE_FROM DATE_TO PHONE_NUMBER
2222 01/01/2004 01/20/2004 222-2222

Person_Details:
EMP_ID START_DATE END_DATE
2222 01/15/2004 01/31/2004

The query should result in:
EMP_ID START_DATE END_DATE PHONE_NUMBER
2222 01/15/2004 01/20/2004 222-2222
2222 01/21/2004 01/31/2004 NULL

To sum it up, the result should have such a break up of dates so that there is no overlapping period(date from and date to) between these two tables, and all such unique periods have the correct phone number(a valid number if period present in phone_details, else null).

here is the script:
CREATE TABLE phone_details (emp_id number, date_from DATE, date_to DATE, phone_number VARCHAR2(20));

INSERT INTO phone_details VALUES(1111, TO_DATE('15-jan-2004','dd-mon-yyyy'), TO_DATE('20-jan-2004','dd-mon-yyyy'),'111-1111');

INSERT INTO phone_details VALUES(2222, TO_DATE('01-jan-2004','dd-mon-yyyy'), TO_DATE('20-jan-2004','dd-mon-yyyy'),'222-2222');

CREATE TABLE person_details (emp_id NUMBER, START_DATE DATE, end_date DATE);

INSERT INTO person_details VALUES(1111, TO_DATE('01-jan-2004','dd-mon-yyyy'), TO_DATE('31-jan-2004','dd-mon-yyyy'));

INSERT INTO person_details VALUES(2222, TO_DATE('15-jan-2004','dd-mon-yyyy'), TO_DATE('31-jan-2004','dd-mon-yyyy'));

Any help would be appreciated.

Tom Kyte
July 29, 2005 - 9:15 am UTC

what other assumptions need be made?

o phone details ever overlap or are you assured they are non-overlapping
o related question phone details - 1 row as your example, or MANY rows as I
presume could be the case
o related question phone details - 0 rows possible? need to cater for that?


if so, be sure to include test cases for those.

Dont understand the following comment

Mikito Harakiri, July 29, 2005 - 2:15 pm UTC

<qiote>
By the time session A inserts, session B
has already committed and therefore released the lock, but because session A's
isolation level is serializable, it doesn't "see" the row inserted by session B.
</quote>

Sure session A sees all the changes caused by committed transaction in session B. Could you please be more specific?

Tom Kyte
July 29, 2005 - 3:07 pm UTC

did you try it?  serializable -> database is FROZEN as of that point in time.


In read committed, you can see rows committed by other transactions
In serializable, you see the database AS IT WAS when your transaction began.


ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set transaction isolation level serializable;
 
Transaction set.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into t values ( 1 );
  5          commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
no rows selected
<b>Nothing</b>
 
ops$tkyte@ORA9IR2> select * from t;
 
no rows selected
<b>Nada</b>
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> select * from t;
 
         X
----------
         1
 
ops$tkyte@ORA9IR2>
<b>data is there.....</b>

 

Still confused

Mikito Harakiri, July 29, 2005 - 4:14 pm UTC

Session A> set transaction isolation level serializable;

Session A> commit; -- assume transaction A starts here

Session A> select * from intervals;

HEAD TAIL
---------- ----------
1 2

Session B> delete from intervals;

1 row deleted.

Session B> commit;

Commit complete.

Session A> /

no rows selected


Serializable is the default level right? I don't undestand how can I query the current state of the database, without comitting after each query, then.

Tom Kyte
July 29, 2005 - 5:53 pm UTC

umm, no -- your commit turned off serializable.

You asked for that transaction to be serializable.

Then you ended the transaction.

Going back to the default of read committed.


The point of serializable is to have "no phantom reads", every time you run a query in serializable (by the VERY definition in the standard), it CANNOT be affected by any outside effects - must return the same results (unless YOUR transaction modified it, then it CAN see those changes).

serializable is there to prevent phantom reads, the query executed at the beginning of the transaction must return a result set that is equal to that first answer (plus YOUR modifications) for the life of your transaction.






OK, up to speed now

Mikito Harakiri, July 29, 2005 - 4:41 pm UTC

Got the basics right.

Here is the question. How could a constraint in principle be implemented if one transaction is isolated from another? Transaction A and B begin at the same time, so that they are unaware of each other, and therefore, how could they check if the global state is consistent? Note, that the statement is general: it applies to constraints other than interval overlapping, and even to constraint enforcement techniques other than triggers.

How about MV enforced constraints (which, BTW, unlike triggers aren't run pages of code)? How could we be sure that MV based constraints don't have the same problem?

Tom Kyte
July 29, 2005 - 5:56 pm UTC

I've seen your MV constraints Mikito, you are right, they don't run pages -- some of yours have run volumes and volumes :)

You might be interested in the transaction processing book I have linked to in links I like. Lots of theory and background in there.


If you are using database enforced constraints, the database ensures things are right. Even MV based ones. It is a quite sophisticated beast, Oracle is.

That was temporary blockout

Mikito Harakiri, July 29, 2005 - 7:26 pm UTC

Funny thing, I've read Gray book sometime in 97. It's obsoleted by Gerhard Weikum and Gottfried Vossen book right?

7 layers of nested MVs were just the result of MV refresh limitations. As soon as those limitations are lifted one by one (and in fact we saw this in 10g, right?), the MV solution becomes more practical.

Your trigger solution is incomplete without accounting for updates and deletes. That's right, 3 times more code. And I'm not sure how you would code the lock, wouldn't you need to have 6 arrays, one per each DML type?

Does MV refresh works because it serializes everything?

Tom Kyte
July 29, 2005 - 8:06 pm UTC

I didn't realize they changed the fundamentals of Transaction processing in the last decade?

I picked on your MV's because you made a totally unsubstantiated, false statement about pages of triggers. In that case, the trigger was totally small, your unusable in the real world MV implementation trick was huge. (You personally have a way with unsubstantiated "i don't like it" sort of statements)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:42170695313022
seems to have insert, update, delete covered and in "a tad less number of keystrokes"

or this
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:42171194352295 <code>

or how about you point to the article you mean with the incomplete solution?

MV refreshes do not serialize everything.


Further to my question....

Pawan Negi, August 01, 2005 - 8:05 am UTC

Hi Tom,
Thanks for pointing out those cases. My answers inline:

<TOM> o phone details ever overlap or are you assured they are non-overlapping
<Pawan> Phone details would never overlap in any of the tables.
<TOM> o related question phone details - 0 rows possible? need to cater for that?
<Pawan> Yes, 0 rows possible in phone_details table. In such a case, we print the record of person_details table as it is, but with a null phone number.
<TOM> o related question phone details - 1 row as your example, or MANY rows as I presume could be the case.
<Pawan> This is one very important case that I missed out in original posting. My apologies for that. Many rows for an empolyee can be there in phone_details table, but as mentioned above, they will never be overlapping. We would only be concerned with those records in phone_details table, which have overlapping dates with person record. Consider this case:
Phone_Details:
EMP_ID DATE_FROM DATE_TO PHONE_NUMBER
3333 01/01/2004 01/15/2004 333-3333
3333 01/16/2004 01/31/2004 333-4444

Person_Details:
EMP_ID START_DATE END_DATE
3333 01/01/2004 01/20/2004

If we take one row from phone_details at a time against person_details record, the two resulting displays would be:
For 1st to 15th(phone) against 1st to 20th(person):
EMP_ID START_DATE END_DATE PHONE_NUMBER
3333 01/01/2004 01/15/2004 333-3333
3333 01/16/2004 01/20/2004 NULL

For 16th to 31st(phone) against 1st to 20th(person):
EMP_ID START_DATE END_DATE PHONE_NUMBER
3333 01/01/2004 01/15/2004 NULL
3333 01/16/2004 01/20/2004 333-4444

Hence, for the above case, the query should give me this result:
EMP_ID START_DATE END_DATE PHONE_NUMBER
3333 01/01/2004 01/15/2004 333-3333
3333 01/16/2004 01/20/2004 333-4444

end of the day, it should fetch all valid phone numbers from phone_details table for the corresponding date slab. Any slab not fitting in phone_details table, should still have an entry in the result, but with a null phone number.
I hope I am able to explain.

here are the scripts for the 3rd case:

INSERT INTO phone_details VALUES(3333, TO_DATE('01-jan-2004','dd-mon-yyyy'),
TO_DATE('15-jan-2004','dd-mon-yyyy'),'333-3333');

INSERT INTO phone_details VALUES(3333, TO_DATE('16-jan-2004','dd-mon-yyyy'),
TO_DATE('31-jan-2004','dd-mon-yyyy'),'333-4444');

INSERT INTO person_details VALUES(3333, TO_DATE('01-jan-2004','dd-mon-yyyy'),
TO_DATE('20-jan-2004','dd-mon-yyyy'));

Thanks.

Tom Kyte
August 01, 2005 - 9:17 am UTC

wish you would have filled in all of the boundary values.  

added some more data:

ops$tkyte@ORA9IR2> select * from phone_details;
 
    EMP_ID DATE_FROM DATE_TO   PHONE_NUMBER
---------- --------- --------- --------------------
      1111 15-JAN-04 20-JAN-04 111-1111
      2222 01-JAN-04 20-JAN-04 222-2222
      3333 01-JAN-04 15-JAN-04 333-3333
      3333 16-JAN-04 31-JAN-04 333-4444
      5555 16-JAN-03 31-JAN-03 333-4444
 
ops$tkyte@ORA9IR2> select * from person_details;
 
    EMP_ID START_DAT END_DATE
---------- --------- ---------
      1111 01-JAN-04 31-JAN-04
      2222 15-JAN-04 31-JAN-04
      3333 01-JAN-04 20-JAN-04
      4444 01-JAN-04 20-JAN-04
      5555 01-JAN-04 20-JAN-04
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select per.emp_id,
  2         greatest(per.start_date,nvl(pho.date_from,per.start_date)),
  3         least(per.end_date,nvl(pho.date_to,per.end_date)),
  4             pho.phone_number
  5    from person_details per left outer join phone_details pho
  6      on ( per.emp_id = pho.emp_id and
  7           per.end_date >= pho.date_from and
  8           per.start_date <= pho.date_to )
  9   order by 1, 2
 10  /
 
    EMP_ID GREATEST( LEAST(PER PHONE_NUMBER
---------- --------- --------- --------------------
      1111 15-JAN-04 20-JAN-04 111-1111
      2222 15-JAN-04 20-JAN-04 222-2222
      3333 01-JAN-04 15-JAN-04 333-3333
      3333 16-JAN-04 20-JAN-04 333-4444
      4444 01-JAN-04 20-JAN-04
      5555 01-JAN-04 20-JAN-04
 
6 rows selected.
 

brilliant stuff

Pawan Negi, August 02, 2005 - 6:05 am UTC

Hi Tom,

Thanks for the wonderful query. But unfortunately, this result seems to be partial to what I wanted to achieve. This result certainly covers all the phone numbers for all valid date ranges, and null phone number where there is no record in phone_details table. But the result should also have an entry for the "remainder" date range. Consider the first example from my original posting:

Phone_Details:
EMP_ID DATE_FROM DATE_TO PHONE_NUMBER
1111 01/15/2004 01/20/2004 111-1111

Person_Details:
EMP_ID START_DATE END_DATE
1111 01/01/2004 01/31/2004

Running your query would give me:
EMP_ID START_DATE END_DATE PHONE_NUMBER
1111 01/15/2004 01/20/2004 111-1111

This result does cover phone number for a valid range(which in this case is 15th to 20th). But it doesnt talk about the remainder ranges (1st to 14th and 21st to 31st). hence, what I wanted to achieve is:
EMP_ID START_DATE END_DATE PHONE_NUMBER
1111 01/01/2004 01/14/2004 NULL
1111 01/15/2004 01/20/2004 111-1111
1111 01/21/2004 01/31/2004 NULL

with all my respect, before you blame me for not putting my requirements clearly, I did mention about this in my initial posting(we all are scared of missing something and then getting a taste of your "technical" sarcasm, ain't we).
I guess "ENGLISH" is restricting me. Here, I still try:
If person_details table has a record starting from X date till Y date, the resulting query SHOULD have all details starting from X to Y, breakup(if overlapping dates with phone_details) or no breakup(no overlapping).

My apologies if I am still not clear with the way I am explaining things, but until I finally do, I will keep trying.
thanks.

Tom Kyte
August 02, 2005 - 7:54 am UTC

we are all scared? why would you be scared? I didn't get that, do you have an example of technical sarcasm to be scared of?

See the problem here is this question is spread over a couple of entries. It is hard to get it all when the problem statement is not concise and somewhat self contained (small examples, with all of the boundary values considered and spec'ed out -- something you need to do just to figure out what the question is in the first place). In order for me to even just put this example together - I've got to do a bit of work (cut and paste from up here, cut and paste from down there, edit it up a little - and then try to put all of the requirements together from here and there and everywhere).

making up those "missing" records is going to be rather expensive and leads to more questions.

can you have a person with start/end dates of say 01-jan-2005 and 30-jan-2005
and phone records of 05-jan-2005 ... 07-jan-2005
15-jan-2005 ... 20-jan-2005
27-jan-2005 ... 28-jan-2005


or even
and phone records of 31-dec-2004 ... 07-jan-2005
15-jan-2005 ... 20-jan-2005
27-jan-2005 ... 28-feb-2005




and yet again..

Pawan Negi, August 02, 2005 - 9:52 am UTC

Hi Tom,

I totally concur with what you have said. I had this feeling all this while that the way I am explaining things might not be concise. But then I am not yet to give up.
The cases you talked about are true. The phone records might not have continuous date brackets, which means that for a person record of 01-jan-2005 to 31-jan-2005, the phone_details could have records like
05-jan-2005 ... 07-jan-2005
15-jan-2005 ... 20-jan-2005
27-jan-2005 ... 28-jan-2005
or even
31-dec-2004 ... 07-jan-2005
15-jan-2005 ... 20-jan-2005
27-jan-2005 ... 28-feb-2005
But my result only bothers about start_date and end_date of person_details. It should only show details starting from the start_date and ending at end_date of person_details. Any record in phone_details before that or after that doesnt really concern us.

I understand your point here. There could be so many boundary conditions which even I can't provide examples for at this point. And thats why, what I really needed was just a clue to start with, so I could keep playing with and modifying the query, as and when I came across new cases. The query you provided in your previous posting was certainly helpful, and could have been a good point for me to start with, but as I mentioned earlier, it didnt really tell me how to get those "missing records".
So may be you could simply consider the following case, see if you can think of a query that gives the desired output, and I could take it from there.
here goes:
phone_details:
EMP_ID DATE_FROM DATE_TO PHONE_NUMBER
1111 01/15/2004 01/20/2004 111-1111

person_details:
EMP_ID START_DATE END_DATE
1111 01/01/2004 01/31/2004

Report:
EMP_ID START_DATE END_DATE PHONE_NUMBER
1111 01/01/2004 01/14/2004 NULL
1111 01/15/2004 01/20/2004 111-1111
1111 01/21/2004 01/31/2004 NULL

The script:
CREATE TABLE phone_details (emp_id number, date_from DATE, date_to DATE,
phone_number VARCHAR2(20));

INSERT INTO phone_details VALUES(1111, TO_DATE('15-jan-2004','dd-mon-yyyy'),
TO_DATE('20-jan-2004','dd-mon-yyyy'),'111-1111');

CREATE TABLE person_details (emp_id NUMBER, START_DATE DATE, end_date DATE);

INSERT INTO person_details VALUES(1111, TO_DATE('01-jan-2004','dd-mon-yyyy'),
TO_DATE('31-jan-2004','dd-mon-yyyy'));

Thanks.

Tom Kyte
August 02, 2005 - 10:33 am UTC

see, this one would be easy -- if I used your test case as the assumptions.

Problem is a solution that works when there is ONE row in the phone table, won't work when there are zero rows and wont work when there are 2 rows necessarily. It gets quite "hairy"


one more question and perhaps we can rewrite this entirely.


Are the date ranges in phone_details always INSIDE the range of the person table?

That is, do the person start/end dates always cover the phone number to and from dates (or not)?

from where we left..

Pawan Negi, August 03, 2005 - 3:27 am UTC

Hi Tom,

Well, phone_details can have any values, any date ranges. There is no specific nature of the data there. The only assumption we can make about the phone_details records is that the date brackets are never overlapping. At any given date for a given employee, it will have at the most one record, least could be none.
About what you mentioned, I am afraid thats not true. The person start and end dates might or might not cover the phone dates. so for a person with 15-jan-2004 to 20-jan-2004, EITHER of the following phone dates is possible(assume phone number is XXX for all these records):
> 01-jan-2004 ... 31-jan-04 (phone record covering person)
Result:
15-jan-2004 20-jan-2004 XXX
> 01-jan-2004 ... 10-jan-2004 (no relevant data)
Result:
15-jan-2004 20-jan-2004 NULL
> 01-jan-2004 ... 17-jan-2004 (phone end date between person dates)
Result:
15-jan-2004 17-jan-2004 XXX
18-jan-2004 20-jan-2004 NULL
> 17-jan-2004 ... 19-jan-2004 (person dates covering phone)
Result:
15-jan-2004 16-jan-2004 NULL
17-jan-2004 19-jan-2004 XXX
20-jan-2004 20-jan-2004 NULL
> 17-jan-2004 ... 25-jan-2004 (phone start date between person dates)
Result:
15-jan-2004 16-jan-2004 NULL
17-jan-2004 20-jan-2004 XXX
> 21-jan-2004 ... 25-jan-2004 (Again, not connected)
Result:
15-jan-2004 ... 20-jan-2004 NULL

If you observe in the above cases, there is really no specific nature of the phone records, they almost take any form(covering, not covering, partially covering...)

I trust this does answer you query. I am sure there are a lot many :)

thanks.

Tom Kyte
August 03, 2005 - 11:00 am UTC

work with this

ops$tkyte@ORA10G> select * from person_details;
 
    EMP_ID START_DAT END_DATE
---------- --------- ---------
      1111 01-JAN-04 31-JAN-04
      2222 15-JAN-04 31-JAN-04
      3333 01-JAN-04 20-JAN-04
      4444 01-JAN-04 20-JAN-04
 
ops$tkyte@ORA10G> select * from phone_details;
 
    EMP_ID DATE_FROM DATE_TO   PHONE_NUMBER
---------- --------- --------- --------------------
      1111 15-JAN-04 20-JAN-04 111-1111
      2222 01-JAN-04 20-JAN-04 222-2222
      3333 01-JAN-04 15-JAN-04 333-3333
      3333 16-JAN-04 31-JAN-04 333-4444
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select emp_id,
  2         decode( r, 1, start_date,
  3                    2, date_to+1 ) date1,
  4         decode( r, 1, nvl(date_from-1,end_date),
  5                        2, end_date ) date2,
  6         null
  7    from (
  8  select per.emp_id, per.start_date, per.end_date, min(greatest(per.start_date,date_from)) date_from,
  9                                                   max(least(per.end_date,date_to)) date_to
 10    from person_details per, phone_details pho
 11   where per.emp_id = pho.emp_id(+)
 12   group by per.emp_id, per.start_date, per.end_date
 13         ) data,
 14             ( select 1 r from dual union all select 2 r from dual)
 15   where (date_from > start_date and r = 1)
 16      or (date_to < end_date and r = 2)
 17          or (date_from is null and r = 1)
 18  union all
 19  select per.emp_id,
 20         greatest(per.start_date,nvl(pho.date_from,per.start_date)),
 21         least(per.end_date,nvl(pho.date_to,per.end_date)),
 22             pho.phone_number
 23    from person_details per join phone_details pho
 24      on ( per.emp_id = pho.emp_id and
 25           per.end_date >= pho.date_from and
 26           per.start_date <= pho.date_to )
 27   order by 1, 2
 28  /
 
    EMP_ID DATE1     DATE2     NULL
---------- --------- --------- --------------------
      1111 01-JAN-04 14-JAN-04
      1111 15-JAN-04 20-JAN-04 111-1111
      1111 21-JAN-04 31-JAN-04
      2222 15-JAN-04 20-JAN-04 222-2222
      2222 21-JAN-04 31-JAN-04
      3333 01-JAN-04 15-JAN-04 333-3333
      3333 16-JAN-04 20-JAN-04 333-4444
      4444 01-JAN-04 20-JAN-04
 
8 rows selected.
 
 

that solves it..

Pawan Negi, August 08, 2005 - 6:40 am UTC

Hi Tom,

This is exactly what I was looking for. You rock yet again. Thanks. I will take it further from here.


Explaination for the query

Narendra, September 20, 2005 - 8:06 am UTC

Hi Tom,

Thanks a lot to you & Pawan for the exhaustive discussion on the problem.
Can you please explain the query that you had proposed (and which Pawan agreed to take further).

Thanks

MISSING RANGES

Asim Naveed, September 29, 2005 - 3:44 am UTC

Tom,

i have a table with following data
from_range to_range
1 10
11 15
18 20
25 30
41 50

what i want is a query which will return me missing ranges
within a given big range.
e.g

If i give following parameters to the query

from_range to_range
1 30

The query should return
from_range to_range
16 17
21 24


If i give following parameters to the query

from_range to_range
1 50

The query should return
from_range to_range
16 17
21 24
31 40


Is it possible in one SQL Query.

Thanks


Tom Kyte
September 29, 2005 - 7:07 am UTC

you'll be using lag or lead.... If I had an example create table and insert........ I could show you - but read about lag and lead and see if you cannot do it.

Here is CREATE , INSERT STATEMENTS

Asim Naveed, September 29, 2005 - 7:40 am UTC

CREATE TABLE RN (RF NUMBER(10), RT NUMBER(10));

INSERT INTO RN (RF, RT) VALUES(1,10);
INSERT INTO RN (RF, RT) VALUES(11,15);
INSERT INTO RN (RF, RT) VALUES(18,20);
INSERT INTO RN (RF, RT) VALUES(25,30);
INSERT INTO RN (RF, RT) VALUES(41,50);

Cant imagine how is it possible using lag lead,
also note that there might be overlapping ranges.



Tom Kyte
September 30, 2005 - 7:18 am UTC

wow, I go away for a day :) look at all of the followups.

but - if there can be "overlaps", why would you test case not present an example of one? test cases should include as many good examples as possible - especially ones with boundary value conditions.

One would naturally assume from your test case that overlaps are not possible.

The next question would be - what kind of overlaps? I mean, can I insert the record

values ( 1, 50 )

into that table - hence removing all gaps? Can a record overlap with more than one record?

How big is the actual table we are talking about here - how many rows and what is the spread between the minimum(rf) and the maximum(rt) (assuming naturally that rf <= rt for every row of course :)



One more query

Asim Naveed, September 29, 2005 - 8:04 am UTC

execute some INSERTS twice , so it will create an exact
duplicate range. Add the following INSERT

INSERT INTO RN (RF, RT) VALUES(2,9);
it will create an overlapping range (not exact duplicate).

Now I want a query to show me all overlapping ranges and
whom they overlap.
I wrote this.
SELECT * FROM rn a, rn b
WHERE (a.rowid != b.rowid)
and
(
a.rf between b.rf and b.rt
or
a.rt between b.rf and b.rt
or
b.rf between a.rf and a.rt
or
b.rt between a.rf and a.rt
)

its working. But is there anyother better way.
Because RN table have 1,100,000 rows, and this query
is taking more than 7, 8 hours, even when I have created
indexes. Looks like it has to make 1,100,000 * 1,100,000
comparisons.

Thanks

Tom Kyte
September 30, 2005 - 7:25 am UTC

this is not going to have a scalable SQL solution, you have to compare every row to an indeterminate number of other rows.

You can see if some of the following suggestions work for you.

(I can say the original problem statement is about 50 light years away from this refinement..... as far as stating what was required is concerned anyway)

clarification

Gabe, September 29, 2005 - 11:26 am UTC

Asim,

While you’re waiting for Tom’s answer you may want to clarify what results you would expect for an input of {16,32}. Do you expect:

A. 16 to 17, 21 to 24, 31 to 32

Or

B. 21 to 24

In other words, do you expect to get whole gaps from the original ranges? … or do you want parts of the gaps from the original ranges too?


Checking overlapped ranges

Mikito Harakiri, September 29, 2005 - 1:11 pm UTC

The condition for overlapping intervals is symmetric. An end point from one interval is insife the other interval boundaries. You can simplify your query to

SELECT * FROM rn a, rn b
WHERE (a.rowid != b.rowid)
and
(
a.rf between b.rf and b.rt
or
a.rt between b.rf and b.rt
)

which (provided there is an index) should execute in 1M*log(1M) time.

clarification

Asim Naveed, September 29, 2005 - 1:58 pm UTC

Gabe,
I want the A option. But its better if
you can provide solutions for
both.

Mikkito

Consider your query in the case there
are following two ranges stored in the table.

RF RT
200 300
100 400

In this case if it compares with row 2 on left
side that is A.RF A.RT, then niether
100 is between 200 and 300 nor 400 is between
200 and 300. But if it comapares with row1
on the left side then it will work.

Thanks

clarification

Asim Naveed, September 29, 2005 - 2:02 pm UTC

Gabe,
I want the A option. But its better if
you can provide solutions for
both.

Mikkito

Consider your query in the case there
are following two ranges stored in the table.

RF RT
200 300
100 400

In this case if it compares with row 2 on left
side that is A.RF A.RT, then niether
100 is between 200 and 300 nor 400 is between
200 and 300. But if it comapares with row1
on the left side then it will work.

Thanks

Better wait for Tom's solution ... till then ...

Gabe, September 29, 2005 - 3:21 pm UTC

Asim,

Here is something to play with:

flip@FLOP> with inp as
2 -- the input segment
3 ( select 16 f, 32 t from dual )
4 ,flt as
5 -- the candidate ranges
6 ( select rn.*
7 from rn, inp
8 where rt >= inp.f
9 and rf <= inp.t
10 )
11 ,pnt as
12 -- all the points
13 ( select rf p, 0 flg from flt
14 union
15 select rt p, 0 flg from flt
16 union
17 select f p, 1 flg from inp
18 where not exists ( select null
19 from flt
20 where inp.f between flt.rf and flt.rt )
21 union
22 select t p, 1 flg from inp
23 where not exists ( select null
24 from flt
25 where inp.t between flt.rf and flt.rt )
26 )
27 ,seg as
28 -- ranges in the input segment build from the points
29 ( select p pf, lead(p) over (order by p) pt, flg
30 from pnt
31 )
32 ,res as
33 -- all the gaps (ranges in the input segment - candidate ranges)
34 ( select pf, pt from seg where pt is not null
35 minus
36 select rf, rt from flt
37 )
38 -- and just some formatting adjustments ...
39 select decode(pf,f,pf,pf+1) f, decode(pt,t,pt,pt-1) t
40 from res, inp
41 ;

F T
---------- ----------
16 17
21 24
31 32

Elapsed: 00:00:00.00

Showing intersection twice?

Mikito Harakiri, September 29, 2005 - 3:55 pm UTC

Ok, in your example you would have symmetric output with 2 records:

100 400 200 300
200 300 100 400

whereas in my case you would have only one record

200 300 100 400

Does it matter to you?

Anyway, this was optimisation on factor of 2. Even with your query you are expected to have a plan like this:

SELECT STATEMENT
UNION ALL
NESTED LOOPS
TABLE ACCESS FULL
TABLE ACCESS BY ROWID
INDEX RANGE SCAN
NESTED LOOPS
TABLE ACCESS FULL
TABLE ACCESS BY ROWID
INDEX RANGE SCAN

What plan are you getting?

to Asim (Query)

Marcio Portes, September 30, 2005 - 12:53 am UTC

Asim, I don't know if I took it so simple, sorry if I did, but I got this. Let me know the result.

I started with b=1 and e=30

ops$mportes@FCCUAT9I> select from_range, to_range
2 from (
3 select rn.*,
4 lag(rt) over (order by rt) +1 from_range,
5 rf-1 to_range
6 from rn
7 where rf >= :b and rt <= :e
8 )
9 where rf <> from_range
10 /

FROM_RANGE TO_RANGE
------------- -------------
16 17
21 24

2 rows selected.

ops$mportes@FCCUAT9I>
ops$mportes@FCCUAT9I> exec :e := 50

PL/SQL procedure successfully completed.


E
-------------
50

ops$mportes@FCCUAT9I> /

FROM_RANGE TO_RANGE
------------- -------------
16 17
21 24
31 40

3 rows selected.

Regards,
Marcio Portes

TWO questions actually

Asim Naveed, September 30, 2005 - 8:12 am UTC

Tom,

I think there is a little bit confusion.

Actually I asked two seperate questions.

Q1. A query that will return missing ranges.

Q2. A query that will return overlapping ranges.

Yes both questions are totally different. But both
queries will be working on the same data set. And
assume that the data set will be having every kind
of duplication and overlapping but RT is always >= RF.







More clarification

Asim Naveed, September 30, 2005 - 8:24 am UTC

Some more clarification.

----------------------------------------------------
For the query that will return overlapping ranges.
Yes there might be a big range that will cover all gaps.
1 --- 50.

Yes one row can overlap more than one rows.
------------------------------------------------------------
For the query that will return missing ranges.
No overlapping or duplicate ranges will be existing.
-----------------------------------------------------------

Sorry for such an incomplete question.


Complete Create /Inserts

Asim Naveed, October 02, 2005 - 9:21 am UTC

Okay here are the complete create and inserts

For the query which will return overlapping ranges
and whom they overlap. The query will not be given
any parameters this time. It will run on the complete
data set.

CREATE TABLE RN (RF NUMBER(10), RT NUMBER(10));
INSERT INTO RN (RF, RT) VALUES(1,10);
INSERT INTO RN (RF, RT) VALUES(11,15);
INSERT INTO RN (RF, RT) VALUES(18,20);
INSERT INTO RN (RF, RT) VALUES(25,30);
INSERT INTO RN (RF, RT) VALUES(41,50);
INSERT INTO RN (RF, RT) VALUES(41,50); -- exact duplicate.
INSERT INTO RN (RF, RT) VALUES(1,50); -- covering all gaps
INSERT INTO RN (RF, RT) VALUES(9,13); -- overlappng
INSERT INTO RN (RF, RT) VALUES(8,12); -- another overlapping


For the query which will return missing ranges.
There will be no duplicate/overlapping ranges in this
data set. There will be no parameters for this query,
it will run on complete dataset.

INSERT INTO RN (RF, RT) VALUES(1,10);
INSERT INTO RN (RF, RT) VALUES(11,15);
INSERT INTO RN (RF, RT) VALUES(18,20);
INSERT INTO RN (RF, RT) VALUES(25,30);
INSERT INTO RN (RF, RT) VALUES(41,50);


Also, keep in mind that the RN table will be contaiing
1 million rows. Storage space is no problem for me
I can have 100s of GBs secondary storage and can create
many indexes and/or partitions. But
query's result time of only 2 Hours is acceptable.

Please reply as soon as possible,I am struggling
for a week now to solve this problem.

Tom I need your response on this.

Thanks



Tom Kyte
October 02, 2005 - 10:58 am UTC

Sorry, you can use some of the ideas above. They were a good start and should be good to demonstrate that your model does not lend itself to a nice SQL solution, in particular given the scope of the rows (cardinality)


(and I am not just a query writing machine, these are reviews/followups - if it isn't something pretty quick, I cannot spend too much time on it)




Muhammad Ibrahim

Muhammad Ibrahim, October 02, 2005 - 10:44 pm UTC

Dear Tom,

Ofcourse you are 1000 times better than a SQL machine! Just a joke (Smile please). Many of us gaining lot of knowledge from you. TOM you are the MAN!

Regards,
Ibrahim.

Overlapping values

Parag J Patankar, December 14, 2006 - 7:19 am UTC

Hi Tom,

If I understand correctly, overlapping can be avoided with package+trigger and materialized view. Can I avoid it using intergrity constraint or using function based index ? for e.g. I am having following table

create table t (a varchar2(10) not null, b number not null, c number not null );

insert into t values ('A', 5, 10 );

If I try to insert overlapping values for same values for a then, it should give me an error.

Kindly suggest.

thanks & regards
PJP

Tom Kyte
December 15, 2006 - 8:17 am UTC

Can I avoid it using intergrity constraint or using function
based index ?

no, else we would have shown that as it would be less complex.

it can at least with timestamps

Matthias Rogel, December 15, 2006 - 10:40 am UTC

SQL> create table t(id integer primary key, a varchar2(10) not null);

Tabelle wurde angelegt.

SQL> create unique index t_uidx on t(a);

Index wurde angelegt.

SQL> exec dbms_wm.enableversioning(table_name => 'T', validTime=>true)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> insert into t(id, a, wm_valid)
  2  values(1, 'a', wm_period(to_date('01.01.2006', 'dd.mm.yyyy'), to_date('01.02.2006', 'dd.mm.yyyy')));

1 Zeile wurde erstellt.

REM now an overlap ...
SQL> insert into t(id, a, wm_valid)
  2  values(2, 'a', wm_period(to_date('02.01.2006', 'dd.mm.yyyy'), to_date('02.02.2006', 'dd.mm.yyyy')));
insert into t(id, a, wm_valid)
            *
FEHLER in Zeile 1:
ORA-20232: unique constraint (MATTHIAS.T_UIDX) violated
ORA-06512: at "MATTHIAS.OVM_INSERT_11", line 1
ORA-04088: error during execution of trigger 'MATTHIAS.OVM_INSERT_11'


REM now no overlap
SQL> insert into t(id, a, wm_valid)
  2  values(2, 'a', wm_period(to_date('02.05.2006', 'dd.mm.yyyy'), to_date('02.06.2006', 'dd.mm.yyyy')));

1 Zeile wurde erstellt.
 

Tom Kyte
December 15, 2006 - 11:43 am UTC

where is the constraint?

sorry, index

Matthis Rogel, December 15, 2006 - 1:00 pm UTC

but less complex, isn't it ?

Tom Kyte
December 15, 2006 - 2:55 pm UTC

not sure I'd recommend it for this - would have to play with it much more, the version enabled table has certain "performance" characteristics.

****

Matthias Rogel, December 16, 2006 - 3:25 am UTC

I discovered this feature of version-enabled tables
"avoiding overlaps" just a few days ago and it looked
impressively.
would be interesting if it really could be used to give
a new solution to the overlap-problem

meanwhile, since I am very interested to use (not misuse)
version-enabled tables in a new project and have no
experiences with them so far, could you please elaborate
what you mean by 'certain "performance" characteristics'
and probably how to overcome them
(I would apreciate any link you have)

Tom Kyte
December 16, 2006 - 5:59 pm UTC

benchmark them...

if you do predominantly indexed access and have a light to moderate workload, it'll likely be fine.

full scans - very much impacted.

it'll increase the logical IO's and latching and CPU to evaluate the predicate involved in the version enabled tables.



Avoiding overlap values...with serializable isolation level

Brian Camire, December 16, 2006 - 12:05 pm UTC

For what it's worth, there is a way to use SELECT...FOR UPDATE to make Tom's trigger-based solution (is that now an oxymoron?) work under serializable isolation level.

This involves creating a table with one row to represent the lock (or one row to represent each lock, if you're using more than one), as in something like:

SQL> create table locks (lock_id integer not null primary key);

Table created.

SQL> insert into locks values(123);

1 row created.


Then, you use SELECT...FOR UPDATE on this row instead of using DBMS_LOCK to serialize access.

Unlike DBMS_LOCK, SELECT...FOR UPDATE will fail in a serializable transaction if another transaction locked the same row since the serializable transaction started, even if the other transaction has already committed.

With this change, the discount_info_ai looks something like this:

create or replace trigger discount_info_ai
after insert on discount_info
declare
    resource_busy exception;
    pragma exception_init(resource_busy, -54);
    dummy integer;
    l_rec  discount_info%rowtype;
    l_cnt  number;
begin
    select
        null
    into
        dummy
    from
        locks
    where
        lock_id = 123
    for update;

    for i in 1 .. state_pkg.newones.count
    loop
        select * into l_rec
          from discount_info
         where rowid = state_pkg.newones(i);

        select count(*)
          into l_cnt
          from discount_info
         where rowid <> state_pkg.newones(i)
           and ( xto between l_rec.xto and l_rec.frm
              OR frm between l_rec.xto and l_rec.frm
              OR ( xto < l_rec.xto and l_rec.xto < frm ) );

        if ( l_cnt > 0 ) then
            raise_application_error( -20001,
              'overlap -- ' || l_rec.xto || ', ' ||
              l_rec.frm || ' rejected' );
        end if;
    end loop;
end;

Now, if you try this in session A...

SQL> set transaction isolation level serializable;

Transaction set.


...this in session B...

SQL> INSERT INTO DISCOUNT_INFO VALUES(5, 10);

1 row created.

SQL> commit;

Commit complete.


...and this in session A...

SQL> INSERT INTO DISCOUNT_INFO VALUES(1, 11);
INSERT INTO DISCOUNT_INFO VALUES(1, 11)
            *
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
ORA-06512: at "TEST.DISCOUNT_INFO_AI", line 8
ORA-04088: error during execution of trigger 'TEST.DISCOUNT_INFO_AI'


...the insert in session A fails, even though it can't "see" the change that session B made (since it's serializable) and even though session B has already committed (unlike with the DBMS_LOCK approach).

Somehow, Oracle "remembers" that a SELECT...FOR UPDATE lock was held.

To make this approach more "bulletproof", you'd also need a way (like maybe similar triggers) to prevent updates from causing overlaps.

Hope this helps. 

???

Max, December 18, 2006 - 6:59 am UTC

Matthias: On my 10.2.0.1.0 (Win XP) the code you 've pasted above doesn't reproduce what you 've observed (no exception at all, 'WM_Valid' left empty) ...

Any ideas?

Regards

Max

Matthias Rogel, December 18, 2006 - 8:19 am UTC

I am on 

SQL> select dbms_utility.port_string || ', banner: ' || banner from v$version where rownum=1;

DBMS_UTILITY.PORT_STRING||',BANNER:'||BANNER
------------------------------------------------------------------------------------------------------------------
x86_64/Linux 2.4.xx, banner: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi



regards
matthias
 

Date Overlapping and Mutatiting table

A reader, October 01, 2007 - 10:16 am UTC

Dear Tom,

I read this post and I also read page 665(how to avoid Mutating table error) of your book Oracle Expert but I still have a question on this subject

Please consider this

create table tt (id number, start_date date, end_date date);

CREATE OR REPLACE TRIGGER TT_BEFOR_INS
 BEFORE INSERT
 ON TT
 FOR EACH ROW
BEGIN
DECLARE
  
   cursor c1 is 
   select id, start_date, end_date
   from tt 
   where id = :New.id;
   
BEGIN
 
 For r1 in c1 loop
 
  IF :NEW.ID = r1.id THEN

 IF :NEW.START_DATE <= r1.END_DATE THEN
  raise_application_error( -20001, 'overlap'); 

    END IF;

 IF :NEW.END_DATE <= r1.END_DATE THEN
    raise_application_error( -20001, 'overlap' ); 
    END IF;
   END IF;
 end loop;
  END;
END TT_BEFOR_INS;
/

insert into tt values(1, '01/JAN/2007', '31/DEC/2007');

insert into tt values(1, '01/JAN/2008', '31/DEC/2008');

> select * from tt;

       ID START_DAT END_DATE
--------- --------- ---------
        1 01-JAN-07 31-DEC-07
        1 01-JAN-08 31-DEC-08

insert into tt values(1, '01/APR/2008', '31/DEC/2008')
            *
ERROR at line 1:
ORA-20001: overlap --
ORA-06512: at "D_SPS.TT_BEFOR_INS", line 17
ORA-04088: error during execution of trigger 'D_SPS.TT_BEFOR_INS

This is working very fine for me and I couldn't succeed to simulate the mutating table error

Could you please let me know if the above trigger has a chance to produce the mutating table error.
Thanks a lot

Tom Kyte
October 03, 2007 - 3:15 pm UTC

DECLARE
  
   cursor c1 is 
   select id, start_date, end_date
   from tt 
   where id = :New.id;
   
BEGIN
 
 For r1 in c1 loop
 
  IF :NEW.ID = r1.id THEN


how could it be that :new.id IS NOT r1.id????
.


insert into tt select 1, '...', '...' from dual;

do that and you'll see your mutant table thing.


Serializing Transactions Inside AT

Dan Clamage, December 03, 2008 - 6:35 pm UTC

So if you have two concurrent/high volume sessions running the same stored subprogram that necessarily has AT inside, how can you guarantee two calls are serialized properly? Under AT, I can't use DBMS_LOCK -- the concurrent sessions can't see the same lock and queue up properly.
Right now, I'm getting duplicate data inserted in this scenario.
Tom Kyte
December 09, 2008 - 11:10 am UTC

"AT" ???

what is AT??



constant date in calling trigger

Fabian Wiktorowski, December 30, 2008 - 8:26 am UTC

i would like to do something similar but not the same. rather then avoiding overlapping i wanted to keep the same date in all inserts until commit tranasaction. so having:
-- ----- code ------------
create table test (id number, d date);

create sequence test_seq;

insert into test(id) values (test_seq.nextval);
-- some seconds later
insert into test(id) values (test_seq.nextval);

commit;

select id, d from test;
-- ----- endcode ------------
wanted to see:
ID D
1 2008-12-30 14:13:47
2 2008-12-30 14:13:47

is it possible (using a simple function) to keep sysdate of first insert in the way that other callings of trigger will remeber it until commit.
the only solution i've found is to rebuild table using rowdependenties and using that in raport instead od date (d).

Tom Kyte
January 05, 2009 - 9:21 am UTC

seems like a massive data model issue here doesn't it? Why is the date field in more than one row? It is "master" data for the transaction.


Is there a trick to do this? Sure.

Will it be good for performance? Absolutely not.

So, tell us, why is date in more than one record?


ps$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          function the_date return date;
  4  end;
  5  /

Package created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body my_pkg
  2  as
  3          g_transaction_id varchar2(4000);
  4          g_date           date;
  5
  6  function the_date return date
  7  as
  8          l_transaction_id varchar2(4000) := dbms_transaction.LOCAL_TRANSACTION_ID;
  9  begin
 10          if ( g_transaction_id is null or
 11               l_transaction_id is null or
 12               g_transaction_id <> l_transaction_id )
 13          then
 14                  g_date := sysdate;
 15                  g_transaction_id := dbms_transaction.LOCAL_TRANSACTION_ID;
 16          end if;
 17          return g_date;
 18  end;
 19
 20  end;
 21  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> select my_pkg.the_date from dual;

THE_DATE
--------------------
05-jan-2009 09:15:02

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select my_pkg.the_date from dual;

THE_DATE
--------------------
05-jan-2009 09:15:02

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> select my_pkg.the_date from dual;

THE_DATE
--------------------
05-jan-2009 09:15:06

ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select my_pkg.the_date from dual;

THE_DATE
--------------------
05-jan-2009 09:15:08




when in a transaction - it'll be a constant, when you commit or are not in a transaction - it'll be whatever sysdate is.

thanks

A reader, January 18, 2009 - 12:46 pm UTC

anwsering your question -- date is in every row because it is inserted by triggers which are some kind of "home-made" audit -- what for to use womething you have in db if you could spend months writing it yourself?! ;-)
so if there are triggers which calls procedures which... well -- then performance is as important as always! and we need that only for reporting.
anyway -- your solution is quite neat solution so thank you for that.

overlapping while swapping Records

ABISHA, May 08, 2009 - 3:02 am UTC

Hi Tom,

I Need your suggestion.
Kindly see the below records.

ROWID M_FROM M_TO TEST ID
------------------ ----------- ----------- ----------
AAJ72iAADAAHO2LAAA 5/2/2009 10/3/2009 36
AAJ72iAADAAHO2LAAB 10/4/2009 1/3/2011 38

Now am swapping the records by using the below update statement.(ie. updating the from and to dates of test_id 36, TO the dates of the testid 38 and vice versa)

update test
set m_from = TO_DATE('05/02/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
m_to = TO_DATE('10/03/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
where test_id =38 ;

update PROFITAUTO.LKUP_LIQ_PREM_0507
set m_from = TO_DATE('10/04/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
m_to = TO_DATE('01/03/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
where test_id =36 ;

while updating am getting overlapping trigger error. Is this possible to swap records vice versa. The query that raises error in the trigger is,
SELECT COUNT ( * )
FROM test
WHERE ROWID <> m_newrowid.newones (i)
AND (m_from BETWEEN t.m_from AND t.m_to
OR m_to BETWEEN t.m_from AND t.m_to
OR (m_from < t.m_to
AND t.m_from < m_to));

I would like to know, is it possible to swap records like this without raising overlapping error? if it is possible, kindly suggest me some ideas about it. If my query is meaningless, kindly excuse me and give your comments.
Awaiting your reply.





Tom Kyte
May 11, 2009 - 3:06 pm UTC

... while updating am getting overlapping trigger error.....

overlapping trigger???

Oh, wait, ok, you wrote some code.... This code is in a trigger... This code in a trigger you own looks for "overlaps". You obviously introduce an overlap since you did two sql's one after the other. When you did the first one - there is an overlap (think about it, it has to be true)

So, don't do two updates, do one.

update test
set m_from =  
decode( test_id, 38, TO_DATE('05/02/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                 36, TO_DATE('10/04/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS') ),
m_to = 
decode( test_id, 38, TO_DATE('10/03/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
                 36, TO_DATE('01/03/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS') )
where test_id in (38,36 ) ;

Gap between dates (trigger)

ABISHA, May 08, 2009 - 4:58 am UTC

Hi Tom,

Need your suggestion.
I have Created 12 records for each month for the date range from 1/1/2008 to 12/31/2008.(ie. 1stjan-31jan,2feb-29feb,1mar-31mar.. and so on)

Now, you'll have 5/1/2008 to 5/31/2008 and 6/1/2008 to 6/30/2008 among the 12 records.Here, am deleting these two rows(ie. /1/2008 to 5/31/2008 and 6/1/2008 to 6/30/2008) and insert 5/1/2008 to 5/15/2008 and 5/16/2008 to 5/31/2008.
and 6/1/2008 to 6/15/2008 and 6/16/2008 to 6/30/2008.
It is supposed to work but it is giving error as gap between dates. How can we solve it? Kindly give ur suggestions.
Tom Kyte
May 11, 2009 - 3:17 pm UTC

kindly give YOUR (it takes four letters to form the proper word "your" - ur is meaningless gibberish. If you meant it to stand for "your", it only makes you sound like a twelve year old using Texting) example.

You know, YOUR CODE - it is after all YOUR CODE that is doing this.

When you explain to us your logic, we may well be able to tell you how to overcome your logic.

Pointes, October 13, 2009 - 7:40 am UTC

Hi Tom,

I have to avoid date overlapping where my table stores as below.

I have a table t_dt

SQL> desc t_dt;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 APP_dt_ID                           NOT NULL NUMBER(10)
 APP_ID                                    NOT NULL NUMBER(10)
 APP_NAME                                  NOT NULL VARCHAR2(250)
 APP_STATE_ID                              NOT NULL NUMBER(10)
 APP_dt_REASON                       NOT NULL VARCHAR2(2000)
 APP_dt_UPDATE_DT                    NOT NULL DATE
 APP_dt_UPDATE_by                   NOT NULL NUMBER(10)
 APP_dt_REITERATE_IND                NOT NULL NUMBER(1)
 APP_dt_SPAN_DAY_IND                 NOT NULL NUMBER(1)
 begin_date                    NOT NULL DATE
 END_Date                       NOT NULL DATE
 BEGIN_TIME                   NOT NULL DATE
 END_TIME                     NOT NULL DATE
 MON_IND                                   NOT NULL NUMBER(1)
 TUE_IND                                   NOT NULL NUMBER(1)
 WED_IND                                   NOT NULL NUMBER(1)
 THU_IND                                   NOT NULL NUMBER(1)
 FRI_IND                                   NOT NULL NUMBER(1)
 SAT_IND                                   NOT NULL NUMBER(1)
 SUN_IND                                   NOT NULL NUMBER(1)


The above table basically stores downtime details of applications.

BEGIN_Date--> stores the downimte begin date

END_Date-->stores the downtime end date

BEGIN_TIME-->stroes the downtime start time (e.g if 2pm then it is stored as 1/1/1111 2:00:00 PM)

END_TIME-->stores the downtime end time (e.g if 2pm then it is stored as 1/1/1111 2:00:00 PM)

APP_dt_SPAN_DAY_IND --> 1 if it is span day, 0 if no span day.

I say span day as a day if end time cross midnight 12 of the day.

sun_ind holds either 0 or 1 (Infact all indications(mon_ind,tue_ind etc..)) 0 for nodowntime and 1 for downtime.

business case goes like this, for a case, if the table has,
the start_date='10/13/2009', end_date='9/9/999', start_time='1/1/1111 2:00:00 PM', end_time='1/1/1111 6:00:00 PM',sun_ind=1  and the rest indications are 0 means
downtime happens on every sunday from the start_date of '10/13/2009' from 2:00 PM to 6:00 PM till end_date of '9/9/9999'.


My tables accepts new downtime settings, all i wanted to do is to avoid downtime overlapping.
input is same as the table structure like i pass, appid,appname, start_date='11/11/2009',end_date='11/20/2009' and sun_ind=1, wed_ind=1 and rest indications are zero.
By the input the overlapping downtime should be avoided.

Could you pls help how to handle this kind of scenario.

I am not sure where to start and how to proceed.
Your help makes me to sleep well in night.

Thanks
Pointers.









Pointes, October 13, 2009 - 7:41 am UTC

Hi Tom,

I have to avoid date overlapping where my table stores as below.

I have a table t_dt

SQL> desc t_dt;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 APP_dt_ID                           NOT NULL NUMBER(10)
 APP_ID                                    NOT NULL NUMBER(10)
 APP_NAME                                  NOT NULL VARCHAR2(250)
 APP_STATE_ID                              NOT NULL NUMBER(10)
 APP_dt_REASON                       NOT NULL VARCHAR2(2000)
 APP_dt_UPDATE_DT                    NOT NULL DATE
 APP_dt_UPDATE_by                   NOT NULL NUMBER(10)
 APP_dt_REITERATE_IND                NOT NULL NUMBER(1)
 APP_dt_SPAN_DAY_IND                 NOT NULL NUMBER(1)
 begin_date                    NOT NULL DATE
 END_Date                       NOT NULL DATE
 BEGIN_TIME                   NOT NULL DATE
 END_TIME                     NOT NULL DATE
 MON_IND                                   NOT NULL NUMBER(1)
 TUE_IND                                   NOT NULL NUMBER(1)
 WED_IND                                   NOT NULL NUMBER(1)
 THU_IND                                   NOT NULL NUMBER(1)
 FRI_IND                                   NOT NULL NUMBER(1)
 SAT_IND                                   NOT NULL NUMBER(1)
 SUN_IND                                   NOT NULL NUMBER(1)


The above table basically stores downtime details of applications.

BEGIN_Date--> stores the downimte begin date

END_Date-->stores the downtime end date

BEGIN_TIME-->stroes the downtime start time (e.g if 2pm then it is stored as 1/1/1111 2:00:00 PM)

END_TIME-->stores the downtime end time (e.g if 2pm then it is stored as 1/1/1111 2:00:00 PM)

APP_dt_SPAN_DAY_IND --> 1 if it is span day, 0 if no span day.

I say span day as a day if end time cross midnight 12 of the day.

sun_ind holds either 0 or 1 (Infact all indications(mon_ind,tue_ind etc..)) 0 for nodowntime and 1 for downtime.

business case goes like this, for a case, if the table has,
the start_date='10/13/2009', end_date='9/9/999', start_time='1/1/1111 2:00:00 PM', end_time='1/1/1111 6:00:00 PM',sun_ind=1  and the rest indications are 0 means
downtime happens on every sunday from the start_date of '10/13/2009' from 2:00 PM to 6:00 PM till end_date of '9/9/9999'.


My tables accepts new downtime settings, all i wanted to do is to avoid downtime overlapping.
input is same as the table structure like i pass, appid,appname, start_date='11/11/2009',end_date='11/20/2009' and sun_ind=1, wed_ind=1 and rest indications are zero.
By the input the overlapping downtime should be avoided.

Could you pls help how to handle this kind of scenario.

I am not sure where to start and how to proceed.
Your help makes me to sleep well in night.

Thanks
Pointers.









Thinking outside the box?

Kevin, January 22, 2010 - 11:54 am UTC

Hi Tom,

Given the interest in this behavior, and the [relative to the syntax I submit below] cludginess of even the most sophisticated / elegant implementations which accomplish it, do you think there is any chance of Oracle ever delivering a little flair to its constraint syntax, e.g.:

CREATE TABLE T1 (
KEY_VAL VARCHAR2,
START_DATE DATE,
END_DATE DATE,
...,
CONSTRAINT T1_PK PRIMARY KEY ON (KEY_VAL, RANGE(START_DATE, END_DATE))

whereby RANGE(column1, column2), in this context, enforced "no overlapping allowed for the range of column1 and column2"?

Is there a theoretical obstacle to such a (seemingly useful) feature? A pragmatic obstacle?
Tom Kyte
January 25, 2010 - 5:41 am UTC

... s there a theoretical obstacle to such a (seemingly useful) feature? ...

a practical obstacle is the current lack of a two dimensional index to enforce the uniqueness on.

Followup questions to your response

Kevin, January 28, 2010 - 2:23 pm UTC

a practical obstacle is the current lack of a two dimensional index to enforce the uniqueness on

Hi Tom,

I've got a few questions related to this response, if you're up for a "teaching moment".

1) What is a two dimensional index, and how does it address the problem of detecting date-range overlaps?
2) "current lack": does this mean "two dimensional indexes" are well-defined but simply not currently supported in Oracle 11; or that "two dimensional indexes" are not well defined and that more research needs to be done before they could be supported in Oracle (or any other commercial DBMS, for that matter)?
3) Certainly I understand that a unique index is a obvious structure for enforcement of a unique constraint (so much so that Oracle mandates their use to support existing UNIQUE and PRIMARY KEY constraints); however, couldn't a non-unique index (going back to my example, a non-unique index on (KEY, START_DATE, END_DATE)) be used to efficiently check for violations of the constraint my example alluded to?

I guess my question really comes back to this: if it's possible to use trigger and mv "tricks" to enforce the desired constraint in a sufficiently efficient manner, couldn't Oracle just make the functionality available directly in the DBMS, and accessible by simple declarative syntax as above?


Thanks!

Tom Kyte
January 29, 2010 - 4:05 pm UTC

normally, an index is one dimensional - it can be mapped on a straight line. There is the concept of order, of greater than, less than, equality and so on.

Now, enter a date range - say "01-jan-2010 .. 05-jan-2010". That isn't a point on a line. That is a range, it is a line itself - a point and a length. Now you have a bunch of lines in the index - not points on a line - and you have to try to see if the lines overlap.

I sort of made up the term two dimensional index here, you have two coordinates, two points, a pair, two dimensions.


... couldn't Oracle just make the functionality available directly in the
DBMS, and accessible by simple declarative syntax as above?
...

it could for some simple cases - but generalizing it would be a different story.

Autoadjusting Date Range

Bradford, March 29, 2010 - 10:17 am UTC

The trigger provided will prevent overlapping values. How should I handle the case when all overlapping date ranges should be automatically shrunk, split, or eliminated so that the new value can be squeezed in? Should this be enforced somewhere? Should this be implemented in PL/SQL or application level? Concurrent access is needed on a table with a date range and a few other columns.

I was thinking of using the trigger provided in the first example. I know nothing about locks, though, so am somewhat "scared". Then have a PL/SQL stored procedure for auto-adjusting. It'd be nice if I could get this abstracted so that I could just call it with table name, start column name, end column name.

Example and algorithm for how to split up date range can be found http://stackoverflow.com/questions/2538648/automatic-adjusting-range-table

Tom, I hope this is a fair question/followup for this thread.

Thanks,
Bradford

Tom Kyte
April 05, 2010 - 10:03 am UTC

... How should I handle the
case when all overlapping date ranges should be automatically shrunk, split, or
eliminated so that the new value can be squeezed in? ...

In your procedural logic that takes place in the stored procedure that gets the error.

I don't know what the difference between "plsql or application level" is, since well written applications naturally use plsql so as to create infinitely reusable transactions. Database code belongs in .... the database.


do NOT do this in a trigger. Nothing but data validation in a trigger. and even then, much of that shouldn't be in the trigger either.

Excellent Stuff

James, M, July 29, 2012 - 3:37 pm UTC

Tom

I have found this topic very helpful and have succesfully implemented it an several tables with VALID_FROM and VALID_TO dates to ensure there are now overlaps.

I noticed the other day that the triggers do not cater for updates, only inserts.

Is it a simple case of updating the trigger: discount_info_ai to include a section for updates or are there a seperate series of triggers that are required.

Forgive my question if it sounds silly but I am very enw to Oracle and PL/SQL so any guidance you can offer will be most helpful.

Tom Kyte
July 30, 2012 - 11:58 am UTC

at a quick glance, it looks like it would work for updates to the dates as well. You only need to capture the modified records and validate them

Overlaps: undocumented reserved word, yet working

Hoek, September 06, 2012 - 6:03 am UTC

Stumbled upon this supercool but unfortunatly *undocumented* feature ( so we cannot use it in our production code :( ):
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * 
  2  from   dual
  3  where (date '2007-01-01', date '2008-01-01') overlaps (date '2005-01-01', date '2006-01-01');

no rows selected

SQL> select * 
  2  from   dual 
  3  where (date '2007-01-01', interval '5' year)  overlaps (date '2005-01-01', date '2007-01-02');

D
-
X

SQL> select * 
  2  from   dual
  3  where (0, 5) overlaps (3, 12);
where (0, 5) overlaps (3, 12)
       *
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

( Got it from http://oraclesponge.wordpress.com/2008/06/12/the-overlaps-predicate/ )

Do you have any knowledge regarding if this will ever become a documented feature?
This makes life sooo much easier, just like LISTAGG did for 11G.
And if so, it would be nice to know whether it will be overloaded and also capable of handling numeric datatypes?

Thanks in advance for whatever it is you want to and/or can share on this secret and promising gem.

Regards,
Martijn
Tom Kyte
September 10, 2012 - 7:50 pm UTC

do not use it, undocumented, unreliable.

there is one undocumented thing I talk about these days - the cardinality hint. if it stops working, it doesn't matter, you'll get the right answer.

if this doesn't work like you think it should - what then?

Maybe..

Hoek, September 16, 2012 - 6:46 am UTC

I should better have phrased my previous post as follows:
"Tom, do you perhaps know if Oracle has plans to document and ship the OVERLAPS predicate with a new database release? If so, will it be within a year or two or will it take much more years? Or is it impossible to implement such a predicate"

Regards,
Martijn
Tom Kyte
September 18, 2012 - 2:01 am UTC

I can tell you that it is not documented in the next release - so it would take longer than a year.

I would suggest anyone interested in this feature file an enhancement request via support.

It helped me a lot

jmgonet, November 29, 2012 - 5:38 am UTC

Really. This is a tricky issue, and the solution is simple, once you got into it.

I got it implemented on my environment in no time.

Compound triggers

Manish Sharma, April 18, 2013 - 2:36 am UTC

Instead of 3 triggers and package - compound triggers can be used to have the same effect
Tom Kyte
April 22, 2013 - 7:42 pm UTC

yes, in 11g and up.

trigger

jaya, October 01, 2013 - 4:50 am UTC

Hi tom,
Can you tel me how to create the trigger on single column of a table such that any dml operations performed on that colmun with in 5mins duration will fire triggers

Predicate?

Duncs, April 17, 2014 - 12:20 pm UTC

Hi Tom
Firstly, thanks for publishing this as it's just the safeguard I need.

We have a form that allowed for some weird combination of date values to use and I can across the following issue:

INSERT INTO discount_info
VALUES
(
TO_DATE ( '17/04/2014 11:25:00', 'DD/MM/YYYY HH24:MI:SS' )
, TO_DATE ( '17/04/2014 11:39:59', 'DD/MM/YYYY HH24:MI:SS' )
);

INSERT INTO discount_info
VALUES
(
TO_DATE ( '17/04/2014 11:40:00', 'DD/MM/YYYY HH24:MI:SS' )
, TO_DATE ( '17/04/2014', 'DD/MM/YYYY HH24:MI:SS' )
);

I was curious why this allowed both records to be inserted and then realized the BETWEEN evaluation was not working quite as I expected.

Changing the predicate to the following seems to capture all permutations:

select count(*)
into l_cnt
from discount_info
where rowid <> state_pkg.newones(i)
and ( xto between l_rec.xto and l_rec.frm
OR frm between l_rec.xto and l_rec.frm
OR xto between l_rec.frm and l_rec.xto -- Added to eval BETWEEN in the other direction
OR frm between l_rec.frm and l_rec.xto -- Added to eval BETWEEN in the other direction
OR ( xto < l_rec.xto and l_rec.xto < frm ) );

I was curious as to your thoughts on this?

Cheers

Avoiding overlap values...

Sean, March 27, 2015 - 3:38 am UTC

Hi

I have data as below:-
DateTimeFrom DateTimeTo
0900 1000
1100 1200
1400 1500
1500 1800

I would like to verify for overlapping range before new values inserted.
eg: new Value DateTimeFrom: 0830 DateTimeTo:1300

Thanks in Advance

Best Regards
Sean

Range (interval) as part of Unique key

Viswa, May 03, 2021 - 6:57 pm UTC

I would like to get an opinion from the experts.
I always enjoy reading various topics on askTom.oracle.com.


Based on my understanding Oracle did not have the ability to directly impose a unique constraint on a range of dates/duration/interval between two dates. I understand it can be achieved indirectly by writing a package and serialize the data by using dbms_lock, and call it in a trigger. I am wondering if there are any plans to implement a way to directly impose a Uniqueness on date ranges. Here is an example:

Create table items(
item_id number primary key,
item_name varchar(30),
item_description VARCHAR(64)
);

create table item_prices
(
item_price_id number primary key,
item_id references items(item_id),
price_from_Date DATE,
price_through_date DATE,
item_price NUMBER
);

We do not want to allow overlaps in the date range.
We know it cannot be achieved by having a unique index on the combination of item_id, price_from_Date, and price_through_date, as we do not have to have any overlaps.

I know some other RDBMSs allow to have uniqueness on the Date range (both inclusive and exclusive).

Are there any plans for Oracle to have DATE range as either part of primary key or unique key or unique index? This is not necessarily just for DATE ranges, but also Integer ranges.



Chris Saxon
May 04, 2021 - 9:47 am UTC

I'm not aware of plans to do this.

If this is something you want to see, I suggest posting it on the Database Ideas forum so we can see community support:

https://community.oracle.com/tech/apps-infra/categories/database-ideas-ideas

Note this already has a hugely popular request to implement SQL assertions, which would allow you to do this:

https://community.oracle.com/tech/apps-infra/discussion/4390732/sql-assertions-declarative-multi-row-constraints

In the meantime, you can create a fast refresh on commit materialized view that counts how many overlapping rows there are. Add a check constraint that tests if the count is zero, and any transactions that insert overlapping rows will raise an exception on commit.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library