Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Checking for Overlaping Date Range

Re: Checking for Overlaping Date Range

From: L120bj <l120bj_at_aol.com>
Date: 1998/01/18
Message-ID: <19980118120901.HAA07483@ladder02.news.aol.com>#1/1

>Subject: Checking for Overlaping Date Range
>From: Thenardier_at_POBoxes.com
>Date: 1/17/98 5:51PM GMT
>Message-id: <885059007.1269104653_at_dejanews.com>
>
>Hi All,
>
>In my program, the users need to enter two dates which
>represent the vacation range, which, supposedly, not
>to overlap with other vacation ranges. If there is vacations
>in the databases, I can check the newly input data against
>them. However, if there is not, i have to check the ranges
>record by record before i commit them. Then the problem
>comes:-
>
>1. I can't use Post-Record, When-Validate, Post-Text-Item
> types of triggers cos i need to loop thru the records
> to check (it involves GO_RECORD(#);
>2. I can't check in Key-Commit trigger before the command
> COMMIT cos i'll use RAISE FORM_TRIGGER_FAILURE to stop
> the program when there is overlapping date range. The
> reason is RAISing such failure activates a dialog box
> which ask me whether i want to save the changes. It
> gives me "Yes, No, and Cancel" options. So, the users can
> save those overlapping dates...
>3. I can't use PL/SQL table which acts as an array in Form.
> Because my version PL/SQL doesn't support such useful
> function.
>
>Then what should i do? Pls rescue me! :P
>Thanks in advance!!!
>
>
>Thenard
>
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
>
>
>
>
>
>
>

You could use option 3 if you create a database package which has the PL/SQL table at package level and a function e.g. add_dates_to_table which accepts thevacation start and end dates as parameters. If the date range is okay the function would add the dates to the PL/SQL table and return zero else it would return 1 say. You could then call this packaged function in a trigger such as POST-RECORD to validate.
Hope this helps,

   Rob Received on Sun Jan 18 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US