UPDATE

The UPDATE command moves analytic workspace changes from a temporary area to the database table in which the workspace is stored. Typically, you use an UPDATE statement when you are finished making changes in an analytic workspace; however, you can also specify UPDATE commands periodically as you go along.

Your changes are not saved until you execute a COMMIT statement, either from Oracle OLAP or from SQL. When you do not use the UPDATE and COMMIT commands, changes made to an analytic workspace during your session are discarded when you end your Oracle session.

Note:

You can detach and reattach an analytic workspace without losing updated changes, even though they are not committed because the detaching and reattaching occur within a single database session

Syntax

UPDATE [MULTI [acquired_objects]] [analytic_workspaces]

Parameters

When you do not specify any parameters, the command updates all analytic workspaces that are attached in read/write non-exclusive and read/write exclusive modes and all acquired objects (that is, all acquired variables, relations, valuesets, and dimensions) in all analytic workspaces that are attached in multiwriter mode.

acquired_objects

A list of the names of acquired objects, separated by commas, in analytic workspaces attached in multiwriter mode. These objects can be any object that you have acquired using an ACQUIRE statement.

To specify individual partitions of a partitioned variable, use the following syntax.

variable_name (PARTITION partition_name [, PARTITION partition_name ]...)

Important:

you cannot update an object when it is dimensioned by an acquired and maintained dimension unless you update that dimension first.

workspaces

A list of names, separated by commas. of one or more workspaces attached in read/write or multiwriter mode.

Usage Notes

Automatic COMMIT

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.

Triggering Program Execution When UPDATE Executes

Using the TRIGGER command, you can make an UPDATE statement an event that automatically executes an OLAP DML program. See "Trigger Programs" for more information

Shared Workspaces

When you have attached a shared workspace and another user has read/write access, that user's UPDATE and COMMIT commands do not affect your view of the workspace. Your view of the data remains the same as when you attached the workspace. When you want access to the changes, you can detach the workspace and reattach it.

Effect of a ROLLBACK Statement

The OLAP DML does not provide a way to issue a SQL ROLLBACK statement; however, you could execute one in your session from outside Oracle OLAP (for example, through PL/SQL). When a ROLLBACK statement is executed in your session, Oracle OLAP checks to see whether there are uncommitted updates in an attached workspace.

When there are uncommitted updates (that is, you have made changes and executed an UPDATE statement, but you have not subsequently executed a COMMIT statement), then Oracle OLAP discards your changes and detaches the workspace.
When you have no uncommitted updates, then Oracle OLAP takes no action in response to a ROLLBACK statement. Consequently, when you have not issued an UPDATE statement since your last COMMIT statement, Oracle OLAP takes no action and all your changes remain in the workspace during your session.

When you rollback to a savepoint and there are uncommitted updates that occurred after the savepoint, Oracle OLAP discards those updates and detaches the workspace. Uncommitted updates that occurred before the savepoint remain in the workspace, and you can see them when you reattach the workspace in the same session.

Examples

Example 10-168 Saving Analytic Workspace Changes

The following statement moves changes in the current workspace session to the database table in which the workspace is stored.

UPDATE

To save the changes in the database, the UPDATE statement must be followed by a COMMIT statement.