Release 8.1.5
A67294-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Time Series User's Guide Release 8.1.5 A67294-01 |
|
The Oracle8i Time Series library consists of:
The procedures described in this chapter simplify the task of creating the schema objects (tables, views, triggers, and so forth) required for using Oracle8i Time Series.
For an overview of these procedures and requirements for using them, see Section 2.12. For an example showing the use of several procedures to create a time series group, see Section 3.1. Many of these procedures are used in the quick-start demo, described in Section 1.6.1, and in the retrofit.sql file included with the retrofit demo.
Syntax notes:
select CAST(TimeSeries.ExtractTable(close) AS ORDTNumTab) select cast(TIMESERIES.extracttable(close) as ordtnumtab) select cast(TiMeSeRiEs.eXtRaCtTaBlE(ClosE) As ordtNUMtab)
ORDSYS.TSTools.Add_Existing_Column(
Adds a column attribute from an existing flat table to a time series.
The name of the column attribute to be added to the time series.
Use this procedure when you are creating a time series from an existing flat table. To use this procedure, you must first call the Set_Flat_Attributes procedure and set detail_table_exists to 1.
An exception is raised if Begin_Create_TS_Group has not been called to initialize the context. Standard Oracle exceptions are raised if the number attributes are invalid.
If an exception is raised, call Get_Status to determine if the exception canceled an ongoing Begin_Create_TS_Group sequence.
Create a time series group, specify the appropriate existing tables, and add existing columns to the time series group. (This example is taken in slightly modified form from the retrofit.sql file in the retrofit demo directory.)
DECLARE BEGIN -- -- Establish 'stockdemo_ts' as the time series group name for purposes of the -- administrative tools procedures. -- ORDSYS.TSTools.Begin_Create_TS_Group('stockdemo_ts','flat'); -- -- Assert that the detail, map, and calendar tables exist, -- and define the names for these tables. -- Note that these tables are defined in a separate file. -- Explicitly set the name of the relational view. -- Explicitly set the names of the timestamp and time series name -- columns. -- ordsys.tstools.set_flat_attributes( detail_table_name => 'stockdemo', detail_table_exists => 1, map_table_name => 'stockdemo_metadata', map_table_exists => 1, cal_table_name => 'stockdemo_calendars', cal_table_exists => 1, tstamp_colname => 'tstamp', tsname_colname => 'ticker', rel_view_name => 'stockdemo_sv'); -- -- Tell TSTools the names of existing time series columns -- (as defined for the table stockdemo). -- ORDSYS.TSTools.Add_Existing_Column('open'); ORDSYS.TSTools.Add_Existing_Column('high'); ORDSYS.TSTools.Add_Existing_Column('low'); ORDSYS.TSTools.Add_Existing_Column('close'); ORDSYS.TSTools.Add_Existing_Column('volume'); -- End the specification of schema objects and create the objects. ORDSYS.TSTools.End_Create_TS_Group; exception when others then begin ORDSYS.TSTools.Cancel_Create_TS_Group; raise; end; END; /
ORDSYS.TSTools.Add_Integer_Column(
Adds an integer column attribute to an ongoing flat time series creation specification.
The name of the column attribute to be added to the time series.
An exception is raised if Begin_Create_TS_Group has not been called to initialize the context. Standard Oracle exceptions are raised if the number attributes are invalid.
An exception returned by this procedure might clear the package state. If the package state is cleared, the ongoing Begin_Create_TS_Group sequence is canceled, and you must reissue the complete sequence of administrative tools procedure calls. If the package state is not cleared, the ongoing Begin_Create_TS_Group sequence is not canceled, and you can reissue just the most recent procedure call. You can call Get_Status to determine if an exception cleared the package state.
The following example specifies a flat-model time series named MYTS and adds one VARCHAR2 column (ticker), four NUMBER columns (open, close, low, and high), and one INTEGER column (volume). The End_Create_TS_Group call ends the specification of the time series and creates the schema objects.
DECLARE BEGIN ORDSYS.TSTools.Begin_Create_TS_Group('MYTS','flat'); ORDSYS.TSTools.Add_Varchar2_Column('ticker',10); ORDSYS.TSTools.Add_Number_Column('open'); ORDSYS.TSTools.Add_Number_Column('close'); ORDSYS.TSTools.Add_Number_Column('low'); ORDSYS.TSTools.Add_Number_Column('high'); ORDSYS.TSTools.Add_Integer_Column('volume'); ORDSYS.TSTools.End_Create_TS_Group; END; /
ORDSYS.TSTools.Add_Number_Column(
Adds a number column attribute to an ongoing flat time series creation specification.
The name of the column attribute to be added to the time series.
The precision of the column attribute, that is, the maximum number of digits permitted to the left of the decimal point. Must be between 1 and 38. If colprecision is specified, colscale must also be specified.
The scale of the column attribute, that is, the number of digits to the right of the decimal point. Must be between -84 and 127. If colscale is specified, colprecision must also be specified.
If you specify colprecision, you must also specify colscale. If you specify either colprecision or colscale, you cannot omit the other parameter or specify a null for it. For example, to specify that close (closing price) can have up to 4 digits to the left of the decimal point and 3 digits to the right of the decimal point, specify the following:
ORDSYS.TSTools.Add_Number_Column('close',4,3);
For this definition, the following close values would be valid: 127.25, 9.875, 53, and 27.5.
For this definition, the following close values would be invalid: 12345.6 (exceeds colprecision) and 6.1234 (exceeds colscale).
An exception is raised if Begin_Create_TS_Group has not been called to initialize the context. Standard Oracle exceptions are raised if the number attributes are invalid.
An exception returned by this procedure might clear the package state. If the package state is cleared, the ongoing Begin_Create_TS_Group sequence is canceled, and you must reissue the complete sequence of administrative tools procedure calls. If the package state is not cleared, the ongoing Begin_Create_TS_Group sequence is not canceled, and you can reissue just the most recent procedure call. You can call Get_Status to determine if an exception cleared the package state.
The following example specifies a flat-model time series named MYTS and adds one VARCHAR2 column (ticker), four NUMBER columns (open, close, low, and high), and one INTEGER column (volume). The End_Create_TS_Group call ends the specification of the time series and creates the schema objects.
DECLARE BEGIN ORDSYS.TSTools.Begin_Create_TS_Group('MYTS','flat'); ORDSYS.TSTools.Add_Varchar2_Column('ticker',10); ORDSYS.TSTools.Add_Number_Column('open'); ORDSYS.TSTools.Add_Number_Column('close'); ORDSYS.TSTools.Add_Number_Column('low'); ORDSYS.TSTools.Add_Number_Column('high'); ORDSYS.TSTools.Add_Integer_Column('volume'); ORDSYS.TSTools.End_Create_TS_Group; END; /
ORDSYS.TSTools.Add_Varchar2_Column(
Adds a VARCHAR2 column attribute to an ongoing flat time series creation specification.
The name of the column attribute to be added to the time series.
The name of the column attribute to be added to the time series. Must be between 1 and 4000.
An exception is raised if Begin_Create_TS_Group has not been called to initialize the context. Standard Oracle exceptions are raised if the number attributes are invalid.
An exception returned by this procedure might clear the package state. If the package state is cleared, the ongoing Begin_Create_TS_Group sequence is canceled, and you must reissue the complete sequence of administrative tools procedure calls. If the package state is not cleared, the ongoing Begin_Create_TS_Group sequence is not canceled, and you can reissue just the most recent procedure call. You can call Get_Status to determine if an exception cleared the package state.
The following example specifies a flat-model time series named MYTS and adds one VARCHAR2 column (ticker), four NUMBER columns (open, close, low, and high), and one INTEGER column (volume). The End_Create_TS_Group call ends the specification of the time series and creates the schema objects.
DECLARE BEGIN ORDSYS.TSTools.Begin_Create_TS_Group('MYTS','flat'); ORDSYS.TSTools.Add_Varchar2_Column('ticker',10); ORDSYS.TSTools.Add_Number_Column('open'); ORDSYS.TSTools.Add_Number_Column('close'); ORDSYS.TSTools.Add_Number_Column('low'); ORDSYS.TSTools.Add_Number_Column('high'); ORDSYS.TSTools.Add_Integer_Column('volume'); ORDSYS.TSTools.End_Create_TS_Group; END; /
ORDSYS.TSTools.Begin_Create_TS_Group(
Initiates the context for creating a time series group (the schema objects for a time series).
Name of the time series group to be created.
Storage model for the time series. Must be 'FLAT' or 'OBJECT' (not case sensitive).
To avoid possible naming conflicts, name should be different from any other object names under the current schema. (For example, user SCOTT should not create a time series group named EMP because there is already a table with that name.)
This procedure returns an error if the context for creating time series schema objects is active, that is, has been initiated and not canceled or closed.
The following example specifies a flat-model time series named MYTS and adds one VARCHAR2 column (ticker), four NUMBER columns (open, close, low, and high), and one INTEGER column (volume). The End_Create_TS_Group call ends the specification of the time series and creates the schema objects.
DECLARE BEGIN ORDSYS.TSTools.Begin_Create_TS_Group('MYTS','flat'); ORDSYS.TSTools.Add_Varchar2_Column('ticker',10); ORDSYS.TSTools.Add_Number_Column('open'); ORDSYS.TSTools.Add_Number_Column('close'); ORDSYS.TSTools.Add_Number_Column('low'); ORDSYS.TSTools.Add_Number_Column('high'); ORDSYS.TSTools.Add_Integer_Column('volume'); ORDSYS.TSTools.End_Create_TS_Group; END; /
ORDSYS.TSTools.Cancel_Create_TS_Group;
Cancels the creation of a time series group, that is, cancels the context initiated by the Begin_Create_TS_Group procedure.
None.
This procedure clears all package state information that was created by Begin_Create_TS_Group and other Oracle8i Time Series administrative tools procedures. To create a time series group, you must reissue the complete sequence of administrative tools procedure calls.
The following example cancels the creation of the current time series group if an exception occurs:
... ORDSYS.TSTools.End_Create_TS_Group; exception when others then begin ORDSYS.TSTools.Cancel_Create_TS_Group; raise; end; ...
ORDSYS.TSTools.Close_Log;
Closes the log file that had been opened by the Open_Log procedure.
None.
This procedure is equivalent to calling UTL_FILE.FCLOSE. For information on the PL/SQL file I/O procedure UTL_FILE, see the Oracle8i Supplied Packages Reference manual.
The log file (Open_Log...Close_Log) and the debug display (Trace_On...Trace_Off) contain the same information.
The following example opens a log file named ts1.log in the logdir directory, creates time series schema objects, and closes the log file:
... ORDSYS.TSTools.Open_Log('logdir','ts1.log'); ORDSYS.TSTools.Begin_Create_TS_Group('myts','flat'); ... ORDSYS.TSTools.End_Create_TS_Group; ORDSYS.TSTools.Close_Log; ...
ORDSYS.TSTools.Display_Attributes:
Displays information about the time series group being created.
None.
This procedure displays the current values of all attributes that can be set using the Set_xxx function (Set_Flat_Attributes or Set_Object_Attributes) appropriate for the current type of time series group.
The output is displayed to SERVEROUTPUT.
The following example displays the attributes for the time series being created:
ORDSYS.TSTools.Display_Attributes;
This example might produce the following output:
current settings for begin_create_ts_group NAME = MYTS STORAGE_MODEL = FLAT SCHEMA = TSDEV REL_VIEW_NAME = MYTS_RVW DETAIL_TABLE_NAME = MYTS_TAB DETAIL_TABLE_ATTR = ORGANIZATION INDEX DETAIL_TABLE_PK = MYTS_TPK DETAIL_TABLE_EXISTS = 0 TSTAMP_COLNAME = TSTAMP TSNAME_COLNAME = TSNAME TSNAME_LENGTH = 25 MAP_TABLE_NAME = MYTS_MAP MAP_TABLE_ATTR = MAP_TABLE_PK = MYTS_MPK MAP_TABLE_EXISTS = 0 CAL_TABLE_NAME = MYTS_CAL CAL_TABLE_ATTR = CAL_TABLE_PK = MYTS_CPK CAL_TABLE_EXISTS = 0 REL_VIEW_TRIGGER_NAME = MYTS_TR ----------------------------------------- COLUMN NAME = TICKER TYPE = VARCHAR2 LENGTH = 10 PRECISION = SCALE = COLUMN NAME = OPEN TYPE = NUMBER LENGTH = 22 PRECISION = SCALE = COLUMN NAME = CLOSE TYPE = NUMBER LENGTH = 22 PRECISION = SCALE = COLUMN NAME = LOW TYPE = NUMBER LENGTH = 22 PRECISION = SCALE = COLUMN NAME = HIGH TYPE = NUMBER LENGTH = 22 PRECISION = SCALE = COLUMN NAME = VOLUME TYPE = NUMBER LENGTH = 22 PRECISION = SCALE =
ORDSYS.TSTools.Drop_TS_Group(
Deletes the time series group definition and views associated with it. However, the underlying tables (calendar tables, detail data tables, and so on) are not deleted.
Name of the time series group to be deleted.
The schema (user) where the name objects are located. The default is the current schema.
Contrast this procedure with Drop_TS_Group_All, which deletes all the underlying tables. For example, if you have an existing time series table filled with data and want to add a column, you could use Drop_TS_Group as follows:
If an attempt to delete a specific object fails, an exception is raised and the procedure attempts to delete any remaining appropriate objects.
To delete time series schema objects that were not created by the current user, you must have been granted the DBA or TIMESERIES_DBA role.
The following example deletes the schema objects, but not the underlying tables, for the time series group MYTS:
DECLARE BEGIN ORDSYS.TSTools.Drop_TS_Group('MYTS'); exception when others then raise; END; /
ORDSYS.TSTools.Drop_TS_Group_All(
Deletes the time series group definition and all tables, views, indexes, constraints, and triggers associated with it.
Name of the time series group to be deleted.
The schema (user) where the name objects are located. The default is the current schema.
Contrast this procedure with Drop_TS_Group, which does not delete the underlying tables.
If an attempt to delete a specific object fails, an exception is raised and the procedure attempts to delete any remaining appropriate objects.
To delete time series schema objects that were not created by the current user, you must have been granted the DBA or TIMESERIES_DBA role.
The following example deletes all schema objects, including underlying tables, for the time series group MYTS:
DECLARE BEGIN ORDSYS.TSTools.Drop_TS_Group_All('MYTS'); exception when others then raise; END; /
ORDSYS.TSTools.End_Create_TS_Group(
Closes the context established by the Begin_Create_TS_Group procedure and creates all appropriate schema objects.
Optional comment or other information; will be included in the log if logging is in effect.
An exception is raised if the time series being created is missing any required elements. For example, at least one column must be specified.
The following example specifies a flat-model time series named MYTS and adds one VARCHAR2 column (ticker), four NUMBER columns (open, close, low, and high), and one INTEGER column (volume). The End_Create_TS_Group call ends the specification of the time series and creates the schema objects.
DECLARE BEGIN ORDSYS.TSTools.Begin_Create_TS_Group('MYTS','flat'); ORDSYS.TSTools.Add_Varchar2_Column('ticker',10); ORDSYS.TSTools.Add_Number_Column('open'); ORDSYS.TSTools.Add_Number_Column('close'); ORDSYS.TSTools.Add_Number_Column('low'); ORDSYS.TSTools.Add_Number_Column('high'); ORDSYS.TSTools.Add_Integer_Column('volume'); ORDSYS.TSTools.End_Create_TS_Group; END; /
ORDSYS.TSTools.Get_Flat_attributes(
tstamp_colname OUT VARCHAR2,
tsname_colname OUT VARCHAR2,
tsname_length OUT NUMBER,
rel_view_name OUT VARCHAR2,
detail_table_name OUT VARCHAR2,
detail_table_attr OUT VARCHAR2,
detail_table_pk OUT VARCHAR2,
detail_table_exists OUT INTEGER,
map_table_name OUT VARCHAR2,
map_table_attr OUT VARCHAR2,
map_table_pk OUT VARCHAR2,
map_table_exists OUT VARCHAR2,
cal_table_name OUT VARCHAR2,
cal_table_attr OUT VARCHAR2,
cal_table_pk OUT VARCHAR2,
cal_table_exists OUT INTEGER
rv_trigger_name OUT VARCHAR2);
Retrieves the attributes of a flat time series.
Name of the timestamp column.
Name of the column that identifies a time series instance.
Length of tsname_colname.
Name of the relational view created on the underlying (detail) table identified by detail_table_name.
Name of the table containing the composite data.
Attributes of the table identified by detail_table_name.
Primary key for the table identified by detail_table_name.
Contains 1 if the table identified by detail_table_name exists; contains 0 if this table does not exist.
Name of the table that maps time series to calendars.
Attributes of the table identified by map_table_name.
Primary key for the table identified by map_table_name.
Contains 1 if the table identified by map_table_name exists; contains 0 if this table does not exist.
Name of the table containing the calendar definitions.
Attributes of the table identified by cal_table_name.
Primary key for the table identified by cal_table_name.
Contains 1 if the table identified by cal_table_name exists; contains 0 if this table does not exist.
Name of the INSTEAD OF trigger for insert, update, or delete operations on the relational view.
This procedure returns the attributes into variables that you specify. If you simply want to display the attributes of the time series being created, you can use the Display_Attributes procedure.
To return the attributes of an object-model time series, use the Get_Object_Attributes procedure.
The following example gets the attributes of the flat time series being created:
DECLARE tstamp_colname VARCHAR2(30); tsname_colname VARCHAR2(30); tsname_length NUMBER; rel_view_name VARCHAR2(30); detail_table_name VARCHAR2(30); detail_table_attr VARCHAR2(30); detail_table_pk VARCHAR2(30); detail_table_exists INTEGER; map_table_name VARCHAR2(30); map_table_attr VARCHAR2(30); map_table_pk VARCHAR2(30); map_table_exists INTEGER; cal_table_name VARCHAR2(30); cal_table_attr VARCHAR2(30); cal_table_pk VARCHAR2(30); cal_table_exists INTEGER; rv_trigger_name VARCHAR2(30); BEGIN ORDSYS.TSTools.Get_Flat_Attributes( tstamp_colname, tsname_colname, tsname_length, rel_view_name, detail_table_name, detail_table_attr, detail_table_pk, detail_table_exists, map_table_name, map_table_attr, map_table_pk, map_table_exists, cal_table_name, cal_table_attr, cal_table_pk, cal_table_exists, rv_trigger_name); DBMS_OUTPUT.PUT_LINE('tstamp_colname = '||tstamp_colname); DBMS_OUTPUT.PUT_LINE('tsname_colname = '||tsname_colname); DBMS_OUTPUT.PUT_LINE('tsname_length = '||tsname_length); DBMS_OUTPUT.PUT_LINE('rel_view_name = '||rel_view_name); DBMS_OUTPUT.PUT_LINE('detail_table_name = '||detail_table_name); DBMS_OUTPUT.PUT_LINE('detail_table_attr = '||detail_table_attr); DBMS_OUTPUT.PUT_LINE('detail_table_pk = '||detail_table_pk); DBMS_OUTPUT.PUT_LINE('detail_table_exists = '||detail_table_exists); DBMS_OUTPUT.PUT_LINE('map_table_name = '||map_table_name); DBMS_OUTPUT.PUT_LINE('map_table_attr = '||map_table_attr); DBMS_OUTPUT.PUT_LINE('map_table_pk = '||map_table_pk); DBMS_OUTPUT.PUT_LINE('map_table_exists = '||map_table_exists); DBMS_OUTPUT.PUT_LINE('cal_table_name = '||cal_table_name); DBMS_OUTPUT.PUT_LINE('cal_table_attr = '||cal_table_attr); DBMS_OUTPUT.PUT_LINE('cal_table_pk = '||cal_table_pk); DBMS_OUTPUT.PUT_LINE('cal_table_exists = '||cal_table_exists); DBMS_OUTPUT.PUT_LINE('rv_trigger_name = '||rv_trigger_name); END; /
This example might produce the following output:
tstamp_colname = TSTAMP tsname_colname = TSNAME tsname_length = 25 rel_view_name = MYTS_RVW detail_table_name = MYTS_TAB detail_table_attr = ORGANIZATION INDEX detail_table_pk = MYTS_TPK detail_table_exists = 0 map_table_name = MYTS_MAP map_table_attr = map_table_pk = MYTS_MPK map_table_exists = 0 cal_table_name = MYTS_CAL cal_table_attr = cal_table_pk = MYTS_CPK cal_table_exists = 0 rv_trigger_name = MYTS_TR
ORDSYS.TSTools.Get_Object_Attributes(
object_table_name OUT VARCHAR2,
object_table_type OUT VARCHAR2,
object_table_exists OUT INTEGER,
storage_table_name OUT VARCHAR2,
rel_view_name OUT VARCHAR2,
ov_trigger_name OUT VARCHAR2,
nt_trigger_name OUT VARCHAR2,
rv_trigger_name OUT VARCHAR2,
object_table_attributes OUT VARCHAR2,
storage_table_attributes OUT VARCHAR2,
object_table_pk OUT VARCHAR2,
);
Retrieves the attributes of an object-model time series.
Name of the table containing the composite data.
Attributes of the table identified by object_table_name.
Contains 1 if the table identified by object_table_name exists; contains 0 if this table does not exist.
Name of the nested storage table.
Name of the relational view created on the object table identified by object_table_name.
Name of the INSTEAD OF trigger for insert and update operations on the object view.
Name of the INSTEAD OF trigger for insert, update, and delete operations on the nested table.
Name of the INSTEAD OF trigger for insert, update, and delete operations on the relational view.
Attributes of the table identified by object_table_name.
Attributes of the nested storage table.
Primary key of the table identified by object_table_name.
This procedure returns the attributes into variables that you specify. If you simply want to display the attributes of the time series being created, you can use the Display_Attributes procedure.
To return the attributes of a flat time series, use the Get_Flat_Attributes procedure.
The following example gets the attributes of an object-model time series being created.
DECLARE object_table_name VARCHAR2(30); object_table_type VARCHAR2(30); object_table_exists INTEGER; storage_table_name VARCHAR2(30); rel_view_name VARCHAR2(30); ov_trigger_name VARCHAR2(30); nt_trigger_name VARCHAR2(30); rv_trigger_name VARCHAR2(30); rv_utrigger_name VARCHAR2(30); rv_dtrigger_name VARCHAR2(30); object_table_attributes VARCHAR2(30); storage_table_attributes VARCHAR2(30); object_table_pk VARCHAR2(30); BEGIN ORDSYS.TSTools.Get_Object_Attributes( object_table_name, object_table_type, object_table_exists, storage_table_name, rel_view_name, ov_trigger_name, nt_trigger_name, rv_trigger_name, object_table_attributes, storage_table_attributes, object_table_pk); DBMS_OUTPUT.PUT_LINE('object_table_name = '||object_table_name); DBMS_OUTPUT.PUT_LINE('object_table_type = '||object_table_type); DBMS_OUTPUT.PUT_LINE('object_table_exists = '||object_table_exists); DBMS_OUTPUT.PUT_LINE('storage_table_name = '||storage_table_name); DBMS_OUTPUT.PUT_LINE('rel_view_name = '||rel_view_name); DBMS_OUTPUT.PUT_LINE('ov_trigger_name = '||ov_trigger_name); DBMS_OUTPUT.PUT_LINE('nt_trigger_name = '||nt_trigger_name); DBMS_OUTPUT.PUT_LINE('rv_trigger_name = '||rv_trigger_name); DBMS_OUTPUT.PUT_LINE('object_table_attributes = '||object_table_attributes); DBMS_OUTPUT.PUT_LINE('storage_table_attributes = ' ||storage_table_attributes); DBMS_OUTPUT.PUT_LINE('object_table_pk = '||object_table_pk); END; /
This example might produce the following output:
object_table_name = AUTO_PROD object_table_type = ORDTNUMSERIES object_table_exists = 0 storage_table_name = MYTS_STAB rel_view_name = MYTS_RVW ov_trigger_name = MYTS_TO nt_trigger_name = MYTS_TNT rv_trigger_name = MYTS_TR object_table_attributes = storage_table_attributes = ORGANIZATION INDEX object_table_pk = MYTS_OTPK
ORDSYS.TSTools.Get_Status(
Checks to see if a time series creation sequence is in progress.
Contains 1 if a time series creation sequence is in progress; contains 0 if a time series creation sequence is not in progress.
This call can be made after a previous TSTools procedure raises an exception, to determine if you need to reissue only the last administrative tools procedure call or the complete sequence of administrative tools procedure calls.
If the exception caused the package state to be cleared, out_status contains 0 and you must reissue the complete sequence of administrative tools procedure calls. If the exception did not cause the package state to be cleared, out_status contains 1 and you can reissue just the most recent administrative tools procedure call.
The following example gets the status, stores it in a variable named status, and displays the value:
DECLARE status INTEGER; BEGIN ORDSYS.TSTools.Get_Status(status); DBMS_OUTPUT.PUT_LINE('Status = '||status); END; /
This example might produce the following output:
Status = 0
ORDSYS.TSTools.Open_Log(
Opens a log file that will contain the data definition language (DDL) statements generated by the administrative tools procedures.
Directory location in which to create the log file on the server system. Must be a valid specification for the server system operating system.
Name of the log file, including any extension.
This procedure is equivalent to calling UTL_FILE.FOPEN. For information on the PL/SQL file I/O procedure UTL_FILE, see the Oracle8i Supplied Packages Reference manual.
To use this procedure, one or more directories for UTL_FILE output must be defined using the UTL_FILE_DIR parameter in the Oracle initialization file. For information about the UTL_FILE_DIR parameter, see the Oracle8i Reference manual.
The log file (Open_Log...Close_Log) and the debug display (Trace_On...Trace_Off) contain the same information.
The following example opens a log file named ts1.log in the logdir directory, creates time series schema objects, and closes the log file:
... ORDSYS.TSTools.Open_Log('logdir','ts1.log'); ORDSYS.TSTools.Begin_Create_TS_Group('myts','flat'); ... ORDSYS.TSTools.End_Create_TS_Group; ORDSYS.TSTools.Close_Log; ...
ORDSYS.TSTools.Set_Flat_Attributes(
tstamp_colname IN VARCHAR2 DEFAULT NULL,
tsname_colname IN VARCHAR2 DEFAULT NULL,
tsname_length IN NUMBER DEFAULT NULL,
rel_view_name IN VARCHAR2 DEFAULT NULL,
detail_table_name IN VARCHAR2 DEFAULT NULL,
detail_table_attr IN VARCHAR2 DEFAULT NULL,
detail_table_pk IN VARCHAR2 DEFAULT NULL,
detail_table_exists IN INTEGER DEFAULT NULL,
map_table_name IN VARCHAR2 DEFAULT NULL,
map_table_attr IN VARCHAR2 DEFAULT NULL,
map_table_pk IN VARCHAR2 DEFAULT NULL,
map_table_exists IN VARCHAR2 DEFAULT NULL,
cal_table_name IN VARCHAR2 DEFAULT NULL,
cal_table_attr IN VARCHAR2 DEFAULT NULL,
cal_table_pk OUT VARCHAR2 DEFAULT NULL,
cal_table_exists IN INTEGER DEFAULT NULL,
rv_trigger_name IN VARCHAR2 DEFAULT NULL
);
Sets the attributes of a flat time series.
Name of the timestamp column in a composite.
Name of the column that identifies a time series instance in a composite.
Length of tsname_colname.
Name of the relational view created on the underlying (detail) table identified by detail_table_name.
Name of the table containing the composite data.
Attributes of the table identified by detail_table_name.
Primary key for the table identified by detail_table_name.
1 if the table identified by detail_table_name exists; 0 if this table does not exist.
Name of the table that maps time series to calendars.
Attributes of the table identified by map_table_name.
Primary key for the table identified by map_table_name.
1 if the table identified by map_table_name exists; 0 if this table does not exist.
Name of the table containing the calendar definitions.
Attributes of the table identified by cal_table_name.
Primary key for the table identified by cal_table_name.
1 if the table identified by cal_table_name exists; 0 if this table does not exist.
Name of the INSTEAD OF trigger for insert, update, and delete operations on the relational view.
This procedure can be used to override some or all of the attributes of a flat-model time series. To leave an attribute unchanged, pass a null value for that attribute. To display the current attributes, use the Display_Attributes procedure; to retrieve the current attributes, use the Get_Flat_Attributes procedure.
If detail_table_exists is 1 (TRUE), the following attributes must be null: detail_table_attr, tsname_length, and detail_table_pk.
If map_table_exists is 1 (TRUE), the following attributes must be null: map_table_attr and map_table_pk.
If cal_table_exists is 1 (TRUE), the following attributes must be null: cal_table_attr and cal_table_pk.
An exception is raised if one or more of the following conditions are true: a time series is not being created, the time series being created is not of the flat model, or a calendar in the table identified by cal_table_name has an invalid frequency.
An exception is also raised if the procedure is called after a successful call to the same procedure during the creation of a time series group (that is, before the call to End_Create_TS_Group). For example, the following sequence of calls is not valid:
ORDSYS.TSTools.Set_Flat_Attributes(detail_table_name => 'mytable'); ORDSYS.TSTools.Set_Flat_Attributes(map_table_name => 'mymap');
However, the following call is valid:
ORDSYS.TSTools.Set_Flat_Attributes(detail_table_name => 'mytable', map_table_name => 'mymap');
For convenience in PL/SQL coding, because of the number of parameters for this procedure, you may want to use the association operator (=>) instead of positional notation. For example, to specify a maximum length of 25 for the timestamp column name, use the following:
ORDSYS.TSTools.Set_Flat_Attributes(tsname_length => 25);
The following example begins the creation of schema objects for a flat time series named MYTS, and sets the tsname_length attribute to 25 (that is, maximum of 25 characters for the name of the time series):
ORDSYS.TSTools.Begin_Create_TS_Group('MYTS','flat'); ORDSYS.TSTools.Set_Flat_Attributes(tsname_length => 25); ....
ORDSYS.TSTools.Set_Object_Attributes(
object_table_name IN VARCHAR2 DEFAULT NULL,
object_table_type IN VARCHAR2 DEFAULT NULL,
object_table_exists IN INTEGER DEFAULT NULL,
storage_table_name IN VARCHAR2 DEFAULT NULL,
rel_view_name IN VARCHAR2 DEFAULT NULL,
ov_trigger_name IN VARCHAR2 DEFAULT NULL,
nt_trigger_name IN VARCHAR2 DEFAULT NULL,
rv_trigger_name IN VARCHAR2 DEFAULT NULL,
object_table_attributes IN VARCHAR2 DEFAULT NULL,
storage_table_attributes IN VARCHAR2 DEFAULT NULL,
object_table_pk IN VARCHAR2 DEFAULT NULL
);
Sets the attributes of an object-model time series.
Name of the object table.
Type associated with the object table: numseries or varchar2series.
1 if the table identified by object_table_name exists; 0 if this table does not exist.
Name of the nested storage table.
Name of the relational view created on the object table identified by object_table_name.
Name of the INSTEAD OF trigger for insert and update operations on the object view.
Name of the INSTEAD OF trigger for insert, update, and delete operations on the nested table.
Name of the INSTEAD OF trigger for insert, update, and delete operations on the relational view.
Attributes of the table identified by object_table_name. Must include an OVERFLOW clause if object_table_type is varchar2series.
Attributes of the nested storage table.
Primary key of the table identified by object_table_name.
This procedure can be used to override some or all of the attributes of an object-model time series. To leave an attribute unchanged, pass a null value for that attribute. To display the current attributes, use the Display_Attributes procedure; to retrieve the current attributes, use the Get_Object_Attributes procedure.
If object_table_exists is 1 (TRUE), the following attributes must be null: object_table_attributes, storage_table_name, storage_table_attributes, and object_table_pk.
An exception is raised if a time series is not being created or if the time series being created is not of the object model.
An exception is also raised if the procedure is called after a successful call to the same procedure during the creation of a time series group (that is, before the call to End_Create_TS_Group). For example, the following sequence of calls is not valid:
ORDSYS.TSTools.Set_Object_Attributes(object_table_name => 'mytable'); ORDSYS.TSTools.Set_Object_Attributes(storage_table_name => 'mystore');
However, the following call is valid:
ORDSYS.TSTools.Set_Object_Attributes(object_table_name => 'mytable', storage_table_name => 'mystore');
For convenience in PL/SQL coding, because of the number of parameters for this procedure, you may want to use the association operator (=>) instead of positional notation. For example, to specify mytable as the object table name, use the following:
ORDSYS.TSTools.Set_Object_Attributes(object_table_name => 'mytable');
The following example starts the creation of schema objects for an object-model time series, sets the object table name to auto-prod (because this time series will contain the number of automobiles produced each calendar frequency interval), and accepts default attributes for the other object-model time series group attributes. The example also displays the attributes.
DECLARE BEGIN ORDSYS.TSTools.Begin_Create_TS_Group('myts','object'); ORDSYS.TSTools.Set_Object_Attributes( object_table_name => 'auto_prod' ); ORDSYS.TSTools.Display_Attributes; END; /
This example might produce the following output:
current settings for begin_create_ts_group NAME = MYTS STORAGE_MODEL = OBJECT SCHEMA = TSDEV OBJECT_TABLE_NAME = AUTO_PROD OBJECT_TABLE_TYPE = ORDTNUMSERIES OBJECT_TABLE_EXISTS = 0 STORAGE_TABLE_NAME = MYTS_STAB OBJECT_TABLE_ATTRIBUTES = STORAGE_TABLE_ATTRIBUTES = ORGANIZATION INDEX OBJECT_TABLE_PK = MYTS_OTPK REL_VIEW_NAME = MYTS_RVW OBJECT_VIEW_TRIGGER_NAME = MYTS_TO NESTED_TABLE_TRIGGER_NAME= MYTS_TNT REL_VIEW_TRIGGER_NAME = MYTS_TR
ORDSYS.TSTools.Trace_Off;
Disables debugging for Oracle8i Time Series administrative tools procedures. Any data definition language (DDL) statements and errors encountered when generating DDL statements will not be logged to SERVEROUTPUT.
None.
The log file (Open_Log...Close_Log) and the debug display (Trace_On...Trace_Off) contain the same information.
The following example enables debugging for Oracle8i Time Series administrative tools procedures, creates time series schema objects, and disables debugging for Oracle8i Time Series administrative tools procedures:
... ORDSYS.TSTools.Trace_On; ORDSYS.TSTools.Begin_Create_TS_Group('myts','flat'); ... ORDSYS.TSTools.End_Create_TS_Group; ORDSYS.TSTools.Trace_Off; ...
ORDSYS.TSTools.Trace_On;
Enables debugging for Oracle8i Time Series administrative tools procedures. Any data definition language (DDL) statements and errors encountered when generating DDL statements will be logged to SERVEROUTPUT.
None.
The log file (Open_Log...Close_Log) and the debug display (Trace_On...Trace_Off) contain the same information.
The following example enables debugging for Oracle8i Time Series administrative tools procedures, creates time series schema objects, and disables debugging for Oracle8i Time Series administrative tools procedures:
... ORDSYS.TSTools.Trace_On; ORDSYS.TSTools.Begin_Create_TS_Group('myts','flat'); ... ORDSYS.TSTools.End_Create_TS_Group; ORDSYS.TSTools.Trace_Off; ...
