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 schema to workspace mappings. APEX_INSTANCE_ADMIN can be executed by the SYS, SYSTEM, and FLOWS_030100 database users as well as any database user granted the role APEX_ADMINISTRATOR_ROLE.
Topics in this section include:
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);
Table 7-1 describes the parameters available in the SET_PARAMETER procedure.
Table 7-1 SET_PARAMETER Parameters
| Parameter | Description |
|---|---|
|
|
The instance parameter to be set. |
|
|
The value of the parameter. |
BEGIN
APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_HOST','mail.mycompany.com');
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 7-2 describes the parameters available in the GET_PARAMETER function.
Table 7-2 GET_PARAMETER Parameters
| Parameter | Description |
|---|---|
|
|
The instance parameter to be retrieved. |
DECLARE L_VAL VARCHAR2(4000);
BEGIN
L_VAL :=
APEX_INSTANCE_ADMIN.GET_PARAMETER('SMTP_HOST');
HTP.P('The SMTP Host Setting Is: '||L_VAL);
END;
Table 7-3 lists all the available parameter values you can set within the APEX_INSTANCE_ADMIN package, including parameters for email, wallet, and reporting printing.
Table 7-3 Available Parameters
| Parameter Name | Description |
|---|---|
|
|
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 |
|
|
The path to the wallet on the file system, for example:
file:/home/<username>/wallets
|
|
|
The password associated with the wallet. |
|
|
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 |
The GET_SCHEMAS function retrieves a list of schemas that are mapped to a given workspace.
APEX_INSTANCE_ADMIN.GET_SCHEMAS(
p_workspace IN VARCHAR2)
RETURN VARCHAR2;
Table 7-4 describes the parameters available in the GET_SCHEMAS function.
Table 7-4 GET_SCHEMAS Parameters
| Parameter | Description |
|---|---|
|
|
The name of the workspace from which to retrieve the schema list. |
DECLARE
L_VAL VARCHAR2(4000);
BEGIN
L_VAL :=
APEX_INSTANCE_ADMIN.GET_SCHEMAS('MY_WORKSPACE');
HTP.P('The schemas for my workspace: '||L_VAL);
END;
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 7-5 describes the parameters available in the ADD_SCHEMA procedure.
Table 7-5 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. |
BEGIN
APEX_INSTANCE_ADMIN.ADD_SCHEMA
('MY_WORKSPACE','FRANK');
END;
This REMOVE_SCHEMA procedure removes a schema from a workspace to schema mapping.
APEX_INSTANCE_ADMIN.REMOVE_SCHEMA(
p_workspace IN VARCHAR2
p_schema IN VARCHAR2);
Table 7-6 describes the parameters available in the REMOVE_SCHEMA procedure.
Table 7-6 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. |
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_SCHEMA
('MY_WORKSPACE','FRANK');
END;
The REMOVE_WORKSPACE procedure removes a workspace from a an Application Express instance.
PROCEDURE REMOVE_WORKSPACE(
p_workspace IN VARCHAR2
p_drop_users IN VARCHAR2 DEFAULT 'N',
p_drop_tablespaces IN VARCHAR2 DEFAULT 'N' );
Table 7-7 describes the parameters available in the REMOVE_WORKSPACE procedure.
Table 7-7 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 |
BEGIN
APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE
('MY_WORKSPACE','Y','Y');
END;
The ADD_WORKSPACE procedure adds a workspace to an Application Express Instance.
PROCEDURE REMOVE_WORKSPACE(
p_workspace_id IN NUMBER DEFAULT NULL,
p_workspace IN VARCHAR2,
p_primary_schema IN VARCHAR2,
p_additional_schemas IN VARCHAR2 );
Table 7-8 describes the parameters available in the ADD_WORKSPACE procedure.
Table 7-8 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. |
|
|
The primary database schema to associate with the new workspace. |
|
|
A colon delimited list of additional schemas to associate with this workspace. |
BEGIN APEX_INSTANCE_ADMIN.ADD_WORKSPACE (8675309,'MY_WORKSPACE','SCOTT','HR:OE'); END;