The APEX_APPLICATION_INSTALL package provides many methods to modify application attributes during the Application Express application installation process.
Oracle Application Express provides two ways to import an application into an Application Express instance:
Upload and installation of an application export file by using the web interface of Application Express.
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 of the imported application. See GET_WORKSPACE_ID Function, SET_WORKSPACE_ID Procedure. |
|
|
Application ID of the imported application. See GENERATE_APPLICATION_ID Procedure, GET_APPLICATION_ID Function, SET_APPLICATION_ID Procedure. |
|
|
Offset value used during application import. See GENERATE_OFFSET Procedure, GET_OFFSET Function, SET_OFFSET Procedure. |
|
|
The parsing schema ("owner") of the imported application. See GET_SCHEMA Function, SET_SCHEMA Procedure. |
|
|
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. |
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
This procedure generates an available application ID on the instance and sets the application ID in APEX_APPLICATION_INSTALL.
APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;
None.
For an example of this procedure call, see "Import Application with Generated Application ID" and Import into Training Instance for Three Different Workspaces.
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.
APEX_APPLICATION_INSTALL.GENERATE_OFFSET;
None.
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".
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.
APEX_APPLICATION_INSTALL.GET_APPLICATION_ALIAS RETURN VARCHAR2;
None.
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;
See Also:
"SET_APPLICATION_ALIAS Procedure"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.
APEX_APPLICATION_INSTALL.GET_APPLICATION_ID RETURN NUMBER;
None.
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;
This function gets the application name of the import application.
APEX_APPLICATION_INSTALL.GET_APPLICATION_NAME RETURN VARCHAR2;
None.
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;
See Also:
"SET_APPLICATION_NAME Procedure"This function gets the image prefix of the import application. Most Application Express instances use the default image prefix of /i/.
APEX_APPLICATION_INSTALL.GET_IMAGE_PREFIX RETURN VARCHAR2;
None.
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;
See Also:
"SET_IMAGE_PREFIX Procedure"Use function to get the offset value used during the import of an application.
APEX_APPLICATION_INSTALL.GET_OFFSET RETURN NUMBER;
None.
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;
Use this function to get the proxy server attribute of an application to be imported.
APEX_APPLICATION_INSTALL.GET_PROXY RETURN VARCHAR2;
None.
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;
See Also:
"SET_PROXY Procedure"Use this function to get the parsing schema ("owner") of the Application Express application.
APEX_APPLICATION_INSTALL.GET_SCHEMA RETURN VARCHAR2;
None.
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;
See Also:
"SET_SCHEMA Procedure"Use this function to get the workspace ID for the application to be imported.
APEX_APPLICATION_INSTALL.GET_WORKSPACE_ID RETURN NUMBER;
None.
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;
See Also:
"SET_WORKSPACE_ID 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.
APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(
p_application_alias IN VARCHAR2);
Table 2-2 describes the parameters available in SET_APPLICATION_ALIAS procedure.
Table 2-2 SET_APPLICATION_ALIAS Parameters
| Parameter | Description |
|---|---|
|
|
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. |
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".
See Also:
"GET_APPLICATION_ALIAS Function"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.
APEX_APPLICATION_INSTALL.SET_APPLICATION_ID (
p_application_id IN NUMBER);
Table 2-3 describes the parameters available in SET_APPLICATION_ID procedure.
Table 2-3 SET_APPLICATION_ID Parameters
| Parameter | Description |
|---|---|
|
|
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. |
For an example of this procedure call, see "Import Application with Specified Application ID".
This procedure sets the application name of the import application.
APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME;(
p_application_name IN VARCHAR2);
Table 2-4 describes the parameters available in SET_APPLICATION_NAME procedure.
Table 2-4 SET_APPLICATION_NAME Parameters
| Parameter | Description |
|---|---|
|
|
This is the application name. The application name cannot be null and cannot be longer than 255 characters. |
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;
See Also:
"GET_APPLICATION_NAME Function"This procedure sets the image prefix of the import application. Most Application Express instances use the default image prefix of /i/.
APEX_APPLICATION_INSTALL.SET_IMAGE_PREFIX(
p_image_prefix IN VARCHAR2);
Table 2-4 describes the parameters available in SET_IMAGE_PREFIX procedure.
Table 2-5 SET_IMAGE_PREFIX Parameters
| Parameter | Description |
|---|---|
|
|
The image prefix. Default is |
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;
See Also:
"GET_IMAGE_PREFIX Function"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.
APEX_APPLICATION_INSTALL.SET_OFFSET(
p_offset IN NUMBER);
Table 2-6 describes the parameters available in SET_OFFSET procedure.
Table 2-6 SET_OFFSET Parameters
| Parameter | Description |
|---|---|
|
|
The offset value. The offset must be a positive integer. In most cases you do not need to specify the offset, and instead, call |
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/
Use this procedure to set the proxy server attributes of an application to be imported.
APEX_APPLICATION_INSTALL.SET_PROXY (
p_proxy IN VARCHAR2);
Table 2-7 describes the parameters available in SET_PROXY procedure.
Table 2-7 SET_PROXY Parameters
| Parameter | Description |
|---|---|
|
|
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: |
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;
See Also:
"SET_PROXY 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.
APEX_APPLICATION_INSTALL.SET_SCHEMA (
p_schema IN VARCHAR2);
Table 2-8 describes the parameters available in the SET_SCHEMA procedure.
For examples of this procedure call, see "Import Application into Different Workspace using Different Schema" and "Import into Training Instance for Three Different Workspaces".
See Also:
"GET_SCHEMA Function"Use this function to set the workspace ID for the application to be imported.
APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID (
p_workspace_id IN NUMBER);
Table 2-9 describes the parameters available in the SET_WORKSPACE_ID procedure.
For examples of this procedure call, see "Import Application into Different Workspace using Different Schema" and "Import into Training Instance for Three Different Workspaces".
See Also:
"SET_WORKSPACE_ID Procedure"