Oracle8 Time Series Cartridge User's Guide Release 8.0.4 A57501-01 |
Library | Product | Contents | Index

# 3
Time Series Usage

This chapter explains important procedures related to using the Oracle8 Time Series Cartridge. It covers the following topics:

- using the cartridge (major steps)
- loading time series data
- deriving calendar exceptions from time series data
- using product-developer functions

## 3.1 Using the Cartridge

This section provides a technical overview of using the Time Series cartridge. It presents the major steps, with examples.

For more detailed explanations of the concepts and terminology, see Chapter 2.

### 3.1.1 Step 1: Create the Underlying Storage (Table)

Create the table to hold the time series data. Example 3-1 shows the table definition for a stock trading database.

#### Example 3-1 Create a Stock Data Table

    /* Table Creation (user) */

    CREATE TABLE stockdemo
      (ticker VARCHAR2(5),
      tstamp DATE,
      	open NUMBER,
      high NUMBER,
      low NUMBER,
      close NUMBER,
      volume INTEGER,
      CONSTRAINT pk_stockdemo PRIMARY KEY (ticker, tstamp))
      ORGANIZATION INDEX;

Notes on Example 3-1:

  The table is named stockdemo and has the columns for the ticker (stock symbol), the timestamp (date on which stocks are traded), that day's opening, high, low, and closing prices, and the trading volume.
  The constraint named pk_stockdemo defines the primary key as the ticker plus the timestamp.
  ORGANIZATION INDEX indicates that this is an index-organized table.

The CREATE TABLE statement can also include other keywords, such as TABLESPACE and STORAGE.

### 3.1.2 Step 2: Define a Calendar

If the calendar does not already exist, create it by inserting its definition in a table of calendars. If the table of calendars does not already exist, create it first.

Your calendar will be based on the system-defined datatype ORDTCalendar, which is supplied with the cartridge. ORDTCalendar has the following definition:

    /* System-Defined Calendar Datatype */

    CREATE TYPE ORDSYS.ORDTCalendar AS OBJECT (
      caltype INTEGER,
      name VARCHAR2(256),
      frequency INTEGER,
      pattern ORDSYS.ORDTPattern,
      minDate DATE,
      maxDate DATE,
      offExceptions ORDSYS.ORDTExceptions,
      onExceptions ORDSYS.ORDTExceptions);

The following example creates a table named stockdemo_calendars and defines a calendar named BusinessDays. The BusinessDays calendar includes Mondays through Fridays in 1997, but excludes 04-Jul-1997 and 25-Dec-1997. Explanatory notes follow the example.

#### Example 3-2 Create a Calendar of Business Days

    CREATE TABLE stockdemo_calendars of ORDSYS.ORDTCalendar;

    INSERT INTO stockdemo_calendars
    	VALUES(
       ORDSYS.ORDTCalendar(
           0,
           `BusinessDays',
                  			4,
           			ORDSYS.ORDTPattern(ORDTPatternBits(0,1,1,1,1,1,0),
           						    (to_date(`01-05-97','MM-DD-YY'))),
           				to_date(`01-01-97','MM-DD-YY'),
           			to_date(`01-01-98','MM-DD-YY'),
           ORDSYS.			ORDTExceptions(to_date(`07-04-97','MM-DD-YY'),
           							    to_date(`12-25-97','MM-DD-YY')),
           			NULL));

Notes on Example 3-2:

  stockdemo_calendars is a table of ORDSYS.ORDTCalendar objects. The ORDTCalendar datatype is described in Section 2.2.2.
  0 (zero) for calendar type (caltype) indicates that this is an exception-based calendar. (This is the only calendar type currently supported.)
  BusinessDays is the name of this calendar.
  4 is the frequency code for day.
  The pattern is defined as an excluded occurrence followed by five included occurrences followed by an excluded occurrence (0,1,1,1,1,1,0). Because the frequency is daily and because the anchor date (05-Jan-1997) is a Sunday, Sundays are excluded, Mondays through Fridays are included, and Saturdays are excluded.
  The calendar begins at the start of 01-Jan-1997 and ends at the start of 01-Jan-1998.
  04-Jul-1997 and 25-Dec-1997 are off-exceptions (that is, excluded from the calendar).
  NULL indicates that there are no on-exceptions (that is, no Saturday or Sunday dates to be included in the calendar).

### 3.1.3 Step 3: Load Time Series Data

Perform a bulk load of the time series data in order to populate the underlying data storage tables. Follow the guidelines and instructions for bulk loading in Section 3.2.

### 3.1.4 Step 4: Create a Security View and INSTEAD OF Triggers

Create a security view and INSTEAD OF triggers, to ensure the consistency and integrity of time series data, as explained in Section 2.6.2.

Example 3-3 creates a security view (stockdemo_sv) to get all ticker values.

#### Example 3-3 Create a Security View

    CREATE OR REPLACE VIEW stockdemo_sv AS SELECT * FROM stockdemo;

After you create the view, create INSTEAD OF triggers using the definitions in the securevw.sql demo file as examples or templates. Example 3-4 creates an INSTEAD OF trigger (stockdemo_sv_delete) that ensures the following:

- An exception is raised if a delete operation is attempted on an empty time series.
- For a non-empty time series, a delete operation is allowed on either the first or last timestamp in an existing series.

If you grant users access to the security view and deny access to the underlying tables, you can ensure that all delete operations are checked and performed by the trigger. (Similar INSTEAD OF triggers can be written to allow safe insert and update operations. For more information about using INSTEAD OF triggers with security views, see Section 2.6.2.2.)

#### Example 3-4 Create an INSTEAD OF Trigger

    CREATE OR REPLACE TRIGGER stockdemo_sv_delete
      INSTEAD OF DELETE on stockdemo_sv
      REFERENCING old AS o
      FOR EACH row
      DECLARE
           cal           ORDSYS.ORDTCalendar := NULL;
           purifieddate  DATE;
           startdate     DATE;
           enddate       DATE;
      BEGIN
        --
        -- Retrieve the calendar that maps to the stock ticker.
        --
        BEGIN
        SELECT VALUE(c) INTO cal
          FROM stockdemo_calendars c, stockdemo_metadata m
          WHERE m.tickername = :o.ticker AND c.name = m.calendarname;
        EXCEPTION
          when NO_DATA_FOUND THEN
            raise_application_error(-20000,'Could not find calendar');
        END;
        IF cal IS null THEN
          raise_application_error(-20000, 'NULL calendar found');
        END IF;
        --
        -- Set the precision of timestamp to correspond to the precision
        -- of the calendar.
        --
        purifieddate := ORDSYS.Calendar.SetPrecision(:o.tstamp,cal.frequency);
        --
        -- Retrieve the current startdate AND enddate for the stock ticker;
        --
        SELECT max(tstamp),min(tstamp) INTO enddate,startdate
          FROM stockdemo_sv
          WHERE ticker = :o.ticker;
        --
        -- There are three cases of deletion to consider:
        --
        -- Case 1:  The table does not have any existing time series
        --          entries for the given ticker.  In this case the
        --          trigger raises an exception.
        --
        -- Case 2:  The tstamp is equal to the current startdate.
        --          This routine verifies this and then deletes
        --          the row.
        --
        -- Case 3:  The tstamp is equal to the current enddate.
        --          This routine verifies this and then deletes the row.
        --
        --          If the time series is not empty and if the row being
        --          deleted is not the startdate or enddate, an exception
        --          is raised.
        --
        IF startdate IS null THEN
          raise_application_error(-20000,'Timeseries is empty');
        ELSE
          IF (NOT ((purifieddate = startdate) or (purifieddate = enddate))) THEN
             raise_application_error(-20000, 'Timestamp date not startdate or
                            enddate');
          END IF;
        END IF;
        --
        -- Delete the row in the time series.
        --
        DELETE FROM stockdemo
          WHERE ticker = :o.ticker AND
                tstamp = purifieddate;
      END;
    --
    -- Next, create two other triggers: one update-specific (for
    -- example, stockdemo_sv_update) and the other insert-specific
    -- (for example, stockdemo_sv_insert). See the Time Series
    -- cartridge usage demo for an example.
    -- After creating all appropriate triggers, grant SELECT, DELETE,
    -- UPDATE, and INSERT privileges on the security view to the
    -- appropriate users. For example:
    --
    GRANT SELECT,DELETE,UPDATE,INSERT on stockdemo_sv TO ordtuser;

### 3.1.5 Step 5: Create a Reference-Based View

Create a reference-based view for convenient and efficient access to time series data, as explained in Section 2.5.2.

Example 3-5 creates a reference-based view for stock price data.

#### Example 3-5 Create a Reference-Based View

    CREATE VIEW stockdemo_ts(ticker,open,high,low,close,volume) AS
      SELECT meta.tickername,
      ORDSYS.ORDTNumSeriesIOTRef(
           substr(meta.tickername, 1, 230) || ' open NumSeries',
                  Ref(cal), 'ORDTDEV.stockdemo',
                  'tstamp', 'open', 'ticker', meta.tickername),
      ORDSYS.ORDTNumSeriesIOTRef(
           substr(meta.tickername, 1, 230) || ' high NumSeries',
                  Ref(cal), 'ORDTDEV.stockdemo',
                  'tstamp', 'high', 'ticker', meta.tickername),
      ORDSYS.ORDTNumSeriesIOTRef(
           substr(meta.tickername, 1, 230) || ' low NumSeries',
                   Ref(cal), 'ORDTDEV.stockdemo',
                   'tstamp', 'low', 'ticker', meta.tickername),
      ORDSYS.ORDTNumSeriesIOTRef(
           substr(meta.tickername, 1, 230) || ' close NumSeries',
                   Ref(cal), 'ORDTDEV.stockdemo',
                   'tstamp', 'close', 'ticker', meta.tickername),
      ORDSYS.ORDTNumSeriesIOTRef(
           substr(meta.tickername, 1, 230) || ' volume NumSeries',
                   Ref(cal), 'ORDTDEV.stockdemo',
                   'tstamp', 'volume', 'ticker', meta.tickername)
      FROM stockdemo_metadata meta, stockdemo_calendars cal
      WHERE meta.calendarname = cal.name;

The refvw.sql demo file creates a reference-based view.

### 3.1.6 Step 6: Validate Time Series Consistency

Choose one of the following approaches to ensuring the consistency of time series data, using the guidelines in Section 2.6.3:

- Adjust calendars to be consistent with the time series.

  Use the DeriveExceptions function in adjusting a calendar to be consistent with the time series. See Section 2.2.4 for more information about this approach.

- Validate that each time series is consistent with the calendar.

  Use the IsValidTS function to check that the time series is consistent with the calendar. See the IsValidTS function reference information in Chapter 5.

### 3.1.7 Step 7: Formulate Time Series Queries

Formulating time series queries involves invoking time series or time scaling functions, or both. Example 3-6 uses the Mavg time series function to obtain 30-day moving averages for stock ACME, and it uses the ScaleupSum time scaling function to obtain monthly volumes for stock ACME. (The results shown in the example reflect sample data for the cartridge usage demo.)

The queries in this step use the reference-based view (stockdemo_ts) that was created in step 6.

#### Example 3-6 Formulate Time Series Queries

    SELECT * FROM THE(
         SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
              ORDSYS.TimeSeries.Mavg(close,
                   to_date('11-01-96','MM-DD-YY'),
                   to_date('12-31-96','MM-DD-YY'),
                   10))
              as ORDSYS.ORDTNumtab)
         FROM stockdemo_ts
         WHERE ticker = 'ACME');

    TSTAMP    VALUE
    --------- ----------
    01-NOV-96
    04-NOV-96
    05-NOV-96
    06-NOV-96
    07-NOV-96
    08-NOV-96
    11-NOV-96
    12-NOV-96
    13-NOV-96
    14-NOV-96       63.5
    15-NOV-96       64.5
    18-NOV-96       65.5
    19-NOV-96       66.5
    20-NOV-96       67.5
    21-NOV-96       68.5
    22-NOV-96       69.5
    25-NOV-96       70.5
    26-NOV-96       71.5
    27-NOV-96       72.5
    29-NOV-96       73.5
    02-DEC-96       74.5
    03-DEC-96       75.5
    04-DEC-96       76.5
    05-DEC-96       77.5
    06-DEC-96       78.5
    09-DEC-96       79.5
    10-DEC-96       80.5
    11-DEC-96       81.5
    12-DEC-96       82.5
    13-DEC-96       83.5
    16-DEC-96       84.5
    17-DEC-96       85.5
    18-DEC-96       86.5
    19-DEC-96       87.5
    20-DEC-96       88.5
    23-DEC-96       89.5
    24-DEC-96       90.5
    26-DEC-96       91.5
    27-DEC-96       92.5
    30-DEC-96       93.5
    31-DEC-96       94.5
    41 rows selected.

    SELECT * FROM THE(
         SELECT CAST(ORDSYS.TimeSeries.ExtractTable(
              ORDSYS.TimeSeries.ScaleupSum(volume,value(cal)))
              as ORDSYS.ORDTNumtab)
         FROM stockdemo_ts, stockdemo_calendars cal
         WHERE ticker = 'ACME' AND cal.name = 'MONTHLY');

    TSTAMP    VALUE
    --------- ----------
    01-NOV-96      20000
    01-DEC-96      21000
    2 rows selected.

## 3.2 Loading Time Series Data

This section describes how to use the SQL*Loader utility to perform bulk loading and incremental loading of time series data.

To ensure the consistency of time series data during loading, you must choose one of the approaches described in Section 2.6.3:

- Adjust calendars to be consistent with the time series, if you are sure that all timestamps are correct.

  This strategy is normally appropriate when there is a unique calendar per time series.

- Validate that each time series is consistent with the calendar, if you expect time series data to adhere to a predefined calendar.

  This approach is particularly useful if the same calendar is used for all time series data being loaded.

This section describes how to perform bulk loading using these two approaches, and it also describes how to perform incremental loading.

The loading of time series data is usually performed under controlled circumstances, so it is safe to perform these loads directly to an underlying table instead of to a security view.

### 3.2.1 Bulk Loading

After you create an index-organized table (IOT) to hold time series data (such as for the stockdemo demo database), you must populate the table with data. For a database of stock information, you may need to load millions of rows of daily summary information into the IOT.

SQL*Loader is recommended for loading large amounts of time series data. The following example shows a SQL*Loader script, with an excerpt from the sample data (stockdat.dat) and the SQL*Loader control file (stockdat.ctl). For complete information about SQL*Loader, see the Oracle8 Utilities manual.

The SQL*Loader script contains the following:

    % sqlldr userid=ordtdev/ordtdev control=stockdat.ctl
           log=stockdat.log bad=stockdat.bad errors=1000

The stockdat.dat sample data file includes the following:

    ACME  01-NOV-96  59.00  60.00  58.00  59.00  1000
    ACME  04-NOV-96  60.00  61.00  59.00  60.00  1000
    ACME  05-NOV-96  61.00  62.00  60.00  61.00  1000
             ...

The stockdat.ctl file contains the following

    options (direct=true)
    unrecoverable
    load data
    infile 'stockdat.dat'
    replace
    into table stockdemo
    sorted indexes (StockTabx)
    fields terminated by whitespace
    (ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)

SQL*Loader can handle many file formats and delimiters, as documented in the Oracle8 Utilities manual.

After the load has completed, you may want to choose one of the following approaches for ensuring calendar consistency:

- Adjust calendars to conform to time series data (see Section 3.2.1.1).
- Validate that the time series conforms to the calendar (see Section 3.2.1.2).

In either case, you may need to update the exception lists of your calendars.

#### 3.2.1.1 Adjusting Calendars to Conform to Time Series Data

Often you will want to create calendars that conform to the time series data that you are receiving. In this case, you usually know the frequency and the pattern of a calendar, but not the specific on- or off-exceptions. You can extract these exceptions from the data by using the DeriveExceptions function.

#### 3.2.1.2 Validating That the Time Series Conforms to the Calendar

Often you will want to ensure that the time series data extracted from the incoming data conforms to a predefined calendar. To do this, insert the exceptions either when you create the calendar or afterward with the InsertExceptions functions (or do both, creating the calendar with some exceptions and then adding others); then use the IsValidTimeSeries function to check that the time series is consistent with the calendar.

You can insert exceptions when you define the calendar. For example, the following statement specifies 28-Nov-1996 and 25-Dec-1996 as off-exceptions in the calendar named BUSINESS-96:

    INSERT INTO stockdemo_calendars VALUES(
           ORDSYS.ORDTCalendar(
                 0,
                 'BUSINESS-96',
                 4,
                 ORDSYS.ORDTPattern(
                          ORDSYS.ORDTPatternBits(0,1,1,1,1,1,0),
                               TO_DATE('01-JAN-1995','DD-MON-YYYY')),
                 TO_DATE('01-JAN-1990','DD-MON-YYYY'),
                 TO_DATE('01-JAN-2001','DD-MON-YYYY'),
                 ORDSYS.ORDTExceptions(
                                 TO_DATE('28-NOV-1996','DD-MON-YYYY'),
                                 TO_DATE('25-DEC-1996','DD-MON-YYYY')),
                 ORDSYS.ORDTExceptions()
                 ));

You can also add exceptions after the calendar is defined by using the InsertExceptions function. For example, the following statement adds 01-Jan-1997, 17-Feb-1997, and 26-May-1997 as off-exceptions:

    UPDATE stockdemo_calendars cal
       SET cal = (SELECT ORDSYS.Calendar.InsertExceptions(
                          VALUE(cal),
                          ORDSYS.ORDTDateTab(
                                to_date('01-JAN-97','DD-MON-YY'),
                                to_date('17-FEB-97','DD-MON-YY'),
                                to_date('26-MAY-97','DD-MON-YY')))
              FROM dual)
    	WHERE cal.name = 'BUSINESS-96';

After you have defined the calendar and populated the exception lists, you can use the IsValidTimeSeries function to check that the time series is consistent with the calendar.

### 3.2.2 Incremental Loading

After you have performed the bulk load of time series data and have started using the Time Series cartridge, you will probably want to add data periodically. For example, every trading day after the stock exchange closes, that day's data for each ticker becomes available.

As with bulk loading, incremental loading is typically done in a controlled environment. You know which timestamps will become off-exceptions, and you can explicitly update the exception lists of the appropriate calendars. The following example demonstrates such an update:

    UPDATE stockdemo_calendars cal
     SET cal = (SELECT ORDSYS.Calendar.InsertExceptions(
                             VALUE(cal),
                             to_date('01-JAN-97','DD-MON-YY'))
                 FROM dual)
            WHERE cal.name = 'XCORP';

The SQL*Loader utility is recommended for performing an incremental load of such additional data. The following example shows a SQL*Loader script, with an excerpt from the sample daily data (stockinc.dat) and the SQL*Loader control file (stockinc.ctl).

The SQL*Loader script contains the following:

    sqlldr userid=ordtdev/ordtdev control=stockinc.ctl
            log=stockinc.log bad=stockinc.bad errors=1000

The stockinc.dat sample data file includes the following:

    ACME  02-JAN-97 100.00 101.00  99.00 100.00  1000
    FUNCO 02-JAN-97  25.00  25.00  25.00  25.00  2000
    SAMCO 02-JAN-97  39.00  40.00  38.00  39.50 30000
            ...

The stockinc.ctl file contains the following:

    load data
    infile 'stockinc.dat'
    append
    into table stockdemo
    fields terminated by whitespace
    (ticker, tstamp DATE(13) "DD-MON-YY", open, high, low, close, volume)

Note the following differences in the control file for incremental loading as opposed to bulk loading:

- The conventional path is used instead of the direct path. That is, the control file for incremental loading does not contain the line options (direct=true).

  The conventional path is better for incremental loading because the amount of new data (daily stock information) is small relative to the total amount of data. For an explanation of conventional and direct paths, including situations in which the conventional path is necessary or preferable, see the SQL*Loader documentation in the Oracle8 Utilities manual.

- The APPEND keyword is specified, so that the new data is appended to the existing tabular data.

## 3.3 Deriving Calendar Exceptions

This section explains in greater detail the two approaches to deriving calendar exceptions from time series data. These two approaches were introduced in Section 2.2.4; see that section for information on concepts related to exceptions and the reasons for choosing a particular approach.

### 3.3.1 Deriving Exceptions Using a Calendar and Table of Dates (Approach 1)

The first approach to deriving exceptions takes a calendar and an ORDTDateTab (that is, a table of dates) as input parameters, using the following form of the function:

DeriveExceptions(cal ORDTCalendar, DateTab ORDTDateTab)

The table of dates (DateTab parameter) includes all dates in the time series, for example, all dates on which stock XYZ traded. A calendar is returned that is defined on the same pattern and frequency as the input calendar, and the exception lists of the returned calendar are populated to be consistent with the time series data in DateTab. The exception lists are updated based on finding timestamps that are in the calendar pattern or in the table of dates, but not in both. (A timestamp is in the calendar pattern if it is within the date range of the calendar and maps to an on (1) bit in the pattern.)

The returned calendar's on- and off- exceptions are populated based on the calendar pattern and the table of dates, as follows:

- All timestamps that are in the calendar pattern but not in the table of dates become off-exceptions.

  For example, 04-Jul-1997 (Friday) is in the pattern of a stock trading calendar, but it is not a date on which U.S. stocks were traded.

- All timestamps that are in the table of dates but are not in the calendar pattern become on-exceptions.

The following example derives the exceptions for all time series in the stockdemo table and updates the corresponding calendars in the stockdemo_calendars table:

    UPDATE stockdemo_calendars cal
      SET cal = (SELECT ORDSYS.Calendar.DeriveExceptions(
                    VALUE(cal),
                    CAST(multiset(
                      SELECT s.tstamp
                      FROM stockdemo s
                      WHERE cal.name = s.ticker) AS ORDSYS.ORDTDateTab))
                FROM dual);

This approach (Approach 1) to deriving calendar exceptions has the following requirements:

- The input table of dates must be sorted in ascending timestamp order before the call to the DeriveExceptions function.
- The precision of the timestamps of the dates in the table must conform to the frequency of the input calendar.

### 3.3.2 Deriving Exceptions Using Two Time Series Parameters (Approach 2)

The second approach to deriving exceptions takes two time series references as input parameters, using the following form of the function:

DeriveExceptions(series1 ORDTNumSeriesIOTRef,

series2 ORDTNumSeriesIOTRef)

or

DeriveExceptions(series1 ORDTVarchar2SeriesIOTRef,

series2 ORDTVarchar2SeriesIOTRef)

This overloading of the DeriveExceptions function allows the input parameters to be time series REFs (either two ORDTNumSeriesIOTRef parameters or two ORDTVarchar2SeriesIOTRef parameters).

Before calling DeriveExceptions, you must construct a time series based on a reference calendar. This time series will contain all the timestamps within the date range (minDate through maxDate) of the calendar.

The following example builds a reference time series based on a calendar named PATTERN-ONLY. An INSERT statement populates the time series named PATTERN-ONLY with the valid timestamps between the starting and ending dates of the calendar.

    INSERT INTO stocks(ticker,tstamp)
    SELECT 'PATTERN-ONLY',
           t1.c1
           FROM
           (SELECT column_value c1 FROM the
            (SELECT CAST(ORDSYS.Calendar.TimeStampsBetween(VALUE(cal),
                                                    						cal.mindate,
                                                    						cal.maxdate)
                   AS ORDSYS.ORDTDateTab)
            FROM stock_calendars cal
            WHERE cal.name = 'PATTERN-ONLY')) t1;

The insertion is made directly into the underlying table, not into the security view. Using the underlying table is safe here because the time series is presumed to be correct, so the mechanisms for ensuring consistency between the time series and the calendar provided by the security view are not needed in this case.

The PATTERN-ONLY calendar should have no exceptions. If this calendar has any exceptions, the resulting time series will have non-null exception lists, which will cause the DeriveExceptions function to report an error.

After you create the reference time series, call the DeriveExceptions function with the reference time series as the first parameter (series1). DeriveExceptions compares the dates in series1 with the dates in series2, and it returns the calendar of series1 with the exceptions created as follows:

- All timestamps that are in series1 but not in series2 become off-exceptions.

  For example, if series2 contains dates on which stock XYZ traded and 04-Jul-1997 (Friday) is not in that time series, then 04-Jul-1997 is added to the calendar as an off-exception.

- All timestamps that are in series2 but not in series1 become on-exceptions.

The following example uses the reference time series created in the preceding statement to update the exception lists of every other calendar in the stockdemo_calendars table, with the exceptions for each calendar derived from the timestamps in the associated time series. (This example assumes that each calendar maps to a time series with the same name.)

    UPDATE stockdemo_calendars cal
     SET cal = (SELECT ORDSYS.TimeSeries.DeriveExceptions(ts1.open,ts2.open)
               FROM stocks_ts ts1, stocks_ts ts2
                 WHERE ts1.ticker = 'PATTERN-ONLY' and ts2.ticker = cal.name)
    WHERE cal.name <> 'PATTERN-ONLY';

This approach (Approach 2) to deriving calendar exceptions has the following requirements:

- The input parameters to the DeriveExceptions function must be either two ORDTNumSeriesIOTRef parameters or two ORDTVarchar2SeriesIOTRef parameters. ORDTNumSeries and ORDTVarchar2Series variants are not supported for this function.
- Calendars of the time series input parameters must have the same frequency and pattern.
- The first time series parameter (PATTERN-ONLY time series) must have no exceptions.
- The starting date (minDate) of the calendar of the second time series must be greater (later) than or equal to the starting date of the calendar of the first time series.
- The ending date (maxDate) of the calendar of the second time series must be less (earlier) than or equal to the ending date of the calendar of the first time series.

## 3.4 Using Product-Developer Functions

Product-developer functions, described in Section 2.7.2, let you modify and expand the Time Series cartridge capabilities. For example, an ISV could develop additional time series analysis functions by calling product-developer functions.

The following example shows the use of the IsValidDate, NumTstampsBetween, and OffsetDate product-developer functions in a PL/SQL implementation of the Lead function. The Lead function inputs a time series and a lead_date, and returns a time series where the starting timestamp is the lead_date. (Note that to simplify the presentation, some error checking has been omitted.)

    create function Lead (ts ORDSYS.ORDTNumSeries, lead_date date)
         return ORDSYS.ORDTNumSeries is
    i integer;
    outts ORDSYS.ORDTNumSeries; /* Temporary Storage for Result */
    new_tstamp date;   /* Changeable version of lead_date */
    last_lead_date date; /* Last timestamp of the output time series*/
    first_tstamp date; /* First timestamp of
                          the input time series */
    last_index integer; /* Last index of the input time series */
    last_tstamp date;       /* Last timestamp of the input time series */
    units integer;      /* Number of timestamps between input and
                           output time series */

    ERR_LEAD_TSTAMP_BOUNDS constant integer := 20540;
    ERR_LEAD_TSTAMP_BOUNDS_MSG constant varchar2(100) :=
             'Projected lead timestamp beyond calendar bounds';

    begin
         first_tstamp :=ts.series(1).tstamp;
         last_index :=ts.series.last;
         last_tstamp :=ts.series(last_index).tstamp;

         if ORDSYS.Calendar.IsValidDate(ts.cal, lead_date) = 0 then
               Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS,
                       ERR_LEAD_TSTAMP_BOUNDS_MSG);
         end if;

         /* units is the number of timestamps between the first timestamp of
         the input time series and lead_date. */
         units := ORDSYS.Calendar.NumTimeStampsBetween(ts.cal, first_tstamp,
                  lead_date);

         last_lead_date := ORDSYS.Calendar.OffsetDate(ts.cal, last_tstamp,
                          units);
         if last_lead_date is null then
               Raise_Application_Error(ERR_LEAD_TSTAMP_BOUNDS,
                        ERR_LEAD_TSTAMP_BOUNDS_MSG);
         end if;

         /* Instantiate output time series. */
         outts := ORDSYS.ORDTNumSeries('Lead Result', ts.cal, ORDSYS.ORDTNumTab());
         outts.series.extend(last_index);

         /* Assign the first timestamp of the output time series to
         first_lead_date. Copy value from input time series to output
         time series. */
         new_tstamp := lead_date;
         outts.series(1) := ORDSYS.ORDTNumCell(new_tstamp, ts.series(1).value);

         /* Assign subsequent timestamps by calling OffsetDate with the
         previous date and an offset of 1. */
         for i in 2..outts.series.last loop
              	new_tstamp := ORDSYS.Calendar.OffsetDate(ts.cal,
                outts.series(i-1).tstamp, 1);
              	outts.series(i) := ORDSYS.ORDTNumCell(new_tstamp,
                ts.series(i).value);
         end loop;

         return(outts);
    end;

For other examples of using product-developer functions, see the files for the advanced-developer demo (described briefly in Table 1-1 in Section 1.6).

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index