Oracle8 Time Series Cartridge User's Guide
Release 8.0.4
A57501-01

Library
Product
Contents
Index

Prev Next

4
Calendar Functions: Reference

The Oracle8 Time Series Cartridge library consists of the following:

Two separate reference chapters are provided for the functions, because the functions described in each are typically done at different times in the application development cycle and by people performing different job roles:

Syntax notes:

CombineCals

Format

ORDSYS.Calendar.CombineCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar,

[startDate DATE,

endDate DATE,]

equalFlag OUT INTEGER

) RETURN ORDSYS.ORDTCalendar;

Description

Combines two calendars. The CombineCals function is provided primarily for use in developing functions that operate on two time series (such as the TSAdd function).

Parameters

cal1

The first calendar to be combined.

cal2

The second calendar to be combined.

startDate

Starting date for the resulting calendar. If startDate is not specified, the starting date is the starting date for the calendars, or the higher (later) of the starting dates if they are different.

endDate

Ending date for the resulting calendar. If endDate is not specified, the ending date is the ending date for the calendars, or the lower (earlier) of the ending dates if they are different.

equalFlag

Contains 1 if the input calendars are equal, and 0 if the input calendars are not equal.

Usage

If the frequencies of the two calendars are not equal, the function returns NULL.

If the aligned patterns of the two calendars are not equal, the function returns NULL.

If startDate is not specified, the starting date of the resulting calendar is the later of the starting dates of the two calendars, that is, resulting minDate = max(minDate1, minDate2).

If endDate is not specified, the ending date of the resulting calendar is the earlier of the ending dates of the two calendars, that is, resulting maxDate = min(maxDate1, maxDate2).

The function intersects the on-exception lists of the two calendars. For example, if cal1 has 30-Mar and 29-Jun as on-exceptions and cal2 has 29-Jun and 28-Sep as on-exceptions, the resulting calendar has only 29-Jun as an on-exception.

The function performs a union of the off-exceptions of the two calendars. For example, if cal1 has 01-Jan and 04-Jul as off-exceptions and cal2 has 01-Jan and 14-Jul as off-exceptions, the resulting calendar has 01-Jan, 04-Jul, and 14-Jul as off-exceptions.

CombineCals and IntersectCals differ as follows:

Example

Combine two calendars (GENERIC-CAL1 and GENERIC-CAL2), then intersect the two calendars: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Combine tstCal1 and tstCal2
 resultCal := ORDSYS.Calendar.CombineCals(tstCal1, tstCal2, equalFlag);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of CombineCals')
 INTO dummyVal 
 FROM dual;
 DBMS_OUTPUT.PUT_LINE('equalFlag = ' || equalFlag);

 -- Intersect tstCal1 and tstCal2
 resultCal := ORDSYS.Calendar.IntersectCals(tstCal1, tstCal2);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of IntersectCals') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output: 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1997 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
     01/04/1997 00:00:00     02/09/1997 00:00:00     03/08/1997 00:00:00
     04/05/1997 00:00:00     05/11/1997 00:00:00     06/08/1997 00:00:00
 offExceptions :
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
     01/01/1997 00:00:00     02/12/1997 00:00:00     03/04/1997 00:00:00
     04/07/1997 00:00:00     05/05/1997 00:00:00     06/09/1997 00:00:00

result of CombineCals :

 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
equalFlag = 0

result of IntersectCals :

 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00

DeleteExceptions

Format

ORDSYS.Calendar.DeleteExceptions(

inputCal IN ORDSYS.ORDTCalendar,

delExcDate IN DATE

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.Calendar.DeleteExceptions(

inputCal IN ORDSYS.ORDTCalendar,

delExcTab IN ORDSYS.ORDTDateTab

) RETURN ORDSYS.ORDTCalendar;

Description

Deletes from the specified calendar all exceptions that either match a specified date (delExcDate) or are included in a table of dates (delExcTab), and returns the resulting calendar.

Parameters

inputCal

The calendar from which one or more exceptions are to be deleted.

delExcDate

The date to be deleted from the exceptions of the calendar.

delExcTab

A table of dates to be deleted from the exceptions of the calendar.

Usage

If a date to be deleted is in either the on-exception list or off-exception list of the calendar, the function deletes the date from the appropriate list.

If delExcDate is not in either the on-exception list or off-exception list of the calendar, the function returns the input calendar with no changes.

For any date in delExcTab that is not in either the on-exception list or off-exception list of the calendar, the function ignores the date. If no date in delExcTab is in either the on-exception list or off-exception list of the calendar, the function returns the input calendar with no changes.

Example

Delete some exceptions from a calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDTab ORDSYS.ordtDateTab;
resultCal ORDSYS.ORDTCalendar;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Delete some exceptions in tstCal.
 tstDTab := ORDSYS.ORDTDateTab(
                  '01/21/1996', -- ON  Exception
                  '05/08/1996', -- OFF Exception
                  '08/04/1996', -- ON  Exception
                  '07/09/1996');-- OFF Exception
 SELECT ORDSYS.TimeSeries.Display(tstDTab, 'Input DateTab') 
 INTO dummyVal
 FROM dual;
 resultCal := ORDSYS.Calendar.DeleteExceptions(tstCal, tstDTab);
 SELECT ORDSYS.TimeSeries.Display(resultCal) INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output. The second display of information about GENERIC-CAL1 does not include the deleted on-exceptions and off-exceptions. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Input DateTab :

     01/21/1996 00:00:00     05/08/1996 00:00:00     08/04/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     02/03/1996 00:00:00     03/24/1996 00:00:00     04/27/1996 00:00:00
     05/19/1996 00:00:00     06/23/1996 00:00:00     07/07/1996 00:00:00
     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     06/25/1996 00:00:00

DisplayValCal Procedure

Format

ORDSYS.Calendar.DisplayValCal(

validFlag IN INTEGER,

outMessage IN VARCHAR2,

invOnExc IN ORDSYS.ORDTDateTab,

invOffExc IN ORDSYS.ORDTDateTab,

impOnExc IN ORDSYS.ORDTDateTab,

impOffExc IN ORDSYS.ORDTDateTab,

inputCal IN ORDSYS.ORDTCalendar,

mesg IN VARCHAR2

);

Description

Displays the results returned by the ValidateCal function.

Note:

DisplayValCal is a procedure, not a function. Procedures do not return values.

 

Parameters

validFlag

The return value from the ValidateCal function call:

Value   Meaning  

0  

The calendar is valid. No errors were found.  

1  

Correctable errors were found and corrected. The resulting calendar is valid.  

-1  

Uncorrectable errors were found. The calendar is not valid.  
outMessage

Message output by ValidateCal describing how the calendar was repaired (if the return value = 1) or why the calendar could not be repaired (if the return
value = -1).

invOnExc

Table of the invalid on-exceptions found in the calendar.

invOffExc

Table of the invalid off-exceptions found in the calendar.

impOnExc

Table of the imprecise on-exceptions found in the calendar.

impOffExc

Table of the imprecise off-exceptions found in the calendar.

inputCal

The calendar returned by ValidateCal (repaired if necessary).

mesg

Optional message.

Usage

This procedure is intended to be used with the ValidateCal function. See the information on ValidateCal in this chapter.

Example

Use the IsValidCal and ValidateCal functions and the DisplayValCal procedure with an invalid calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
outMessage  varchar2(32750);
invOnExc    ORDSYS.ORDTDateTab;
invOffExc   ORDSYS.ORDTDateTab;
impOnExc    ORDSYS.ORDTDateTab;
impOffExc   ORDSYS.ORDTDateTab;
dummyval     integer;
validFlag     integer;
tstCal1     ORDSYS.ORDTCalendar := 
               ORDSYS.ORDTCalendar(
                  0,
                  'CALENDAR FOO',
                  4, 
                  ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1,1,1,1,1,0,0),
                                              TO_DATE('01-08-1996 01:01:01')),
                  TO_DATE('01-01-1975'),
		  TO_DATE('01-01-1999'),
                  ORDSYS.ORDTExceptions(
           TO_DATE('02-03-1969'), -- Date < minDate,
           TO_DATE('02-14-1969'), -- Date < minDate,
           TO_DATE('02-03-1999'), -- Date > maxDate,
           TO_DATE('02-17-1999'), -- Date > maxDate,
           TO_DATE('12-31-1995'), -- Maps to 0 in pattern (Sunday)
           TO_DATE('01-13-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-24-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('03-30-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-02-1996 01:01:01'), -- Imprecise
           TO_DATE('03-04-1996 01:01:01'), -- Imprecise 
           TO_DATE('04-05-1996 02:02:02'), -- Imprecise
           TO_DATE('03-25-1996'), -- Valid off-exception
           TO_DATE('01-22-1996'), -- Valid, but out of sequence
           TO_DATE('02-12-1996'),  
           TO_DATE('04-30-1996'),  
           NULL,                  -- Null date
           TO_DATE('02-12-1996'), -- Duplicate date within OFFs
           NULL,                  -- Null date
           TO_DATE('04-30-1996'), -- Duplicate off-exception
           NULL,                  -- Null date
           TO_DATE('03-25-1996'), -- Duplicate off-exception
           TO_DATE('01-22-1996'), -- Duplicate off-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('02-02-1996'),  
           TO_DATE('03-04-1996'),
           TO_DATE('05-06-1997')),
		    ORDSYS.ORDTExceptions(
           TO_DATE('02-08-1969'), -- Date < minDate,
           TO_DATE('02-15-1969'), -- Date < minDate,
           TO_DATE('02-13-1999'), -- Date > maxDate,
           TO_DATE('02-20-1999'), -- Date > maxDate,
           TO_DATE('01-03-1996'), -- Maps to 1 in pattern (Wednesday)
           TO_DATE('02-19-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-18-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('05-27-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-23-1996 01:01:01'), -- Imprecise
           TO_DATE('02-18-1996 01:01:01'), -- Imprecise
           TO_DATE('05-26-1996 01:01:01'), -- Imprecise
           TO_DATE('01-13-1996'), -- Valid on-exception
           TO_DATE('01-14-1996'), -- Valid on-exception
           NULL,                  -- Null date
           NULL,                  -- Null date
           TO_DATE('02-24-1996'), -- Valid on-exception
           TO_DATE('03-23-1996'), -- Valid on-exception
           TO_DATE('01-13-1996'), -- Duplicate on-exception
           TO_DATE('01-14-1996'), -- Duplicate on-exception
           TO_DATE('02-24-1996'), -- Duplicate on-exception
           TO_DATE('03-23-1996'), -- Duplicate on-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('01-06-1996'), -- Valid, but out of sequence
           TO_DATE('02-03-1996'),
           TO_DATE('05-04-1997'))
                  );
BEGIN
   SELECT ORDSYS.TIMESERIES.Display(tstCal1, 'tstCal1') INTO dummyval
   FROM dual;
   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal1);
   IF(validFlag = 0)
   THEN
       validFlag := ORDSYS.CALENDAR.ValidateCal(
                tstCal1, outMessage, invOnExc, invOffExc, impOnExc, impOffExc
                );
 
       ORDSYS.TIMESERIES.DisplayValCal(
             validFlag,
             outMessage,
             invOnExc,
             invOffExc,
             impOnExc,
             impOffExc,
             tstCal1,
             'Your Message'
             );
   END IF;
END;
/

This example might produce the following output: 

tstCal1 :

Calendar Name = CALENDAR FOO
 Frequency = 4
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 01:01:01
 onExceptions  :
     02/08/1969 00:00:00     02/15/1969 00:00:00     02/13/1999 00:00:00
     02/20/1999 00:00:00     01/03/1996 00:00:00     02/19/1996 00:00:00
     03/18/1996 00:00:00     05/27/1996 00:00:00     03/23/1996 01:01:01
     02/18/1996 01:01:01     05/26/1996 01:01:01     01/13/1996 00:00:00
     01/14/1996 00:00:00
     02/24/1996 00:00:00     03/23/1996 00:00:00     01/13/1996 00:00:00
     01/14/1996 00:00:00     02/24/1996 00:00:00     03/23/1996 00:00:00
     01/17/1996 00:00:00     05/28/1996 00:00:00     06/18/1996 00:00:00
     04/23/1996 00:00:00     01/06/1996 00:00:00     02/03/1996 00:00:00
     05/04/1997 00:00:00
 offExceptions :
     02/03/1969 00:00:00     02/14/1969 00:00:00     02/03/1999 00:00:00
     02/17/1999 00:00:00     12/31/1995 00:00:00     01/13/1996 00:00:00
     02/24/1996 00:00:00     03/30/1996 00:00:00     02/02/1996 01:01:01
     03/04/1996 01:01:01     04/05/1996 02:02:02     03/25/1996 00:00:00
     01/22/1996 00:00:00     02/12/1996 00:00:00     04/30/1996 00:00:00
          02/12/1996 00:00:00
     04/30/1996 00:00:00          03/25/1996 00:00:00
     01/22/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00     02/02/1996 00:00:00
     03/04/1996 00:00:00     05/06/1997 00:00:00

DisplayValCal Your Message:

TS-WRN: the input calendar has rectifiable errors. See the message for details

message output by validateCal:

TS-WRN: fixed precision of the pattern anchor date
TS-WRN: removed superfluous dates in the on exception list (refer invalidOnExc)
TS-WRN: fixed imprecise dates in the on exception list (refer impreciseOnExc)
TS-WRN: removed null dates in the on exception list
TS-WRN: sorted the on exceptions list
TS-WRN: removed duplicate dates in the on exceptions list
TS-WRN: removed superfluous dates in off exceptions list (refer invalidOffExc)
TS-WRN: fixed imprecise dates in the off exception list (refer impreciseOffExc)
TS-WRN: removed null dates in the off exception list
TS-WRN: sorted the off exceptions list
TS-WRN: removed duplicate dates in the off exceptions list
TS-WRN: the on exceptions list was trimmed between calendar minDate & maxDate
TS-WRN: the off exceptions list was trimmed between calendar minDate & maxDate

list of invalid on exceptions :

     01/03/1996 00:00:00     02/19/1996 00:00:00     03/18/1996 00:00:00
     05/27/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00

list of invalid off exceptions :

     12/31/1995 00:00:00     01/13/1996 00:00:00     02/24/1996 00:00:00
     03/30/1996 00:00:00

list of imprecise on exceptions :

     03/23/1996 01:01:01     02/18/1996 01:01:01     05/26/1996 01:01:01
list of imprecise off exceptions :

     02/02/1996 01:01:01     03/04/1996 01:01:01     04/05/1996 02:02:02

the validated calendar :

Calendar Name = CALENDAR FOO
 Frequency = 4
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/06/1996 00:00:00     01/13/1996 00:00:00     01/14/1996 00:00:00
     02/03/1996 00:00:00     02/18/1996 00:00:00     02/24/1996 00:00:00
     03/23/1996 00:00:00     05/26/1996 00:00:00     05/04/1997 00:00:00
 offExceptions :
     01/17/1996 00:00:00     01/22/1996 00:00:00     02/02/1996 00:00:00
     02/12/1996 00:00:00     03/04/1996 00:00:00     03/25/1996 00:00:00
     04/05/1996 00:00:00     04/23/1996 00:00:00     04/30/1996 00:00:00
     05/28/1996 00:00:00     06/18/1996 00:00:00     05/06/1997 00:00:00

EqualCals

Format

ORDSYS.Calendar.EqualCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar

[, startDate DATE

, endDate DATE]

) RETURN BINARY_INTEGER;

Description

Checks if two calendars (completely or within a specified date range) are equal.

Parameters

cal1

The first calendar to be checked.

cal2

The second calendar to be checked.

startDate

Starting date for the checking. If startDate is not specified, the starting date is the starting date for the calendars, or the higher (later) of the starting dates if they are different.

endDate

Ending date for the checking. If endDate is not specified, the ending date is the ending date for the calendars, or the lower (earlier) of the ending dates if they are different.

Usage

The function checks if the frequencies, off-exceptions, on-exceptions, and aligned patterns are the same for the two calendars. If they are all the same, the function returns 1; if they are not all the same, the function returns 0.

The function does not require the calendars to have the same starting and ending dates.

Example

Check if two calendars (GENERIC-CAL1 and GENERIC-CAL2) are equal: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Compare tstCal1 and tstCal2 for equality.
 DBMS_OUTPUT.NEW_LINE;
 equalFlag := ORDSYS.Calendar.EqualCals(tstCal1, tstCal2);
 DBMS_OUTPUT.PUT_LINE('EqualCals(GENERIC-CAL1, GENERIC-CAL2) = ' || equalFlag);

END;
/

This example might display the following output. In this example, the returned value of 0 indicates that the calendars are not equal. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1997 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
     01/04/1997 00:00:00     02/09/1997 00:00:00     03/08/1997 00:00:00
     04/05/1997 00:00:00     05/11/1997 00:00:00     06/08/1997 00:00:00
 offExceptions :
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
     01/01/1997 00:00:00     02/12/1997 00:00:00     03/04/1997 00:00:00
     04/07/1997 00:00:00     05/05/1997 00:00:00     06/09/1997 00:00:00

EqualCals(GENERIC-CAL1, GENERIC-CAL2) = 0

GetOffset

Format

ORDSYS.TimeSeries.GetOffset(

inputCal IN ORDSYS.ORDTCalendar,

origin_date IN DATE,

reference_date IN DATE

) RETURN INTEGER;

Description

Given a calendar, one date (origin_date), and another date (reference_date), returns the number of timestamps that the second date is offset from the first.

Parameters

inputCal

The input calendar.

origin_date

Date from which the offset is to be computed.

reference_date

Date whose offset from origin_date is to be returned.

Usage

The function considers the frequency, pattern, and exceptions of the calendar.

The returned integer is positive if reference_date is one or more timestamps in the future with respect to origin_date, and negative if it is in the past with respect to origin_date. For example, assume that the calendar includes Mondays through Fridays, that 04-Jul-1997 (Friday) is an off-exception, and that 03-Jul-1997 (Thursday) is the origin_date. If 10-Jul-1997 (Thursday) is the reference_date, the returned offset is 4; if the reference_date is 01-Jul-1997 (Monday), the returned offset is -2.

If origin_date and reference_date are the same, the function returns 0 (zero).

An exception is returned if the calendar has an empty or null pattern.

Example

Return the offset of 05-Jun-1996 from 04-Mar-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get offset of 05-JUN-1996 from 04-MAR-1996.
 tstDate1 := TO_DATE('04/03/1996');
 tstDate2 := TO_DATE('06/05/1996');
 result  := ORDSYS.Calendar.GetOffset(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('GetOffset(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. In this example, 05-Jun-1996 is 45 timestamps later than 04-Mar-1996. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

GetOffset(04/03/1996 00:00:00 , 06/05/1996 00:00:00) = 45

InsertExceptions

Format

ORDSYS.Calendar.InsertExceptions(

inputCal IN ORDSYS.ORDTCalendar,

newExcDate IN DATE

) RETURN ORDSYS.ORDTCalendar;

or

ORDSYS.Calendar.InsertExceptions(

inputCal IN ORDSYS.ORDTCalendar,

newExcTab IN ORDSYS.ORDTDateTab

) RETURN ORDSYS.ORDTCalendar;

Description

Inserts into the specified calendar all exceptions that either match a specified date (newExcDate) or are included in a table of dates (newExcTab), and returns the resulting calendar.

Parameters

inputCal

The calendar into which one or more exceptions are to be inserted.

newExcDate

The date to be inserted as an exception in the calendar.

newExcTab

A table of dates to be inserted as exceptions in the calendar.

Usage

For each date to be inserted, the function inserts it in the appropriate list (off-exceptions or on-exceptions), according to the frequency and pattern of the calendar.

If a date to be inserted is already an exception in the calendar, the function ignores the request to insert the date.

If newExcDate or newExcTab is empty or null, or if all dates to be inserted already exist in the calendar as exceptions, the function returns the input calendar with no changes.

Example

Insert some exceptions into a calendar. 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDTab ORDSYS.ordtDateTab;
resultCal ORDSYS.ORDTCalendar;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Populate tstDTab with some on- and off-exceptions.
 tstDTab := ORDSYS.ORDTDateTab(
                  '02/10/1996', -- ON  Exception
                  '07/09/1996', -- OFF Exception
                  '03/17/1996', -- ON  Exception
                  '04/08/1996');-- OFF Exception
 SELECT ORDSYS.TimeSeries.Display(tstDTab, 'Input DateTab') 
 INTO dummyVal
 FROM dual;

                   
 -- Insert some exceptions in tstCal.
 resultCal := ORDSYS.Calendar.InsertExceptions(tstCal, tstDTab);
 SELECT ORDSYS.TimeSeries.Display(resultCal) INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output. The second display of information about GENERIC-CAL1 includes the added on-exceptions and off-exceptions. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Input DateTab :

     02/10/1996 00:00:00     07/09/1996 00:00:00     03/17/1996 00:00:00
     04/08/1996 00:00:00

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     02/10/1996 00:00:00
     03/17/1996 00:00:00     03/24/1996 00:00:00     04/27/1996 00:00:00
     05/19/1996 00:00:00     06/23/1996 00:00:00     07/07/1996 00:00:00
     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     04/08/1996 00:00:00     05/08/1996 00:00:00
     06/25/1996 00:00:00     07/09/1996 00:00:00

IntersectCals

Format

ORDSYS.Calendar.IntersectCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar

) RETURN ORDSYS.ORDTCalendar;

Description

Returns the intersection of two calendars.

Parameters

cal1

The first calendar to be intersected.

cal2

The second calendar to be intersected.

Usage

The function performs an intersection of the two input calendars, as follows:

If the frequencies of the two calendars are not equal, the function returns NULL.

Contrast this function with UnionCals, which performs a union of two calendars.

IntersectCals and CombineCals differ as follows:

Example

Combine two calendars (GENERIC-CAL1 and GENERIC-CAL2), then intersect the two calendars: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Combine tstCal1 and tstCal2.
 resultCal := ORDSYS.Calendar.CombineCals(tstCal1, tstCal2, equalFlag);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of CombineCals')
 INTO dummyVal 
 FROM dual;
 DBMS_OUTPUT.PUT_LINE('equalFlag = ' || equalFlag);

 -- Intersect tstCal1 and tstCal2.
 resultCal := ORDSYS.Calendar.IntersectCals(tstCal1, tstCal2);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of IntersectCals') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output: 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1997 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
     01/04/1997 00:00:00     02/09/1997 00:00:00     03/08/1997 00:00:00
     04/05/1997 00:00:00     05/11/1997 00:00:00     06/08/1997 00:00:00
 offExceptions :
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
     01/01/1997 00:00:00     02/12/1997 00:00:00     03/04/1997 00:00:00
     04/07/1997 00:00:00     05/05/1997 00:00:00     06/09/1997 00:00:00

result of CombineCals :

 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
equalFlag = 0

result of IntersectCals :

 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00

InvalidTimeStampsBetween

Format

ORDSYS.Calendar.InvalidTimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN ORDSYS.ORDTDateTab;

Description

Given starting and ending input timestamps, returns a table (ORDTDateTab) containing the invalid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for invalid timestamps.

endDate

Ending date in the range to be checked for invalid timestamps.

Usage

A timestamp is invalid if any of the following conditions is true:

startDate and endDate are included in the check for invalid timestamps.

If there are no invalid timestamps in the date range, the function returns an empty ORDTDateTab.

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with TimeStampsBetween, which returns a table containing the valid timestamps in a date range.

Example

Return a table of invalid timestamps between 03-Mar-1996 and 03-Jun-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
resultDTab ORDSYS.ordtDateTab;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get all the invalid timestamps between 03-MAR-1996 and 03-JUN-1996.
 tstDate1 := TO_DATE('03/03/1996');
 tstDate2 := TO_DATE('06/03/1996');
 resultDTab := ORDSYS.Calendar.InvalidTimeStampsBetween
                                     (tstCal, tstDate1, tstDate2);
 SELECT ORDSYS.TimeSeries.Display(resultDTab, 'InValid timestamps') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output: 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

InValid timestamps :

     03/03/1996 00:00:00     03/05/1996 00:00:00     03/09/1996 00:00:00
     03/10/1996 00:00:00     03/16/1996 00:00:00     03/17/1996 00:00:00
     03/23/1996 00:00:00     03/30/1996 00:00:00     03/31/1996 00:00:00
     04/04/1996 00:00:00     04/06/1996 00:00:00     04/07/1996 00:00:00
     04/13/1996 00:00:00     04/14/1996 00:00:00     04/20/1996 00:00:00
     04/21/1996 00:00:00     04/28/1996 00:00:00     05/04/1996 00:00:00
     05/05/1996 00:00:00     05/08/1996 00:00:00     05/11/1996 00:00:00
     05/12/1996 00:00:00     05/18/1996 00:00:00     05/25/1996 00:00:00
     05/26/1996 00:00:00     06/01/1996 00:00:00     06/02/1996 00:00:00

IsValidCal

Format

ORDSYS.Calendar.IsValidCal(

inputCal IN ORDSYS.ORDTCalendar

) RETURN BINARY_INTEGER

Description

Returns 1 if the calendar is valid and 0 if the calendar is not valid.

Parameters

inputCal

The calendar to be checked for validity.

Usage

A calendar is invalid (not valid) if it contains any errors. This function does not correct any errors or perform any repair operations on the calendar.

Contrast this function with the ValidateCal function, which checks the validity of the calendar and repairs any correctable errors. For detailed information on calendar errors, see the information on ValidateCal in this chapter.

If the IsValidCal function returns 0, you should do the following before you attempt to use the calendar:

  1. Use the ValidateCal function to repair any correctable errors.
  2. If there are any errors that ValidateCal cannot correct, correct these errors yourself.
  3. Repeat steps 1 and 2 as often as necessary until the resulting calendar is valid.

Example

Use the IsValidCal and ValidateCal functions and the DisplayValCal procedure with an invalid calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
outMessage  varchar2(32750);
invOnExc    ORDSYS.ORDTDateTab;
invOffExc   ORDSYS.ORDTDateTab;
impOnExc    ORDSYS.ORDTDateTab;
impOffExc   ORDSYS.ORDTDateTab;
dummyval     integer;
validFlag     integer;
tstCal1     ORDSYS.ORDTCalendar := 
               ORDSYS.ORDTCalendar(
                  0,
                  'CALENDAR FOO',
                  4, 
                  ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1,1,1,1,1,0,0),
                                              TO_DATE('01-08-1996 01:01:01')),
                  TO_DATE('01-01-1975'),
		  TO_DATE('01-01-1999'),
                  ORDSYS.ORDTExceptions(
           TO_DATE('02-03-1969'), -- Date < minDate,
           TO_DATE('02-14-1969'), -- Date < minDate,
           TO_DATE('02-03-1999'), -- Date > maxDate,
           TO_DATE('02-17-1999'), -- Date > maxDate,
           TO_DATE('12-31-1995'), -- Maps to 0 in pattern (Sunday)
           TO_DATE('01-13-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-24-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('03-30-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-02-1996 01:01:01'), -- Imprecise
           TO_DATE('03-04-1996 01:01:01'), -- Imprecise 
           TO_DATE('04-05-1996 02:02:02'), -- Imprecise
           TO_DATE('03-25-1996'), -- Valid off-exception
           TO_DATE('01-22-1996'), -- Valid, but out of sequence
           TO_DATE('02-12-1996'),  
           TO_DATE('04-30-1996'),  
           NULL,                  -- Null date
           TO_DATE('02-12-1996'), -- Duplicate date within OFFs
           NULL,                  -- Null date
           TO_DATE('04-30-1996'), -- Duplicate off-exception
           NULL,                  -- Null date
           TO_DATE('03-25-1996'), -- Duplicate off-exception
           TO_DATE('01-22-1996'), -- Duplicate off-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('02-02-1996'),  
           TO_DATE('03-04-1996'),
           TO_DATE('05-06-1997')),
		    ORDSYS.ORDTExceptions(
           TO_DATE('02-08-1969'), -- Date < minDate,
           TO_DATE('02-15-1969'), -- Date < minDate,
           TO_DATE('02-13-1999'), -- Date > maxDate,
           TO_DATE('02-20-1999'), -- Date > maxDate,
           TO_DATE('01-03-1996'), -- Maps to 1 in pattern (Wednesday)
           TO_DATE('02-19-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-18-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('05-27-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-23-1996 01:01:01'), -- Imprecise
           TO_DATE('02-18-1996 01:01:01'), -- Imprecise
           TO_DATE('05-26-1996 01:01:01'), -- Imprecise
           TO_DATE('01-13-1996'), -- Valid on-exception
           TO_DATE('01-14-1996'), -- Valid on-exception
           NULL,                  -- Null date
           NULL,                  -- Null date
           TO_DATE('02-24-1996'), -- Valid on-exception
           TO_DATE('03-23-1996'), -- Valid on-exception
           TO_DATE('01-13-1996'), -- Duplicate on-exception
           TO_DATE('01-14-1996'), -- Duplicate on-exception
           TO_DATE('02-24-1996'), -- Duplicate on-exception
           TO_DATE('03-23-1996'), -- Duplicate on-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('01-06-1996'), -- Valid, but out of sequence
           TO_DATE('02-03-1996'),
           TO_DATE('05-04-1997'))
                  );
BEGIN
   SELECT ORDSYS.TIMESERIES.Display(tstCal1, 'tstCal1') INTO dummyval
   FROM dual;
   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal1);
   IF(validFlag = 0)
   THEN
       validFlag := ORDSYS.CALENDAR.ValidateCal(
                tstCal1, outMessage, invOnExc, invOffExc, impOnExc, impOffExc
                );
 
       ORDSYS.TIMESERIES.DisplayValCal(
             validFlag,
             outMessage,
             invOnExc,
             invOffExc,
             impOnExc,
             impOffExc,
             tstCal1,
             'Your Message'
             );
   END IF;
END;
/

This example might produce the following output: 

tstCal1 :

Calendar Name = CALENDAR FOO
 Frequency = 4
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 01:01:01
 onExceptions  :
     02/08/1969 00:00:00     02/15/1969 00:00:00     02/13/1999 00:00:00
     02/20/1999 00:00:00     01/03/1996 00:00:00     02/19/1996 00:00:00
     03/18/1996 00:00:00     05/27/1996 00:00:00     03/23/1996 01:01:01
     02/18/1996 01:01:01     05/26/1996 01:01:01     01/13/1996 00:00:00
     01/14/1996 00:00:00
     02/24/1996 00:00:00     03/23/1996 00:00:00     01/13/1996 00:00:00
     01/14/1996 00:00:00     02/24/1996 00:00:00     03/23/1996 00:00:00
     01/17/1996 00:00:00     05/28/1996 00:00:00     06/18/1996 00:00:00
     04/23/1996 00:00:00     01/06/1996 00:00:00     02/03/1996 00:00:00
     05/04/1997 00:00:00
 offExceptions :
     02/03/1969 00:00:00     02/14/1969 00:00:00     02/03/1999 00:00:00
     02/17/1999 00:00:00     12/31/1995 00:00:00     01/13/1996 00:00:00
     02/24/1996 00:00:00     03/30/1996 00:00:00     02/02/1996 01:01:01
     03/04/1996 01:01:01     04/05/1996 02:02:02     03/25/1996 00:00:00
     01/22/1996 00:00:00     02/12/1996 00:00:00     04/30/1996 00:00:00
          02/12/1996 00:00:00
     04/30/1996 00:00:00          03/25/1996 00:00:00
     01/22/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00     02/02/1996 00:00:00
     03/04/1996 00:00:00     05/06/1997 00:00:00

DisplayValCal Your Message:

TS-WRN: the input calendar has rectifiable errors. See the message for details

message output by validateCal:

TS-WRN: fixed precision of the pattern anchor date
TS-WRN: removed superfluous dates in the on exception list (refer invalidOnExc)
TS-WRN: fixed imprecise dates in the on exception list (refer impreciseOnExc)
TS-WRN: removed null dates in the on exception list
TS-WRN: sorted the on exceptions list
TS-WRN: removed duplicate dates in the on exceptions list
TS-WRN: removed superfluous dates in off exceptions list (refer invalidOffExc)
TS-WRN: fixed imprecise dates in the off exception list (refer impreciseOffExc)
TS-WRN: removed null dates in the off exception list
TS-WRN: sorted the off exceptions list
TS-WRN: removed duplicate dates in the off exceptions list
TS-WRN: the on exceptions list was trimmed between calendar minDate & maxDate
TS-WRN: the off exceptions list was trimmed between calendar minDate & maxDate

list of invalid on exceptions :

     01/03/1996 00:00:00     02/19/1996 00:00:00     03/18/1996 00:00:00
     05/27/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00

list of invalid off exceptions :

     12/31/1995 00:00:00     01/13/1996 00:00:00     02/24/1996 00:00:00
     03/30/1996 00:00:00

list of imprecise on exceptions :

     03/23/1996 01:01:01     02/18/1996 01:01:01     05/26/1996 01:01:01
list of imprecise off exceptions :

     02/02/1996 01:01:01     03/04/1996 01:01:01     04/05/1996 02:02:02

the validated calendar :

Calendar Name = CALENDAR FOO
 Frequency = 4
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/06/1996 00:00:00     01/13/1996 00:00:00     01/14/1996 00:00:00
     02/03/1996 00:00:00     02/18/1996 00:00:00     02/24/1996 00:00:00
     03/23/1996 00:00:00     05/26/1996 00:00:00     05/04/1997 00:00:00
 offExceptions :
     01/17/1996 00:00:00     01/22/1996 00:00:00     02/02/1996 00:00:00
     02/12/1996 00:00:00     03/04/1996 00:00:00     03/25/1996 00:00:00
     04/05/1996 00:00:00     04/23/1996 00:00:00     04/30/1996 00:00:00
     05/28/1996 00:00:00     06/18/1996 00:00:00     05/06/1997 00:00:00

IsValidDate

Format

ORDSYS.Calendar.IsValidDate(

inputCal IN ORDSYS.ORDTCalendar,

checkDate IN DATE

) RETURN BINARY_INTEGER;

Description

Checks whether an input date is valid or invalid according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether the input timestamp is valid or invalid.

checkDate

The timestamp to be checked for validity according to the calendar.

Usage

If checkDate is valid, the function returns 1; if checkDate is invalid, the function returns 0.

A timestamp is invalid if any of the following conditions is true:

Example

Check if 02-Jan-1996 is a valid timestamp for a calendar (GENERIC-CAL1): 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;
 
 -- Verify if 02-JAN-1996 (a Monday) is a valid date and display the result.
 tstDate1 := TO_DATE('01/02/1996');
 result  := ORDSYS.Calendar.IsValidDate(tstCal,tstDate1);
 DBMS_OUTPUT.PUT_LINE('IsValidDate(' || tstDate1 || ') = ' || result);

END;
/

This example might produce the following output. In this example, the returned value of 1 indicates that 02-Jan-1996 is a valid timestamp for the BUSINESS-96 calendar. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

IsValidDate(01/02/1996 00:00:00) = 1

NumInvalidTimeStampsBetween

Format

ORDSYS.Calendar.NumInvalidTimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of invalid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for invalid timestamps.

endDate

Ending date in the range to be checked for invalid timestamps.

Usage

A timestamp is invalid if any of the following conditions is true:

startDate and endDate are included in the check for invalid timestamps.

If there are no invalid timestamps in the date range, the function returns 0 (zero).

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with NumTimeStampsBetween, which returns the number of valid timestamps in a date range.

Example

Return the number of invalid timestamps between 03-Feb-1996 and 16-May-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get the number of invalid timestamps between 03-FEB-1996 and 16-MAY-1996.
 tstDate1 := TO_DATE('02/03/1996');
 tstDate2 := TO_DATE('05/16/1996');
 result  := ORDSYS.Calendar.NumInvalidTimeStampsBetween(
                                                   tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumInvalidTimeStampsBetween(' || tstDate1 ||' , ' || 
                                                  tstDate2|| ') = ' || result);
END;
/

This example might produce the following output. In this example, there are 30 invalid timestamps in the specified date range. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumInvalidTimeStampsBetween(02/03/1996 00:00:00 , 05/16/1996 00:00:00) = 30

NumOffExceptions

Format

ORDSYS.Calendar.NumOffExceptions(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of off-exceptions within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used in computing the number of off-exceptions.

startDate

Starting date in the range to be checked for off-exceptions.

endDate

Ending date in the range to be checked for off-exceptions.

Usage

startDate and endDate are included in the check for off-exceptions. (For an explanation of off-exceptions and on-exceptions, see Section 2.2.)

If startDate is greater (later) than endDate, an exception is raised.

Example

Return the number of off-exceptions between 02-Feb-1996 and 07-Jul-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get the number of off-exceptions between 02-FEB-1996 and 07-JUL-1996.
 tstDate1 := TO_DATE('02/02/1996');
 tstDate2 := TO_DATE('07/07/1996');
 result  := ORDSYS.Calendar.NumOffExceptions(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumOffExceptions(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. As the last line of the output indicates, there are five off-exceptions in the specified date range (02-Feb-1996 through 07-Jul-1996). 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumOffExceptions(02/02/1996 00:00:00 , 07/07/1996 00:00:00) = 5

NumOnExceptions

Format

ORDSYS.Calendar.NumOnExceptions(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of on-exceptions within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used in computing the number of on-exceptions.

startDate

Starting date in the range to be checked for on-exceptions.

endDate

Ending date in the range to be checked for on-exceptions.

Usage

startDate and endDate are included in the check for on-exceptions. (For an explanation of off-exceptions and on-exceptions, see Section 2.2.)

If startDate is greater (later) than endDate, an exception is raised.

Example

Return the number of on-exceptions between 02-Feb-1996 and 07-Jul-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;
 
 -- Get the number of ON Exceptions between 02-FEB-1996 and 07-JUL-1996.
 tstDate1 := TO_DATE('02/02/1996');
 tstDate2 := TO_DATE('07/07/1996');
 result  := ORDSYS.Calendar.NumOnExceptions(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumOnExceptions(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. As the last line of the output indicates, there are six on-exceptions in the specified date range (02-Feb-1996 through 07-Jul-1996). 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumOnExceptions(02/02/1996 00:00:00 , 07/07/1996 00:00:00) = 6

NumTimeStampsBetween

Format

ORDSYS.Calendar.NumTimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN INTEGER;

Description

Given starting and ending input timestamps, returns the number of valid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for invalid timestamps.

endDate

Ending date in the range to be checked for invalid timestamps.

Usage

A timestamp is invalid (not valid) if any of the following conditions is true:

startDate and endDate are included in the check for valid timestamps.

If there are no valid timestamps in the date range, the function returns 0 (zero).

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with NumInvalidTimeStampsBetween, which returns the number of invalid timestamps in a date range.

Example

Return the number of valid timestamps between 03-Feb-1996 and 16-May-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
result INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;
 
 -- Get the number of Valid timestamps between 03-FEB-1996 and 16-MAY-1996.
 tstDate1 := TO_DATE('02/03/1996');
 tstDate2 := TO_DATE('05/16/1996');
 result  := ORDSYS.Calendar.NumTimeStampsBetween(tstCal,tstDate1, tstDate2);
 DBMS_OUTPUT.PUT_LINE('NumTimeStampsBetween(' || tstDate1 ||' , ' || tstDate2
                                                  || ') = ' || result);
END;
/

This example might produce the following output. In this example, there are 74 valid timestamps in the specified date range. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

NumTimeStampsBetween(02/03/1996 00:00:00 , 05/16/1996 00:00:00) = 74

OffsetDate

Format

ORDSYS.Calendar.OffsetDate(

inputCal IN ORDSYS.ORDTCalendar,

origin IN DATE,

relOffset IN INTEGER

) RETURN DATE;

Description

Given a reference date (origin) and an offset with respect to the origin (relOffset), returns the timestamp corresponding to the offset input.

Parameters

inputCal

Calendar from which the date is to be returned.

origin

The date to which the offset value (relOffset) is to be applied in computing the returned date.

relOffset

The relative offset of the returned date with respect to the origin.

Usage

The function returns the date of the timestamp at the relOffset number of timestamps from the origin date. If relOffset is positive, the returned date is later than origin; if relOffset is negative, the returned date is earlier than origin. If relOffset is zero (0), the returned date is origin if origin is a valid date; however, if relOffset is zero (0) and origin is not a valid date, the function returns NULL.

For example, assume a Monday through Friday business day calendar for 1997 with 04-Jul-1997 (Friday) defined as an off-exception, and assume that origin is 02-Jul-1997 (Wednesday):

If the origin date is not in the calendar (inputCal), the next later date is used if relOffset is positive or zero, and the next earlier date is used if relOffset is negative. Using the calendar in the preceding example, if origin is specified as 04-Jul-1997 and if relOffset = 2, then 07-Jul-1997 (Monday, the next business day) is used as origin, and the returned date is 09-Jul-1997 (Wednesday).

If the calendar pattern is empty or null, an exception is raised.

Example

Get the dates 20 timestamps later and 20 timestamps earlier than 03-Mar-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
resultDate date;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Offset 03-MAR-1996 by 20.
 tstDate1 := TO_DATE('03/03/1996');
 relOffset := 20;
 resultDate := ORDSYS.Calendar.OffsetDate(tstCal, tstDate1, relOffset);
 DBMS_OUTPUT.PUT_LINE('OffsetDate(' || tstDate1 || ' , ' || relOffset
                                                 || ') = ' || resultDate);
 DBMS_OUTPUT.NEW_LINE;

 -- Offset 03-MAR-1996 by -20.
 tstDate1 := TO_DATE('03/03/1996');
 relOffset := -20;
 resultDate := ORDSYS.Calendar.OffsetDate(tstCal, tstDate1, relOffset);
 DBMS_OUTPUT.PUT_LINE('OffsetDate(' || tstDate1 || ' , ' || relOffset
                                                 || ') = ' || resultDate);
 DBMS_OUTPUT.NEW_LINE;

END;
/

This example might produce the following output. In this example, 29-Mar-1996 is 20 timestamps later than 03-Mar-1996, and 05-Feb-1996 is 20 timestamps earlier than 03-Mar-1996. 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

OffsetDate(03/03/1996 00:00:00 , 20) = 03/29/1996 00:00:00

OffsetDate(03/03/1996 00:00:00 , -20) = 02/05/1996 00:00:00

SetPrecision

Format

ORDSYS.Calendar.SetPrecision(

timestamp IN DATE,

frequency IN INTEGER

) RETURN DATE;

Description

Given a timestamp and a frequency, returns a timestamp that reflects the level of precision implied by the frequency.

Parameters

timestamp

Timestamp whose precision is to be set.

frequency

Frequency to be applied in setting the precision.

Usage

The returned timestamp reflects the precision implied by the frequency, as explained in Section 2.2.1. For example, if the input timestamp is 29-Dec-1997 12:45:00 and frequency is 6 (month), the returned timestamp is 01-Dec-1997 00:00:00. Table 4-1 shows the frequencies, their precision conventions, and the resulting precision if an input timestamp of 19-Sep-1997 09:09:09 is supplied.

Table 4-1 SetPrecision and Timestamp of 19-Sep-1997 09:09:09
Frequency (Every:)   Precision Convention   Result  
second   MM-DD-YYYY HH24:MI:SS   09-19-1997 09:09:09  
minute   MM-DD-YYYY HH24:MI:00   09-19-1997 09:09:00  
hour   MM-DD-YYYY HH24:00:00   09-19-1997 09:00:00  
day   MM-DD-YYYY 00:00:00 (midnight)   09-19-1997 00:00:00  
month   MM-01-YYYY 00:00:00 (midnight of first day of month)   09-01-1997 00:00:00  
year   01-01-YYYY 00:00:00 (midnight of first day of year)   01-01-1997 00:00:00  

If the frequency value is not valid, an exception is raised.

Example

Set the precision of an imprecise timestamp (here, a timestamp containing hour, minute, and second values where the calendar has a day frequency): 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
resultDate date;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

-- Set the precision of an imprecise date.
 tstDate1 := TO_DATE('03/03/1996 01:01:01');
 resultDate := ORDSYS.Calendar.SetPrecision(tstDate1, tstCal.frequency);
 DBMS_OUTPUT.PUT_LINE('SetPrecision(' || 
                              TO_CHAR(tstDate1) ||
                              ' , ' || tstCal.frequency || ') = ' || 
                              TO_CHAR(resultDate) );
END;
/

This example might produce the following output. In this example, the hour, minute, and second components of the timestamp are set to zeroes because the calendar frequency is 4 (day). 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

SetPrecision(03/03/1996 01:01:01 , 4) = 03/03/1996 00:00:00

TimeStampsBetween

Format

ORDSYS.Calendar.TimeStampsBetween(

inputCal IN ORDSYS.ORDTCalendar,

startDate IN DATE,

endDate IN DATE

) RETURN ORDSYS.ORDTDateTab;

Description

Given starting and ending input timestamps, returns a table (ORDTDateTab) containing the valid timestamps within that range according to the specified calendar.

Parameters

inputCal

The calendar to be used to determine whether a timestamp is valid or invalid.

startDate

Starting date in the range to be checked for valid timestamps.

endDate

Ending date in the range to be checked for valid timestamps.

Usage

A timestamp is invalid if any of the following conditions is true:

startDate and endDate are included in the check for valid timestamps.

If there are no valid timestamps in the date range, the function returns an empty ORDTDateTab.

If startDate is greater (later) than endDate, an exception is raised.

Contrast this function with InvalidTimeStampsBetween, which returns a table containing the invalid timestamps in a date range.

Example

Return a table of valid timestamps between 03-Mar-1996 and 03-Jun-1996 in the GENERIC-CAL1 calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal ORDSYS.ORDTCalendar;
tstDate1 DATE;
tstDate2 DATE;
resultDTab ORDSYS.ordtDateTab;
dummyVal INTEGER;
relOffset INTEGER;

BEGIN

 -- Select a calendar (say, GENERIC-CAL1) into tstCal
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';

 -- Display the calendar.
 SELECT ORDSYS.TimeSeries.Display(tstCal) INTO dummyVal FROM dual;
 DBMS_OUTPUT.NEW_LINE;

 -- Get all the valid timestamps between 03-MAR-1996 and 03-JUN-1996.
 tstDate1 := TO_DATE('03/03/1996');
 tstDate2 := TO_DATE('06/03/1996');
 resultDTab := ORDSYS.Calendar.TimeStampsBetween(tstCal, tstDate1, tstDate2);
 SELECT ORDSYS.TimeSeries.Display(resultDTab, 'Valid timestamps') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output: 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Valid timestamps :

     03/04/1996 00:00:00     03/06/1996 00:00:00     03/07/1996 00:00:00
     03/08/1996 00:00:00     03/11/1996 00:00:00     03/12/1996 00:00:00
     03/13/1996 00:00:00     03/14/1996 00:00:00     03/15/1996 00:00:00
     03/18/1996 00:00:00     03/19/1996 00:00:00     03/20/1996 00:00:00
     03/21/1996 00:00:00     03/22/1996 00:00:00     03/24/1996 00:00:00
     03/25/1996 00:00:00     03/26/1996 00:00:00     03/27/1996 00:00:00
     03/28/1996 00:00:00     03/29/1996 00:00:00     04/01/1996 00:00:00
     04/02/1996 00:00:00     04/03/1996 00:00:00     04/05/1996 00:00:00
     04/08/1996 00:00:00     04/09/1996 00:00:00     04/10/1996 00:00:00
     04/11/1996 00:00:00     04/12/1996 00:00:00     04/15/1996 00:00:00
     04/16/1996 00:00:00     04/17/1996 00:00:00     04/18/1996 00:00:00
     04/19/1996 00:00:00     04/22/1996 00:00:00     04/23/1996 00:00:00
     04/24/1996 00:00:00     04/25/1996 00:00:00     04/26/1996 00:00:00
     04/27/1996 00:00:00     04/29/1996 00:00:00     04/30/1996 00:00:00
     05/01/1996 00:00:00     05/02/1996 00:00:00     05/03/1996 00:00:00
     05/06/1996 00:00:00     05/07/1996 00:00:00     05/09/1996 00:00:00
     05/10/1996 00:00:00     05/13/1996 00:00:00     05/14/1996 00:00:00
     05/15/1996 00:00:00     05/16/1996 00:00:00     05/17/1996 00:00:00
     05/19/1996 00:00:00     05/20/1996 00:00:00     05/21/1996 00:00:00
     05/22/1996 00:00:00     05/23/1996 00:00:00     05/24/1996 00:00:00
     05/27/1996 00:00:00     05/28/1996 00:00:00     05/29/1996 00:00:00
     05/30/1996 00:00:00     05/31/1996 00:00:00     06/03/1996 00:00:00

Section 3.3.2 contains an example showing the use of TimeStampsBetween to create a time series for use with the DeriveExceptions function.

UnionCals

Format

ORDSYS.Calendar.UnionCals(

cal1 ORDSYS.ORDTCalendar,

cal2 ORDSYS.ORDTCalendar

) RETURN ORDSYS.ORDTCalendar;

Description

Returns a calendar that is the union of two input calendars.

Parameters

cal1

The first calendar on which the union operation is to be performed.

cal2

The second calendar on which the union operation is to be performed.

Usage

The function performs a union of the two input calendars, as follows:

If the frequencies of the two calendars are not equal, the function returns NULL.

Contrast this function with IntersectCals, which intersects two calendars.

Example

Perform a union of two calendars: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
tstCal1 ORDSYS.ORDTCalendar;
tstCal2 ORDSYS.ORDTCalendar;
resultCal ORDSYS.ORDTCalendar;
equalFlag INTEGER;
dummyVal INTEGER;

BEGIN

 -- Select the calendars GENERIC-CAL1 into tstCal1 
 -- and GENERIC-CAL2 into tstCal2
 -- from stockdemo_calendars.
 SELECT value(cal) INTO tstCal1
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL1';
 SELECT value(cal) INTO tstCal2
 FROM ORDTDEV.stockdemo_calendars cal
 WHERE cal.name = 'GENERIC-CAL2';

 -- Display the calendars tstCal1 and tstCal2.
 SELECT ORDSYS.TimeSeries.Display(tstCal1) INTO dummyVal FROM dual;
 SELECT ORDSYS.TimeSeries.Display(tstCal2) INTO dummyVal FROM dual;

 -- Union tstCal1 and tstCal2.
 resultCal := ORDSYS.Calendar.Unioncals(tstCal1, tstCal2);
 SELECT ORDSYS.TimeSeries.Display(resultCal, 'result of UnionCals') 
 INTO dummyVal 
 FROM dual;

END;
/

This example might produce the following output: 

Calendar Name = GENERIC-CAL1
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          0111110
 patAnchor = 01/07/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
 offExceptions :
     01/08/1996 00:00:00     02/02/1996 00:00:00     03/05/1996 00:00:00
     04/04/1996 00:00:00     05/08/1996 00:00:00     06/25/1996 00:00:00
     07/09/1996 00:00:00

Calendar Name = GENERIC-CAL2
 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1997 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
     01/04/1997 00:00:00     02/09/1997 00:00:00     03/08/1997 00:00:00
     04/05/1997 00:00:00     05/11/1997 00:00:00     06/08/1997 00:00:00
 offExceptions :
     07/09/1996 00:00:00     08/05/1996 00:00:00     09/10/1996 00:00:00
     10/23/1996 00:00:00     11/19/1996 00:00:00     12/12/1996 00:00:00
     01/01/1997 00:00:00     02/12/1997 00:00:00     03/04/1997 00:00:00
     04/07/1997 00:00:00     05/05/1997 00:00:00     06/09/1997 00:00:00

result of UnionCals :

 Frequency = 4
 MinDate = 01/01/1996 00:00:00
 MaxDate = 12/31/1996 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/21/1996 00:00:00     02/03/1996 00:00:00     03/24/1996 00:00:00
     04/27/1996 00:00:00     05/19/1996 00:00:00     06/23/1996 00:00:00
     07/07/1996 00:00:00     08/04/1996 00:00:00     09/15/1996 00:00:00
     10/13/1996 00:00:00     11/10/1996 00:00:00     12/14/1996 00:00:00
 offExceptions :
     07/09/1996 00:00:00

ValidateCal

Format

ORDSYS.Calendar.ValidateCal(

cal INOUT ORDSYS.ORDTCalendar,

outMessage OUT VARCHAR2,

invOnExc OUT ORDTDateTab,

invOffExc OUT ORDTDateTab,

impOnExc OUT ORDTDateTab,

impOffExc OUT ORDTDateTab

) RETURN BINARY_INTEGER;

Description

Validates a calendar and, if necessary, repairs the calendar and outputs information related to the problems and repairs.

Parameters

cal

The calendar to be validated and (if necessary) repaired.

outMessage

Message describing how the calendar was repaired (if the return value = 1) or why the calendar could not be repaired (if the return value = -1).

invOnExc

Table of the invalid on-exceptions found in the calendar.

invOffExc

Table of the invalid off-exceptions found in the calendar.

impOnExc

Table of the imprecise on-exceptions found in the calendar.

impOffExc

Table of the imprecise off-exceptions found in the calendar.

Usage

This function returns one of the following values:

Value   Meaning  

0  

The calendar is valid. No errors were found.  

1  

Correctable errors were found and corrected. The resulting calendar is valid.  

-1  

Uncorrectable errors were found. The calendar is not valid.  

Errors in the input calendar make it invalid. Depending on the error, it may be correctable or uncorrectable. Correctable errors are repaired by the ValidateCal function. If all errors are correctable, the resulting calendar is valid.

For a calendar to be valid, all timestamps in the off-exception and on-exception lists must be consistent with the defined pattern for the calendar. If one or more exception timestamps are not consistent with the pattern, the calendar is invalid. For example, if 04-Jan-1997 (Saturday) is in the off-exception list of a calendar whose pattern includes only Mondays through Fridays as normal business days, 04-Jan-1997 is an invalid off-exception (because as a Saturday is would normally be an "off" day).

Imprecise exception timestamps are repaired. For an explanation of precision, see Section 2.2.1.

Table 4-2 lists correctable errors and the repair actions taken by the ValidateCal function:

Table 4-2 Errors Repaired by ValidateCal
Error   Repair Action  

Imprecise anchor date  

The precision is adjusted.  

Character other than 1 or 0 in the pattern  

All pattern characters other than 0 or 1 are set to 1.  

Imprecise date  

The precision is adjusted.  

Superfluous date (for example, a regular valid date in the on-exceptions list)  

The date is removed from the exceptions list.  

Null date  

The date is removed from the calendar.  

Unsorted dates  

The dates are sorted.  

Duplicate dates in the on-exceptions or off-exceptions list  

Duplicates are removed; the date appears only once in the list.  

Date appearing in both the on-exceptions and off-exceptions lists  

The date is removed from the inappropriate list, depending on the pattern and the anchor date.  

Date outside the date range of the calendar  

The date is removed from the exceptions list.  

The following errors are not correctable. The function returns -1 if one or more of these errors are found:

If the function returns -1, you should not use the calendar until you have fixed the errors that ValidateCal could not fix. Then use ValidateCal again, and use the calendar only if the function returns 0 or 1.

You can use the DisplayValCal procedure to display the information returned by the ValidateCal function. See the information on DisplayValCal in this chapter.

The IsValidCal function (described in this chapter) checks the validity of the calendar but does not perform any repair operations.

Example

Use the IsValidCal and ValidateCal functions and the DisplayValCal procedure with an invalid calendar: 

CONNECT ORDTUSER/ORDTUSER
SET SERVEROUTPUT ON
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

DECLARE
outMessage  varchar2(32750);
invOnExc    ORDSYS.ORDTDateTab;
invOffExc   ORDSYS.ORDTDateTab;
impOnExc    ORDSYS.ORDTDateTab;
impOffExc   ORDSYS.ORDTDateTab;
dummyval     integer;
validFlag     integer;
tstCal1     ORDSYS.ORDTCalendar := 
               ORDSYS.ORDTCalendar(
                  0,
                  'CALENDAR FOO',
                  4, 
                  ORDSYS.ORDTPattern(ORDSYS.ORDTPatternBits(1,1,1,1,1,0,0),
                                              TO_DATE('01-08-1996 01:01:01')),
                  TO_DATE('01-01-1975'),
		  TO_DATE('01-01-1999'),
                  ORDSYS.ORDTExceptions(
           TO_DATE('02-03-1969'), -- Date < minDate,
           TO_DATE('02-14-1969'), -- Date < minDate,
           TO_DATE('02-03-1999'), -- Date > maxDate,
           TO_DATE('02-17-1999'), -- Date > maxDate,
           TO_DATE('12-31-1995'), -- Maps to 0 in pattern (Sunday)
           TO_DATE('01-13-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-24-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('03-30-1996'), -- Maps to 0 in pattern (Saturday)
           TO_DATE('02-02-1996 01:01:01'), -- Imprecise
           TO_DATE('03-04-1996 01:01:01'), -- Imprecise 
           TO_DATE('04-05-1996 02:02:02'), -- Imprecise
           TO_DATE('03-25-1996'), -- Valid off-exception
           TO_DATE('01-22-1996'), -- Valid, but out of sequence
           TO_DATE('02-12-1996'),  
           TO_DATE('04-30-1996'),  
           NULL,                  -- Null date
           TO_DATE('02-12-1996'), -- Duplicate date within OFFs
           NULL,                  -- Null date
           TO_DATE('04-30-1996'), -- Duplicate off-exception
           NULL,                  -- Null date
           TO_DATE('03-25-1996'), -- Duplicate off-exception
           TO_DATE('01-22-1996'), -- Duplicate off-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('02-02-1996'),  
           TO_DATE('03-04-1996'),
           TO_DATE('05-06-1997')),
		    ORDSYS.ORDTExceptions(
           TO_DATE('02-08-1969'), -- Date < minDate,
           TO_DATE('02-15-1969'), -- Date < minDate,
           TO_DATE('02-13-1999'), -- Date > maxDate,
           TO_DATE('02-20-1999'), -- Date > maxDate,
           TO_DATE('01-03-1996'), -- Maps to 1 in pattern (Wednesday)
           TO_DATE('02-19-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-18-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('05-27-1996'), -- Maps to 1 in pattern (Monday)
           TO_DATE('03-23-1996 01:01:01'), -- Imprecise
           TO_DATE('02-18-1996 01:01:01'), -- Imprecise
           TO_DATE('05-26-1996 01:01:01'), -- Imprecise
           TO_DATE('01-13-1996'), -- Valid on-exception
           TO_DATE('01-14-1996'), -- Valid on-exception
           NULL,                  -- Null date
           NULL,                  -- Null date
           TO_DATE('02-24-1996'), -- Valid on-exception
           TO_DATE('03-23-1996'), -- Valid on-exception
           TO_DATE('01-13-1996'), -- Duplicate on-exception
           TO_DATE('01-14-1996'), -- Duplicate on-exception
           TO_DATE('02-24-1996'), -- Duplicate on-exception
           TO_DATE('03-23-1996'), -- Duplicate on-exception
           TO_DATE('01-17-1996'), -- Added to on- and off-exceptions
           TO_DATE('05-28-1996'), -- Added to on- and off-exceptions
           TO_DATE('06-18-1996'), -- Added to on- and off-exceptions
           TO_DATE('04-23-1996'), -- Added to on- and off-exceptions
           TO_DATE('01-06-1996'), -- Valid, but out of sequence
           TO_DATE('02-03-1996'),
           TO_DATE('05-04-1997'))
                  );
BEGIN
   SELECT ORDSYS.TIMESERIES.Display(tstCal1, 'tstCal1') INTO dummyval
   FROM dual;
   validFlag := ORDSYS.CALENDAR.IsValidCal(tstCal1);
   IF(validFlag = 0)
   THEN
       validFlag := ORDSYS.CALENDAR.ValidateCal(
                tstCal1, outMessage, invOnExc, invOffExc, impOnExc, impOffExc
                );
 
       ORDSYS.TIMESERIES.DisplayValCal(
             validFlag,
             outMessage,
             invOnExc,
             invOffExc,
             impOnExc,
             impOffExc,
             tstCal1,
             'Your Message'
             );
   END IF;
END;
/

This example might produce the following output: 

tstCal1 :

Calendar Name = CALENDAR FOO
 Frequency = 4
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 01:01:01
 onExceptions  :
     02/08/1969 00:00:00     02/15/1969 00:00:00     02/13/1999 00:00:00
     02/20/1999 00:00:00     01/03/1996 00:00:00     02/19/1996 00:00:00
     03/18/1996 00:00:00     05/27/1996 00:00:00     03/23/1996 01:01:01
     02/18/1996 01:01:01     05/26/1996 01:01:01     01/13/1996 00:00:00
     01/14/1996 00:00:00
     02/24/1996 00:00:00     03/23/1996 00:00:00     01/13/1996 00:00:00
     01/14/1996 00:00:00     02/24/1996 00:00:00     03/23/1996 00:00:00
     01/17/1996 00:00:00     05/28/1996 00:00:00     06/18/1996 00:00:00
     04/23/1996 00:00:00     01/06/1996 00:00:00     02/03/1996 00:00:00
     05/04/1997 00:00:00
 offExceptions :
     02/03/1969 00:00:00     02/14/1969 00:00:00     02/03/1999 00:00:00
     02/17/1999 00:00:00     12/31/1995 00:00:00     01/13/1996 00:00:00
     02/24/1996 00:00:00     03/30/1996 00:00:00     02/02/1996 01:01:01
     03/04/1996 01:01:01     04/05/1996 02:02:02     03/25/1996 00:00:00
     01/22/1996 00:00:00     02/12/1996 00:00:00     04/30/1996 00:00:00
          02/12/1996 00:00:00
     04/30/1996 00:00:00          03/25/1996 00:00:00
     01/22/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00     02/02/1996 00:00:00
     03/04/1996 00:00:00     05/06/1997 00:00:00

DisplayValCal Your Message:

TS-WRN: the input calendar has rectifiable errors. See the message for details

message output by validateCal:

TS-WRN: fixed precision of the pattern anchor date
TS-WRN: removed superfluous dates in the on exception list (refer invalidOnExc)
TS-WRN: fixed imprecise dates in the on exception list (refer impreciseOnExc)
TS-WRN: removed null dates in the on exception list
TS-WRN: sorted the on exceptions list
TS-WRN: removed duplicate dates in the on exceptions list
TS-WRN: removed superfluous dates in off exceptions list (refer invalidOffExc)
TS-WRN: fixed imprecise dates in the off exception list (refer impreciseOffExc)
TS-WRN: removed null dates in the off exception list
TS-WRN: sorted the off exceptions list
TS-WRN: removed duplicate dates in the off exceptions list
TS-WRN: the on exceptions list was trimmed between calendar minDate & maxDate
TS-WRN: the off exceptions list was trimmed between calendar minDate & maxDate

list of invalid on exceptions :

     01/03/1996 00:00:00     02/19/1996 00:00:00     03/18/1996 00:00:00
     05/27/1996 00:00:00     01/17/1996 00:00:00     05/28/1996 00:00:00
     06/18/1996 00:00:00     04/23/1996 00:00:00

list of invalid off exceptions :

     12/31/1995 00:00:00     01/13/1996 00:00:00     02/24/1996 00:00:00
     03/30/1996 00:00:00

list of imprecise on exceptions :

     03/23/1996 01:01:01     02/18/1996 01:01:01     05/26/1996 01:01:01
list of imprecise off exceptions :

     02/02/1996 01:01:01     03/04/1996 01:01:01     04/05/1996 02:02:02

the validated calendar :

Calendar Name = CALENDAR FOO
 Frequency = 4
 MinDate = 01/01/1975 00:00:00
 MaxDate = 01/01/1999 00:00:00
 patBits:
          1111100
 patAnchor = 01/08/1996 00:00:00
 onExceptions  :
     01/06/1996 00:00:00     01/13/1996 00:00:00     01/14/1996 00:00:00
     02/03/1996 00:00:00     02/18/1996 00:00:00     02/24/1996 00:00:00
     03/23/1996 00:00:00     05/26/1996 00:00:00     05/04/1997 00:00:00
 offExceptions :
     01/17/1996 00:00:00     01/22/1996 00:00:00     02/02/1996 00:00:00
     02/12/1996 00:00:00     03/04/1996 00:00:00     03/25/1996 00:00:00
     04/05/1996 00:00:00     04/23/1996 00:00:00     04/30/1996 00:00:00
     05/28/1996 00:00:00     06/18/1996 00:00:00     05/06/1997 00:00:00


Prev
Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index