Released	APEX version	Database version	Cloud or On Premise
December 2016	all	all	Cloud and On Premise

Introduction: Oracle Workspace Manager

In business applications, there is often the requirement to "play" with data, to simulate business scenarios or to maintain multiple versions of data. Such scenarios require "long running transactions"; changes are being made to tables from within multiple database sessions, over a longer period of time even from different users. At the end, all changes of that transactions are being applied or rejected as a whole. We have transactions in the Oracle database since its early days, but these are restricted to the very database session and therefore also to one user; this is, as seen, totally different to a long running transaction.

Long running transactions vs. database transactions

The Oracle Database contains a feature this "long running transactions" requirement since Oracle9i: Oracle Workspace Manager. In this document, "Workspace" refers to Workspace Manager Workspaces, which are logical containers for long running transactions. Within a workspace, we can ...

• modify table data with SQL DML commands from multiple database sessions over a longer period of time. Database transactions are being committed as usual. DML statements can come from an Application Express application, from a SQL client or from any other application.
• changes within one Workspace Manager transaction can be committed or rolled back as a whole.
• changes within a workspace are only visible within that workspace.

Workspaces in Oracle Workspace Manager are being organized hierarchically. The root of the hierarchy is the LIVE workspace, in which every database session starts. A new workspace is being created as a child of the current workspace; so workspaces are organized as a hierarchy. In order to keep things simple, in this document we will create all workspaces as children of LIVE. When no workspaces are being created at all, the database will behave as without workspace manager.

Fig. 1: two workspaces are being created as children of "LIVE" - we have one hierarchy level

A database session can switch to one of the workspaces and execute SQL commands. All changes on version-enabled tables are only visible in the current workspace, even after a COMMIT has been executed. Another database session, which is switched to LIVE or another workspace, cannot see the changes.

At the end of a workspace manager transaction, changes are either merged into the parent workspace or rolled back. Even if a workspace manager transaction contains hundredths or thousands of committed DML statements, it can be rolled back as a whole. If the workspace is being merged, all changes get visible in the parent (here: LIVE) workspace.

In addition, Workspace Manager provides the "Refresh" operation; that is important when the data in the parent workspace changes after the child workspace has been created. By default, these changes are not visible; until been "pulled in" with a refresh operation.

Fig. 2: "Merge" applies child changes to parent; "Refresh" applies parent changes to child

A detailed technical description of workspace manager, how it works internally and how it's implemented, would be way beyond the scope of this how to document; this and more information is contained in the Introduction to Workspace Manager chapter of the Oracle Documentation.

Workspace Manager and Application Express: Prerequisites

Workspace Manager operations are being performed using the DBMS_WM PL/SQL package. A few procedures in that package (e.g. DBMS_WM.createWorkspace) require the physical connection as the database user owning the tables and workspaces. When working with SQL*Plus or SQL Developer, that is the case. But Application Express connects physically as APEX_PUBLIC_USER or ANONYMOUS; the engine makes sure that all SQL is being executed in the correct context of the applications' parsing schema.

Thus, calling DBMS_WM.createWorkspace directly from APEX will not work; but the workaround is easy:

Create a database link in your database schema pointing to the same schema in the same database (a "loopback" database link). For the SCOTT database schema this can look as follows.

create database link loopback_link connect to scott 
                                   identified by *****
                                   using 'localhost:1521/orcl'; 

We now can call DBMS_WM.createWorkspace over the database link; so it will execute in the correct context.

begin
  dbms_wm.createWorkspace@loopback_link( 'My Workspace Name' );
end; 

We will need that database link only for a few dedicated procedurs in the DBMS_WM package (in this how to document it's only DBMS_WM.createWorkspace). All other SQL and PL/SQL will be executed as usual.

The next step is to activate your table or tables for workspace manager. Tables which are connected to each other with foreign keys have to be enabled or disabled together; otherwise you will see an error message. The following PL/SQL block will enable the EMP and DEPT tables for workspace manager.

begin
  dbms_wm.enableVersioning(
    table_name => 'emp,dept'
  );
end; 

Now, having done the required prerequisite steps, we can start integrating Oracle Workspace Manager into an Application Express application.

Integrate Oracle Workspace Manager with your application

As a starting point for this how to, we will use a simple application based on the EMP table. Create an application with a few pages: It might contain a report, a form and a chart. If you are working on Application Express 5.1, you can, of course, also use the new interactive Grid component. Such an application page could look as follows:

Fig. 3: We'll start with a simple application based on the EMP table

We already have enabled the table for workspace manager with the DBMS_WM.enableVersioning procedure, but so far we did not create any workspace. So all SQL will be executed in the LIVE workspace and all changes will be visible to everyone. This will now change, as we will allow the end users of our application to create workspaces. When workspaces exist, our application has to switch into the correct workspace before executing SQL or PL/SQL.

Each request to Application Express (Page Show or Page Processing) can happen in a different database session; that is due to the web architecture of Application Express. So Application Express needs to switch into the correct workspace for each request; we cannot rely on the database session; the following steps illustrate this:

Create an Application Item, which stores the current workspace for the Application Express session. Navigate to Shared Components, then to Application Items and click the Create button. Name your new application item APP_CURRENT_WORKSPACE, accept the defaults and save the changes.

Fig. 4: The "APP_CURRENT_WORKSPACE" application item stores current the workspace for the application

Then create an Application Computation for the APP_CURRENT_WORKSPACE item to replace NULL with LIVE.

Fig. 5: Set "APP_CURRENT_WORKSPACE" to "LIVE", if NULL

Then go to Shared Components again and navigate to the Security Attributes section. Scroll down to Database Session and use the following block as Initialization PL/SQL Code. That code will switch to the workspace stored in :APP_CURRENT_WORKSPACE for each request before executing any application SQL.

begin
  dbms_wm.gotoWorkspace( nvl( :APP_CURRENT_WORKSPACE, 'LIVE' ) );
end; 

As the last operation, after executing all application SQL, we switch back the LIVE workspace.

begin
  dbms_wm.gotoWorkspace( 'LIVE' );
end;  

Fig. 6: Switching between workspaces is done in session initialization and cleanup code

Now our Application Express application is able to switch workspaces based on the content of the APP_CURRENT_WORKSPACE item. We then need some user interface to control Workspace Manager; to create, remove or to determine the current workspace. Create a new, empty application page and perform the following steps (note to replace the PX_ prefix with the correct item prefix for your page).

• In Page Designer, create a new Region of type Static Content
• Create a page item named PX_CURRENT_WORKSPACE as Display Only. Choose PL/SQL Expression as Source and use dbms_wm.getWorkspace as PL/SQL Expression. That item will display the current workspace.
• Create a page item named PX_NEW_WORKSPACE as a text field - that is to create a new workspace.
• Create a page item named PX_WORKSPACE to show existing workspaces as a select list:
  • Use SQL Query as the List Of Values Source and use the following query:

    select 'LIVE' d, 'LIVE' r from dual union all (
       select workspace d, workspace r from user_workspaces
    )

  • Use " - New Workspace -" as Display NULL Value.
• Then we will need the following buttons:
  • Create Workspace (CREATE_WORKSPACE)
  • Switch Workspace (GOTO_WORKSPACE)
  • Delete Workspace (REMOVE_WORKSPACE)
  • Rollback Workspace Changes (ROLLBACK_WORKSPACE)
  • Commit Workspace Changes (MERGE_WORKSPACE)

Using Dynamic Actions, you might make that page more user-friendly. For instance, the PX_NEW_WORKSPACE text field and the CREATE_WORKSPACE button should only be shown when the select list item PX_WORKSPACE is NULL and displays - New Workspace -. Use the Show and Hide dynamic actions for this. At the end, your page should look like the following screenshot.

Fig. 7: Example for the Workspace Manager administration page in your application

When clicking a button, of course, nothing will happen - we did not add any page processing. Perform the following steps to make the page actually working.

1. Process Create Workspace (to be executed when button CREATE_WORKSPACE has been clicked) - with the following PL/SQL code:
   

   begin
     -- execute DBMS_WM.createWorkspace over DB Link; 
     -- correct physical connection is important here.
     --
     dbms_wm.gotoWorkspace( 'LIVE' );
     dbms_wm.createWorkspace@loopback_link( :PX_NEW_WORKSPACE );
     :APP_CURRENT_WORKSPACE := :PX_NEW_WORKSPACE;
   end;

2. Process Goto Workspace (to be executed when button GOTO_WORKSPACE has been clicked) - with the following PL/SQL code:
   

   begin
     :APP_CURRENT_WORKSPACE := :PX_WORKSPACE;
   end;

3. Process Remove Workspace (to be executed when button REMOVE_WORKSPACE has been clicked) - with the following PL/SQL code:
   

   begin
     dbms_wm.gotoWorkspace( 'LIVE' );
     dbms_wm.removeWorkspace( :PX_WORKSPACE );
     :APP_CURRENT_WORKSPACE := 'LIVE';
     :PX_WORKSPACE := 'LIVE';
   end;

4. Process Merge Workspace (to be executed when button MERGE_WORKSPACE has been clicked) - with the following PL/SQL code:
   

   begin
     dbms_wm.mergeWorkspace( :PX_WORKSPACE );
   end;

5. Process Rollback Workspace (to be executed when button ROLLBACK_WORKSPACE has been clicked) - with the following PL/SQL code:
   

   begin
     dbms_wm.rollbackWorkspace( :PX_WORKSPACE );
   end;

Make sure to have good success messages for each page process. Finally create a branch to the administration page itself.

A nice tweak is to add a link to this administration page to the Navigation Bar in the upper right corner of the screen - this can also be used to display the current workspace. Go to Shared Components, then to Navigation Bar List and then choose Desktop Navigation Bar. In most cases, there is one entry: Log Out. Add another one by clicking the Create Entry button. Use &APP_CURRENT_WORKSPACE. as the entry label and use the new workspace administration page as the link target.

Fig. 8: Add an entry for the Workspace Manager administration page to the navigation bar

Oracle Workspace Manager and Application Express: in Action!

And that's it - you can now try out your application and do some data simulations. Run the application and navigate to the new Workspace Manager administration page. Create a new workspace named Simulation and click the Create Workspace button.

Fig. 9: Create a new Workspace Manager workspace - in your application

Then navigate to the page with all your reports, charts and components based on the EMP table. Note the navigation bar in the upper left of the screen. After the workspace Simulation has been created, the application immediately switched to it. Remember? The PL/SQL code, we have added as Database Session Initialization PL/SQL Code makes sure that all application SQL is being executed in the chosen workspace.

Fig. 10: Application Express components on the "EMP" table: - current workspace is "Simulation"

Now change the EMP table as you never did before. Don't hesitate - it's only in your workspace. After having some fun, your page might look as follows.

Fig. 11: Application Express components on the "EMP" table: - current workspace is "Simulation"

Next, log out and back into your application as the same or another user. Each Application Express session will default to the LIVE workspace. Thus the EMP table data looks as before - the changes you have made, are not visible on the LIVE workspace.

Fig. 12: Application Express components on the "EMP" table: - current workspace is "LIVE"

As said: the changes you made, are private to the Simulation workspace, all other users or the rest of your application see the original table data and are not affected by your changes. One needs to explicity switch to the Simulation workspace in order to see what you have done.

Fig. 13: Application Express components on the "EMP" table: - current workspace is "Simulation""

Finally, we want to make the new data version visible to everyone, the "simulation" has to become the new "reality". For this, we need to merge the changes done in Simulation to the LIVE workspace.

Fig. 14: Merge changes in "Simulation" to "LIVE"

From now on, everyone can see the changes.

Fig. 15: Everyone sees the new data now

Summary and outlook

The Workspace Manager feature of the Oracle database allows to run versioning, simulations or simply long running transactions on database tables with minimal effort. After a table has been enabled for workspace manager, SQL DML statements can be executed and committed in the scope of a workspace. Those changes are isolated from other workspaces which still see the original data. At some point in time, these changes can be accepted as a whole, making them visible to all database sessions and users. As an alternative, the changes can be rolled back as a whole. As this how to document shows, the integration of Workspace Manager with Application Express can be done with minimal effort.

Workspace Manager is even more powerful than highlighted in the document. Imagine two different workspaces making changes to the same table row. The first workspace merges their changes, then the second workspace does the same. If workspace manager simply did the second merge, it would silently overwrite the changes done in the first workspace. For these situations, Workspace Manager has conflict detection and handling functionality. Using the DBMS_WM package and database views, the application developer can get information about conflicts, visualize them and resolve them. A more detailed explanation would also be out of scope of this document.

Back to the Application Express Blog