APEX_APPLICATION_INSTALL

Overview

Oracle Application Express provides two ways to import an application into an Application Express instance:

1. Upload and installation of an application export file via the Web interface of Application Express.
2. Execution of the application export file as a SQL script, typically in the command-line utility SQL*Plus

Using the file upload capability of the Web interface of Application Express, developers can import an application with a different application ID, different workspace ID and different parsing schema. But when importing an application via a command-line tool like SQL*Plus, none of these attributes (application ID, workspace ID, parsing schema) can be changed without directly modifying the application export file.

As more and more Application Express customers create applications which are meant to be deployed via command-line utilities or via a non-Web-based installer, they are faced with this challenge of how to import their application into an arbitrary workspace on any APEX instance.

Another common scenario is in training classes, to install an application into 50 different workspaces, all using the same application export file. Today, customers work around this by adding their own global variables to an application export file (never recommended and certainly not supported) and then varying the values of these global variables at installation time. However, this manual modification of the application export file (usually done with a post-export sed or awk script) shouldn't be necessary - and again, not supported.

In Oracle Application Express 4.0, there is a new API available named APEX_APPLICATION_INSTALL. This PL/SQL API provides a number of methods to set application attributes during the Application Express application installation process. All export files in Application Express 4.0 contain references to the values set by the APEX_APPLICATION_INSTALL API. However, the methods in this API will only be used to override the default application installation behavior.

APEX_APPLICATION_INSTALL Summary

Workspace

Used to set and get the workspace ID for the application to be imported. This number can be determined by querying the view APEX_WORKSPACES.

procedure set_workspace_id( p_workspace_id in number );

function get_workspace_id return number;

Application ID

Used to set and get the application ID for the application to be imported. The application ID should either not exist in the instance, or if it does exist in the instance, it must be in the workspace where the application will be imported into. This number must be a positive integer and must not be from the reserved range of Application Express application IDs.

procedure set_application_id( p_application_id in number );

function get_application_id return number;

Generates an available application ID on the instance and sets the application ID in APEX_APPLICATION_INSTALL.

procedure generate_application_id;

Offset

Used to set the offset value during application import. This value is used to ensure that the metadata for the Application Express application definition does not collide with other metadata on the instance. For a new application installation, it's almost always sufficient to call generate_offset to have Application Express generate this offset value for you. This number must be a positive integer.

procedure set_offset( p_offset in number );

function get_offset return number;

procedure generate_offset;

Schema

Used to set the parsing schema ("owner") of the Application Express application. The database user of this schema must already exist, and this schema name must already be mapped to the workspace which will be used to import the application.

procedure set_schema( p_schema in varchar2 );

function get_schema return varchar2;

Name

Sets the application name of the application to be imported.

procedure set_application_name( p_application_name in varchar2 );

function get_application_name return varchar2;

Alias

Sets the application alias of the application to be imported. This will only be used if the application to be imported has an alias specified. An application alias must be unique within a workspace, and it's recommended to be unique within an instance.

procedure set_application_alias( p_application_alias in varchar2 );

function get_application_alias return varchar2;

Image Prefix

Sets the image prefix of the application to be imported. The default can usually be used, as most Application Express instances use the default image prefix of /i/.

procedure set_image_prefix( p_image_prefix in varchar2 );

function get_image_prefix return varchar2;

Proxy

Sets the proxy server attributes of the application to be imported.

procedure set_proxy( p_proxy in varchar2 );

function get_proxy return varchar2;

Clear

Clears all values currently maintained in the APEX_APPLICATION_INSTALL package.

procedure clear_all;

Examples

Using the workspace FRED_DEV on the development instance, you generate an application export of application 645 and save it as file f645.sql. All examples below assume you are connected to SQL*Plus.

To import this application back into the FRED_DEV workspace on the same development instance using the same application ID:

@f645.sql

To import this application back into the FRED_DEV workspace on the same development instance, but using application ID 702:

begin
  apex_application_install.set_application_id( 702);
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@645.sql

To import this application back into the FRED_DEV workspace on the same development instance, but using an available application ID generated by Application Express:

begin
  apex_application_install.generate_application_id;
  apex_application_install.generate_offset;
  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

To import this application into the FRED_PROD workspace on the production instance, using schema FREDDY, and the workspace ID of FRED_DEV and FRED_PROD are different:

declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'FRED_PROD';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'FREDDY' );
    apex_application_install.set_application_alias( 'FREDPROD_APP' );
end;
/

@f645.sql

To import this application into the Training instance for 3 different workspaces (each workspace with their own schema):

declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'TRAINING1';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT1' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'TRAINING2';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT2' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

declare
    l_workspace_id number;
begin
    select workspace_id into l_workspace_id
      from apex_workspaces
     where workspace = 'TRAINING3';
    --
    apex_application_install.set_workspace_id( l_workspace_id );
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset;
    apex_application_install.set_schema( 'STUDENT3' );
    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );
end;
/

@f645.sql

Now a final word of caution - with great power comes great responsibility. You should almost never set the offset value yourself unless you absolutely know what you're doing. One of the primary benefits of letting Application Express generate the meta data offset value for you is you avoid the possibility of any "collisions" with the meta data of any other application on any other APEX instance on the planet. If you have no known reason to manually set the offset value, then simply let Application Express set it for you.

In summary, the APEX_APPLICATION_INSTALL API in Application Express 4.0 now enables you to overcome a limitation in all previous versions of Application Express - namely, to take an arbitrary application export file and import it into any workspace on any arbitrary Application Express instance using SQL*Plus or any other command-line tool.