2 APEX_APPLICATION_INSTALL

The APEX_APPLICATION_INSTALL package provides many methods to modify application attributes during the Application Express application installation process.

Topics:

Package 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 by using 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 by using 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 by using command-line utilities or by using a non-web-based installer, they are faced with this challenge of how to import their application into an arbitrary workspace on any Application Express instance.

Another common scenario is in a training class when installing an application into 50 different workspaces that all use the same application export file. Today, customers work around this by adding their own global variables to an application export file and then varying the values of these globals at installation time. However, this manual modification of the application export file (usually done with a post-export sed or awk script) should not be necessary.

Application Express 4.0 and higher includes the APEX_APPLICATION_INSTALL API. This PL/SQL API provides many methods to set application attributes during the Application Express application installation process. All export files in Application Express 4.0 and higher contain references to the values set by the APEX_APPLICATION_INSTALL API. However, the methods in this API is only used to override the default application installation behavior.

Attributes Manipulated by APEX_APPLICATION_INSTALL

The table below lists the attributes that can be set by functions in this API.

Table 2-1 Attributes Manipulated by the APEX_APPLICATION_INSTALL API

Attribute Description

Workspace ID

Workspace ID of the imported application. See GET_WORKSPACE_ID Function, SET_WORKSPACE_ID Procedure.

Application ID

Application ID of the imported application. See GENERATE_APPLICATION_ID Procedure, GET_APPLICATION_ID Function, SET_APPLICATION_ID Procedure.

Offset

Offset value used during application import. See GENERATE_OFFSET Procedure, GET_OFFSET Function, SET_OFFSET Procedure.

Schema

The parsing schema ("owner") of the imported application. See GET_SCHEMA Function, SET_SCHEMA Procedure.

Name

Application name of the imported application. See GET_APPLICATION_NAME Function, SET_APPLICATION_NAME Procedure.

Alias

Application alias of the imported application. See GET_APPLICATION_ALIAS Function, SET_APPLICATION_ALIAS Procedure.

Image Prefix

The image prefix of the imported application. See GET_IMAGE_PREFIX Function, SET_IMAGE_PREFIX Procedure.

Proxy

The proxy server attributes of the imported application. See GET_PROXY Function, SET_PROXY Procedure.


Import Script 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 in this section assume you are connected to SQL*Plus.

Import Application without Modification

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

@f645.sql

Import Application with Specified Application ID

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.get_application_id );
end;
/
 
@645.sql

Import Application with Generated Application ID

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.get_application_id );
end;
/
 
@f645.sql

Import Application into Different Workspace using Different Schema

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

Import into Training Instance for Three Different Workspaces

To import this application into the Training instance for 3 different workspaces:

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.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.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.get_application_id );
end;
/
 
@f645.sql

CLEAR_ALL Procedure

This procedure clears all values currently maintained in the APEX_APPLICATION_INSTALL package.

Syntax

APEX_APPLICATION_INSTALL.CLEAR_ALL;

Parameters

None.

Example

The following example clears all values currently set by the APEX_APPLICATION_INSTALL package.

begin
    apex_application_install.clear_all;
end;

GENERATE_APPLICATION_ID Procedure

This procedure generates an available application ID on the instance and sets the application ID in APEX_APPLICATION_INSTALL.

Syntax

APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;

Parameters

None.

Example

For an example of this procedure call, see "Import Application with Generated Application ID" and Import into Training Instance for Three Different Workspaces.

GENERATE_OFFSET Procedure

This procedure generates the offset value used during application import. Use the offset value 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 is usually sufficient to call this procedure to have Application Express generate this offset value for you.

Syntax

APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

Parameters

None.

Example

For examples of this procedure call, see "Import Application with Specified Application ID", "Import Application with Generated Application ID", and "Import into Training Instance for Three Different Workspaces".

GET_APPLICATION_ALIAS Function

This function gets the application alias for the application to be imported. This is only used if the application to be imported has an alias specified. An application alias must be unique within a workspace and it is recommended to be unique within an instance.

Syntax

APEX_APPLICATION_INSTALL.GET_APPLICATION_ALIAS
RETURN VARCHAR2;

Parameters

None.

Example

The following example returns the value of the application alias value in the APEX_APPLICATION_INSTALL package. The application alias cannot be more than 255 characters.

declare
    l_alias varchar2(255);
begin
    l_alias := apex_application_install.get_application_alias;
end;

GET_APPLICATION_ID Function

Use this function to get the application ID of the application to be imported. The application ID should either not exist in the instance or, if it does exist, must be in the workspace where the application is being imported to.

Syntax

APEX_APPLICATION_INSTALL.GET_APPLICATION_ID
RETURN NUMBER;

Parameters

None.

Example

The following example returns the value of the application ID value in the APEX_APPLICATION_INSTALL package.

declare
    l_id number;
begin
    l_id := apex_application_install.get_application_id;
end;

GET_APPLICATION_NAME Function

This function gets the application name of the import application.

Syntax

APEX_APPLICATION_INSTALL.GET_APPLICATION_NAME
RETURN VARCHAR2;

Parameters

None.

Example

The following example returns the value of the application name value in the APEX_APPLICATION_INSTALL package.

declare
    l_application_name varchar2(255);
begin
    l_application_name := apex_application_install.get_application_name;
end;

GET_IMAGE_PREFIX Function

This function gets the image prefix of the import application. Most Application Express instances use the default image prefix of /i/.

Syntax

APEX_APPLICATION_INSTALL.GET_IMAGE_PREFIX
RETURN VARCHAR2;

Parameters

None.

Example

The following example returns the value of the application image prefix in the APEX_APPLICATION_INSTALL package. The application image prefix cannot be more than 255 characters.

declare
    l_image_prefix varchar2(255);
begin
    l_image_prefix := apex_application_install.get_image_prefix;
end;

GET_OFFSET Function

Use function to get the offset value used during the import of an application.

Syntax

APEX_APPLICATION_INSTALL.GET_OFFSET
RETURN NUMBER;

Parameters

None.

Example

The following example returns the value of the application offset value in the APEX_APPLICATION_INSTALL package.

declare
    l_offset number;
begin
    l_offset := apex_application_install.get_offset;
end;

GET_PROXY Function

Use this function to get the proxy server attribute of an application to be imported.

Syntax

APEX_APPLICATION_INSTALL.GET_PROXY
RETURN VARCHAR2;

Parameters

None.

Example

The following example returns the value of the proxy server attribute in the APEX_APPLICATION_INSTALL package. The proxy server attribute cannot be more than 255 characters.

declare
    l_proxy varchar2(255);
begin
    l_proxy := apex_application_install.get_proxy;
end;

GET_SCHEMA Function

Use this function to get the parsing schema ("owner") of the Application Express application.

Syntax

APEX_APPLICATION_INSTALL.GET_SCHEMA
RETURN VARCHAR2;

Parameters

None.

Example

The following example returns the value of the application schema in the APEX_APPLICATION_INSTALL package.

declare
    l_schema varchar2(30);
begin
    l_schema := apex_application_install.get_schema;
end;

GET_WORKSPACE_ID Function

Use this function to get the workspace ID for the application to be imported.

Syntax

APEX_APPLICATION_INSTALL.GET_WORKSPACE_ID
RETURN NUMBER;

Parameters

None.

Example

The following example returns the value of the workspace ID value in the APEX_APPLICATION_INSTALL package.

declare
    l_workspace_id number;
begin
    l_workspace_id := apex_application_install.get_workspace_id;
end;

SET_APPLICATION_ALIAS Procedure

This procedure sets the application alias for the application to be imported. This is only used if the application to be imported has an alias specified. An application alias must be unique within a workspace and it is recommended to be unique within an instance.

Syntax

APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(
    p_application_alias IN VARCHAR2);

Parameters

Table 2-2 describes the parameters available in SET_APPLICATION_ALIAS procedure.

Table 2-2 SET_APPLICATION_ALIAS Parameters

Parameter Description

p_application_alias

The application alias. The application alias is an alphanumeric identifier. It cannot exceed 255 characters, must unique within a workspace and, ideally, is unique within an entire instance.


Example

For examples of this procedure call, see "Import Application with Specified Application ID", "Import Application with Generated Application ID", "Import Application into Different Workspace using Different Schema" and "Import into Training Instance for Three Different Workspaces".

SET_APPLICATION_ID Procedure

Use this procedure to set the application ID of the application to be imported. The application ID should either not exist in the instance or, if it does exist, must be in the workspace where the application is being imported to. This number must be a positive integer and must not be from the reserved range of Application Express application IDs.

Syntax

APEX_APPLICATION_INSTALL.SET_APPLICATION_ID (
    p_application_id IN NUMBER);

Parameters

Table 2-3 describes the parameters available in SET_APPLICATION_ID procedure.

Table 2-3 SET_APPLICATION_ID Parameters

Parameter Description

p_application_id

This is the application ID. The application ID must be a positive integer, and cannot be in the reserved range of application IDs (3000 - 8999). It must be less than 3000 or greater than or equal to 9000.


Example

For an example of this procedure call, see "Import Application with Specified Application ID".

SET_APPLICATION_NAME Procedure

This procedure sets the application name of the import application.

Syntax

APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME;(
    p_application_name  IN VARCHAR2);

Parameters

Table 2-4 describes the parameters available in SET_APPLICATION_NAME procedure.

Table 2-4 SET_APPLICATION_NAME Parameters

Parameter Description

p_application_name

This is the application name. The application name cannot be null and cannot be longer than 255 characters.


Example

The following example sets the application name in APEX_APPLICATION_INSTALL to "Executive Dashboard".

declare
    l_name varchar2(255) := 'Executive Dashboard';
begin
    apex_application_install.set_application_name( p_application_name => l_name );
end;

SET_IMAGE_PREFIX Procedure

This procedure sets the image prefix of the import application. Most Application Express instances use the default image prefix of /i/.

Syntax

APEX_APPLICATION_INSTALL.SET_IMAGE_PREFIX(
    p_image_prefix  IN VARCHAR2);

Parameters

Table 2-4 describes the parameters available in SET_IMAGE_PREFIX procedure.

Table 2-5 SET_IMAGE_PREFIX Parameters

Parameter Description

p_image_prefix

The image prefix. Default is /i/.


Example

The following example sets the value of the image prefix variable in APEX_APPLICATION_INSTALL.

declare
    l_prefix varchar2(255) := '/i/';
begin
    apex_application_install.set_image_prefix( p_image_prefix => l_prefix );
end;

SET_OFFSET Procedure

This procedure sets the offset value used during application import. Use the offset value 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 is usually sufficient to call the generate_offset procedure to have Application Express generate this offset value for you.

Syntax

APEX_APPLICATION_INSTALL.SET_OFFSET(
    p_offset IN NUMBER);

Parameters

Table 2-6 describes the parameters available in SET_OFFSET procedure.

Table 2-6 SET_OFFSET Parameters

Parameter Description

p_offset

The offset value. The offset must be a positive integer. In most cases you do not need to specify the offset, and instead, call APEX_APPLICATION_INSTALL.GENERATE_OFFSET, which generates a large random value and then set it in the APEX_APPLICATION_INSTALL package.


Example

The following example generates a random number from the database and uses this as the offset value in APEX_APPLICATION_INSTALL.

declare
    l_offset number;
begin
    l_offset := dbms_random.value(100000000000, 999999999999);
    apex_application_install.set_offset( p_offset => l_offset );
end/

SET_PROXY Procedure

Use this procedure to set the proxy server attributes of an application to be imported.

Syntax

APEX_APPLICATION_INSTALL.SET_PROXY (
    p_proxy IN VARCHAR2);

Parameters

Table 2-7 describes the parameters available in SET_PROXY procedure.

Table 2-7 SET_PROXY Parameters

Parameter Description

p_proxy

The proxy server. There is no default value. The proxy server cannot be more than 255 characters and should not include any protocol prefix such as http://. A sample value might be: www-proxy.company.com


Example

The following example sets the value of the proxy variable in APEX_APPLICATION_INSTALL.

declare
    l_proxy varchar2(255) := 'www-proxy.company.com'
begin
    apex_application_install.set_proxy( p_proxy => l_proxy );
end;

SET_SCHEMA Procedure

Use this function 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 used to import the application.

Syntax

APEX_APPLICATION_INSTALL.SET_SCHEMA (
    p_schema  IN VARCHAR2);

Parameters

Table 2-8 describes the parameters available in the SET_SCHEMA procedure.

Table 2-8 SET_SCHEMA Parameters

Parameter Description

p_schema

The schema name.


Example

For examples of this procedure call, see "Import Application into Different Workspace using Different Schema" and "Import into Training Instance for Three Different Workspaces".

SET_WORKSPACE_ID Procedure

Use this function to set the workspace ID for the application to be imported.

Syntax

APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID (
    p_workspace_id  IN NUMBER);

Parameters

Table 2-9 describes the parameters available in the SET_WORKSPACE_ID procedure.

Table 2-9 SET_WORKSPACE_ID Parameters

Parameter Description

p_workspace_id

The workspace ID.


Example

For examples of this procedure call, see "Import Application into Different Workspace using Different Schema" and "Import into Training Instance for Three Different Workspaces".