PK
P6Aoa, mimetypeapplication/epub+zipPK P6A iTunesMetadata.plist?
The SPARSEINDEX option controls the type of index algorithm that composites use to load and access their values. The value of SPARSEINDEX at the time a named composite is defined, or an unnamed composite is created, determines the type of algorithm the composite uses by default. When you specify an index algorithm in a DEFINE COMPOSITE statement, this overrides the default specified by the SPARSEINDEX option.
Choosing an index algorithm is important only in regard to performance issues. Any recommendations are for the version of Oracle OLAP that is associated with this documentation. You can test how using different algorithms affect performance by using a CHGDFN statement to change the algorithm for a composite (for example, before loading data).
Data Type
TEXT
Syntax
SPARSEINDEX = {'BTREE'|'HASH'}
Parameters
A standard indexing method that is recommended for composites. Use BTREE unless you are an advanced user. BTREE tends to group similar values together, which results in better locality of access. BTREE is the default algorithm.
A standard indexing method that should only be used when a composite has only two or three base dimensions. HASH is generally not recommended for composites since using HASH results in a very large index table, which can be too large to fit into memory.
Examples
The MAXCHARS function counts the number of characters in the longest line of a multiline text expression. The result returned by MAXCHARS has the same dimensions as the specified expression.
Tip: When you are using a multibyte character set, you can use the MAXBYTES function instead of the MAXCHARS function. |
Return Value
INTEGER
Syntax
MAXCHARS(text-expression)
Parameters
The text expression whose characters for each line are to be counted. MAXCHARS accepts either a TEXT or NTEXT argument. It does not perform an automatic conversion to either data type. It returns the information that is correct for the data type of the specified argument.
Examples
Example 8-23 Finding the Length of the Longest Line Using Characters
You would like to know the length of the longest line in a text variable called mytext
. The following example shows the value of the variable and the result returned by MAXCHARS.
The statement
SHOW mytext
produces the following output.
This is a multiline text variable. The longest line is this one in the middle. The third line is short.
The statement
SHOW MAXCHARS(mytext)
produces the following output.
43
The ERRORTEXT option holds the text of the first error message that occurs when you execute a program or a statement. The name of the error whose message is found in ERRORTEXT is contained in the ERRORNAME option.
Data Type
TEXT
Syntax
ERRORTEXT
Examples
Example 5-35 ERRORTEXT with the SIGNAL Command
In a report program that uses a TRAP command to handle errors, you can use the SIGNAL command to send the appropriate error message to the current outfile.
DEFINE myreport PROGRAM LD Monthly Report PROGRAM TRAP ON CLEANUP NOPRINT PUSH month DECIMALS LSIZE PAGESIZE LIMIT month TO LAST 1 ... POP month DECIMALS LSIZE PAGESIZE RETURN CLEANUP: POP month DECIMALS LSIZE PAGESIZE SIGNAL ERRORNAME ERRORTEXT END
Within an OLAP DML program, the RETURN command terminates execution of a program before its last line. You can optionally specify a value that the program returns when the program is called as a function. The value should have the same data type or dimension that you specified when you defined the program.
Syntax
RETURN [expression]
Parameters
The expression to be returned to the calling program when the called program terminates.
Usage Notes
Return Value Dimensionality
The value returned by a program is a single value, without any dimensions. However, within the context of the statement that calls a user-defined function, the function expression has the dimensions of its arguments. In this case, the program is called once for every combination of the dimension values of the function expression.
Return Value Data Type
When you specify a data type when you define a program, the return value has that data type. When you specify a dimension when you define a program, the return value is a single value in that dimension. When the expression in a RETURN statement does not match the declared data type or dimension, Oracle OLAP converts it to the declared data type.
When you do not specify a data type or dimension in the definition of a program, its return value is treated as worksheet data and Oracle OLAP converts any return value to the data type that is required by the calling context which may lead to unexpected results.
Dimension Location
When the program returns values of a dimension, the dimension must be declared in the same analytic workspace as the program. The program is in the output of the LISTBY program, and OBJ(ISBY) is TRUE
for the dimension.
No Return Value
When a program has been invoked as a function, but it does not provide a return value, the value that is returned to the calling program is NA
.
Examples
Example 10-106 Terminating a Program Early
In this example, suppose you want a report program that produces a report only when a variable called newfigures
is present in the current analytic workspace. In your program, you can use an IF statement to check whether newfigures
exists and a RETURN to stop execution when it does not.
DEFINE sales.report PROGRAM PROGRAM IF NOT EXISTS('newfigures') THEN DO SHOW 'The new data is not yet available.' RETURN DOEND PUSH month TRAP ON cleanup LIMIT month TO LAST 3 REPORT ACROSS month: newfigures cleanup: POP month END
Now when you run the program without newfigures
in the analytic workspace, the program produces a message and the RETURN statement terminates execution of the program at that point.
Example 10-107 Returning a Value
The following program derives next year's budget figures from the actual
variable. It is a temporary calculation. You could call this program in a REPORT statement, thus calculating and reporting the budget figures without storing them in an analytic workspace.
DEFINE budget.growth PROGRAM DECIMAL PROGRAM VARIABLE growth DECIMAL VARIABLE factor DECIMAL growth = TOTAL(actual(year 'Yr97') year) - TOTAL(actual(year - 'Yr96') year) factor = ( 1 + growth ) / TOTAL(actual(year 'Yr96') year) RETURN TOTAL(actual(year 'Yr97') year) * (factor * factor/2) END
OLAP_TABLE
is a SQL function that extracts multidimensional data from an analytic workspace and presents it in the two-dimensional format of a relational table.
The OLAP_TABLE
function returns multidimensional data in an analytic workspace as a logical table.
The order in which OLAP_TABLE
processes information specified in its input parameters is described in "Order of Processing in OLAP_TABLE".
OLAP_TABLE
is the fundamental mechanism in the database for querying an analytic workspace. Within a SQL statement, you can specify an OLAP_TABLE
function call wherever you would provide the name of a table or view.
OLAP_TABLE
returns a table of objects that can be joined to relational tables and views, and to other tables of objects populated by OLAP_TABLE
.
Note: You cannot execute this function from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL Worksheet. |
Returns
A table type whose rows are objects (ADTs) that identify the selected workspace data. See "Creating Relational Views Using OLAP_TABLE".
Syntax
OLAP_TABLE( analytic_workspace IN VARCHAR2, table_object IN VARCHAR2, olap_command IN VARCHAR2, limit_map1 IN VARCHAR2, limit_map2 IN VARCHAR2, . . . limit_map8 IN VARCHAR2) RETURN TYPE;
Parameters
Provides the name of the analytic workspace where the source data is stored. It also specifies how long the analytic workspace is attached to your OLAP session, which opens on your first call to OLAP_TABLE
.
This parameter is always required by OLAP_TABLE
.
The syntax of this parameter is:
'[owner.]aw_name DURATION QUERY | SESSION'
For example:
'olapuser.xademo DURATION SESSION'
owner
Specify owner whenever you are creating views to be accessed by other users. Otherwise, you can omit the owner if you own the analytic workspace. It is required only when you are logged in under a different user name than the owner.
QUERY
Attaches an analytic workspace for the duration of a single query. Use QUERY
only when you must see updates to the analytic workspace made in other sessions.
SESSION
Attaches an analytic workspace and keeps it attached after the query. It provides better performance than QUERY
because it keeps the OLAP session open. This performance difference is significant when the function is called without either a table_object parameter or AS
clauses in the limit map; in this case, the OLAP_TABLE
function must determine the appropriate table definition. See "Using OLAP_TABLE With Automatic ADTs".
The name of a predefined table of objects, as described in "Using OLAP_TABLE With Predefined ADTs".
This parameter is optional. Omit this parameter when you are using automatic ADTs.
The syntax of this parameter is:
'table_name'
For example:
'product_dim_tbl'
When you specify the table_name parameter, the column data types for the returned data are predefined. In this case you cannot use AS
clauses in the limit map.
When you omit the table_name parameter, the column data types for the returned data are generated at run time. You can either provide the target data types with AS
clauses in the limit map, or you can use the default data types described in Table A-1, "Default Data Type Conversions". See "Using OLAP_TABLE With Automatic ADTs".
A single OLAP DML command. To execute multiple commands, create a program in your analytic workspace and call the program in this parameter. The power and flexibility of this parameter comes from its ability to process virtually any data manipulation commands available in the OLAP DML.
The order in which OLAP_TABLE
processes the olap_command parameter is specified in "Order of Processing in OLAP_TABLE".
The syntax of this parameter is:
'olap_command'
There are two distinct ways of using the olap_command parameter:
To make changes in the workspace session immediately before the data is fetched (after all the limits have been applied) as described in "Using FETCH in the olap_command Parameter".
To specify the source data directly instead of using a limit map as described in "Using olap_command with a Limit Map".
Maps workspace objects to relational columns and identifies the role of each one. See "Creating Relational Views Using OLAP_TABLE".
The limit map can also specify special instructions to be executed by OLAP_TABLE
. For example: It can cause an OLAP DML command to execute before or after the limit map is processed; it can specify a ROW2CELL
column for the OLAP_CONDITION
and OLAP_EXPRESSION
functions. (See "OLAP_CONDITION" and "OLAP_EXPRESSION".)
The order in which OLAP_TABLE
processes information in the limit map is specified in "Order of Processing in OLAP_TABLE".
The limit map parameter is generally a required parameter. It can only be omitted when you specify a FETCH
command in the olap_command parameter. See the discussion of olap_command.
You can supply the entire text of the limit map as a parameter to OLAP_TABLE
, or you can store all or part of the limit map in a text variable in the analytic workspace and reference it using ampersand substitution. For example, the following OLAP_TABLE
query uses a limit map stored in a variable called limitmapvar
in the MYAW
analytic workspace of the MYAW_AW
schema.
SELECT * FROM TABLE(OLAP_TABLE( 'myaw_aw.myaw DURATION SESSION', '', '', '&(myaw_aw.myaw!limitmapvar)');
If you supply the limit map as text within the call to OLAP_TABLE
, then it has a maximum length of 4,000 characters, which is imposed by PL/SQL. If you store the limit map in the analytic workspace, then the limit map has no maximum length.
Note: Several analytic workspace objects must be predefined within the workspace to support the mapping of dimension hierarchies in the limit map. For more information, see "Required OLAP DML Objects". |
The syntax of the limit map has numerous clauses, primarily for defining dimension hierarchies. Pay close attention to the presence or absence of commas, since syntax errors prevents your limit map from being parsed.
'[MEASURE column [AS datatype] FROM {measure | AW_EXPR expression}] . . DIMENSION [column [AS datatype] FROM] dimension [WITH [HIERARCHY [column [AS datatype] FROM] parent_relation [(hierarchy_dimension ''hierarchy_name'')] [INHIERARCHY inhierarchy_obj] [GID column [AS datatype] FROM gid_relation] [PARENTGID column [AS datatype] FROM gid_relation] [FAMILYREL column1 [AS datatype], column2 [AS datatype], ... columnn [AS datatype] FROM {expression1, expression2, ... expressionn | family_relation USING level_dimension } [LABEL label_variable]] [HATTRIBUTE column [AS datatype] FROM hier_attribute_variable] . . ] [ATTRIBUTE column [AS datatype] FROM attribute_variable] . . ] [ROW2CELL column] [LOOP loop-clause] [PREDMLCMD olap_command] [POSTDMLCMD olap_command]'
Where:
column is the name of a column in the target table.
datatype is the data type of column.
measure is a variable, formula, or relation in the analytic workspace.
expression is a formula or qualified data reference for objects in the analytic workspace.
dimension is a dimension in the analytic workspace.
parent_relation is a self-relation in the analytic workspace that defines the hierarchies for dimension. See "Parentrel Relation" for more information.
hierarchy_dimension is a dimension in the analytic workspace that contains the names of the hierarchies for dimension. See "Hierlist Dimension" for more information
hierarchy_name is a member of hierarchy_dimension.
inhierarchy_obj is a variable or valueset in the analytic workspace that identifies which dimension members are in each level of the hierarchy. See "Inhier Valueset or Variable" for more information
gid_relation is a relation in the analytic workspace that contains the grouping ID of each dimension member in the hierarchy. See "Gidrel Relation" for more information
family_relation is a self-relation that provides the full parentage of each dimension member in the hierarchy. See "Familyrel Relation" for more information
level_dimension is a dimension in the analytic workspace that contains the names of the levels for the hierarchy. See "Levellist Dimension" for more information
label_variable is a variable in the analytic workspace that contains descriptive text values for dimension.
hier_attribute_variable is a variable in the analytic workspace that contains attribute values for hierarchy_name.
attribute_variable is a variable in the analytic workspace that contains attribute values for dimension.
loop_clause specifies how Oracle OLAP determines how it loops through data values and what rows to create in the relational table. When you exclude a LOOP clause, Oracle OLAP loops through all of the data values that are identified by the dimensions in the DIMENSION clauses in the limit map
For the complete syntax of the LOOP clause see "LOOP Clause".
olap_command is an OLAP DML command.
Detailed syntax for each of the clauses of the limit-map parameter follows.
MEASURE Clause
The MEASURE
clause maps a variable, formula, or relation in the analytic workspace to a column in the target table. You can list any number of MEASURE
clauses. This clause is optional when, for example, you want to create a dimension view.
The AS
subclause specifies the data type of the target column. You can specify an AS
subclause when the table of objects has not been predefined. See "Using OLAP_TABLE With Automatic ADTs".
In the FROM
subclause, you can either specify the name of a variable, formula, or relation or an OLAP expression that evaluates to one of these objects. For example:
AW_EXPR analytic_cube_sales - analytic_cube_cost
or
AW_EXPR LOGDIF(analytic_cube_sales, 1, time, LEVELREL time.lvlrel)
DIMENSION Clause
The DIMENSION
clause identifies a dimension or conjoint in the analytic workspace that dimensions one or more measures or attributes, or provides the dimension members for one or more hierarchies in the limit map.
The column subclause is optional when you do not want the dimension members themselves to be represented in the table. In this case, include a dimension attribute that can be used for data selection.
For a description of the AS
subclause, see "MEASURE Clause".
Every limit map should have at least one DIMENSION
clause. If the limit map contains MEASURE
clauses, then it should also contain a single DIMENSION
clause for each dimension of the measures, unless a dimension is being limited to a single value. If the measures are dimensioned by a composite, then you must identify each dimension in the composite with a DIMENSION
clause. For the best performance when fetching a large result set, identify the composite in a LOOP
clause. See "LOOP Clause".
A dimension can be named in only one DIMENSION
clause. Subclauses of the DIMENSION
clause identify the dimension hierarchies and attributes.
WITH Subclause for Dimension Hierarchies and Attributes
The WITH
subclause introduces a HIERARCHY
or ATTRIBUTE
subclause. If you do not specify hierarchies or attributes, then omit the WITH
keyword. If you specify both hierarchies and attributes, then precede them with a single WITH
keyword.
WITH HIERARCHY Subclause
The HIERARCHY
subclause identifies the parent self-relation in the analytic workspace that defines the hierarchies for the dimension. See "Parentrel Relation" for more information,
The HIERARCHY
subclause is optional when the dimension does not have a hierarchy, or when the status of the dimension has been limited to a single level of the hierarchy. When a dimension has multiple hierarchies, specify a HIERARCHY
subclause for each one and specify a hierarchy_dimension phrase. The hierarchy_dimension identifies a dimension in the analytic workspace which holds the names of the hierarchies for this dimension. See "Hierlist Dimension" for more information.
hierarchy_name is a member of hierarchy_dimension. The hierarchy dimension is limited to hierarchy_name for all workspace objects that are referenced in subsequent subclauses for this hierarchy (that is, INHIERARCHY
, GID
, PARENTGID
, FAMILYREL
, and HATTRIBUTE
).
For a description of the column subclause, see "DIMENSION Clause ".
The keywords in the HIERARCHY
subclause are described as follows:
The INHIERARCHY
subclause identifies a boolean variable or a valueset in the analytic workspace that identifies the dimension members in each level of the hierarchy. See "Inhier Valueset or Variable" for more information.
It is good practice to include an INHIERARCHY
subclause, because OLAP_TABLE
saves the status of all dimensions with INHIERARCHY
subclauses during the processing of the limit map. It is required when there are members of the dimension that are omitted from the hierarchy.
The GID
subclause maps an integer variable in the analytic workspace, which contains the grouping ID for each dimension member, to a column in the target table. The grouping ID variable is populated by the OLAP DML GROUPINGID command command. See "Gidrel Relation" for more information.
The GID
subclause is required for Java applications that use the OLAP API.
For a description of the AS
subclause, see "MEASURE Clause".
The PARENTGID
subclause calculates the grouping IDs for the parent relation using the GID variable in the analytic workspace. The parent GIDs are not stored in an analytic workspace object. Instead, you specify the same GID variable for the PARENTGID
clause that you used in the GID
clause. The PARENTGID
clause is recommended for Java applications that use the OLAP API.
For a description of the AS
subclause, see "MEASURE Clause".
The FAMILYREL
subclause is used primarily to map a family relation in the analytic workspace to multiple columns in the target table. See "Familyrel Relation" for more information. You can use multiple FAMILYREL
clauses for each hierarchy.
List the columns in the order of level_dimension which is a dimension in the analytic workspace that holds the names of all the levels for the dimension. See "Levellist Dimension" for more information.
If you do not want a particular level included, then specify null for the target column. For a description of the AS
subclause, see "MEASURE Clause".
The LABEL
keyword identifies a text attribute that provides more meaningful names for the dimension members.
The tabular data resulting from a FAMILYREL
clause is in rollup form, in which each level of the hierarchy is represented in a separate column, and the full parentage of each dimension member is identified within the row. See Example A-7, "Script for a Rollup View of Products Using OLAP_TABLE".
The HATTRIBUTE
subclause maps a hierarchy-specific attribute variable, dimensioned by hierarchy_dimension in the analytic workspace, to a column in the target table.
WITH ATTRIBUTE Subclause
The ATTRIBUTE
subclause maps an attribute variable in the analytic workspace to a column in the target table.
If attribute_variable has multiple dimensions, then values are mapped for all members of dimension, but only for the first member in the current status of additional dimensions. For example, if your attributes have a language dimension, then you must set the status of that dimension to a particular language. You can set the status of dimensions in a PREDMLCMD
clause.
ROW2CELL Clause
The ROW2CELL
clause creates a RAW
column, between 16 and 32 characters wide, in the target table and populates it with information that is used by the OLAP expression functions. The OLAP_CONDITION
function also uses the ROW2CELL
column. Specify a ROW2CELL
column when creating a view to be used by these functions. See "Using OLAP DML Expressions in SELECT FROM OLAP_TABLE Statements" and "Adding Calculated Columns to the Relational View".
LOOP Clause
The LOOP
clause specifies how Oracle OLAP loops through the data when retrieving values. When you omit a LOOP clause in a limit map, Oracle OLAP loops uses the DIMENSION clauses in that limit map to determine what values that Oracle OLAP loops over. Oracle OLAP loops over only those tuples that identify measure cells that do not contain NA or null.When you include a LOOP clause, you specify one of the following types of subclauses: the optimized_subclause or the union_subclause.
The optimized_subclause specifies that Oracle OLAP automatically create the union_subclause by which it loops through the data. The union_subclause created never includes any DENSE phrases. Consequently, when you specify the optimized_subclause, Oracle OLAP loops over only those tuples that identify measure cells that do not contain NA or null. The optimized_subclause has the following syntax.
OPTIMIZED [MEASURES]
where the optional MEASURES keyword specifies that after identifying the tuples to loop through, Oracle OLAP remove any values that are dimension values that are not dimensions of the objects identified in the MEASURES clauses of the limit map.
Tip: You can use the$LOOP_AGGMAP, $LOOP_DENSE, and $LOOP_VAR properties, to specify more information as to how OLAP_TABLE loops over a formula. |
The union_subclause specifies exactly how Oracle OLAP determines which base dimensions it uses to identify what data values to loop through. For a successful union to occur, the objects that are referenced in this subclause must have the same base dimensions. The union_subclause has the syntax show below.
[ignore_phrase] [dense_phrase] UNION ({aggmap_phrase | list_phrase }...)]
where:
aggmap_phrase has the following syntax which specifies how Oracle OLAP loops through the values of an aggregated variable.
AGGMAP (ignore_phrase] [dense_phrase] aggmap {variable | dimension_list})
list_phrase has the following syntax which specifies how Oracle OLAP loops through the values of a composite, partition template, or dimension. It has the following syntax.
LIST ([ignore_phrase] [dense_phrase] dimension_list)
ignore_phrase — Specifies the dimension values that you do not want Oracle OLAP to loop over. It has the following syntax.
IGNORE (ignore_list )
For ignore_list you can specify one or more of the following separated by commas.
dimension_name valueset_name COMPLEMENT (valueset_name)
Within a single ignore_list you cannot specify a dimension in multiple ways. In other words, you cannot specify both a dimension and a valueset for that same dimension; you cannot specify two valuesets for the same dimension.
Note: The result of specifying IGNOR valueset-name is similar to specifying LIMIT REMOVE valueset-name (see LIMIT command). |
dense_phrase — Specifies values that Oracle OLAP loops over even when the measure cells identified by those values contain NA or null. In other words, specifying a dimension in a DENSE phrase is similar to requesting a relational outer join. Typically, you include dimension values in a DENSE phrase to perform time-series processing (for example, to lag over time). A DENSE phrase has the following syntax.
DENSE (dense_list )
For dense_list you can specify one or more of the following separated by commas.
dimension_name valueset_name COMPLEMENT (valueset_name)
Within a single dense_list you cannot specify a dimension in multiple ways. In other words, you cannot specify both a dimension and a valueset for that same dimension; you cannot specify two valuesets for the same dimension.
However, you can have one valueset for a dimension in the outer DENSE
phrase and another valueset for the same dimension in an inner DENSE
phrase. If a dimension or a valueset of a dimension is specified in both an inner and outer DENSE
phrase, Oracle OLAP loops densely over the union of the dense regions. If the dimension, itself, appears in either place, Oracle OLAP loops densely over the whole dimension.
aggmap — The name of an aggmap object. When you specify only the name of an aggmap object, Oracle OLAP uses the values in the PRECOMPUTE phrases of the aggmap to identify the values to loop
variable — The name of the variable aggregated by aggmap_name.
dimension_list — A list of one or more composites, partition templates, or dimensions.
PREDMLCMD Clause
The PREDMLCMD
clause specifies an OLAP DML command that is executed before the data is fetched from the analytic workspace into the target table. It can be used, for example, to execute an OLAP model or f orecast whose results will be fetched into the table. The results of the command are in effect during execution of the limit map, and continue into your session after execution of OLAP_TABLE
is complete. See "Order of Processing in OLAP_TABLE".
POSTDMLCMD Clause
The POSTDMLCMD
clauses specifies an OLAP DML command that is executed after the data is fetched from the analytic workspace into the target table. It can be used, for example, to delete objects or data that were created by commands in the PREDMLCMD
clause, or to restore the dimension status that was changed in a PREDMLCMD
clause. See "Order of Processing in OLAP_TABLE" .
Usage Notes
Limit Maps
OLAP_TABLE
uses a limit map to map dimensions and measures defined in an analytic workspace to columns in a logical table. The limit map combines with the WHERE
clause of a SQL SELECT
statement to generate a series of OLAP DML LIMIT commands that are executed in the analytic workspace.
OLAP_TABLE
can use a limit map with a predefined logical table, or it can use the information in a limit map to dynamically generate a logical table at run time.
Logical Tables
The logical table populated by OLAP_TABLE
is actually a table type whose rows are user-defined object types, also known as Abstract Data Types or ADTs.
A user-defined object type is composed of attributes, which are equivalent to the columns of a table. The basic syntax for defining a row is as follows.
CREATE TYPE object_name AS OBJECT ( attribute1 datatype, attribute2 datatype, attributen datatype);
A table type is a collection of object types; this collection is equivalent to the rows of a table. The basic syntax for creating a table type is as follows.
CREATE TYPE table_name AS TABLE OF object_name;
See Also:
|
Using OLAP_TABLE With Predefined ADTs
You can predefine the table of objects or generate it dynamically. When you create the table type in advance, it is available in the database for use by any invocation of OLAP_TABLE
. Queries that use predefined objects typically perform better than queries that dynamically generate the objects. See "Using OLAP_TABLE With Predefined ADTs".
Using OLAP_TABLE With Automatic ADTs
If you do not supply the name of a table type as an argument, OLAP_TABLE
uses information in the limit map to generate the logical table automatically. In this case, the table type is only available at run time within the context of the calling SQL SELECT
statement. See "Using OLAP_TABLE With Automatic ADTs".
When automatically generating ADTs, OLAP_TABLE
uses default relational data types for the target columns unless you override them with AS
clauses in the limit map. The default data type conversions used by OLAP_TABLE
are described in Table A-1.
Table A-1 Default Data Type Conversions
Analytic Workspace Data Type | SQL Data Type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other |
|
Using olap_command with a Limit Map
You may want your application to modify the analytic workspace on the fly during the execution of OLAP_TABLE
.
A common use of the olap_command parameter is to limit one or more dimensions. If you limit any of the dimensions that have INHIERARCHY
clauses in the limit map, then the status of those dimensions is changed only during execution of this call to OLAP_TABLE
; the limits do not affect the rest of your OLAP session. However, other commands (for example, commands that limit dimensions not referenced with INHIERARCHY
clauses) can affect your session.
If you want a limit on a dimension in the limit map to stay in effect for the rest of your session, and not just during the command, specify it in the PREDMLCMD
clause of the limit map or specify an OLAP_CONDITION
function in the SQL SELECT
statement.
The following is an example of a LIMIT
command in the olap_command parameter.
'LIMIT product TO product_member_levelrel ''L2'''
Using FETCH in the olap_command Parameter
If you specify an OLAP DML FETCH command in the olap_command parameter, OLAP_TABLE
uses it, instead of the instructions in the limit map, to fetch the source data for the table object. Because of this usage, the olap_command parameter is sometimes referred to as the data map. In general, do not specify a limit map if you specify a FETCH
command.
Note: Normally, use theFETCH command with OLAP_TABLE only if you are upgrading an Express application that used the FETCH command for SNAPI. If you are upgrading, note that the full syntax is the same in Oracle as in Express 6.3. You can use the same FETCH commands in OLAP_TABLE that you used previously in SNAPI. For the syntax of the FETCH command, see "FETCH". |
FETCH
specifies explicitly how analytic workspace data is mapped to a table object. The basic syntax is:
FETCH expression...
Enter one expression for each target column, listing the expressions in the same order they appear in the row definition. Separate expressions with spaces or commas.You must enter the entire statement on one line, without line breaks or continuation marks of any type.
Order of Processing in OLAP_TABLE
The following list identifies the order in which the OLAP_TABLE
function processes instructions in the limit map that can change the status of dimensions in the analytic workspace.
Execute any OLAP DML command specified in the PREDMLCMD
parameter of the limit map.
Save the current status of all dimensions in the limit map so that it can be restored later (PUSH
status).
Keep in status only those dimension members specified by INHIERARCHY
subclauses in the limit map (LIMIT KEEP
).
Within the status set during step 3, keep only those dimension members that satisfy the WHERE
clause of the SQL SELECT
statement containing the OLAP_TABLE
function (LIMIT KEEP
).
Execute any OLAP DML command specified in the olap_command parameter of the OLAP_TABLE
function. (If olap_command includes a FETCH
, fetch the data.)
Fetch the data (unless an OLAP DML FETCH
command was specified in the olap_command parameter).
Restore the status of all dimensions in the limit map (POP
status).
Execute any OLAP DML command specified in the POSTDMLCMD
parameter of the limit map.
Examples
Because different applications have different requirements, several different formats are commonly used for fetching data into SQL from an analytic workspace. The examples in this section show how to create views using a variety of different formats.
Although these examples are shown as views, the SELECT
statements can be extracted from them and used directly to fetch data from an analytic workspace into an application.
Note: The examples in this section use predefined ADTs. You could modify them to use automatic ADTs. See "Using OLAP_TABLE With Automatic ADTs". |
Example A-5 Script for an Embedded Total Dimension View Using OLAP_TABLE
This example shows the PL/SQL script used to create an embedded total view of the TIME
dimension in an analytic workspace named MYAW. This view is similar to the view in Example A-2, "Sample View of the TIME Dimension Using Predefined ADTs", but it specifies both a Calendar and a Fiscal hierarchy, and it includes HATTRIBUTE
subclauses for hierarchy-specific End Date attributes.
The INHIERARCHY
subclause identifies a valueset in the analytic workspace that lists all the dimension members in each hierarchy of a dimension. OLAP_TABLE
saves the status of all dimensions in the limit map that have INHIERARCHY
subclauses during the processing of the limit map. See "Order of Processing in OLAP_TABLE".
CREATE TYPE awtime_row AS OBJECT ( awtime_id VARCHAR2(12), awtime_short_label VARCHAR2(12), awtime_cal_end_date DATE, awtime_fis_end_date DATE); / CREATE TYPE awtime_table AS TABLE OF awtime_row; / CREATE OR REPLACE VIEW awtime_view AS SELECT awtime_id, awtime_short_label, awtime_cal_end_date, awtime_fis_end_date FROM TABLE(OLAP_TABLE( 'myaw DURATION SESSION', 'awtime_table', '', 'DIMENSION awtime_id FROM time WITH HIERARCHY time_parentrel (time_hierlist ''CALENDAR'') INHIERARCHY time_inhier HATTRIBUTE awtime_cal_end_date FROM time_cal_end_date HIERARCHY time_parentrel (time_hierlist ''FISCAL'') INHIERARCHY time_inhier HATTRIBUTE awtime_fis_end_date FROM time_fis_end_date ATTRIBUTE awtime_short_label FROM time_short_description')); / SQL>SELECT * FROM awtime_view; AWTIME_ID AWTIME_SHORT_LABEL AWTIME_CAL_END_DATE AWTIME_FIS_END_DATE --------- ------------------ ------------------- ------------------- 19 Jan-98 31-JAN-98 31-JAN-98 20 Feb-98 28-FEB-98 28-FEB-98 21 Mar-98 31-MAR-98 31-MAR-98 22 Apr-98 30-APR-98 30-APR-98 23 May-98 31-MAY-98 31-MAY-98 24 Jun-98 30-JUN-98 30-JUN-98 . . . . 98 Q1-03 31-MAR-03 30-SEP-03 99 Q2-03 30-JUN-03 31-DEC-03 1 1998 31-DEC-98 30-JUN-99 102 2003 31-DEC-03 30-JUN-04 119 2004 31-DEC-04 30-JUN-05 2 1999 31-DEC-99 30-JUN-00 3 2000 31-DEC-00 30-JUN-01 4 2001 31-DEC-01 30-JUN-02 85 2002 31-DEC-02 30-JUN-03
Note that you must be sure to verify that you have created the views correctly by issuing SELECT
statements against them. Only at that time do any errors in the call to OLAP_TABLE
appear.
Example A-6 Creating a View of an Embedded Total Measure Using OLAP_TABLE
In a star schema, a separate measure view is needed with columns that can be joined to each of the dimension views. This example shows the PL/SQL script used to create a measure view with a column populated by a ROW2CELL
clause to support custom measures. For information on ROW2CELL, "ROW2CELL Clause".
CREATE TYPE awunits_row AS OBJECT ( awtime VARCHAR2(12), awcustomer VARCHAR2(30), awproduct VARCHAR2(30), awchannel VARCHAR2(30), awunits NUMBER(16), r2c RAW(32)); / CREATE TYPE awunits_table AS TABLE OF awunits_row; / CREATE OR REPLACE VIEW awunits_view AS SELECT awunits, awtime, awcustomer, awproduct, awchannel, r2c FROM TABLE(OLAP_TABLE( 'myaw DURATION SESSION', 'awunits_table', '', 'MEASURE awunits FROM units_cube_units DIMENSION awtime FROM time WITH HIERARCHY time_parentrel DIMENSION awcustomer FROM customer WITH HIERARCHY customer_parentrel (customer_hierlist ''MARKET_ROLLUP'') INHIERARCHY customer_inhier DIMENSION awproduct FROM product WITH HIERARCHY product_parentrel DIMENSION channel WITH HIERARCHY channel_parentrel ATTRIBUTE awchannel FROM channel_short_description ROW2CELL r2c')) WHERE awunits IS NOT NULL; SQL>SELECT awchannel, awunits FROM awunits_view WHERE awproduct = '1' AND awcustomer = '7' AND awtime = '4'; AWCHANNEL AWUNITS --------- ------- All Channels 415392 Direct Sales 43783 Catalog 315737 Internet 55872
Example A-7 Script for a Rollup View of Products Using OLAP_TABLE
Rollup form uses a column for each hierarchy level to show the full parentage of each dimension member. The only difference between the syntax for rollup form and the syntax for embedded total form is the addition of a FAMILYREL
clause in the definition of each dimension in the limit map. For information on FAMILYREL
, see "WITH HIERARCHY Subclause".
This example shows the PL/SQL script used to create a rollup view of the PRODUCT
dimension. It shows a dimension view to highlight the differences in the syntax of the limit map from the one used for the embedded total form, as shown in Example A-5, "Script for an Embedded Total Dimension View Using OLAP_TABLE". Note that the target columns for these levels are listed in the FAMILYREL
clause from most aggregate (CLASS
) to least aggregate (ITEM
), which is the order they are listed in the level list dimension. The family relation returns four columns. The most aggregate level (all products) is omitted from the view by mapping it to null.
Example A-8, "Script Using QDRs in the FAMILYREL Clause of OLAP_TABLE" shows the alternate syntax for the FAMILYREL
clause, which uses QDRs to identify exactly which columns are mapped from the family relation.
The limit maps in Example A-7 and Example A-8 generate identical views.
CREATE TYPE awproduct_row AS OBJECT ( class VARCHAR2(50), family VARCHAR2(50), item VARCHAR2(50)); / CREATE TYPE awproduct_table AS TABLE OF awproduct_row; / CREATE OR REPLACE VIEW awproduct_view AS SELECT class, family, item FROM TABLE(OLAP_TABLE( 'myaw DURATION QUERY', 'awproduct_table', '', 'DIMENSION product WITH HIERARCHY product_parentrel FAMILYREL null, class, family, item FROM product_familyrel USING product_levellist LABEL product_short_description')); SQL> SELECT * FROM awproduct_view ORDER BY class, family, item; CLASS FAMILY ITEM -------------- ---------------- ------------------------ Hardware CD-ROM Envoy External 6X CD-ROM Hardware CD-ROM Envoy External 8X CD-ROM Hardware CD-ROM External 6X CD-ROM Hardware CD-ROM External 8X CD-ROM Hardware CD-ROM Internal 6X CD-ROM Hardware CD-ROM Internal 8X CD-ROM Hardware CD-ROM Hardware Desktop PCs Sentinel Financial Hardware Desktop PCs Sentinel Multimedia . . . Software/Other Operating Systems UNIX/Windows 1-user pack Software/Other Operating Systems UNIX/Windows 5-user pack Software/Other Operating Systems Software/Other
Example A-8 Script Using QDRs in the FAMILYREL Clause of OLAP_TABLE
CREATE OR REPLACE TYPE awproduct_row AS OBJECT ( class VARCHAR2(50), family VARCHAR2(50), item VARCHAR2(50)); / CREATE TYPE awproduct_table AS TABLE OF awproduct_row; / CREATE OR REPLACE VIEW awproduct_view AS SELECT class, family, item FROM TABLE(OLAP_TABLE( 'myaw DURATION QUERY', 'awproduct_table', '', 'DIMENSION product WITH HIERARCHY product_parentrel FAMILYREL class, family, item FROM product_familyrel(product_levellist ''CLASS''), product_familyrel(product_levellist ''FAMILY''), product_familyrel(product_levellist ''ITEM'') LABEL product_short_description')); SQL> SELECT * FROM awproduct_view ORDER BY by class, family, item; CLASS FAMILY ITEM -------------- ---------------- ------------------------ Hardware CD-ROM Envoy External 6X CD-ROM Hardware CD-ROM Envoy External 8X CD-ROM Hardware CD-ROM External 6X CD-ROM Hardware CD-ROM External 8X CD-ROM Hardware CD-ROM Internal 6X CD-ROM Hardware CD-ROM Internal 8X CD-ROM Hardware CD-ROM Hardware Desktop PCs Sentinel Financial Hardware Desktop PCs Sentinel Multimedia . . . Software/Other Operating Systems UNIX/Windows 1-user pack Software/Other Operating Systems UNIX/Windows 5-user pack Software/Other Operating Systems Software/Other
Example A-9 Script Using FETCH with OLAP_TABLE
Oracle Express Server applications that are being revised for use with Oracle Database can use an OLAP DML FETCH command instead of a limit map to map workspace objects to relational columns.
The FETCH
command is supplied in the third parameter of OLAP_TABLE
, which specifies a single OLAP DML command. See olap_command.
The script that follows fetches data from two variables (SALES
and COST
) in an analytic workspace named MYAW
, and calculates two custom measures (COST_PRIOR_PERIOD
and PROFIT
). This example also shows the use of OLAP_TABLE
directly by an application, without creating a view.
Note that the FETCH
statement in the following example is formatted with indentation for readability. In reality, the entire FETCH
statement must be entered on one line, without line breaks or continuation characters
CREATE TYPE measure_row AS OBJECT ( time VARCHAR2(20), geography VARCHAR2(30), product VARCHAR2(30), channel VARCHAR2(30), sales NUMBER(16), cost NUMBER(16), cost_prior_period NUMBER(16), profit NUMBER(16)); / CREATE TYPE measure_table AS TABLE OF measure_row; / SELECT time, geography, product, channel, sales, cost, cost_prior_period, profit FROM TABLE(OLAP_TABLE( 'xademo DURATION SESSION', 'measure_table', 'FETCH time, geography, product, channel, analytic_cube_f.sales, analytic_cube_f.costs, LAG(analytic_cube_f.costs, 1, time, LEVELREL time_member_levelrel), analytic_cube_f.sales - analytic_cube_f.costs', '')) WHERE channel = 'STANDARD_2.TOTALCHANNEL' AND product = 'L1.TOTALPROD' AND geography = 'L1.WORLD' ORDER BY time;
This SQL SELECT
statement returns the following result set:
TIME GEOGRAPHY PRODUCT CHANNEL SALES COST COST_PRIOR_PERIOD PROFIT --------- --------- ------------ ----------------------- --------- --------- ------------------ -------- L1.1996 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 118247112 2490243 115756869 L1.1997 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 46412113 1078031 2490243 45334082 L2.Q1.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 26084848 560379 25524469 L2.Q1.97 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 26501765 615399 560379 25886367 L2.Q2.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 30468054 649004 615399 29819049 L2.Q2.97 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 19o910347 462632 649004 19447715 L2.Q3.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 27781702 582693 462632 27199009 L2.Q4.96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 33912508 698166 582693 33214342 L3.APR96 L1.WORLD L1.TOTALPROD STANDARD_2.TOTALCHANNEL 8859808 188851 8670957 . . . 27 rows selected.
The RESERVED function can provide a list of all the words that are reserved because they are known to the OLAP DML parser, or it can indicate if a word that you specify is known to the OLAP DML parser. Some other words are also reserved as discussed in "Other Reserved Words".
Return Value
Either a multiline text expression or BOOLEAN, depending on whether or not you specify an argument to the function.
Syntax
RESERVED [(word-expression)]
Parameters
A text expression that represents a word that may or may not be reserved in the OLAP DML. When you specify word-expression, the RESERVED function returns a BOOLEAN value indicating if the word is reserved in OLAP DML. When you do not specify an argument, RESERVED returns a TEXT value consisting of all the reserved words in OLAP DML, with each word on a separate line.
Usage Notes
Other Reserved Words
The RESERVED function only recognizes words known to the OLAP DML parser. The RESERVED function des not recognize the names of option objects and some other objects in the EXPRESS
analytic workspace. The names of these objects are reserved in Oracle OLAP, but are ignored by the RESERVED function. To identify the names of these objects, issue the following statements.
AW ATTACH EXPRESS LISTNAMES
NA is Reserved
When you specify NA
for the argument, the RESERVED function returns NO
. When you specify NA
, the RESERVED function returns YES
.
Case-Sensitivity
The list of reserved words returned by the RESERVED function contains some words in all uppercase and some in mixed case. Words all in uppercase are reserved in their entirety. Words in mixed case can be abbreviated to the uppercase portion. For such words, any subset of the word containing the uppercase portion is reserved. For example, one word in the list returned by RESERVED is CODEVERsion
. The following are all reserved: codever
, codeversi
, codeversio
, and codeversion
. However, codeve
is not reserved.
Examples
The ACROSS command specifies a text expression that contains one or more statements to be executed in a loop. ACROSS temporarily sets status to the values that are in current status for the specified dimensions. After the ACROSS statement executes, dimension status is restored to what it was before the loop, and execution of the program resumes with the next statement. The repetition of the statements in the DO clause statements is controlled by the status of the dimensions and composites specified in the ACROSS statement and by the results of the WHERE clause when included.E
Syntax
ACROSS dimension... DO dml-statements [WHERE boolean-expression]
Parameters
One or more dimensions or composites whose current status controls the repetition of one or more statements, which are contained in dml-statements. The statements are repeated for each combination of the values of the specified dimensions in the current status. When two or more dimensions are specified, the first one varies the slowest.
A multiline text expression that is one or more OLAP DML statements to be executed for each iteration of the loop. You can specify any OALAP DML statement except one that is typically used as part of a multiple-line construct in a program. For example, the IF...THEN...ELSE, WHILE, FOR, or SWITCH commands cannot be executed by an ACROSS statement.
For each iteration of the loop, specifies that the command evaluate boolean-expression before executing dml-statements and, when the result of boolean-expression is either NA
or FALSE
, to not execute dml-statements for that iteration.
Usage Notes
Code May Change Between Compilation and Execution
Oracle OLAP does not generate the code for the loop body until an ACROSS statement or the program that contains it is executed. Waiting until execution to generate the code allows for the possibility that, because the statements are contained within a text expression, the contents of an ACROSS loop may change between compilation and execution.
Examples
Example 9-4 Using ACROSS to Repeat ROW Commands
In a report program, you want to show the unit sales of tents for each of three months. Use the following ACROSS statement to repeat ROW commands for each value of the month
dimension.
LIMIT product TO 'Tents' LIMIT month TO 'Jan95' to 'Mar95' ACROSS month DO 'ROW INDENT 5 month WIDTH 6 unit' Jan95 533363 Feb95 572796 Mar95 707198
The SOUNDEX function returns a character string containing the phonetic representation of a text expression. This function lets you compare words that are spelled differently, but sound alike in English.
See: For a discussion of how the phonetic representation is constructed, see the SOUNDEX function in Oracle Database SQL Language Reference |
Return Value
The same data type as text_exp.
Syntax
SOUNDEX(text_exp)
Parameters
The expression for which you want a phonetic representation. It can be any of the text data types.
The STATEQUAL function compares the status lists of a dimension.
Return Value
BOOLEAN
Syntax
STATEQUAL (statlist1, statlist2 [INORDER])
Parameters
The status list of a dimension as specified using the name of the dimension, a valueset dimensioned by the dimension; or a LIMIT or SORT function for the dimension.
The status list of the same dimension as that specified by statlist1. You can specify statlist2 using the name of the dimension, a valueset dimensioned by the dimension; or a LIMIT or SORT function for the dimension.
Specifies that the values must be in the same order.
The MOVINGMAX function (abbreviated MVMAX) returns a series of maximum values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMAX searches the data for the maximum value in the range specified, relative to the current dimension value.
When the variable or expression has only the specified dimension, MOVINGMAX produces a single series of maximum values, one for each dimension value in the status. When the variable or expression has dimensions other than the one specified, MOVINGMAX produces a separate series of maximum values for each combination of values in the status list of the other dimensions
Return Value
DECIMAL when the data type of expression is DECIMAL or SHORT; otherwise, NUMBER.
Syntax
MOVINGMAX(expression, start, stop, step, [dimension [STATUS|limit-clause]])
Parameters
A numeric variable or calculation from whose values you want to find the maximum values; for example, units
or sales-expense
.
Integer values that specify the range of values over which you want to find the maximum values. The value of start specifies the beginning of the range. The value of stop specifies the end of the range. Specify the values of start and stop relative to the current value of dimension. To specify a negative position for start or stop preceed the value with a comma. Thus, you specify zero (0
) for the current dimension value, and -1
for the value preceding the current value. (See also "How the Moving Functions Handle Out-of-Range and NA Values".)
Note: By default this function uses the default status list when identifying the range of values to average. You can specify either the STATUS or limit-clause phrase to change this behavior. |
Tip: When you want to range to the end of status, for convience and to document your intent, specify the value of stop as OBJ function with the DIMMAX keyword |
A positive whole number that specifies whether to search every value in the range, every other value, every third value, and so on. A value of 1
for step means search every value. A value of 2
means check the first value, the third value, the fifth value, and so on. For example, when the current month is Jun96
and the start and stop values are -3
and 3
, a step value of 2
means search the months Mar96
, May96
, Jul96
, and Sep96
and return the maximum value that occurs in one of those four months.
The dimension over which the moving maximum is calculated. While this can be any dimension, it is typically a hierarchical time dimension of type TEXT that is limited to a single level (for example, the month or year level) or a dimension with a type of DAY, WEEK, MONTH, Quarter, or YEAR.
When expression has a dimension with a type of DAY, WEEK, MONTH, QUARTER, or YEAR and you want MOVINGMAX to use that dimension, you can omit the dimension argument.
Specifies that MOVINGMAX uses the values specified by a LIMIT function or those specified with the same syntax as any of the limit-clause arguments in the various forms of the LIMIT command (that is, the syntax of the LIMIT command after the limit-type argument such as "TO"). For the syntax of these arguments, see LIMIT (using values) command, LIMIT using LEVELREL command, LIMIT (using parent relation), LIMIT (using related dimension) command, LIMIT NOCONVERT command, and LIMIT command (using POSLIST).
Examples
For an example of calculating maximum sales, see Example 8-31, "Calculating a Moving Average".
The CURRENT_TIMESTAMP function returns the current date and time in the session time zone, as a value of data type TIMESTAMP_TZ
. The time zone offset reflects the current local time of the session.
When you want to retrieve the current date and time in the session time zone as a TIMESTAMP
value, use the LOCALTIMESTAMP function.
Return Values
TIMESTAMP_TZ
Syntax
CURRENT_TIMESTAMP [ (precision) ]
Parameters
The fractional second precision of the time value returned. When you omit this argument, then the function uses a default value of 6
.
Examples
The SIGNAL command produces a user-defined error message from within a program. When Oracle OLAP executes a SIGNAL statement when TRAP is ON, execution branches to the trap label. Any statements following the trap label in the program are then executed.When the program contains an active trap label, execution branches to the label. Without a trap label, execution of the program terminates and, when the program was called by another program, execution control returns to the calling program.
Syntax
SIGNAL {errname [message]|STOP}
Parameters
A TEXT expression that indicates the name of the error message to be produced. When Oracle OLAP executes a SIGNAL statement, it stores the errname in the ERRORNAME option. Normally, the name of the error does not appear in the error message. However, when you omit message, the error name (errname) appears along with a stock message as described in the message argument.
You can use the special name PRGERR
to communicate to a calling program that an error has occurred. The statement SIGNAL
PRGERR
sets ERRORNAME to a blank value and passes an error condition to the calling program without causing another error message to be displayed. For a complete explanation of how to use SIGNAL to pass an error up a chain of nested programs, see the TRAP command.
A TEXT expression that specifies the error message to be produced. When you supply a long line as your error message, you must add your own line breaks to format the text. Type the newline escape sequence (\n) where you want each line to end. You can type up to a limit of 6 lines or 4,000 characters, whichever you reach first. An error occurs when you try to supply a single line longer than 4,000 characters.
When you omit this argument, SIGNAL produces the following message.
ERROR: (errname) Please contact the administrator of your Oracle Oracle OLAP application.
When Oracle OLAP executes a SIGNAL statement, it stores message in the ERRORTEXT option.
Immediately stops execution of all currently running programs. No error message is produced. The error condition is not trapped by an active TRAP label.
Examples
Example 10-123 Signaling an Error
Suppose you have written a program that requires one argument. When no argument is supplied, there is no purpose in running the program. Therefore, the first thing the program does is check if an argument has been passed. When it has not, the program terminates after sending an error message to the current outfile.
The following program lines check for the argument and signal an error when it is not found.
IF ARGS EQ '' THEN SIGNAL msg1 'You must supply an argument.'
SIGNAL sends the following message to the current outfile.
ERROR: You must supply an argument.
Example 10-124 Signaling an Error When an Argument Value is Invalid
Suppose your program produces a report that can present from one to nine months of data. You can signal an error when the program is called with an argument value greater than nine. In this example, nummonths
is the name of the argument that must be no greater than nine.
select: TRAP ON error PUSH month LIMIT month TO nummonths IF STATLEN(month) GT 9 THEN SIGNAL toomany - 'You can specify no more than 9 months.' REPORT DOWN district W 6 units finish: POP month RETURN error: POP month IF ERRORNAME EQ 'TOOMANY' THEN SHOW 'No report produced'
The DATEFORMAT option holds the template used for displaying DATE-only data type values and converting DATE-only values to text values. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). It can also include additional text.
Data Type
TEXT
Syntax
DATEFORMAT = template
Parameters
A TEXT expression that specifies the template for displaying dates. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components. The default template is '<DD><MTXT><YY>'
.
Table 5-2, "DATEFORMAT Templates for Day", Table 5-3, "DATEFORMAT Templates for Week", Table 5-4, "DATEFORMAT Templates for Month", and Table 5-5, "DATEFORMAT Templates for Year" present the valid formats for each component. The tables provide two display examples, one for March 1, 1990 and another for November 12, 2051.
Table 5-2 DATEFORMAT Templates for Day
Format | Meaning | March 1, 1990 | November 12, 2051 |
---|---|---|---|
|
One digit or two digits |
|
|
|
Two digits |
|
|
|
Space-padded, two digits |
|
|
|
Ordinal, uppercase |
|
|
|
Ordinal, lowercase |
|
|
Table 5-3, "DATEFORMAT Templates for Week" presents the valid formats for weeks. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.
Table 5-3 DATEFORMAT Templates for Week
Format | Meaning | March 1, 1990 | November 12, 2051 |
---|---|---|---|
|
Numeric |
|
|
|
First letter, uppercase |
|
|
|
First three letters, uppercase. |
|
|
|
First three letters, lowercase |
|
|
|
Full name, uppercase |
|
|
|
Full name, lowercase |
|
|
Note that when you specify a format of <WTXT>
, <WTXTL>
, <WTEXT>
, or <WTEXTL>
, the case in which the value is specified in DAYNAMES affects the displayed value:
When the name in DAYNAMES is entered as all lowercase, the entire name is converted to uppercase. Otherwise, the first letter is converted to uppercase and the second and subsequent letters remain in their original case.
When the name in DAYNAMES is entered as all uppercase, the second and subsequent letters are converted to lowercase. Otherwise, the entire name remains in the case specified in DAYNAMES.
Table 5-4, "DATEFORMAT Templates for Month" presents the valid formats for months. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.
Table 5-4 DATEFORMAT Templates for Month
Format | Meaning | March 1, 1990 | November 12, 2051 |
---|---|---|---|
|
One digit or two digits |
1 |
|
|
Two digits |
|
|
|
Space-padded, two digits |
|
|
|
First letter, uppercase |
|
|
|
First three letters, uppercase |
|
|
|
First three letters, lowercase |
|
|
Note that when you specify a format of <MTXT>
or <MTXTL>
, the case in which the value is specified in MONTHNAMES affects the displayed value:
When the name in MONTHNAMES is entered as all lowercase, the entire name is converted to uppercase. Otherwise, the first letter is converted to uppercase and the second and subsequent letters remain in their original case.
When the name in MONTHNAMES is entered as all uppercase, the second and subsequent letters are converted to lowercase. Otherwise, the entire name remains in the case specified in MONTHNAMES.
Table 5-5, "DATEFORMAT Templates for Year" presents the valid formats for years. The table provides two display examples, one for March 1, 1990 and another for November 12, 2051.
Usage Notes
Specifying Angle Brackets as Text in a DATEFORMAT Template
To include an angle bracket as additional text in a template, specify two angle brackets for each angle bracket to be included as text (for example, to display the entire date in angle brackets, specify '<<<D><M><YY>>>'
).
Month and Day Names
The names used in the month component for the MT, MTXT, MTXTL, MTEXT, and MTEXTL formats are drawn from the current setting of the MONTHNAMES option. The names used in the day-of-the-week component for the WT, WTXT, WTXTL, WTEXT, and WTEXTL formats are drawn from the current setting of the DAYNAMES option.
Specifying Abbreviations for Day and Month
You can set the DAYABBRLEN and MONTHABBRLEN options to use abbreviations of different lengths for day and month names.
Out-of-Range Years for DATEFORMAT
When you specify the YY format, and a year outside the range of 1950 to 2049 is to be displayed, the year is displayed in four digits.
Automatic Conversion of DATE-only Values to Text Values
When you use a value with DATE-only data type where a text data type is expected. Oracle OLAP also uses the date template in the DATEFORMAT option to automatically convert the date to a text value. When you want to override the current DATEFORMAT template, you can convert the date result to text by using the CONVERT function with a date-format argument.
Once a DATE-only value is stored in a text variable, the DATEFORMAT template is no longer used to format the display of the value, and subsequent changes to DATEFORMAT have no impact.
DATE-only Dimension Values
The DATEFORMAT option does not how Oracle OLAP displays DATE-only values of DAY, WEEK, MONTH, QUARTER, and YEAR dimensions. How these values are displayed is controlled by a VNF (value name format) attached to the dimension definition, or by default conventions for DAY, WEEK, MONTH, QUARTER, and YEAR dimensions as described in Table 2-5, "Default VNFs for DWMQY Dimensions".
Examples
Example 5-13 Changing the Format of Dates
The following statements define a DATE-only variable and set its value to March 24, 1997, then set the date format to two digits each in the order of day, month, and year, and send the result to the current outfile.
DEFINE datevar VARIABLE DATE datevar = '24Mar97' DATEFORMAT = '<DD>/<MM>/<YY>' SHOW datevar
These statements produce the following output.
24/03/97
The following statements change the date format to month (text), day (two digits), and year (four digits), and send the result to the current outfile.
DATEFORMAT = '<MTEXTL> <D>, <YYYY>' SHOW DATEVAR
These statements produce the following output.
March 24, 1997
The following statements change the date format to day of the week (text), month (text), day (one or two digits), and year (four digits), and send the result to the current outfile.
DATEFORMAT = '<WTEXTL> <MTEXTL> <D>, <YYYY>' SHOW DATEVAR
These statements produce the following output.
Monday March 24, 1997
Example 5-14 Including Text in the Format of a Date
The following statements save and then change the DATEFORMAT option to include extra text for an analytic workspace startup greeting.
PUSH DATEFORMAT DATEFORMAT = 'Hello. Today is <wtextl>, the <dtl> - OF <MTEXTL>.' SHOW TODAY POP DATEFORMAT
When today's date is May 30, 1997, the following output is sent to the current outfile when the program is run.
Hello. Today is Friday, the 30th of May.
The AW_DELETE
procedure deletes an existing analytic workspace.
Syntax
AW_DELETE ( awname IN VARCHAR2);
AW_DELETE ( schema IN VARCHAR2, awname IN VARCHAR2);
Parameters
Table B-14 AW_DELETE Procedure Parameters
Parameter | Description |
---|---|
|
The schema that owns awname. |
|
The name of an existing analytic workspace that you want to delete along with all of its contents. You must be the owner of awname or have DBA rights to delete it, and it cannot currently be attached to your session. The AW$awname file is deleted from the database. |
Example
The following command deletes the SALES_DEMO
analytic workspace in the user's default schema.
EXECUTE dbms_aw.aw_delete('sales_demo');
The DEFAULTAWSEGSIZE option holds the default maximum segment size for an analytic workspace created in your Database session. The setting is in effect for the duration of your session. For each new session, DEFAULTAWSEGSIZE reverts to the default value.
Tip: To change the maximum size for new segments in an existing workspace, use the AW command with the SEGMENTSIZE keyword. To discover the current maximum size for new segments, use the AW function with the SEGMENTSIZE keyword. |
Syntax
DEFAULTAWSEGSIZE = n
Parameters
The number of bytes.
Examples
This chapter provides reference topics for the second set (in alphabetical order) of the OLAP DML commands. There is one topic for each of the OLAP DML commands that begins with the letters H-Z, beginning with HEADING.
Alphabetical and categorical listings of the OLAP DML commands and reference topics for the remaining OLAP DML commands appear in Chapter 9, "OLAP DML Commands: A-G".
For other OLAP DML reference topics, see Chapter 4, "OLAP DML Properties", Chapter 5, "OLAP DML Options", Chapter 7, "OLAP DML Functions: A - K", and Chapter 8, "OLAP DML Functions: L - Z".
Tip: Many OLAP DML statements can be coded as a 3-character abbreviation that consists of the first letter of the statement plus the next two consonants. |
Within an OLAP DML program, the IF...THEN...ELSE command executes one or more statements in a program when a specified condition is met. Optionally, it also executes an alternative statement or group of statements when the condition is not met.
Note: You can also use IF as a conditional operator in an expression. See "IF...THEN...ELSE expression" |
Syntax
IF boolean-expression
THEN statement1
[ELSE statement2]
Parameters
Any valid Boolean expression that returns either TRUE
or FALSE
.
Oracle OLAP executes the statement1 argument when the Boolean expression is TRUE
. The statement1 must be on the same line as THEN.
Oracle OLAP executes the statement2 argument when the Boolean expression is FALSE
. The statement2 must be on the same line as ELSE. When you omit the ELSE phrase, execution continues with the statement after the whole IF...THEN... statement in the program.
Usage Notes
IF with DO
You can use an IF statement for conditional execution of two or more statements by following the THEN or ELSE (or both) keywords with a DO ... DOEND sequence. See Example 10-5, "Using IF...THEN...ELSE".
Single or Multiple Lines
When IF is used as an expression, the THEN and ELSE keywords must be on the same line as IF. When IF is used as a command, THEN and ELSE must be on separate lines.
Examples
Example 10-5 Using IF...THEN...ELSE
The following lines from a program illustrate the use of IF...THEN...ELSE.... When the Boolean expression ANY(DOLLARS LT 200000)
is TRUE
, the statements following THEN (statement group 1
) are executed. When the expression is FALSE
, the statements following ELSE (statement group 2
) are executed instead.
IF ANY(DOLLARS LT 200000) THEN DO ... " (statement group 1) DOEND ELSE DO ... "(statement group 2) DOEND
Example 10-6 Using IF as a Conditional Operator
In a program that produces a report, you would like to report a previous year's actual expenses or the current year's budget, depending on the year passed to the program as an argument. A conditional expression in a JOINCHARS function produces a heading with the word Actual or Budget. Another conditional expression selects the variable to report. The program would include the following lines.
ARGUMENT cur.year year LIMIT month TO year cur.year REPORT - HEADING JOINCHARS( 'Expenses: ' - IF cur.year LT 'Yr95' - THEN 'Actual FOR ' - ELSE 'Budget FOR ', - cur.year ) - IF cur.year LT 'Yr95' THEN actual ELSE budget
The CONTINUE command transfers program control to the end of a FOR or WHILE loop (just before the DO/DOEND statement), allowing the loop to repeat. You can use CONTINUE only within programs and only with FOR or WHILE.
For more information on controlling program execution, see also "Program Flow-of-Control".
Syntax
CONTINUE
Examples
Example 9-73 Skipping Over Code in a FOR Loop
In the following lines from a program, an IF statement is used to test whether total sales for a district exceed 5,000,000. When sales are more this amount, the program goes on to produce a report for that district. However, when a district's sales are less than the amount, the CONTINUE statement is used to transfer control to the end of the FOR loop (just before the DOEND statement). No lines are produced for that district, and the program goes on to test the next district in the status list.
... FOR district DO IF TOTAL(sales, district) LT 5000000 THEN CONTINUE ... "(report statements for districts with total sales above 5,000,000) DOEND ...
The NUMCHARS function counts the number of characters in a text expression. When the value is a multiline text value, NUMCHARS returns the total number of characters in all the lines. The result returned by NUMCHARS has the same dimensions as the specified expression.
Tip: When you are using a multibyte character set, you can use the NULLIF function instead of the NUMCHARS function. |
Return Value
INTEGER
Syntax
NUMCHARS(text-expression)
Parameters
The text expression whose characters are to be counted. NUMCHARS accepts either a TEXT or NTEXT argument. It does not perform an automatic conversion to either data type. It returns the information that is correct for the data type of the specified argument.
Examples
Example 8-43 Counting the Characters in the Longest Name
You would like to know the length of the names of your products so you can specify the appropriate width for the label column in a report. You can use the NUMCHARS function in combination with the LARGEST function to find the length of the longest label. Then use that value to set the column size. The following statements in a program find the longest name and use the character count to format a report.
firstcol = LARGEST(NUMCHARS(name.product))+1 LIMIT month TO FIRST 3 FOR product DO ROW WIDTH FIRSTCOL name.product WIDTH 6 ACROSS month - FIRST 3: units DOEND
When the program is run, it produces the following output.
3-Person Tents 200 203 269 Aluminum Canoes 347 400 482 Tennis Racquets 992 1,076 1,114 Warm-up Suits 1,096 1,214 1,294 Running Shoes 2,532 2,405 2,775
For output produced by the REPORT and ROW commands, the ZEROROW option suppresses report rows with numeric values that are all NAs or all zeros or would be represented as zeros. When your report includes a small number, such as 0.004
, the number of decimal places being shown affects whether ZEROROW treats that number as zero. When you are producing a report with totals, the actual number is used to calculate the total, even when the number is suppressed.
Data Type
BOOLEAN
Syntax
ZEROROW = {YES|NO}
Parameters
Suppresses report rows that contain any numeric values when all the numeric values would be shown either as zeros or NAs.
(Default) Produces all rows of the report, regardless of the values they contain.
Usage Notes
Non-Numeric Data
Even when a row contains non-numeric data, such as TEXT, ID, or BOOLEAN values, along with numeric values, the row is suppressed when ZEROROW is YES and all the numeric values would be shown either as zeros or NAs.
The Effect of NASPELL and ZSPELL
The value of NASPELL does not affect the way ZEROROW handles NA
values. The value of ZSPELL does not affect the functioning of ZEROROW; numeric zero values are treated as zeros regardless of their spelling in output.
Examples
Example 5-116 Suppressing Report Rows of All-Zero Data
Suppose you have a variable called worstcase
, that is dimensioned by division
, month
, and line
, in which you store the results of calculations to project sales. When you produce a report of the results, you want to suppress any rows for which the value of the worst-case projections is zero for all months in the status. Set ZEROROW to YES, as shown in the following statements.
ZEROROW = YES LIMIT line TO 'Revenue' LIMIT month TO 'Nov95' TO 'Feb96' REPORT WIDTH 8 DOWN division ACROSS month: worstcase
These statements produce the following report.
LINE: REVENUE -----------------WORSTCASE----------------- -------------------MONTH------------------- DIVISION Nov95 Dec95 Jan96 Feb96 -------- ---------- ---------- ---------- ---------- Camping 0.00 0.00 45,500.00 47,400.00 Sporting 0.00 0.00 29,200.00 28,400.00 Clothing 0.00 0.00 15,200.00 14,900.00
In the preceding report, no rows are suppressed, since some months for each division have projected sales. However, when you lay out this report with month
down and division
across, the rows for Nov95
and Dec95
are suppressed, because these months have no projected sales.
REPORT DOWN month ACROSS division: worstcase
This statement produces the following report.
LINE: REVENUE -----------WORSTCASE------------ ------------DIVISION------------ MONTH Camping Sporting Clothing -------------- ---------- ---------- ---------- Jan96 45,500.00 29,200.00 15,200.00 Feb96 47,400.00 28,400.00 14,900.00
The COLWIDTH option controls the default width of data columns in report output. For output from the ROW command and HEADING command, COLWIDTH affects all columns except the first column. For output from REPORT, COLWIDTH affects all data columns and the label columns for a composite or a conjoint dimension.
Note: For an individual column, the COLWIDTH value is always overridden by a WIDTH attribute in a HEADING, REPORT, or ROW command |
Data Type
INTEGER
Syntax
COLWIDTH = n
Parameters
An INTEGER
expression that specifies the desired column width in number of characters. You can set COLWIDTH to any value from 1
to 4,000
. The default is 10
.
Note: The maximum width of a line in a report is 4,000 characters. Therefore, the combined width of all the columns of a report cannot be greater than 4,000 characters. |
Examples
Example 5-9 Setting the Default Column Width in a Report
Suppose you want to look at unit sales for six months. Since the data values are not large, you do not need a width of 10 characters for your data columns. You can set COLWIDTH to provide a narrower default column.
LIMIT district TO 'Atlanta' LIMIT month TO 'Oct95' TO 'Mar96' COLWIDTH = 6 REPORT ACROSS month: units
These statements produce the following output.
DISTRICT: ATLANTA ------------------UNITS------------------ ------------------MONTH------------------ PRODUCT Oct95 Nov95 Dec95 Jan96 Feb96 Mar96 -------------- ------ ------ ------ ------ ------ ------ Tents 503 345 259 279 305 356 Canoes 317 282 267 281 309 386 Racquets 1,365 1,270 1,357 1,125 1,304 1,263 Sportswear 3,065 2,327 1,955 2,591 2,829 3,137 Footwear 3,445 3,247 2,831 3,089 3,282 3,475
The ADVISE_DIMENSIONALITY
procedure evaluates the information provided by the ADVISE_SPARSITY
procedure and generates the OLAP DML commands for defining a composite and a variable in the analytic workspace.
Note: You cannot execute this procedure from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL*Plus. |
Syntax
ADVISE_DIMENSIONALITY ( output OUT CLOB, cubename IN VARCHAR2, sparsename IN VARCHAR2 DEFAULT NULL, dtype IN VARCHAR2 DEFAULT 'NUMBER', advtable IN VARCHAR2 DEFAULT NULL);
Parameters
Table B-5 ADVISE_DIMENSIONALITY Procedure Parameters
Parameter | Description |
---|---|
|
The name of an object (such as a PL/SQL variable) in which the recommendations of the procedure is stored. |
|
The same cubename value provided in the call to |
|
An object name for the sample composite. The default value is |
|
The OLAP DML data type of the sample variable. |
|
The name of the table created by the |
Example
The following PL/SQL program fragment defines a variable named DEFS
to store the recommended definitions.
DECLARE defs CLOB; BEGIN -- Calls to ADD_DIMENSION_SOURCE and ADVISE_SPARSITY omitted here . . . dbms_aw.advise_dimensionality(defs, 'units_cube_measure_stored', 'units_cube_composite', 'DECIMAL'); dbms_output.put_line('Definitions: '); dbms_aw.printlog(defs); END; /
The program uses the DBMS_OUTPUT.PUT_LINE
and DBMS_AW.PRINTLOG
procedures to display the recommended object definitions.
Definitions: DEFINE units_cube.cp COMPOSITE <product customer time> DEFINE units_cube NUMBER VARIABLE <channel units_cube.cp<product customer time>>
In contrast to the Global schema, which is small and dense, the Sales cube in the Sales History sample schema is large and very sparse, and the Sparsity Advisor recommends 11 partitions. The following excerpt shows some additional OLAP DML definitions for defining a partition template and moving the TIME
dimension members to the various partitions.
Definitions: DEFINE sales_cube_composite_p1 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p2 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p3 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p4 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p5 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p6 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p7 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p8 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p9 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p10 COMPOSITE COMPRESSED <time channel product promotion customer> DEFINE sales_cube_composite_p11 COMPOSITE <channel product promotion customer> DEFINE sales_cube_pt PARTITION TEMPLATE <time channel product promotion customer> - PARTITION BY LIST (time) - (PARTITION p1 VALUES () <sales_cube_composite_p1<>> - PARTITION p2 VALUES () <sales_cube_composite_p2<>> - PARTITION p3 VALUES () <sales_cube_composite_p3<>> - PARTITION p4 VALUES () <sales_cube_composite_p4<>> - PARTITION p5 VALUES () <sales_cube_composite_p5<>> - PARTITION p6 VALUES () <sales_cube_composite_p6<>> - PARTITION p7 VALUES () <sales_cube_composite_p7<>> - PARTITION p8 VALUES () <sales_cube_composite_p8<>> - PARTITION p9 VALUES () <sales_cube_composite_p9<>> - PARTITION p10 VALUES () <sales_cube_composite_p10<>> - PARTITION p11 VALUES () <time sales_cube_composite_p11<>>) MAINTAIN sales_cube_pt MOVE TO PARTITION p1 - '06-JAN-98', '07-JAN-98', '14-JAN-98', '21-JAN-98', - '24-JAN-98', '28-JAN-98', '06-FEB-98', '07-FEB-98', - '08-FEB-98', '16-FEB-98', '21-FEB-98', '08-MAR-98', - '20-MAR-98', '03-JAN-98', '26-JAN-98', '27-JAN-98' MAINTAIN sales_cube_pt MOVE TO PARTITION p1 - '31-JAN-98', '11-FEB-98', '12-FEB-98', '13-FEB-98', - '15-FEB-98', '17-FEB-98', '14-MAR-98', '18-MAR-98', - '26-MAR-98', '30-MAR-98', '05-JAN-98', '08-JAN-98', - '10-JAN-98', '16-JAN-98', '23-JAN-98', '01-FEB-98' MAINTAIN sales_cube_pt MOVE TO PARTITION p1 - '14-FEB-98', '28-FEB-98', '05-MAR-98', '07-MAR-98', - '15-MAR-98', '19-MAR-98', '17-JAN-98', '18-JAN-98', - '22-JAN-98', '25-JAN-98', '03-FEB-98', '10-FEB-98', - '19-FEB-98', '22-FEB-98', '23-FEB-98', '26-FEB-98' . . .
The LAST_DAY function returns the last day of the month in which a particular date falls.
Return Value
DATETIME
Syntax
LAST_DAY(datetime-expression)
Parameters
An expression that has the DATETIME data type.
Examples
Example 8-5 Calculating Remaining Days in a Month
The following statement calculates how many days remain between today's date and the end of the month.
SHOW JOINCHARS('Days left: ' LAST_DAY(SYSDATE) - SYSDATE)
When today's date is September 8, 2000, then this statement returns the following.
Days left: 22
The NLS_CHARSET_NAME function returns the name of the character set corresponding to a specified character set identification number.
Return Value
When the number is recognized as a valid character, VARCHAR2; otherwise, NA.
Syntax
NSL_CHARSET_NAME (number-exp)
Parameters
A number that is the character set ID.
The AW_TABLESPACE
function returns the name of the tablespace in which a particular analytic workspace is stored.
Note: You cannot execute this function from within the OLAP Worksheet. You must execute if in a SQL tool such as SQL*Plus. |
Syntax
AW_TABLESPACE ( awname IN VARCHAR2) RETURN VARCHAR2;
AW_TABLESPACE ( schema IN VARCHAR2, awname IN VARCHAR2) RETURN VARCHAR2;
Returns
Name of a tablespace.
Parameters
Table B-17 AW_TABLESPACE Function Parameters
Parameter | Description |
---|---|
|
The schema that owns awname. |
|
The name of an analytic workspace. |
Example
The following example shows the tablespace in which the GLOBAL
analytic workspace is stored.
SET serveroutput ON EXECUTE dbms_output.put_line('Sales History is stored in tablespace ' || dbms_aw.aw_tablespace('sh_aw', 'sales_history'));
This command generates the following statement:
Sales History is stored in tablespace SH_AW
The TO_NUMBER function converts a formatted TEXT or NTEXT expression to a number. This function is typically used to convert the formatted numeric output of one application (which includes currency symbols, decimal markers, thousands group markers, and so forth) so that it can be used as input to another application.
Return Value
NUMBER. Negative return values contain a leading negative sign, and positive values contain a leading space, unless the format model contains the MI
, S
, or PR
format elements.
Syntax
TO_NUMBER(text-exp, [fmt,] [nlsparams])
Parameters
A text expression that contains a number to be converted. The expression can have the TEXT or NTEXT data type. A conversion from NTEXT can result in an incorrect result when the NTEXT value cannot be interpreted as a number.
A text expression that identifies a number format model. This model specifies how the conversion to NUMBER should be performed.
The default number format identifies a period (.
) as the decimal marker and does not recognize any other symbol.
A text expression that specifies the thousands group marker, decimal marker, and currency symbols used in text-exp. This expression contains one or more of the following parameters, separated by commas:
NLS_CURRENCY symbol
NLS_ISO_CURRENCY territory
NLS_NUMERIC_CHARACTERS dg
A text expression that specifies the local currency symbol. It can be no more than 10 characters.
A text expression that identifies the territory whose ISO currency symbol is used.
A text expression composed of two different, single-byte characters for the decimal marker (d
) and thousands group marker (g
).
These parameters override the default values specified by the NLS_CURRENCY, NLS_ISO_CURRENCY, and NLS_NUMERIC_CHARACTERS options. Refer to NLS Options for additional information.
Usage Notes
Default Number Format Values
The values of some formats are determined by the value of NLS_TERRITORY.
Possible Effects of TO_NUMBER Rounding
All number format models cause the number to be rounded to the specified number of significant digits. Table 8-17, "Possible Effects of Rounding" identifies some effects of rounding.
Examples
Example 8-144 Converting Text Data to Decimal Data
The following statements convert a text string to a DECIMAL data type by identifying the local currency symbol (L
), the thousands group separator (G
) and the decimal marker (D
). The NLS_NUMERIC_CHARACTERS option identifies the characters used for the G
and D
format, since they are different from the current setting for the session.
DEFINE money VARIABLE DECIMAL money = TO_NUMBER('$94 567,00', 'L999G999D00', NLS_NUMERIC_CHARACTERS ', ') SHOW money
The output of this statement is:
94,567.00
The FINDBYTES function returns the byte position of the beginning of a specified group of bytes within a text expression.
Tip: When you are using a single-byte character set, you can use the FINDCHARS function instead of the FINDBYTES function. |
Return Value
INTEGER
Syntax
FINDBYTES(text-expression, bytes [starting-pos [LINENUM]])
Parameters
The TEXT
expression in which you are searching for the specified bytes. The value of text-expression can be a multiline value. In this case, FINDBYTES searches all lines for the specified bytes. The match must be exact, including a match of upper- and lowercase characters.
Tip: When you must use this function on NTEXT values, use the CONVERT or TO_CHAR function to convert the NTEXT value to TEXT. |
The group of bytes for which you are searching. When bytes is a multiline value, FINDBYTES ignores all lines except the first one.
When bytes is not found in text-expression, FINDBYTES returns zero. When the group of bytes occurs more than once, FINDBYTES returns the position of its first occurrence.
An INTEGER expression that specifies the byte position where the search in text-expression should start. The default is at position 1 (the first byte) in text-expression.
Specifies that FINDBYTES should return the line number instead of the byte position of the beginning of the specified text.
Examples
Example 7-95 Finding the Starting Position of a Byte Group
This example shows how to find the starting position of various groups of bytes in the literal TEXT value hellotherejoe
.
The statement
SHOW FINDBYTES('hellotherejoe', 'joe')
produces the following output.
11
The statement
SHOW FINDBYTES('hellotherejoe', 'al')
produces the following output.
0
The VARCACHE option specifies whether Oracle OLAP stores or caches all variable data that is the result of the execution of an AGGREGATE function or $NATRIGGER property expression.
Important: The value of the VARCACHE option is only one factor that Oracle OLAP uses to determine whether variable data computed when the AGGREGATE function or $NATRIGGER property executes is stored or cached. For a discussion of the other factors and their interrelationship, see "How Oracle OLAP Determines Whether to Store or Cache Results of $NATRIGGER" and "How Oracle OLAP Determines Whether to Store or Cache Aggregated Data". |
Syntax
VARCACHE = {VARIABLE | SESSION | NONE}
Parameters
Specifies that Oracle OLAP stores the data in the variable in the Database. When you specify this option, the results of the calculation are permanently stored in the variable when the analytic workspace is updated and committed.
Specifies that Oracle OLAP caches the calculated data in the session cache (See "What is an Oracle OLAP Session Cache?"). When you specify this option, the results of the calculation are ignored during updates and commits and are discarded after the session.
Important: When SESSCACHE is set toNO , Oracle OLAP does not cache the data even when you specify SESSION . In this case, specifying SESSION is the same as specifying NONE . |
For data that is calculated on the fly using the AGGREGATE function, specifies that Oracle OLAP calculates the data each time the AGGREGATE function executes; Oracle OLAP does not store or cache the data calculated by the AGGREGATE function
Usage Notes
The VARCACHE Option Can Affect All Variables
When you set the VARCACHE option, its setting can affect all variables. When you have not set the $VARCACHE
property on a variable and there is no CACHE statement in the aggmaps that you use with the AGGREGATE function to calculate data on the fly, then it is the VARCACHE option that determines how or if that data is stored.
The LD command adds the description to the current object definition. The description consists of text that you specify to describe the object. You can assign a description to any type of definition.
Tip: The current object definition is the definition of the object that has been most recently defined or considered during the current session. To make an object definition the current definition, use a CONSIDER statement. |
Syntax
LD [text]
Parameters
The text of the description you want to assign to the definition. When text is omitted, any existing description for the current definition is deleted.
You can create a multiline description by using a hyphen as a continuation character. However, you cannot create a description with an initial blank line with an LD statement.
Examples
Example 10-13 Adding a Description to the Definition of a Variable
This example changes the description associated with the variable units
. First, execute the CONSIDER statement to make units
the current definition. Then use a LD statement to assign a new description. The units
variable has the following definition.
DEFINE units VARIABLE INTEGER <month product district> LD Actual Unit Shipments
The statements
CONSIDER units ld Actual Unit Shipments for Each Division DESCRIBE units
produce the following definition for units
.
DEFINE units VARIABLE INTEGER <month product district> LD Actual Unit Shipments for Each Division
The UNHIDE command makes visible the text of a program that has been made invisible by a HIDE statement. To use UNHIDE, you must know the seed expression that was used with the HIDE statement when the program was hidden.
Note: The HIDE command performs simple encoding. For information on using secure encryption and other security features in Oracle, see Oracle Database Security Guide. |
Syntax
UNHIDE prog-name seed-exp
Parameters
The name of a program whose text has been made invisible by using the HIDE statement. Do not enclose the program name in quotes.
The single-line text expression that was used in the HIDE statement when "prog-name" was hidden. The seed expression must be byte-for-byte the same value as you used in the HIDE statement. Also, since the seed expression is case-sensitive, specify uppercase and lowercase characters carefully.
Usage Notes
Forgetting the Seed Expression
When you want to use an UNHIDE statement on a program but you have forgotten the seed expression, you can call Oracle OLAP Products Technical Support for help in solving your problem. Before calling, make a connection to Oracle OLAP from OLAP Worksheet, and in Oracle OLAP, attach the analytic workspace that contains the hidden program.
Examples
The INSCOLS function inserts into the columns of a multiline TEXT value all the columns of another TEXT value. The inserted columns are placed after the column position you specify, and the original columns in each line are moved to the right. The function returns a multiline TEXT value composed of the resulting columns.
Return Value
TEXT or NTEXT
This function accepts TEXT values and NTEXT values as arguments. The data type of the return value depends on the data type of the values specified for the arguments:
When all arguments are TEXT values, the return value is TEXT.
When all arguments are NTEXT values, the return value is NTEXT.
When the arguments include both TEXT and NTEXT values, the function converts all TEXT values to NTEXT before performing the function operation, and the return value is NTEXT.
The number of lines in the return value is always the same as the number of lines in text-expression. When the columns TEXT expression has fewer lines, INSCOLS repeats its last line in each subsequent line of the return value.
Syntax
INSCOLS(text-expression columns [after])
Parameters
The expression into which you want to insert columns.
The expression containing one or more columns in each line. All the columns of this expression is inserted into the corresponding lines of text-expression.
An INTEGER
between 0 and 4,000 representing the column position after which columns should be inserted. The column position of the first character in each line is 1. When you do not specify after, insertion begins at the end of each line. The total length of a line cannot exceed 4,000 columns of single-byte characters or 2,000 columns of double-byte characters.
When you specify an after column that is to the right of the last character in a given line in text-expression, the corresponding line in the return value has spaces filling in the intervening columns.
Examples
Example 7-121 Inserting Text Columns
In the following example, a color code (stored in the multiline TEXT value itemcolor
) is inserted into item identifiers that are stored in the itemid
text value. The columns are inserted after Column 3.
itemcolor
has the following value.
Blu Red Gre Ora
itemid
has the following value.
542-Fra 379-Eng 968-USA 369-Can
The INSCOLS function call
INSCOLS(itemid itemcolor 3)
returns the following.
542Blu-Fra 379Red-Eng 968Gre-USA 369Ora-Can
The ZSPELL option holds the default text that is used for representing numeric zero values in output produced by the HEADING, REPORT, and ROW commands.
Data Type
TEXT
Syntax
ZSPELL = {'text'|'OFF'}
Parameters
The spelling to use as the default spelling for numeric zero values. When you specify an expression rather than a text literal, you can omit the single quotes.
(Default) Shows a zero (0) with the appropriate number of decimal places (determined by a DECIMAL attribute) for each numeric zero value.
Usage Notes
Assigning Zero Values
ZSPELL affects output only; it does not affect the way you assign a zero value. For example, even when you have set ZSPELL to NONE
, you still assign a zero value as follows.
var1 = 0
Showing Decimal Places
The default of OFF means that a zero value is shown as 0 (zero), with the number of decimal places indicated by a DECIMAL attribute (for example, 0.00
). When you set ZSPELL to the text character 0
, zero values are shown as a 0
with no decimal places, regardless of any DECIMAL specification.
Effect of ZSPELL on Values Close to Zero
When your output includes a small number, such as 0.004
, the number of decimal places shown affects whether ZSPELL treats the number as zero. See Example 5-118, "Showing Very Small Numbers".
Examples
Example 5-117 Showing Zero Values as NONE
This example changes the value of ZSPELL, so that a zero value in the DECIMAL variable testvar
is shown as NONE in report output. When ZSPELL is set to its default value of OFF
, the Oracle OLAP statements
testvar = 0.00 ROW testvar
produce the following output.
0.00
In contrast, these OLAP DML statements
ZSPELL = 'NONE' ROW testvar
produce the following output.
NONE
Example 5-118 Showing Very Small Numbers
This example illustrates how the number of decimal places shown in output affects whether ZSPELL treats very small numbers as zeros. When ZSPELL is set to its default value of OFF
, these OLAP DML statements
ZSPELL = 'OFF' testvar = 0.004 ROW DECIMAL 3 testvar
produce the following output.
0.004
The following statements set ZSPELL to NONE
and specify two decimal places for the output.
ZSPELL = 'NONE' ROW DECIMAL 2 testvar
These statements produce the following output.
NONE
With ZSPELL still set to NONE
, the following statement specifies three decimal places for the output.
ROW DECIMAL 3 testvar
This statement produces the following output.
0.004
For expressions dimensioned by a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR, the ENDOF function returns the last date of a time period that is first in the current status list of the dimension.
ENDOF is particularly useful when the dimension has a phase that differs from the default or when the time periods are formed from multiple weeks or years. For example, when the dimension has four-week time periods, the ENDOF function identifies the final date of a particular four-week period.
Return Value
DATE-only or text
Syntax
ENDOF(dwmqy-dimension)
Parameters
A dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. When you have explicitly defined your own relation between dimensions of this type, you can use the name of this time relation here.
Examples
Example 7-76 Finding the Fiscal Year End Date
The following statements define a year dimension (called taxyear
, for a tax year that begins in July), add dimension values for tax years 1998
through 2000
, and produce a report showing the last date of each tax year.
DEFINE taxyear DIMENSION YEAR BEGINNING july VNF 'TY<ffb>' MAINTAIN taxyear ADD '01july98' '01july00' REPORT W 14 ENDOF(taxyear)
These statements produce the following output.
TAXYEAR ENDOF(TAXYEAR) -------------- -------------- TY98 30JUN99 TY99 30JUN00 TY00 30JUN01
The COMMIT command executes a SQL COMMIT
statement. When you want changes that you have made in an analytic workspace to be committed when you execute the COMMIT command, then you must first update the workspace using an UPDATE statement. UPDATE moves changes from a temporary work area to the database table in which the workspace is stored. Changes that have not been moved to the table are not committed. When you do not use UPDATE and COMMIT statements, changes made to an analytic workspace during your session are discarded when you end your Oracle session.
When you execute a SQL COMMIT
statement in your database session, all changes made in your session (including all updated changes in workspaces that you have attached with read/write access) are committed. All committed changes are visible to other users who subsequently attach the workspace. However, another user's UPDATE and COMMIT statements do not affect your view of an already attached workspace.
Note: Many users execute DML statements using SQL*Plus® or OLAP Worksheet. Both of these tools automatically execute a COMMIT statement when you end your session |
Syntax
COMMIT
Examples
This chapter contains information about the following:
In the OLAP DML, as in other languages, a data type is a collection of values and the definition of one or more operations on those values.
The Oracle OLAP DML supports the data types outlined in Table 2-1, "Summary of OLAP DML Data Types".
Table 2-1 Summary of OLAP DML Data Types
Data Type | Abbreviation | Description |
---|---|---|
|
BOOL |
Represents the logical |
|
None |
Does not correspond to the SQL data type of the same name; but, instead, is an older data type that is unique to the OLAP DML. Day, month, and year data (but not hour and minute data) between January 1, 1000 A.D. and December 31, 9999 A.D. |
|
None |
Corresponds to the SQL Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the |
|
None |
Corresponds to the SQL Year, month, and day values of date, and hour, minute, and second values of time up to a precision of 9 places for the fractional part of the |
|
None |
Corresponds to the SQL All values of |
|
Corresponds to the SQL All values of
The default format is determined explicitly by the | |
|
None |
Corresponds to the SQL Stores a period of time in days, hours, minutes, and seconds. |
|
None |
Corresponds to the SQL Stores a period of time in years and months. |
|
INT |
A whole number in the range of (-2**31) to (2**31)-1. |
|
SHORTINT |
A whole number in the range of (-2**15) to (2**15)-1. |
|
LONGINT |
A whole number in the range of (-2**63) to (2**63)-1. |
|
DEC |
A decimal number with up to 15 significant digits in the range of -(10**308) to +(10**308). |
|
SHORT |
A decimal number with up to 7 significant digits in the range of -(10**38) to +(10**38). |
|
None |
A decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125). |
|
None |
Up to 4,000 bytes for each line in the Database character set. This data type is equivalent to the |
|
None |
Up to 4,000 bytes for each line in UTF-8 character encoding. This data type is equivalent to the |
|
None |
Up to 8 single-byte characters for each line in the database character set. ( |
|
None |
Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value. (Note that when defining a variable of this data type you specify the RANSPACE64 keyword in the DEFINE VARIABLE statement to increase the maximum number of characters for the values of the variable from nearly |
|
None. |
Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the ROWID pseudocolumn. |
|
None |
Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4,000 bytes. |
|
Specified for arguments and temporary variables in an OLAP DML program when you want to handle arguments without converting values to a specific data type. Use the WKSDATA function to retrieve the data type of an argument with a |
Categories of Data Types
Frequently, these data types are thought of as belonging to the following categories:
Numeric Data Types which are INTEGER
, SHORTINTEGER
, LONGINTEGER
, DECIMAL
, SHORTDECIMAL
, and NUMBER
Text Data Typeswhich are TEXT
, NTEXT
and ID
.
Datetime and Interval Data Types which includes the datetime data types of DATETIME
, TIMESTAMP
, TIMESTAMP_TZ
, and TIMESTAMP-LTZ
and the interval data types of DSINTERVAL
and YMINTERVAL
.
Date-only Data Type which is the DATE
data type that is unique to the OLAP DML.
Boolean Data Type which is BOOLEAN.
Row Identifier Data Types which are ROWID and UROWID.
Which OLAP DML Data Objects Can Have Which Data Type?
Different objects support the use of different data types for their values:
For variables, all of the data types are supported.
For dimensions and surrogates, the INTEGER
, NUMBER
, TEXT
, ID
(simple dimensions only), NTEXT
, DATETIME
, TIMESTAMP
, TIMESTAMP_TZ
, TIMESTAMP-LTZ
, DSINTERVAL
, and YMINTERVAL
data types are supported. Additionally, when you define a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR using a DEFINE DIMENSION (DWMQY) statement, the data type of the values of that dimension are DATE
-only.
The numeric data types described in Table 2-2, "OLAP DML Numeric Data Types" are supported.
Table 2-2 OLAP DML Numeric Data Types
Data Type | Data Value |
---|---|
|
A whole number in the range of (-2**31) to (2**31)-1. |
|
A whole number in the range of (-2**15) to (2**15)-1. |
|
A whole number in the range of (-2**63) to (2**63)-1. |
|
A decimal number with up to 15 significant digits in the range of -(10**308) to +(10**308). |
|
A decimal number with up to 7 significant digits in the range of -(10**38) to +(10**38). |
|
A decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125). |
For data entry, a value for any of these data types can begin with a plus (+) or minus (-) sign; it cannot contain commas. Note, however, that a comma is required before a negative number that follows another numeric expression, or the minus sign is interpreted as a subtraction operator. Additionally, a decimal value can contain a decimal point. For data display, thousands and decimal markers are controlled by the NLS_NUMERIC_CHARACTERS option.
Most of the numeric data types return NA
when a value is outside its range. However, the LONGINTEGER
data type does not have overflow protection and returns an incorrect value when, for example, a calculation produces a number that exceeds its range. Use the NUMBER
data type instead of LONGINTEGER
when this is likely to be a problem.
When you define a NUMBER
variable, you can specify its precision (p) and scale (s) so that it is sufficiently, but not unnecessarily, large. Precision is the number of significant digits. Scale can be positive or negative. Positive scale identifies the number of digits to the right of the decimal point; negative scale identifies the number of digits to the left of the decimal point that can be rounded up or down.
The NUMBER
data type is supported by Oracle Database standard libraries and operates the same way as it does in SQL. It is used for dimensions and surrogates when a text or INTEGER
data type is not appropriate. It is typically assigned to variables that are not used for calculations (like forecasts and aggregations), and it is used for variables that must match the rounding behavior of the Database or require a high degree of precision. When deciding whether to assign the NUMBER
data type to a variable, keep the following facts in mind to maximize performance:
Analytic workspace calculations on NUMBER
variables is slower than other numeric data types because NUMBER
values are calculated in software (for accuracy) rather than in hardware (for speed).
When data is fetched from an analytic workspace to a relational column that has the NUMBER
data type, performance is best when the data has the NUMBER
data type in the analytic workspace because a conversion step is not required.
The text data types described in Table 2-3, "OLAP DML Text Data Types" are supported by Oracle OLAP.
Table 2-3 OLAP DML Text Data Types
Data Type | Data Value |
---|---|
|
Up to 4,000 bytes for each line in the database character set. This data type is equivalent to the |
|
Up to 4,000 bytes for each line in UTF-8 character encoding. This data type is equivalent to the |
|
Up to 8 single-byte characters for each line in the database character set. ( |
Enclose text literals in single quotes. Oracle OLAP recognizes unquoted alpha-numeric values as object names and double quotes as the beginning of a comment.
You can embed quoted strings within a quoted string, which is necessary when you want to specify the base dimension value of a composite or conjoint dimension or when a value includes an apostrophe. Since a single quotation mark is used in Oracle OLAP to indicate a text string, it is considered a special character when used within such a string. Consequently, to specify the literal value of a single quotation mark within a text string, precede the quotation mark with a backslash.
For example, suppose you want to find out if New
York
and Apple
Sauce
are a valid combination of base dimension values in the markprod
conjoint dimension. The following statement produces the answer YES or NO
.
SHOW ISVALUE(markprod, '<\'New York\' \'Apple Sauce\'>')
When embedded quoted strings have a further level of embedding, you must use backslashes before each special character, such as the apostrophe and the backslash that must precede it in "Joe's Deli," as shown in the following statement.
SHOW ISVALUE(markprod, '<\'Joe\\\'s Deli\' \'Apple Sauce\'>')
Table 2-4, "Recognized Escape Sequences" shows escape sequences that are recognized by Oracle OLAP.
Table 2-4 Recognized Escape Sequences
Sequence | Meaning |
---|---|
|
Backspace |
|
Form feed |
|
Line feed |
|
Carriage return |
|
Horizontal tab |
|
Double quote |
|
Single quote |
|
Backslash |
|
Character with ASCII code nnn decimal, where \ |
|
Character with ASCII code nn hexadecimal, where |
|
Character with Unicode nnnn, where |
The Oracle OLAP DML DATE data type does not correspond to the SQL data type of the same name. It is, instead, is an older data type that is unique to the OLAP DML. The OLAP DML DATE
data type is a valid data type for variables and for dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR as discussed in the DEFINE DIMENSION (DWMQY) command topic. It is used to store day, month, and year data (but not hour and minute data) between January 1, 1000 A.D. and December 31, 9999 A.D. Because the OLAP DML DATE
data type does not include hour and minute data, it is often referred to as the DATE-only data type.
Tip: The Oracle OLAP DML data type that corresponds to the SQL DATE data type is namedDATETIME . See DATETIME Data Type for more information. |