The APEX_INSTANCE_ADMIN package provides utilities for managing an Oracle Application Express runtime environment. You use the APEX_INSTANCE_ADMIN package to get and set email settings, wallet settings, report printing settings and to manage scheme to workspace mappings. APEX_INSTANCE_ADMIN can be executed by the SYS, SYSTEM, and APEX_040100 database users as well as any database user granted the role APEX_ADMINISTRATOR_ROLE.
The ADD_SCHEMA procedure adds a schema to a workspace to schema mapping.
APEX_INSTANCE_ADMIN.ADD_SCHEMA(
p_workspace IN VARCHAR2,
p_schema IN VARCHAR2);
Table 8-1 describes the parameters available in the ADD_SCHEMA procedure.
Table 8-1 ADD_SCHEMA Parameters
| Parameter | Description |
|---|---|
|
|
The name of the workspace to which the schema mapping will be added. |
|
|
The schema to add to the schema to workspace mapping. |
The following example demonstrates how to use the ADD_SCHEMA procedure to map a schema mapped to a workspace.
BEGIN
APEX_INSTANCE_ADMIN.ADD_SCHEMA('MY_WORKSPACE','FRANK');
END;
The ADD_WORKSPACE procedure adds a workspace to an Application Express Instance.
APEX_INSTANCE_ADMIN.ADD_WORKSPACE(
p_workspace_id IN NUMBER DEFAULT NULL,
p_workspace IN VARCHAR2,
p_source_identifier IN VARCHAR2 DEFAULT NULL,
p_primary_schema IN VARCHAR2,
p_additional_schemas IN VARCHAR2,
p_rm_consumer_group IN VARCHAR2 DEFAULT NULL );
Table 8-2 describes the parameters available in the ADD_WORKSPACE procedure.
Table 8-2 ADD_WORKSPACE Parameters
| Parameter | Description |
|---|---|
|
|
The ID to uniquely identify the workspace in an Application Express instance. This may be left null and a new unique ID will be assigned. |
|
|
The name of the workspace to be added. |
|
|
A short identifier for the workspace used when synchronizing feedback between different instances. |
|
|
The primary database schema to associate with the new workspace. |
|
|
A colon delimited list of additional schemas to associate with this workspace. |
|
|
Resource Manager consumer group which should be used when executing applications of this workspace. |
The following example demonstrates how to use the ADD_WORKSPACE procedure to add a new workspace named MY_WORKSPACE using the primary schema, SCOTT, along with additional schema mappings for HR and OE.
BEGIN
APEX_INSTANCE_ADMIN.ADD_WORKSPACE (
p_workspace_id => 8675309,
p_workspace => 'MY_WORKSPACE',
p_primary_schema => 'SCOTT',
p_additional_schemas => 'HR:OE' );
END;
The GET_PARAMETER function retrieves the value of a parameter used in administering a runtime environment.
APEX_INSTANCE_ADMIN.GET_PARAMETER(
p_parameter IN VARCHAR2)
RETURN VARCHAR2;
Table 8-3 describes the parameters available in the GET_PARAMETER function.
Table 8-3 GET_PARAMETER Parameters
| Parameter | Description |
|---|---|
|
|
The instance parameter to be retrieved. |
The following example demonstrates how to use the GET_PARAMETER function to retrieve the SMTP_HOST_ADDRESS parameter currently defined for an Oracle Application Express instance.
DECLARE
L_VAL VARCHAR2(4000);
BEGIN
L_VAL :=APEX_INSTANCE_ADMIN.GET_PARAMETER('SMTP_HOST_ADDRESS');
DBMS_OUTPUT.PUT_LINE('The SMTP Host Setting Is: '||L_VAL);
END;
The GET_SCHEMAS function retrieves a comma-delimited list of schemas that are mapped to a given workspace.
APEX_INSTANCE_ADMIN.GET_SCHEMAS(
p_workspace IN VARCHAR2)
RETURN VARCHAR2;
Table 8-4 describes the parameters available in the GET_SCHEMAS function.
Table 8-4 GET_SCHEMAS Parameters
| Parameter | Description |
|---|---|
|
|
The name of the workspace from which to retrieve the schema list. |
The following example demonstrates how to use the GET_SCHEMA function to retrieve the underlying schemas mapped to a workspace.
DECLARE
L_VAL VARCHAR2(4000);
BEGIN
L_VAL :=APEX_INSTANCE_ADMIN.GET_SCHEMAS('MY_WORKSPACE');
DBMS_OUTPUT.PUT_LINE('The schemas for my workspace: '||L_VAL);
END;
The REMOVE_APPLICATION procedure removes the application specified from the Application Express instance.
APEX_INSTANCE_ADMIN.REMOVE_APPLICATION (
p_application_id IN NUMBER);
Table 8-5 describes the REMOVE_APPLICATION procedure parameters.
Table 8-5 REMOVE_APPLICATION Parameters
| Parameter | Description |
|---|---|
|
p_application_id |
The ID of the application to remove. |
The following example demonstrates how to use the REMOVE_APPLICATION procedure to remove an application with an ID of 100 from an Application Express instance.
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_APPLICATION(100);
END;
The REMOVE_SAVED_REPORT procedure removes a specific user's saved interactive report settings for a particular application.
APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORT(
p_application_id IN NUMBER,
p_report_id IN NUMBER);
Table 8-6 describes the parameters available in the REMOVE_SAVED_REPORT procedure.
Table 8-6 REMOVE_SAVED_REPORT Parameters
| Parameter | Description |
|---|---|
|
|
The ID of the application for which to remove user saved interactive report information. |
|
p_report_id |
The ID of the saved user interactive report to be removed. |
The following example demonstrates how to use the REMOVE_SAVED_REPORT procedure to remove user saved interactive report with the ID 123 for the application with an ID of 100.
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORT(100,123);
END;
The REMOVE_SAVED_REPORTS procedure removes all user saved interactive report settings for a particular application or for the entire instance.
APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORTS(
p_application_id IN NUMBER DEFAULT NULL);
Table 8-7 describes the parameters available in the REMOVE_SAVED_REPORTS procedure.
Table 8-7 REMOVE_SAVED_REPORTS Parameters
| Parameter | Description |
|---|---|
|
|
The ID of the application for which to remove user saved interactive report information. If this parameter is left null, all user saved interactive reports for the entire instance will be removed. |
The following example demonstrates how to use the REMOVE_SAVED_REPORTS procedure to remove user saved interactive report information for the application with an ID of 100.
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORTS(100);
END;
This REMOVE_SCHEMA procedure removes a workspace to schema mapping.
APEX_INSTANCE_ADMIN.REMOVE_SCHEMA(
p_workspace IN VARCHAR2,
p_schema IN VARCHAR2);
Table 8-8 describes the parameters available in the REMOVE_SCHEMA procedure.
Table 8-8 REMOVE_SCHEMA Parameters
| Parameter | Description |
|---|---|
|
|
The name of the workspace from which the schema mapping will be removed. |
|
|
The schema to remove from the schema to workspace mapping. |
The following example demonstrates how to use the REMOVE_SCHEMA procedure to remove the schema named Frank from the MY_WORKSPACE workspace to schema mapping.
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_SCHEMA('MY_WORKSPACE','FRANK');
END;
The REMOVE_WORKSPACE procedure removes a workspace from an Application Express instance.
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(
p_workspace IN VARCHAR2,
p_drop_users IN VARCHAR2 DEFAULT 'N',
p_drop_tablespaces IN VARCHAR2 DEFAULT 'N' );
Table 8-9 describes the parameters available in the REMOVE_WORKSPACE procedure.
Table 8-9 REMOVE_WORKSPACE Parameters
| Parameter | Description |
|---|---|
|
|
The name of the workspace to be removed. |
|
|
|
|
|
'Y' to drop the tablespace associated with the database user associated with the workspace. The default is |
The following example demonstrates how to use the REMOVE_WORKSPACE procedure to remove an existing workspace named MY_WORKSPACE, along with the associated database users and tablespace.
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE('MY_WORKSPACE','Y','Y');
END;
The SET_PARAMETER procedure sets a parameter used in administering a runtime environment.
APEX_INSTANCE_ADMIN.SET_PARAMETER(
p_parameter IN VARCHAR2,
p_value IN VARCHAR2 DEFAULT 'N');
Table 8-10 describes the parameters available in the SET_PARAMETER procedure.
Table 8-10 SET_PARAMETER Parameters
| Parameter | Description |
|---|---|
|
|
The instance parameter to be set. |
|
|
The value of the parameter. |
The following example demonstrates how to use the SET_PARAMETER procedure to set the SMTP_HOST_ADDRESS parameter for an Oracle Application Express instance.
BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS','mail.mycompany.com');
END;
The TRUNCATE_LOG procedure truncates the log entries specified by the input parameter.
APEX_INSTANCE_ADMIN.TRUNCATE_LOG(
p_log IN VARCHAR2);
Table 8-11 describes the parameters available in the TRUNCATE_LOG procedure.
Table 8-11 TRUNCATE_LOG Parameters
| Parameter | Description |
|---|---|
|
|
This parameter can have one of the following values:
|
The following example demonstrates how to use the TRUNCATE_LOG procedure to remove all log entries that record access to Application Express application pages.
BEGIN
APEX_INSTANCE_ADMIN.TRUNCATE_LOG('ACTIVITY');
END;
Table 8-12 lists all the available parameter values you can set within the APEX_INSTANCE_ADMIN package, including parameters for email, wallet, and reporting printing.
Table 8-12 Available Parameters
| Parameter Name | Description |
|---|---|
|
|
The maximum number of days an end-user account password may be used before the account is expired. |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
Controls instance wide setting of application activity log ([A]lways, [N]ever, [U]se application settings) |
|
|
If set to |
|
|
Uploaded files like application export files, websheet export files, spreadsheet data load files will be automatically deleted after this number of days. Default is |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
The number of seconds an internal application may be idle. |
|
|
The number of seconds an internal application session may exist. |
|
|
The alphabetic characters used for password complexity rules. Default list of alphabetic characters include the following: abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ |
|
|
The punctuation characters used for password complexity rules. Default list of punctuation characters include the following: !"#$%&()``*+,-/:;<=>?_ |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
If set to |
|
|
Determines default provisioning mode. Default is |
|
|
Defines the "from" address for administrative tasks that generate email, such as approving a provision request or resetting a password. Enter a valid email address, for example:
|
|
|
Defines the server address of the SMTP server. If you are using another server as an SMTP relay, change this parameter to that server's address. Default setting: localhost |
|
|
Defines the port the SMTP server listens to for mail requests. Default setting: 25 |
|
|
Defines the password APEX takes to authenticate itself against the SMTP server, in conjunction with the parameter |
|
|
Defines whether or not APEX opens an encrypted connection to the SMTP server. Encryption is only supported on database versions 11.2.0.2 and later. On earlier database versions, the connection is not encrypted. If set to If set to If |
|
|
Defines the username APEX takes to authenticate itself against the SMTP server (default is null). Starting with database version 11.2.0.2, APEX uses |
|
|
The maximum allowable size for an individual script result. Default is |
|
|
If set to |
|
|
Location of the help and documentation accessed from the Help link within the development environment. Default is |
|
|
The regular expression used to validate a username if the Builder authentication scheme is not APEX. Default is as follows: ^[[:alnum:]._%-]+@[[:alnum:].-]+\.[[:alpha:]]{2,4}$ |
|
|
The path to the wallet on the file system, for example:
file:/home/<username>/wallets
|
|
|
The password associated with the wallet. |
|
|
If set to |
|
|
Maximum number of emails allowed to be sent via APEX_MAIL per workspace in a 24 hour period. Default is |
|
|
The maximum space allocated for script results. Default is |
|
|
If set to |
|
|
If set to |
|
|
Defines the maximum number of days a developer or administrator account password may be used before the account expires. The default value is 45 days. |
|
|
Specify either standard support or advanced support. Advanced support requires an Oracle BI Publisher license. Valid values include:
|
|
|
Valid values include:
|
|
|
Specifies the host address of the print server converting engine, for example, |
|
|
Defines the port of the print server engine, for example |
|
|
Defines the script that is the print server engine, for example: /xmlpserver/convert |
|
|
Set to |