Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anil.

Asked: February 14, 2005 - 10:35 am UTC

Last updated: February 25, 2005 - 4:57 pm UTC

Version: 10.1.0.3.0

Viewed 1000+ times

You Asked

Hi Tom


I have booked pieces data for a product like this


Product Start_date end_date Booked
----------------------------------------
PXP 5-Jan-2005 15_jan-2005 10
PXP 13-Jan-2005 25_jan-2005 5



Intial product avilability for the period of 1-Jan-2005 to 31-Jan-2005 was 20.

Now if some one books 7 peices for the product PX for a period of 1-jan-2005 to 7th-Jan-2005 , He should be able to book. But if he try 7 pieces for a period of 1-Jan-2005 to 15-Jan-2005 He should get a reply could not book since it execeeds the total pieces 20 for dates 13/14/15 Jan-2005.

Can we write this in once query to check

Scripts attached

The input for the query would be product_code=PXP , start_date=1-jan-2005 , end_date 15th_ajn-2005 pieces=7

ANIL@NGDEV1-SQL> create table product (prod_code varchar2(10),sdate date,edate date,pieces number);

Table created.

ANIL@NGDEV1-SQL> insert into product values('PXP','01-jan-2005','31-jan-2005',20);

1 row created.

ANIL@NGDEV1-SQL> commit;

Commit complete.


ANIL@NGDEV1-SQL> create table product_booking(prod_code varchar2(10),sdate date,edate date,bkd_pieces number);

Table created.

ANIL@NGDEV1-SQL> insert into product values('PXP','01-jan-2005','15-jan-2005',10);

1 row created.

ANIL@NGDEV1-SQL> insert into product values('PXP','13-jan-2005','25-jan-2005',5);

1 row created.

ANIL@NGDEV1-SQL> commit;

Commit complete.



Thanks & Rgds
Anil



and we said...

this is harder than it looks -- both from a "how to approach" to "how to do concurrently" (i find the second part more interesting actually :)


Ok, so we start with:

ops$tkyte@ORA10G> select * from product;

PROD_CODE SDATE EDATE PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 31-JAN-05 20

ops$tkyte@ORA10G> select * from product_booking;

PROD_CODE SDATE EDATE BKD_PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 15-JAN-05 10
PXP 13-JAN-05 25-JAN-05 5



Now, I need a query that shows me for a range of dates -- the "truly" available amount.

So, we need a set of rows that cover the dates in question...

8 (select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
9 from dual
10 connect by 1=1
11 and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')


will do that for a range of dates from :x to :y

We need to join that to the "booked amounts" for a given (sole) product. In fact, we want to outer join -- so as to "have a value for each day and make sure was have SOME availability on that day"


7 from product_booking pb,
8 (select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
9 from dual
10 connect by 1=1
11 and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
12 ) x
13 where x.dt between pb.sdate(+) and pb.edate(+)
14 and pb.prod_code(+) = 'PXP'


so, that'll give us all of the booked pieces by day in that range, we need to get the "initially available" and subtract:

ops$tkyte@ORA10G> variable x varchar2(20)
ops$tkyte@ORA10G> variable y varchar2(20)
ops$tkyte@ORA10G> exec :x := '01-jan-2005'; :y := '15-jan-2005';

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select x.dt,
2 (select pieces
3 from product
4 where sdate <= x.dt
5 and edate >= x.dt )
6 - nvl(sum(pb.bkd_pieces),0) really_available
7 from product_booking pb,
8 (select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
9 from dual
10 connect by 1=1
11 and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
12 ) x
13 where x.dt between pb.sdate(+) and pb.edate(+)
14 and pb.prod_code(+) = 'PXP'
15 group by x.dt
16 /

DT REALLY_AVAILABLE
--------- ----------------
01-JAN-05 10
02-JAN-05 10
03-JAN-05 10
04-JAN-05 10
05-JAN-05 10
06-JAN-05 10
07-JAN-05 10
08-JAN-05 10
09-JAN-05 10
10-JAN-05 10
11-JAN-05 10
12-JAN-05 10
13-JAN-05 5
14-JAN-05 5
15-JAN-05 5

15 rows selected.


So, now we have a query that tells us, by day -- for a product -- what the real availability is.....

We are ready to code our transaction:

ops$tkyte@ORA10G> create or replace
2 procedure book_a_product( p_name in varchar2,
3 p_sdate in date,
4 p_edate in date,
5 p_amt in number )
6 as
7 l_cnt number;
8 l_row product%rowtype;
9 begin
10 update product
11 set prod_code = p_name
12 where prod_code = p_name;
13
14 select count(*) into l_cnt
15 from dual
16 where exists
17 ( select null
18 from ( select x.dt,
19 (select pieces
20 from product
21 where sdate <= x.dt
22 and edate >= x.dt )
23 - nvl(sum(pb.bkd_pieces),0) really_available
24 from product_booking pb,
25 (select p_sdate+rownum-1 dt
26 from dual
27 connect by 1=1
28 and rownum <= p_edate-p_sdate
29 ) x
30 where x.dt between pb.sdate(+) and pb.edate(+)
31 and pb.prod_code(+) = p_name
32 group by x.dt
33 )
34 where really_available < p_amt
35 );
36
37 if ( l_cnt = 0 )
38 then
39 insert into product_booking
40 ( prod_code, sdate, edate, bkd_pieces )
41 values ( p_name, p_sdate, p_edate, p_amt );
42 else
43 raise_application_error(-20001, 'Insufficient pieces available' );
44 end if;
45 end;
46 /

Procedure created.


You might ask "why the update?"

The update is there for concurrency control reasons (i would suggest you revoke insert on product_booking, ONLY let them modify it via the stored procedure which correctly does the job -- no one else will do this correctly for you!). You must make sure one person at a time inserts into product booking for a given product when doing this overlap detection.


And now you have:


ops$tkyte@ORA10G> exec book_a_product( 'PXP', to_date('01-jan-2005'), to_date('07-jan-2005'), 7 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10G> select * from product_booking;

PROD_CODE SDATE EDATE BKD_PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 15-JAN-05 10
PXP 13-JAN-05 25-JAN-05 5
PXP 01-JAN-05 07-JAN-05 7

ops$tkyte@ORA10G> rollback;

Rollback complete.

ops$tkyte@ORA10G> exec book_a_product( 'PXP', to_date('01-jan-2005'), to_date('15-jan-2005'), 7 );
BEGIN book_a_product( 'PXP', to_date('01-jan-2005'), to_date('15-jan-2005'), 7 ); END;

*
ERROR at line 1:
ORA-20001: Insufficient pieces available
ORA-06512: at "OPS$TKYTE.BOOK_A_PRODUCT", line 42
ORA-06512: at line 1



I'm pretty sure I got the bases covered on this one -- read committed and serializable transactions should be "safe" with this method (using update).



Rating

  (17 ratings)

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

Comments

Very cool

Dan Kefford, February 14, 2005 - 1:40 pm UTC

Tom...

Do you need to actually update the product table, or would a SELECT... FOR UPDATE suffice?



Tom Kyte
February 14, 2005 - 5:37 pm UTC

serializable....

the select on product_booking would not see any changes (since you issued the set transaction).

so, the transactions would serialize, but not necessarily see others work.

the update is there to raise an ora-8177 if someone else updated that table, I'd rather fail than get the wrong answer ;)

if you don't use serializable, select for update would do it.

Play with two sessions and read committed/serializable -- with select for update vs update. This is important stuff at the end of the day (to be able to recognize)

I'm still learning sometimes myself (programmers tend to think "linear", the database however thinks "massively concurrent"...)



Amazing

Anil, February 14, 2005 - 2:03 pm UTC

Amazing Tom, This is simply superb, I didn't expect such a wonderful answer in such short time. This is very usefull.


I added one additional predicate to make it work for multiple product(other wise query was giving ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
)
. Hope it does not change anything. Initial tests proved every thing fine



select x.dt,
(select pieces
from product
where
prod_code='PXP' and -- Added this line
sdate <= x.dt
and edate >= x.dt )
- nvl(sum(pb.bkd_pieces),0) really_available
from product_booking pb,
(select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <=
to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
) x
where x.dt between pb.sdate(+) and pb.edate(+)
and pb.prod_code(+) = 'PXP'
group by x.dt

Thanks once again for the wonderful answer.

Rgds
Anil

Tom Kyte
February 14, 2005 - 5:44 pm UTC

exactly -- i missed that predicate (should have myself expanded your test case!)

(thanks to a guy named Mikito for that neat connect by trick with dual btw ;)

Correction

Tamil, February 14, 2005 - 2:30 pm UTC

Tom, 
Excellent explanation and very useful SQL statements. 

How do you get 15 rows for the following query:
var x varchar2(30);
var y varchar2(30);

exec :x := '01-JAN-2005';
exec :y := '15-JAN-2005';

select x.dt
       ,
       (select pieces
         from product
        where sdate <=  x.dt
          and edate >=  x.dt )
        - nvl(sum(pb.bkd_pieces),0) really_available
        from product_booking pb,
             (select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
                from dual
                 connect by 1=1
                 and rownum <=
                     (to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY'))
             ) x
where x.dt between pb.sdate(+) and pb.edate(+)
  and pb.prod_code(+) = 'PXP'
group by x.dt
;
13:32:08 SQL> @gen_dates

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

DT        REALLY_AVAILABLE
--------- ----------------
01_JAN-05               10
02_JAN-05               10
03_JAN-05               10
04_JAN-05               10
05_JAN-05               10
06_JAN-05               10
07_JAN-05               10
08_JAN-05               10
09_JAN-05               10
10_JAN-05               10
11_JAN-05               10
12_JAN-05               10
13_JAN-05                5
14_JAN-05                5

14 rows selected.

I got only 14 days. 
If I modify the line 
and rownum <=
   (to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY'))+1,

I get 15 rows ( including the last day 15-JAN-2005).

Thanks
Tamil

 

Tom Kyte
February 14, 2005 - 5:50 pm UTC

hmm, something different between 9ir2 and 10gr1 on that.

ok, the dual think is a trick -- use any table with "plenty of rows" instead of the connect by.

OR

use

(select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
from dual
connect by 1=1
and LEVEL <=
(to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY') +1)

which is consistent -- and I'll file a bug.





Some doubts

Anil, February 14, 2005 - 2:48 pm UTC

Hi Tom 

Some clarifiactions, Why below SQl returns different results in Oracle 9i. I am getting only one  row in 9i but in 10g I am getting 15 rows


 variable x varchar2(20)
 variable y varchar2(20)
 exec :x := '01-jan-2005'; :y := '15-jan-2005';
 select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
                 from dual
                connect by 1=1
                     and rownum <= 
to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
          


DT
---------
01-JAN-05


In 10g 
========

 select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
  2                   from dual
  3                  connect by 1=1
  4                       and rownum <=
  5  to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy')
  6
ANIL@NGDEV1-SQL> /

DT
---------
01-JAN-05
02-JAN-05
03-JAN-05
04-JAN-05
05-JAN-05
06-JAN-05
07-JAN-05
08-JAN-05
09-JAN-05
10-JAN-05
11-JAN-05
12-JAN-05
13-JAN-05
14-JAN-05
15-JAN-05

Would you please explain what is the difference between connect by 1=1 and connect by prior ...
Rgds
Anil 
 

Tom Kyte
February 14, 2005 - 5:52 pm UTC

see above -- use level.

prior is just a modifier.

connect by PRIOR column = something

says "look at the row you just processed, the prior row, grab COLUMN from that, now find all of the rows where something = that prior value"

nice locking technique...oracle does the Job for us :)

pasko, February 14, 2005 - 3:42 pm UTC

Hi Tom,

thanks for a nice Locking Technique there, but i have several Questions:

1.
I would like know why didn't you use the 'select for update' on product Table and opted for the update instead?
Is this a better Technique than using the for update clause?

-->Does it mean that all Concurrent Users wanting to Book Pieces will be forced to wait on that single Update for a given Product ? Is this locking guaranteed to happen without causing any Deadlocks ?


2.Is it also necessary to lock the Product_Booking Table first , even before locking the Product-Table ?

Thanks in advance.


Tom Kyte
February 14, 2005 - 6:00 pm UTC

1) see above -- serializable.

the goal is -- you HAVE to serialize at the product level, have to. overlap detection is IMPOSSIBLE.

it won't deadlock, it'll block and wait and in serializable isolation, it'll get a 8177 if someone modified the dependant data (which is important -- you want to FAIL and restart the transaction, you have to)

2) nope, you serialize at the level of a product, easiest way to do that is lock the product row

Great Nice to know that dual can be used for this

Raj, February 14, 2005 - 4:11 pm UTC

Hi Tom,

Thanks for all your help and great website.

I tried this it works for the date difference upto 25 days and does not working beyond it. What I mean is when I give :x = '01-JAN-2005' and :y = '31-JAN-2005' I only get date upto 01-25-2005 and not upto 01/31/2005.



Tom Kyte
February 14, 2005 - 6:02 pm UTC

are you sure, version? cut and paste -- i cannot reproduce.

I am sorry my mistake

Raj, February 14, 2005 - 7:22 pm UTC

Hi Tom,

I am sorry, I was running below given SQL in TOAD hence I had that problem. Now I tried with extra select on top of it and it works like a charm.

OLD

select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy' )

NEW

select * from (
select to_date(:x,'dd-mon-yyyy')+rownum-1 dt
from dual
connect by 1=1
and rownum <= to_date(:y,'dd-mon-yyyy')-to_date(:x,'dd-mon-yyyy' ) )

Thank you for immediate response and correcting me.

DB Version 9.2.0.3


Interesting...

Doug, February 15, 2005 - 9:03 am UTC

I get different results from this query from different versions. In 9iR2, I only get one row unless I use it as a subquery. It only returns one row - and doesn't display the number of rows selected.

10g returns one more row than 9i.

This was run with SQL*Plus 9.2.0.5:

doug@DOUG> select * from v$version where rownum < 2;

BANNER
----------------------------------------------------------------
Personal Oracle9i Release 9.2.0.5.0 - Production

doug@DOUG> select rownum from dual connect by 1=1 and rownum <= 10;

ROWNUM
----------
1

doug@DOUG> select * from (select rownum from dual connect by 1=1 and rownum <= 10);

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

doug@DOUG> @connect doug/<pw>@d650

doug@D650> select * from v$version where rownum < 2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod

doug@D650> select rownum from dual connect by 1=1 and rownum <= 10;

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11

11 rows selected.


CONNECT BY trick not quite working

Dan Kefford, February 15, 2005 - 9:16 am UTC

Tom...

I, too, was not able to reproduce the behavior of your/Mikito's CONNECT BY trick until I inlined the SQL:

SQL> variable x varchar2(20)
SQL> variable y varchar2(20)
SQL> exec :x := '01-jan-2005';

PL/SQL procedure successfully completed.

SQL> exec :y := '15-jan-2005';

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
  2                  from dual
  3                   connect by 1=1
  4                   and LEVEL <=
  5*                      (to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY') +1)
SQL> /

DT
---------
01-JAN-05

SQL> ed
Wrote file afiedt.buf

  1  select * from
  2  (
  3  select to_date(:x,'DD-MON-YYYY')+rownum-1 dt
  4                  from dual
  5                   connect by 1=1
  6                   and LEVEL <=
  7                       (to_date(:y,'DD-MON-YYYY')-to_date(:x,'DD-MON-YYYY') +1)
  8* )
SQL> /

DT
---------
01-JAN-05
02-JAN-05
03-JAN-05
04-JAN-05
05-JAN-05
06-JAN-05
07-JAN-05
08-JAN-05
09-JAN-05
10-JAN-05
11-JAN-05
12-JAN-05
13-JAN-05
14-JAN-05
15-JAN-05

15 rows selected.

I am using Oracle9iEE 9.2.0.4 on AIX 5.1. 

Wow, what can I say, way cool thread

Kevin Meade, February 15, 2005 - 11:05 am UTC

The people I work with think I am good, but you... you are a freak Tom (in a good way (he smiles slyly)). I didn't understand half what people were saying here and you blamm out way cool answers in under 30 minutes. I've been doing Oracle since 1985? and I still get humbled ever time I come to this site. Thanks Tom, and thanks to you guys who asked these questions, great stuff; this old dog still learns tricks from all of you.

Kevin Meade

SQL Check Constraints or Trigger?

David R. Thrash, February 23, 2005 - 3:33 pm UTC

Tom;

What's your opinion on declarative constraints such as those allowed by the
ANSI SQL standard? Specifically Multi-Row/Multi-Table CHECK constraints and
database ASSERTIONS. If you agree that these constructs are useful could you
influence Oracle Corporation to fully support them in their RDBMS?

Seems the main problem addressed in this thread could be more easily solved
using declarative CHECK constraints. In lieu of constraints what's you opinion
on using a trigger? Wouldn't all of the concurrency issues be "automigically"
handled by the RDBMS? Also the constraint or trigger would work for the
UPDATE operator too.

Note: The RDBMS should optimize CHECK constraints by utilizing
the fact that only modified rows can violate a CHECK condition.
Unfortunately The RDBMS has no such opportunity with triggers.


Thanks for your comments and efforts here at "Ask Tom".


Concretely, he's my nickel’s worth showing the check constraints, commented out, and a
trigger solution.





/* Generate IOT one-time - Add four years */
CREATE TABLE production_date (
adate DATE PRIMARY KEY,
CONSTRAINT adate_midnight CHECK ( adate = TRUNC( adate ) )
)
ORGANIZATION INDEX
/

INSERT INTO production_date
SELECT TRUNC( SYSDATE ) - 365 * 2 + LEVEL
FROM DUAL CONNECT BY LEVEL < 365 * 4
/


CREATE TABLE product (
prod_code VARCHAR2( 10 ) NOT NULL,
sdate NOT NULL REFERENCES production_date,
edate NOT NULL REFERENCES production_date,
pieces NUMBER NOT NULL,
CONSTRAINT product_pk PRIMARY KEY ( prod_code, sdate, edate ),
CONSTRAINT product_temporal_pk1 CHECK ( sdate < edate ),
CONSTRAINT pieces_gt_0 CHECK ( pieces > 0 )
--
-- table constraint prohibiting overlapping production dates
-- not supported by Oracle. Must be coded as TRIGGER.
--
/*
CONSTRAINT product_temporal_pk2
CHECK ( NOT EXISTS
( SELECT 1
FROM product p1, product p2
WHERE p1.prod_code = p2.prod_code
AND ( p1.sdate BETWEEN p2.sdate AND p2.edate OR
p1.edate BETWEEN p2.sdate AND p2.edate )
)
)
*/
)
/

CREATE TABLE product_booking (
txn_number VARCHAR2( 1 ) PRIMARY KEY, -- Just had to add a PK
prod_code VARCHAR2( 10 ) NOT NULL,
sdate NOT NULL REFERENCES production_date,
edate NOT NULL REFERENCES production_date,
bkd_pieces NUMBER NOT NULL,
/*
CONSTRAINT prod_code_ck CHECK ( NOT EXSSTS
( SELECT prod_code FROM product_booking
MINUS
SELECT prod_code FROM product )
),

*/
CONSTRAINT bkd_pieces_gt_0 CHECK ( bkd_pieces > 0 )
--
-- database constraint to prohibit overbooking not supported
-- by Oracle. MUST be coded as TRIGGER.
--
/*
CONSTRANT qty_on_hand_all_days CHECK
( NOT EXISTS
( SELECT 1
FROM ( SELECT prod_Code, adate, pieces
FROM product, production_date
WHERE adate BETWEEN sdate AND edate
) p,
( SELECT prod_code, adate, SUM( bkd_pieces ) bkd_pieces
FROM product_booking, production_date
WHERE adate BETWEEN sdate AND edate
GROUP BY prod_code, adate
) b
WHERE p.prod_code = b.prod_code
AND p.adate = b.adate
AND p.pieces < b.bkd_pieces
)
)
*/
)
/

CREATE OR REPLACE TRIGGER product_booking_aiu

AFTER INSERT OR UPDATE ON product_booking

BEGIN
-- Get the overbookings
FOR aErr IN
( SELECT 'Overbooking of ' || p.prod_code || ' by ' ||
TO_CHAR( b.bkd_pieces - p.pieces ) ||
' pieces on ' || TO_CHAR( p.adate, 'dd-MON-YYYY' ) msg
FROM ( SELECT prod_code, adate, pieces
FROM product, production_date
WHERE adate BETWEEN sdate AND edate
) p,
( SELECT prod_code, adate, SUM( bkd_pieces ) bkd_pieces
FROM product_booking, production_date
WHERE adate BETWEEN sdate AND edate
GROUP BY prod_code, adate
) b
WHERE p.prod_code = b.prod_code
AND p.adate = b.adate
AND p.pieces < b.bkd_pieces
ORDER by p.adate asc ) LOOP
RAISE_APPLICATION_ERROR( -20001, aErr.msg );
END LOOP;

END;
/

14:12:10 DTHRASH@zeus9i >select * from product;
more...

PROD_CODE SDATE EDATE PIECES
---------- --------- --------- ----------
PXP 01-JAN-05 31-JAN-05 20

1 row selected.

Elapsed: 00:00:00.86
14:12:15 DTHRASH@zeus9i >select * from product_booking;
more...

T PROD_CODE SDATE EDATE BKD_PIECES
- ---------- --------- --------- ----------
A PXP 01-JAN-05 15-JAN-05 10
B PXP 13-JAN-05 25-JAN-05 5

2 rows selected.

1 insert into product_booking
2* values ( 'C', 'PXP', to_date('01-jan-2005'), to_date('07-jan-2005'), 7 )

1 row created.

Elapsed: 00:00:00.56
14:14:00 DTHRASH@zeus9i >commit;

Commit complete.

14:14:51 DTHRASH@zeus9i >select * from product_booking;
more...

T PROD_CODE SDATE EDATE BKD_PIECES
- ---------- --------- --------- ----------
A PXP 01-JAN-05 15-JAN-05 10
B PXP 13-JAN-05 25-JAN-05 5
C PXP 01-JAN-05 07-JAN-05 7

3 rows selected.

14:15:03 DTHRASH@zeus9i >delete from product_booking where txn_number = 'C';

1 row deleted.

Elapsed: 00:00:00.15
14:15:48 DTHRASH@zeus9i >commit;

Commit complete.

1 insert into product_booking
2* values ( 'D', 'PXP', to_date('01-jan-2005'), to_date('15-jan-2005'), 7 )
insert into product_booking
*
ERROR at line 1:
ORA-20001: Overbooking of PXP by 2 pieces on 13-JAN-2005
ORA-06512: at "DTHRASH.PRODUCT_BOOKING_AIU", line 20
ORA-04088: error during execution of trigger 'DTHRASH.PRODUCT_BOOKING_AIU'



Tom Kyte
February 24, 2005 - 5:20 am UTC

all feature requests must be logged via support -- else they will never get into the system.

I can say 'this would be really good to have', but what we need is for customers to state their desire for a feature with a business case to back it up. That is how you get new features into the database.


The ansi sql standard is by now so huge that I doubt any vendor will fully comply with it at any point in time (opinion). I remember the first copy I saw in the 80's, it was a pamphlet, now it is a multi-volume document.


I cannot really comment on the check constraint in this example (since it doesn't exist) but.... you are missing some things with the trigger.

tell you what, to make the thing simplier to see (why you will have a really hard time enforcing a constraint that CROSSES ROWS in a table or CROSSES tables -- like referential integrity) do this:

try to write a trigger in the manner you did above that makes it so that only one row can be in a table.

Then, open two sessions and in each, insert a row into the table. You'll find your trigger in session 1 won't see session2's insert (perhaps because it hasn't happened) but you'll also find that session 2's trigger won't see session 1's inserted record because we haven't committed.

When you commit both sessions, you'll find from a third session that you have 2 rows in there, but the trigger did fire both times, it just could not see the other sessions data.




Period Query

ANUPAM MANNA, February 24, 2005 - 5:41 am UTC

Hi Tom,
It's good and useful.
My problem is as below:

CREATE TABLE EMP_HOURS
(EMP_ID NUMBER(6),
TRAN_DATE DATE,
HOURS_WORKED NUMBER(4,2),
CONSTRAINT PK_EMP_HOURS PRIMARY KEY(EMP_ID,TRAN_DATE))
/

I want a query which will display all the employees in each date even though

employees didn't book his time on a particular date.If the employee didn't
book time on a particular date then HOURS_WORKED will be shown as 0.You can

consider the period as one month.

INSERT INTO EMP_HOURS(1,'01-jan-2005',8.5);
INSERT INTO EMP_HOURS(1,'03-jan-2005',8);
INSERT INTO EMP_HOURS(1,'04-jan-2005',9.5);
INSERT INTO EMP_HOURS(1,'06-jan-2005',8);
INSERT INTO EMP_HOURS(1,'08-jan-2005',8.5);
INSERT INTO EMP_HOURS(1,'09-jan-2005',9.5);
INSERT INTO EMP_HOURS(1,'10-jan-2005',10.5);
INSERT INTO EMP_HOURS(1,'11-jan-2005',8);
INSERT INTO EMP_HOURS(1,'13-jan-2005',8);
INSERT INTO EMP_HOURS(1,'15-jan-2005',7.6);
INSERT INTO EMP_HOURS(1,'16-jan-2005',6);
INSERT INTO EMP_HOURS(1,'17-jan-2005',8);
INSERT INTO EMP_HOURS(1,'21-jan-2005',8);
INSERT INTO EMP_HOURS(1,'29-jan-2005',5);

INSERT INTO EMP_HOURS(2,'01-jan-2005',9.5);
INSERT INTO EMP_HOURS(2,'02-jan-2005',10);
INSERT INTO EMP_HOURS(2,'06-jan-2005',11);
INSERT INTO EMP_HOURS(2,'09-jan-2005',12);
INSERT INTO EMP_HOURS(2,'10-jan-2005',16);
INSERT INTO EMP_HOURS(2,'11-jan-2005',7.5);
INSERT INTO EMP_HOURS(2,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS(2,'13-jan-2005',9);
INSERT INTO EMP_HOURS(2,'14-jan-2005',10);
INSERT INTO EMP_HOURS(2,'17-jan-2005',8);
INSERT INTO EMP_HOURS(2,'18-jan-2005',8);
INSERT INTO EMP_HOURS(2,'22-jan-2005',8);
INSERT INTO EMP_HOURS(2,'25-jan-2005',8);
INSERT INTO EMP_HOURS(2,'30-jan-2005',8);
INSERT INTO EMP_HOURS(2,'31-jan-2005',8);

INSERT INTO EMP_HOURS(3,'01-jan-2005',10.5);
INSERT INTO EMP_HOURS(2,'02-jan-2005',9);
INSERT INTO EMP_HOURS(2,'04-jan-2005',11);
INSERT INTO EMP_HOURS(2,'06-jan-2005',12);
INSERT INTO EMP_HOURS(2,'8-jan-2005',16);
INSERT INTO EMP_HOURS(2,'10-jan-2005',7.5);
INSERT INTO EMP_HOURS(2,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS(2,'14-jan-2005',9);
INSERT INTO EMP_HOURS(2,'16-jan-2005',10);
INSERT INTO EMP_HOURS(2,'20-jan-2005',4);
INSERT INTO EMP_HOURS(2,'22-jan-2005',8);
INSERT INTO EMP_HOURS(2,'24-jan-2005',8);
INSERT INTO EMP_HOURS(2,'26-jan-2005',11);
INSERT INTO EMP_HOURS(2,'28-jan-2005',5);
INSERT INTO EMP_HOURS(2,'30-jan-2005',6);


Therefore employee 1,2,3 should have output in each date in between the range
'01-jan-2005' to '31-jan-2005'.


Regards,
Anupam Manna

Tom Kyte
February 24, 2005 - 5:52 am UTC

what version.

Period Query

Anupam, February 24, 2005 - 5:52 am UTC

Previous asked Insert statements having problem ...
Here are the corrections

INSERT INTO EMP_HOURS VALUES(1,'01-jan-2005',8.5);
INSERT INTO EMP_HOURS VALUES(1,'03-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'04-jan-2005',9.5);
INSERT INTO EMP_HOURS VALUES(1,'06-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'08-jan-2005',8.5);
INSERT INTO EMP_HOURS VALUES(1,'09-jan-2005',9.5);
INSERT INTO EMP_HOURS VALUES(1,'10-jan-2005',10.5);
INSERT INTO EMP_HOURS VALUES(1,'11-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'13-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'15-jan-2005',7.6);
INSERT INTO EMP_HOURS VALUES(1,'16-jan-2005',6);
INSERT INTO EMP_HOURS VALUES(1,'17-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'21-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(1,'29-jan-2005',5);

INSERT INTO EMP_HOURS VALUES(2,'01-jan-2005',9.5);
INSERT INTO EMP_HOURS VALUES(2,'02-jan-2005',10);
INSERT INTO EMP_HOURS VALUES(2,'06-jan-2005',11);
INSERT INTO EMP_HOURS VALUES(2,'09-jan-2005',12);
INSERT INTO EMP_HOURS VALUES(2,'10-jan-2005',16);
INSERT INTO EMP_HOURS VALUES(2,'11-jan-2005',7.5);
INSERT INTO EMP_HOURS VALUES(2,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS VALUES(2,'13-jan-2005',9);
INSERT INTO EMP_HOURS VALUES(2,'14-jan-2005',10);
INSERT INTO EMP_HOURS VALUES(2,'17-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'18-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'22-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'25-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'30-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(2,'31-jan-2005',8);

INSERT INTO EMP_HOURS VALUES(3,'01-jan-2005',10.5);
INSERT INTO EMP_HOURS VALUES(3,'02-jan-2005',9);
INSERT INTO EMP_HOURS VALUES(3,'04-jan-2005',11);
INSERT INTO EMP_HOURS VALUES(3,'06-jan-2005',12);
INSERT INTO EMP_HOURS VALUES(3,'8-jan-2005',16);
INSERT INTO EMP_HOURS VALUES(3,'10-jan-2005',7.5);
INSERT INTO EMP_HOURS VALUES(3,'12-jan-2005',4.5);
INSERT INTO EMP_HOURS VALUES(3,'14-jan-2005',9);
INSERT INTO EMP_HOURS VALUES(3,'16-jan-2005',10);
INSERT INTO EMP_HOURS VALUES(3,'20-jan-2005',4);
INSERT INTO EMP_HOURS VALUES(3,'22-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(3,'24-jan-2005',8);
INSERT INTO EMP_HOURS VALUES(3,'26-jan-2005',11);
INSERT INTO EMP_HOURS VALUES(3,'28-jan-2005',5);
INSERT INTO EMP_HOURS VALUES(3,'30-jan-2005',6);

Period Query

Anupam, February 24, 2005 - 5:54 am UTC

I am using Oracle 9i earlier version(9.0.1) ...
Please do the needful ...

Tom Kyte
February 24, 2005 - 6:34 am UTC

ops$tkyte@ORA9IR2> with
  2  dates
  3  as
  4  (select to_date(:x,'mon-yyyy')+level-1 dt
  5     from dual
  6    connect by 1=1
  7    and  level <= to_number(to_char(last_day(to_date(:x,'mon-yyyy')),'dd'))
  8  ),
  9  emps
 10  as
 11  (select distinct emp_id from emp_hours -- if you have a table of emps elsewhere, use it
 12  ),
 13  both
 14  as
 15  (select * from dates, emps)
 16  select *
 17    from both, emp_hours
 18   where both.emp_id = emp_hours.emp_id(+)
 19     and both.dt = emp_hours.tran_date(+)
 20  /
 
DT            EMP_ID     EMP_ID TRAN_DATE HOURS_WORKED
--------- ---------- ---------- --------- ------------
01-JAN-05          1          1 01-JAN-05          8.5
02-JAN-05          1
03-JAN-05          1          1 03-JAN-05            8
04-JAN-05          1          1 04-JAN-05          9.5
05-JAN-05          1
06-JAN-05          1          1 06-JAN-05            8
......


You need to generate the set of dates, the set of distinct emp_ids, cartesian product that together to get all of the dates/emp_ids you want and outer join to that the hours.


In 10g, you'll be able to use a partitioned outer join....


ops$tkyte@ORA10G> with
  2  dates
  3  as
  4  (select to_date(:x,'mon-yyyy')+level-1 dt
  5     from dual
  6    connect by 1=1
  7    and  level <= to_number(to_char(last_day(to_date(:x,'mon-yyyy')),'dd'))
  8  )
  9  select *
 10    from emp_hours partition by (emp_id)
 11            right outer join dates on (dates.dt=emp_hours.tran_date)
 12  /
 
    EMP_ID TRAN_DATE HOURS_WORKED DT
---------- --------- ------------ ---------
         1 01-JAN-05          8.5 01-JAN-05
         1                        02-JAN-05
         1 03-JAN-05            8 03-JAN-05
         1 04-JAN-05          9.5 04-JAN-05
         1                        05-JAN-05
         1 06-JAN-05            8 06-JAN-05
         1                        07-JAN-05
....... 

SQL Check Constraints

David R. Thrash, February 24, 2005 - 9:35 pm UTC

Thanks for your reply.
You write: "you will have a really hard time enforcing a constraint that CROSSES ROWS in a table or CROSSES tables...".
I couldn't agree more; enforcing business rules using procedural code is MOST difficult and error prone. Using declarative constraints delegates the enforcement work to the DBMS. The programmer is not concerned about serialization (with respect to the constraint) because the DBMS enforces the constraint AT ALL TIMES.
As you point out, the DBMS has a lot of issues to consider but it is in a much better position to do it efficiently and with less chance of error, than having every programmer write their own routines.
Alas, moving the mountain named Oracle Corp will take more than an enhancement request and one lone wolf. Hopefully forums, like this one, will inform programmers of not only solutions that can be implemented with the current product but also of solutions discussed and debated by many people ( ANSI committee ) much more qualified than me.


Tom Kyte
February 25, 2005 - 4:44 pm UTC

But I hope that means you actually filed the request -- for unless you do, all of the comments out here not withstanding -- it won't stand a chance.

Period Query

Anupam Manna, February 25, 2005 - 12:58 am UTC

Dear Tom,
 
   It's really helpful for me what you have suggested.Thanks for your quick operation.

Please tell me if I make the query like as below:

  1  SELECT P.EMP_ID,P.DT,NVL(Q.HOURS_WORKED,0) WHRS
  2  FROM
  3  (SELECT B.EMP_ID,A.X DT
  4  FROM
  5  (SELECT X FROM
  6  (SELECT TO_DATE('01-JAN-2005','DD/MON/YYYY')+ROWNUM-1 "X"
  7  FROM ALL_OBJECTS WHERE ROWNUM <= 31)) A,
  8  (SELECT DISTINCT EMP_ID FROM EMP_HOURS) B) P,
  9  (SELECT EMP_ID,TRAN_DATE,HOURS_WORKED FROM EMP_HOURS) Q
 10  WHERE P.EMP_ID=Q.EMP_ID(+)
 11* AND P.DT=Q.TRAN_DATE(+)
SQL> /

    EMP_ID DT              WHRS
---------- --------- ----------
         1 01-JAN-05        8.5
         1 02-JAN-05          0
         1 03-JAN-05          8
         2 01-JAN-05        9.5
         2 02-JAN-05         10

    EMP_ID DT              WHRS
---------- --------- ----------
         2 03-JAN-05          0
         2 04-JAN-05          0
         3 01-JAN-05       10.5
         3 02-JAN-05          9
         3 03-JAN-05          0
         3 04-JAN-05         11

    EMP_ID DT              WHRS
---------- --------- ----------
         3 05-JAN-05          0

Is there any peformance hit? 
My second question is related with architecture(9.0.1).

If I have a dedicated Oracle Server with 1GB RAM. I made the parameters as :
db_block_size=16384 bytes = 16KB
db_cache_size=536870912 bytes = 512MB
java_pool_size=33554432 bytes = 32MB
large_pool_size=67108864 bytes = 64MB
shared_pool_reserved_size=4194304 bytes = 4MB
shared_pool_size=33554432 bytes =32MB
log_buffer=8388608 bytes=8MB
sort_area_retained_size=1048576 bytes = 1MB 
sort_area_size=1048576 bytes = 1MB

And all the data tablespace,Temp make local management with 1 MB uniform extent size. 

So there is any problem with this configuration?  

Tom Kyte
February 25, 2005 - 4:57 pm UTC

is there a performance hit?

well, tell me how to evaluate that -- a performance hit is comparable only across two different implementations that do the same thing. Here we have "one" method. So, no -- no performance "hit", just the necessary processing to generate the dates, generate the unique emp_ids (like i said, you probably have them in a table elsewhere and don't need to distinct), generate the cartesian product of them and outer join that to the details.

if the database starts up, there are no problems with the configuration.

Now, is it the best setup for you? No one can answer that without knowing your system a bit better ;) (not asking you to put that here -- just a comment that if there were a 'best' configuration, we would have no parameters..)

Period Query

Anupam, June 08, 2006 - 4:55 pm UTC

Thanks for your response.