How I hacked the Apex 4 Websheets

Or, to be more specific (and less sensationalistic), how I struggled to, and finally succeeded in, using the new Apex 4 Websheets feature in a "Runtime-Only" environment.

What is an Apex runtime environment?

"Oracle recommends that you run any sensitive production Oracle Application Express applications with a runtime installation of Oracle Application Express. A runtime installation does not expose the Web-based application development environment, thus preventing the use of Application Builder, SQL Workshop, and related utilities on a production installation. Additionally, a runtime environment only includes the Oracle Application Express database objects and privileges necessary to run applications, making it a more hardened environment."

So, naturally, I set up Apex production environments using the runtime-only installation. But I hit a problem when I tried to deploy a Websheet application to such a production environment.

The setup

Here is what I did:

1. Installed Oracle XE
2. Installed Apex 4 runtime (apxrtins.sql)
3. Manually created an application schema (SMALL_APPS) in the database
4. Manually created the Websheet tables (APEX$ tables) in the SMALL_APPS schema (to do this I had to manually export the table scripts from the Apex development instance using TOAD, but I guess it could also have been done through the SQL Workshop in Apex itself)
5. Manually created an Apex workspace for the SMALL_APPS schema (via the APEX_INSTANCE_ADMIN package)
6. Exported the Workspace definition from the development instance. The generated workspace script contains the statements to create the workspace users. I removed the part of the script that creates the workspace itself (as I had already done this in the preceding step), and changed the workspace ID to the newly created workspace ID before I ran the script.

So far, so good.

The problem

I then proceeded to import the actual Websheet application.

This, however, threw up the following error:

WEBSHEET APPLICATION 112 - WebSheetSandbox
Set Credentials...
Check Compatibility...
API Last Extended:20100513
Your Current Version:20100513
This import is compatible with version: 20100513
COMPATIBLE (You should be able to run this import without issues.)


Set Application ID...
...Remove Websheet Application
...Create Websheet Application
...Create Access Control List
...Create Application Authentication Set Up
...Create Data Grid
Rollback

Error starting at line 163 in command:
declare
  q varchar2(32767) := null;
begin
q := null;
wwv_flow_api.create_ws_worksheet (
  p_id => 1311502709921962+wwv_flow_api.g_id_offset,
  p_flow_id => 4900,
  p_page_id => 2,

(snip...)

ORA-02291: integrity constraint (APEX_040000.WWV_FLOW_WORKSHEETS_FLOW_FK) violated - parent key not found
ORA-06512: at "APEX_040000.WWV_FLOW_API", line 14562
ORA-06512: at line 5
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.


From this it was evident that the data grids in the exported application are linked to application 4900, which is the built-in Websheets application. However, the Apex runtime-only installation does not install application 4900, hence the integrity error.

At this point I started to wonder if Websheets are supported in a runtime-only installation of Apex, and I posted the question in the Apex discussion forum on OTN.

But the only answer there was silence, so after a few days I decided to just go ahead and try to install application 4900 in the runtime environment, by running the f4900.sql script (from the apex\builder folder).

More problems

With application 4900 installed successfully, I was able to install my own Websheets application.

However, after login I was greeted with the following error message:

ORA-06550: line 9, column 46: PLS-00201: identifier 'WWV_FLOW_F4000_PLUGINS.RENDER_SEARCHBOX' must be declared ORA-06550: line 9, column 1: PL/SQL: Statement ignored

So, a package is missing. I located the package scripts (in the apex\core folder).

The comments in the package header (wwv_flow_f4000_plugins.sql) actually state:

"RUNTIME DEPLOYMENT: YES"

But evidently the package is not installed by the runtime installation script, so either this is a bug or the comment is wrong.

So I added the missing package by running:

alter session set current_schema = APEX_040000;


@wwv_flow_f4000_plugins.sql
@wwv_flow_f4000_plugins.plb

The final problem

This fixed the PLS-00201 error, but now I got several other errors, all similar to the following:

Unable to bind ":WS_APP_ID"
Unable to bind ":WEBPAGE_ID"
ORA-20001: run_query error q=select id, title, content, section_type, data_grid_id, report_id, data_section_style, nav_start_webpage_id, nav_max_level, nav_include_link, created_by from apex$_ws_webpg_sections where ws_app_id = :WS_APP_ID and webpage_id = :WEBPAGE_ID order by display_sequence
ORA-01003: no statement parsed


Still no feedback in the discussion forum, so I decided to dig deeper into the Apex internals (an interesting exercise in itself).

A solution!

In the end, the solution turned out to be simple. The WWV_FLOW_COMPANY_SCHEMAS table contains the workspace to schema mappings. This table contains a column called IS_APEX$_SCHEMA, and this needs to be set to "Y" (the APEX_INSTANCE_ADMIN.ADD_WORKSPACE procedure leaves the column value as NULL).

So just update the column to enable Websheets:

update wwv_flow_company_schemas
set is_apex$_schema = 'Y'
where schema = 'SMALL_APPS';

Voila! I now have a working Websheet application in my runtime-only Apex environment.


Postscript

While I was typing up this for the blog post, I stumbled across the following statement in the Apex Administration Guide:

"Tip: Websheets are not supported in an Oracle Application Express runtime environment."

I wish somebody could have pointed that out to me in the discussion forum thread. But then again, if they did, I probably wouldn't have discovered how to make it work anyway.

And if anyone from Oracle is reading this, consider this an enhancement request for Apex 4.1: Support Websheets in a runtime-only environment by:

• Including application 4900 in the runtime installation
• Including the wwv_flow_f4000_plugins package in the runtime installation
• Add a parameter to the ADD_WORKSPACE and ADD_SCHEMA procedures to specify whether websheets should be enabled or not