Home » SQL & PL/SQL » SQL & PL/SQL » Check Time In Query (Oralce 10g)
| Check Time In Query [message #630660] |
Fri, 02 January 2015 04:10  |
mamalik
Messages: 268
Registered: November 2008
Location: Pakistan
|
Senior Member |
|
|
Dear All,
I have following table for booking of vehicles.
create table vehicle_booking (vehicle_number number,booked_from date,booked_too date)
SET DEFINE OFF;
Insert into VEHICLE_BOOKING
(VEHICLE_NUMBER, BOOKED_FROM, BOOKED_TOO)
Values
(1, TO_DATE('12/01/2014 11:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/01/2014 21:30:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into VEHICLE_BOOKING
(VEHICLE_NUMBER, BOOKED_FROM, BOOKED_TOO)
Values
(1, TO_DATE('01/01/2015 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into VEHICLE_BOOKING
(VEHICLE_NUMBER, BOOKED_FROM, BOOKED_TOO)
Values
(1, TO_DATE('01/01/2015 12:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 13:30:00', 'MM/DD/YYYY HH24:MI:SS'));
Now I have following data.
Select * from Vehicle_Booking
VEHICLE_NUMBER BOOKED_FROM BOOKED_TOO
1 12/1/2014 11:30:00 AM 12/1/2014 9:30:00 PM
1 1/1/2015 1:00:00 PM 1/1/2015 3:30:00 PM
1 1/1/2015 12:30:00 PM 1/1/2015 1:30:00 PM
In above data please note that in third row i have booked vehicle number 1 from time 12:30 to 1:30 Pm but same vehicle is booked from 1:00 Pm To 3:30 Pm in second record, Third record must not be created because this time is between above time in second row.
Please suggest how can i check before inserting new record that the vehicle is already booked in between time or for time which is going to be booked.
Thanks in Advance.
Best Regards,
Asif.
|
|
|
|
|
|
|
|
|
|
| Re: Check Time In Query [message #630664 is a reply to message #630663] |
Fri, 02 January 2015 05:30  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
cookiemonster wrote on Fri, 02 January 2015 16:52That trigger will throw a mutating table error if you ever do an insert select.
Yes, agreed. And not only that, the trigger won't work for concurrent sessions. It will allow the record if the other session is not yet committed. And after committing both the sessions, all the records would already be inserted.
Sorry, it was a bad idea.
|
|
|
|
|
|
|
|
| Re: Check Time In Query [message #630668 is a reply to message #630667] |
Fri, 02 January 2015 05:55 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Littlefoot wrote on Fri, 02 January 2015 17:06you shouldn't abuse your moderator privileges to circumvent it.
You have certainly mistaken. I didn't use any moderator privilege to edit, I was on the same page and edited normally. I submitted my answer at Fri, 02 January 2015 16:47 and I didn't refresh the page, and then simply used Edit and edit was made at Fri, 02 January 2015 16:55.
No issues, I am re-posting the trigger approach. Here it is -
SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS';
Session altered.
SQL>
SQL> DROP TABLE vehicle_booking;
Table dropped.
SQL>
SQL> CREATE TABLE vehicle_booking (vehicle_number NUMBER,booked_from DATE,booked_too DATE);
Table created.
SQL>
SQL> SET DEFINE OFF;
SQL> CREATE OR REPLACE TRIGGER trg_book_time BEFORE
2 INSERT ON vehicle_booking FOR EACH ROW WHEN (new.booked_from IS NOT NULL) DECLARE cnt NUMBER;
3 BEGIN
4 SELECT COUNT(*)
5 INTO cnt
6 FROM vehicle_booking
7 WHERE (:NEW.booked_from BETWEEN BOOKED_FROM AND BOOKED_TOO
8 OR :new.booked_too BETWEEN BOOKED_FROM AND BOOKED_TOO);
9 IF ( cnt > 0 ) THEN
10 raise_application_error(-20000,'Overlapping date ranges');
11 END IF;
12 END;
13 /
Trigger created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> Insert into VEHICLE_BOOKING
2 (VEHICLE_NUMBER, BOOKED_FROM, BOOKED_TOO)
3 Values
4 (1, TO_DATE('12/01/2014 11:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/01/2014 21:30:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into VEHICLE_BOOKING
2 (VEHICLE_NUMBER, BOOKED_FROM, BOOKED_TOO)
3 Values
4 (1, TO_DATE('01/01/2015 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into VEHICLE_BOOKING
2 (VEHICLE_NUMBER, BOOKED_FROM, BOOKED_TOO)
3 VALUES
4 (1, TO_DATE('01/01/2015 12:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 13:30:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into VEHICLE_BOOKING
*
ERROR at line 1:
ORA-20000: Overlapping date ranges
ORA-06512: at "LALIT.TRG_BOOK_TIME", line 9
ORA-04088: error during execution of trigger 'LALIT.TRG_BOOK_TIME'
SQL>
SQL>
SQL> SELECT * FROM vehicle_booking;
VEHICLE_NUMBER BOOKED_FROM BOOKED_TOO
-------------- ------------------- -------------------
1 12/01/2014 11:30:00 12/01/2014 21:30:00
1 01/01/2015 13:00:00 01/01/2015 15:30:00
SQL>
|
|
|
|
|
|
|
|
| Re: Check Time In Query [message #630671 is a reply to message #630670] |
Fri, 02 January 2015 07:04 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Apart from all that, what about a solution? I thought the Rules Manager might do it (example here, http://www.orafaq.com/node/2449) but as it doesn't exist in 12.x perhaps one shouldn't suggest it.
I think a variation on te trigger approach could work. Consider this:create global temporary table vb_tmp as select * from VEHICLE_BOOKING;
CREATE OR REPLACE TRIGGER trg_book_time BEFORE
INSERT ON vb_tmp FOR EACH ROW WHEN (new.booked_from IS NOT NULL) DECLARE cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO cnt
FROM vehicle_booking
WHERE (:NEW.booked_from BETWEEN BOOKED_FROM AND BOOKED_TOO
OR :new.booked_too BETWEEN BOOKED_FROM AND BOOKED_TOO);
IF ( cnt > 0 ) THEN
raise_application_error(-20000,'Overlapping date ranges');
END IF;
insert into VEHICLE_BOOKING values (:new.vehicle_number,:new.booked_from,:new.booked_too);
END;
/
It is all very awkward, though. Isn't there a better way?
--update: corrected the trigger code, it had a change I was using for testing.
[Updated on: Fri, 02 January 2015 07:05] Report message to a moderator |
|
|
|
| Re: Check Time In Query [message #630672 is a reply to message #630671] |
Fri, 02 January 2015 08:00 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
I think in the trigger approach, we should also add the filter on vehicle_number. User should be allowed to book a vehicle having different vehicle_number if not overlapping the interval.
|
|
|
|
| Re: Check Time In Query [message #630675 is a reply to message #630671] |
Fri, 02 January 2015 09:22 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And how does it solve multi-session issue? You still have to serialize somewhere. One possible solution is based on booking rules. For example, I'll assume book from/to time is on quarter-hour:
create table vehicle_booking(
vehicle_number number,
booked_from date,
booked_to date
)
/
create table vehicle_booking_detail(
vehicle_number number,
booked_dt date
)
/
alter table vehicle_booking_detail
add constraint vehicle_booking_detail_pk
primary key(vehicle_number,booked_dt)
/
create or replace
trigger prevent_double_booking
before insert
on vehicle_booking
for each row
begin
insert
into vehicle_booking_detail
select :new.vehicle_number,
:new.booked_from + (level - 1) / 96
from dual
connect by :new.booked_from + (level - 1) / 96 <= :new.booked_to;
end;
/
Single session:
SQL> Insert into VEHICLE_BOOKING
2 Values
3 (1, TO_DATE('12/01/2014 11:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/01/2014 21:30:00',
'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into VEHICLE_BOOKING
2 Values
3 (1, TO_DATE('01/01/2015 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 15:30:00',
'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into VEHICLE_BOOKING
2 Values
3 (1, TO_DATE('01/01/2015 12:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 13:30:00',
'MM/DD/YYYY HH24:MI:SS'));
Insert into VEHICLE_BOOKING
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.VEHICLE_BOOKING_DETAIL_PK) violated
ORA-06512: at "SCOTT.PREVENT_DOUBLE_BOOKING", line 2
ORA-04088: error during execution of trigger 'SCOTT.PREVENT_DOUBLE_BOOKING'
SQL>
Multi-session:
SQL> -- Session 1
SQL> Insert into VEHICLE_BOOKING
2 Values
3 (1, TO_DATE('12/01/2014 11:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/01/2014 21:30:00',
'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL> Insert into VEHICLE_BOOKING
2 Values
3 (1, TO_DATE('01/01/2015 13:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 15:30:00',
'MM/DD/YYYY HH24:MI:SS'));
1 row created.
SQL>
Session 1 didn't commit yet while session 2 issues:
SQL> --Session 2
SQL> Insert into VEHICLE_BOOKING
2 Values
3 (1, TO_DATE('01/01/2015 12:30:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/01/2015 13:30:00',
'MM/DD/YYYY HH24:MI:SS'));
At this point session 2 waits since it is trying inserting vehicle_booking_detail row(s) with same PK value.
Now session 1 commits:
SQL> commit;
Commit complete.
SQL>
This releases locks and session 2 fails with:
Insert into VEHICLE_BOOKING
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.VEHICLE_BOOKING_DETAIL_PK) violated
ORA-06512: at "SCOTT.PREVENT_DOUBLE_BOOKING", line 2
ORA-04088: error during execution of trigger 'SCOTT.PREVENT_DOUBLE_BOOKING'
SQL>
SY.
|
|
|
|
| Re: Check Time In Query [message #630715 is a reply to message #630675] |
Sat, 03 January 2015 09:45 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Here is another good one from Tom. It gets pretty deep, especially when talking about using different concurrency models (whooooo!), but it is the kind of stuff people need to read more.
Of particular note is the idea that you need to serialize your transaction at some point in order to prevent transaction anomalies.
Good luck. Kevin
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 01:43:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
