Oracle8 Time Series Cartridge User's Guide
Release 8.0.4






Prev Next


The Oracle8 Time Series Cartridge is an extension to Oracle8 that provides storage and retrieval of timestamped data through object types. The cartridge is a building block for applications rather than being an end-user application in itself. It consists of datatypes along with related functions for managing and processing time series data.

For example, applications can use this cartridge to process historical data derived from financial market transactions, such as trades of stocks, bonds, and mutual fund shares. In such applications, the functions included with the Time Series cartridge let you conveniently perform operations ranging from the simple to the complex, such as:

Time series applications have certain distinct requirements and some degree of commonality. The time series datatypes accommodate the commonality and support extensions that address application-specific requirements. With this cartridge, time series data can be managed more conveniently and efficiently than is possible using only traditional datatypes and user-defined functions.

With the Time Series cartridge, you can use or adapt existing tables for time series applications, or you can create new tables. You can also extend the capabilities of the cartridge to add or modify functions and to create customized calendars.

1.1 Data Cartridges

Within the Oracle Network Computing ArchitectureTM (NCA), data cartridges facilitate the storage and retrieval of complex datatypes required by nontraditional database applications, such as geographic information systems, imaging, workflow, document management, and digital libraries. These applications are built using components or modules that support the capture, input, processing, analysis, storage, retrieval, and display of the complex datatypes.

A data cartridge is the mechanism by which clients, application-specific servers, and database servers can be easily and reliably extended. The Oracle8 Time Series Cartridge provides support for time series domain-specific types, functions, and interfaces. The Time Series cartridge focuses on a set of time series data representation and access mechanisms sufficient to support many applications and the development of more specialized cartridges.

1.2 Object Relational Technology

The objects option makes Oracle8 an object-relational database management system, which means that users can define additional kinds of data -- specifying both the structure of the data and the ways of operating on it -- and use these types within the relational model. This approach adds value to the data stored in a database.

Oracle8 with the objects option stores structured business data in its natural form and allows applications to retrieve it that way. For that reason it works efficiently with applications developed using object-oriented programming techniques.

1.3 Storing and Accessing Data

The Time Series cartridge can store time series data in the database under transactional control.

Once stored in the database, this data can be queried and retrieved by finding a row in a table that contains the primary key (which includes the timestamp) using the various alphanumeric columns (attributes) of the table. Typical queries might include the following:

Applications access and manipulate time series data using SQL or PL/SQLTM. See the Oracle8 SQL Reference manual for information on SQL syntax.

1.4 Installing the Kit

The exact procedure for installing the Time Series cartridge is platform-dependent. For more detailed information, see the README.txt file for your platform which can be found either in $ORACLE_HOME/ord/ts/admin (UNIX systems) or $ORACLE_HOME\ord80\ts\admin (Windows NT systems). On many platforms, automated default installation is available using Oracle Installer; however, on some platforms manual installation of the database objects is required.

This section describes the steps required to manually install the Time Series cartridge. It is intended for users who want to customize the database installation or who simply want a better understanding of the Time Series installation process.

To use the Time Series cartridge, at least the following software components must be installed: Oracle8 Enterprise Edition (RDBMS), PL/SQL (on platforms on which it is a separate installation option), and the Oracle8 Time Series Cartridge. These components can be installed all at once, or the Oracle8 Time Series Cartridge can be added to an existing Oracle8 Enterprise Edition release 8.0.4 installation that includes PL/SQL.

Follow these steps to perform a manual installation of the Time Series cartridge:

  1. If the ORDSYS user does not already exist, decide on a password.

    ORDSYS is the standard schema for Oracle-supplied cartridges, and it has special privileges for data cartridges.

  2. Create and start the database.

    For detailed information about database creation and startup, see the Oracle8 Installation and Configuration Guide for your operating system, the Oracle8 Administrator's Guide, and the Oracle8 Concepts manual.

  3. Install the UTLREF package, which is needed by the Time Series cartridge:
    SVRMGR> @$ORACLE_HOME/rdbms/admin/utlrefld.sql
  4. If the ORDSYS account does not already exist, create it:

    The <password> is the password you chose in step 1.

  5. Set the privileges for ORDSYS user:
    SVRMGR> GRANT connect,resource,create library TO ORDSYS;
  6. Install the Time Series cartridge datatypes and stored procedures:
    SVRMGR> @$ORACLE_HOME/ord/ts/admin/ordtinst.sql

    The user group PUBLIC is granted execute privilege on all Time Series cartridge datatypes and packages.

1.5 Creating Public Synonyms for Time Series Packages

All Time Series cartridge packages and datatypes are installed under the ORDSYS schema, and all users must include the ORDSYS schema name when referring to these packages and datatypes. However, to simplify references to packages, you can define public synonyms for the Calendar and TimeSeries packages.

To create public synonyms, run the ordtsyn.sql file supplied with the Time Series cartridge in the admin directory. The ordtsyn.sql file creates the following public synonyms:


1.6 Time Series Cartridge Demos (Demonstrations)

Table 1-1 shows the demos (SQL scripts and related files that demonstrate capabilities) included with the Time Series cartridge. This table includes a description of each demo and the default directory in which its files are installed. (The exact location and directory syntax are platform-dependent.)

Table 1-1 Time Series Cartridge Demos
Description   Directory  

Usage demo for end users and product developers who want to use existing cartridge feature  


Advanced-developer demo for those who want to extend the cartridge features  


OCI demo showing how to call cartridge functions using the Oracle Call Interface  


PRO*C/C++ demo showing how to call cartridge functions in applications created using the Oracle Pro*C/C++ Precompiler  


Developer/2000TM demo showing how to call cartridge functions in an Oracle FormsTM application  


Each demo is described in a README file in its directory.

The usage demo, described in this section, is a working example of the usage of the cartridge. The example models a historical database of stock pricing and provides sample queries over this stock database.

The usage demo is designed to guide you through the cartridge in a step-by-step fashion. It includes example code for creating and populating tables and calendars, constructing security views, constructing views to synthesize the interface to time series functions, and running some example queries.

1.6.1 Running the Usage Demo

After the cartridge has been installed, you can run the usage demo by going to the appropriate directory (see Table 1-1) and invoking the demo.sql procedure, as follows:

% svrmgrl 
SVRMGR> @demo 

1.6.2 Usage Demo Files

The usage demo files include examples of bulk and incremental loading, defining tables, calendars, and views for the time series cartridge, and running example queries. These files are listed in Table 1-2.

Table 1-2 Time Series Cartridge Usage Demo Files
File   Description  


Main procedure file  


SQL*Loader® control file  


SQL*Loader data file for time series data  


DDL for tables  


Defines calendars and populates calendar table  


Example security view  


Example reference-based view  


Example time series queries (SQL)  


Example time series queries (PL/SQL)  


Example calendar queries (PL/SQL)  


Incremental load script  


SQL*Loader control file for incremental load  


SQL*Loader data file for incremental time series data  


Deletes database objects created by the demo  

1.6.3 Tables and Views in the Usage Demo

The stock database consists of three tables:

To maintain time series consistency and provide a collection-based interface for time series functions, two views are constructed using these tables.

The security view ensures that insert, update, and delete operations maintain a time series that is consistent with the associated calendar. (Time series consistency is explained in Section 2.6.) The security view and the reference-based view access the three underlying tables. The reference-based view synthesizes references to collections. (Reference-based views are explained in Section 2.5.2.)

Figure 1-1 shows the layering and interdependencies between underlying tables and the security and reference-based views.

Figure 1-1 Tables and Views in the Time Series Usage Demo


Copyright © 1997 Oracle Corporation.

All Rights Reserved.