7 APEX_INSTANCE_ADMIN

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_030200 database users as well as any database user granted the role APEX_ADMINISTRATOR_ROLE.

Topics in this section include:

ADD_SCHEMA Procedure
ADD_WORKSPACE Procedure
GET_PARAMETER Function
GET_SCHEMAS Function
REMOVE_SAVED_REPORTS Procedure
REMOVE_SCHEMA Procedure
REMOVE_WORKSPACE Procedure
SET_PARAMETER Procedure
Available Parameter Values

ADD_SCHEMA Procedure

The ADD_SCHEMA procedure adds a schema to a workspace to schema mapping.

Syntax

APEX_INSTANCE_ADMIN.ADD_SCHEMA(
    p_workspace    IN VARCHAR2,
    p_schema       IN VARCHAR2);

Parameters

Table 7-1 describes the parameters available in the ADD_SCHEMA procedure.

Table 7-1 ADD_SCHEMA Parameters

Parameter	Description
`p_workspace`	The name of the workspace to which the schema mapping will be added.
`p_schema`	The schema to add to the schema to workspace mapping.

Example

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;

ADD_WORKSPACE Procedure

The ADD_WORKSPACE procedure adds a workspace to an Application Express Instance.

Syntax

APEX_INSTANCE_ADMIN.ADD_WORKSPACE(
    p_workspace_id        IN NUMBER DEFAULT NULL,
    p_workspace           IN VARCHAR2,
    p_primary_schema      IN VARCHAR2,
    p_additional_schemas  IN VARCHAR2 );

Parameters

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

Table 7-2 ADD_WORKSPACE Parameters

Parameter	Description
`p_workspace_id`	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.
`p_workspace`	The name of the workspace to be added.
`p_primary_schema`	The primary database schema to associate with the new workspace.
`p_additional_schemas`	A colon delimited list of additional schemas to associate with this workspace.

Example

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(8675309,'MY_WORKSPACE','SCOTT','HR:OE');
END;

GET_PARAMETER Function

The GET_PARAMETER function retrieves the value of a parameter used in administering a runtime environment.

Syntax

APEX_INSTANCE_ADMIN.GET_PARAMETER(
    p_parameter     IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 7-3 describes the parameters available in the GET_PARAMETER function.

Table 7-3 GET_PARAMETER Parameters

Parameter Description

Parameter	Description
`p_parameter`	The instance parameter to be retrieved. See "Available Parameter Values".

p_parameter

The instance parameter to be retrieved.

See "Available Parameter Values".

Example

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;

GET_SCHEMAS Function

The GET_SCHEMAS function retrieves a comma-delimited list of schemas that are mapped to a given workspace.

Syntax

APEX_INSTANCE_ADMIN.GET_SCHEMAS(
    p_workspace     IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 7-4 describes the parameters available in the GET_SCHEMAS function.

Table 7-4 GET_SCHEMAS Parameters

Parameter	Description
`p_workspace`	The name of the workspace from which to retrieve the schema list.

Example

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;

REMOVE_SAVED_REPORTS Procedure

The REMOVE_SAVED_REPORTS procedure removes all user saved interactive report settings for a particular application or for the entire instance.

Syntax

APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORTS(
    p_application_id     IN NUMBER DEFAULT NULL);

Parameters

Table 7-5 describes the parameters available in the REMOVE_SAVED_REPORTS procedure.

Table 7-5 REMOVE_SAVED_REPORTS Parameters

Parameter	Description
`p_application_id`	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.

Example

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;

REMOVE_SCHEMA Procedure

This REMOVE_SCHEMA procedure removes a workspace to schema mapping.

Syntax

APEX_INSTANCE_ADMIN.REMOVE_SCHEMA(
    p_workspace     IN VARCHAR2,
    p_schema        IN VARCHAR2);

Parameters

Table 7-6 describes the parameters available in the REMOVE_SCHEMA procedure.

Table 7-6 REMOVE_SCHEMA Parameters

Parameter	Description
`p_workspace`	The name of the workspace from which the schema mapping will be removed.
`p_schema`	The schema to remove from the schema to workspace mapping.

Example

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;

REMOVE_WORKSPACE Procedure

The REMOVE_WORKSPACE procedure removes a workspace from an Application Express instance.

Syntax

APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(
    p_workspace         IN VARCHAR2,
    p_drop_users        IN VARCHAR2 DEFAULT 'N',
    p_drop_tablespaces  IN VARCHAR2 DEFAULT 'N' );

Parameters

Table 7-7 describes the parameters available in the REMOVE_WORKSPACE procedure.

Table 7-7 REMOVE_WORKSPACE Parameters

Parameter	Description
`p_workspace`	The name of the workspace to be removed.
`p_drop_users`	`'Y'` to drop the database user associated with the workspace. The default is `'N'`.
`p_drop_tablespaces`	'Y' to drop the tablespace associated with the database user associated with the workspace. The default is `'N'`.

Example

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;

SET_PARAMETER Procedure

The SET_PARAMETER procedure sets a parameter used in administering a runtime environment.

Syntax

APEX_INSTANCE_ADMIN.SET_PARAMETER(
    p_parameter     IN VARCHAR2,
    p_value         IN VARCHAR2 DEFAULT 'N');

Parameters

Table 7-8 describes the parameters available in the SET_PARAMETER procedure.

Table 7-8 SET_PARAMETER Parameters

Parameter Description

Parameter	Description
`p_parameter`	The instance parameter to be set.
`p_value`	The value of the parameter. See "Available Parameter Values".

p_parameter

The instance parameter to be set.

p_value

The value of the parameter.

See "Available Parameter Values".

Example

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;

Available Parameter Values

Table 7-9 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-9 Available Parameters

Parameter Name	Description
`SMTP_FROM`	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: `someone@somewhere.com`
`SMTP_HOST_ADDRESS`	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
`SMTP_HOST_PORT`	Defines the port the SMTP server listens to for mail requests. Default setting: 25
`WALLET_PATH`	The path to the wallet on the file system, for example: file:/home/<username>/wallets
`WALLET_PWD`	The password associated with the wallet.
`PRINT_BIB_LICENSED`	Specify either standard support or advanced support. Advanced support requires an Oracle BI Publisher license. Valid values include: `STANDARD` `ADVANCED`
`PRINT_SVR_PROTOCOL`	Valid values include: `http` `https`
`PRINT_SVR_HOST`	Specifies the host address of the print server converting engine, for example, `localhost`. Enter the appropriate host address if the print server is installed at another location.
`PRINT_SVR_PORT`	Defines the port of the print server engine, for example `8888`. Value must be a positive integer.
`PRINT_SVR_SCRIPT`	Defines the script that is the print server engine, for example: /xmlpserver/convert