Skip Headers
Oracle® Application Express API Reference
Release 4.2 for Oracle Database 12c

E17963-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 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_040200 database users and any database user granted the role APEX_ADMINISTRATOR_ROLE.

Topics:


Available Parameter Values

Table 10-1 lists all the available parameter values you can set within the APEX_INSTANCE_ADMIN package, including parameters for email, wallet, and reporting printing.

Table 10-1 Available Parameters

Parameter Name Description

ACCOUNT_LIFETIME_DAYS

The maximum number of days an end-user account password may be used before the account is expired.

ALLOW_DB_MONITOR

If set to Y, the default, database monitoring is enabled. If set to N, it is disabled.

ALLOW_PUBLIC_FILE_UPLOAD

If set to Y, file uploads are allowed without user authentication. If set to N, the default, they are not allowed.

ALLOW_REST

If set to Y, the default, developers are allowed to expose report regions as RESTful services. If set to N, the are not allowed.

APPLICATION_ACTIVITY_LOGGING

Controls instance wide setting of application activity log ([A]lways, [N]ever, [U]se application settings)

AUTOEXTEND_TABLESPACES

If set to Y, the default, provisioned tablespaces is autoextended up to a maximum size. If set to N tablespaces are not autoextended.

BIGFILE_TABLESPACES_ENABLED

If set to Y, the tablespaces provisioned through Oracle Application Express are created as bigfile tablespaces. If set to N, the tablespaces are created as smallfile tablespaces.

DELETE_UPLOADED_FILES_AFTER_DAYS

Uploaded files like application export files, websheet export files, spreadsheet data load files are automatically deleted after this number of days. Default is 14.

DISABLE_ADMIN_LOGIN

If set to Y, administration services are disabled. If set to N, the default, they are not disabled.

DISABLE_WORKSPACE_LOGIN

If set to Y, the workspace login is disabled. If set to N, the default, the login is not disabled.

DISABLE_WS_PROV

If set to Y, the workspace creation is disabled for requests sent out by using e-mail notification. If set to N, the default, they are not disabled.

EMAIL_IMAGES_URL

Specifies the full URL to the images directory of Application Express instance, including the trailing slash after the images directory. For example: http://your_server/i/

EMAIL_INSTANCE_URL

Specifies the URL to Application Express instance, including the trailing slash after the Database Access Descriptor. For example: http://your_server/pls/apex/

ENABLE_TRANSACTIONAL_SQL

If set to Y, the default, transactional SQL commands are enabled on this instance. If set to N, they are not enabled.

ENCRYPTED_TABLESPACES_ENABLED

If set to Y, the tablespaces provisioned through Oracle Application Express are created as encrypted tablespaces. If set to N, the tablespaces are not encyrpted.

EXPIRE_FIND_USER_ACCOUNTS

If set to Y, expiration of Application Express accounts is enabled. If set to N, they are not enabled.

INBOUND_PROXIES

Comma-separated list of IP addresses for proxy servers through which requests come in.

LOGIN_THROTTLE_DELAY

The flag which determines the time increase in seconds after failed logins.

LOGIN_THROTTLE_METHODS

The methods to count failed logins. Colon-separated list of USERNAME_IP, USERNAME, IP.

MAX_SESSION_IDLE_SEC

The number of seconds an internal application may be idle.

MAX_SESSION_LENGTH_SEC

The number of seconds an internal application session may exist.

PASSWORD_ALPHA_CHARACTERS

The alphabetic characters used for password complexity rules. Default list of alphabetic characters include the following:

abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ

PASSWORD_MIN_LENGTH

A positive integer or 0 which specifies the minimum character length for passwords for instance administrators, workspace administrators, developers, and end user Application Express accounts.

PASSWORD_NEW_DIFFERS_BY

A positive integer or 0 which specifies the number of differences required between old and new passwords. The passwords are compared character by character, and each difference that occurs in any position counts toward the required minimum difference. This setting applies to accounts for instance administrators, workspace administrators, developers, and end user Application Express accounts.

PASSWORD_PUNCTUATION_CHARACTERS

The punctuation characters used for password complexity rules. Default list of punctuation characters include the following: !"#$%&()``*+,-/:;<=>?_

PLSQL_EDITING

If set to Y, the default, the SQL Workshop Object Browser is enabled to allow users to edit and compile PL/SQL. If set to N, users are not allowed.

REQUIRE_HTTPS

If set to Y, access to the instance must be over SSL. If set to N, the default, access is not restricted to SSL.

REQUIRE_VERIFICATION_CODE

If set to Y, the Verification Code is displayed and is required for someone to request a new workspace. If set to N, the default, the Verification Code is not required.

REQ_NEW_SCHEMA

If set to Y, the option for new schema for new workspace requests is enabled. If set to N, the default, the option is disabled.

RESTFULL_SERVICES_ENABLED

If set to Y, the default, RESTful services development is enabled. If set to N, RESTful services are not enabled.

SERVICE_REQUESTS_ENABLED

If set to Y, the default, workspace service requests for schemas, storage, and termination is enabled. If set to N, these requests are disabled.

SERVICE_REQUEST_FLOW

Determines default provisioning mode. Default is MANUAL.

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

SMTP_PASSWORD

Defines the password Application Express takes to authenticate itself against the SMTP server, with the parameter SMTP_USERNAME.

SMTP_TLS_MODE

Defines whether Application Express 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 N, the connection is unencrypted (default).

If set to Y, the connection is encrypted before data is sent.

If STARTTLS, Application Express sends the SMTP commands EHLO <SMTP_HOST_ADDRESS> and STARTTLS before encrypting the connection.

SMTP_USERNAME

Defines the username Application Express takes to authenticate itself against the SMTP server (default is null). Starting with database version 11.2.0.2, Application Express uses UTL_MAIL's AUTH procedure for authentication. This procedure negotiates an authentication mode with the SMTP server. With earlier database versions, the authentication mode is always AUTH LOGIN. If SMTP_USERNAME is null, no authentication is used.

SQL_SCRIPT_MAX_OUTPUT_SIZE

The maximum allowable size for an individual script result. Default is 200000.

STRONG_SITE_ADMIN_PASSWORD

If set to Y, the default, the apex_admin password must conform to the default set of strong complexity rules. If set to N, the password is not required to follow the strong complexity rules.

SYSTEM_HELP_URL

Location of the help and documentation accessed from the Help link within the development environment. Default is http://apex.oracle.com/doc41.

TRACING_ENABLED

If set to Y (the default), an application with Debug enabled can also generate server side db trace files using &p_trace=YES on the URL.

If set to N, the request to create a trace file is ignored.

USERNAME_VALIDATION

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}$

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.

WEBSHEET_SQL_ACCESS

If set to Y, the default, SQL tags and SQL reports are possible in Websheet applications. If set to N, they are not possible.

WORKSPACE_EMAIL_MAXIMUM

Maximum number of emails allowed to be sent by using APEX_MAIL per workspace in a 24 hour period. Default is 1000.

WORKSPACE_MAX_OUTPUT_SIZE

The maximum space allocated for script results. Default is 2000000.

WORKSPACE_PROVISION_DEMO_OBJECTS

If set to Y, the default, demonstration applications and database objects are created in new workspaces. If set to N, they are not created in the current workspace.

WORKSPACE_WEBSHEET_OBJECTS

If set to Y, the default, Application Express Websheet database objects are created in new workspaces. If set to N, they are not created in the current workspace.

PASSWORD_HISTORY_DAYS

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.

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

REQUIRE_HTTPS

Set to Y to allow authentication pages within the Application Express development and administration applications to be used only when the protocol is HTTPS. Select N to allow these application pages to be used when the protocol is either HTTP or HTTPS.



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 10-2 describes the parameters available in the ADD_SCHEMA procedure.

Table 10-2 ADD_SCHEMA Parameters

Parameter Description

p_workspace

The name of the workspace to which the schema mapping is 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_source_identifier   IN VARCHAR2 DEFAULT NULL,
    p_primary_schema      IN VARCHAR2,
    p_additional_schemas  IN VARCHAR2,
    p_rm_consumer_group   IN VARCHAR2 DEFAULT NULL );

Parameters

Table 10-3 describes the parameters available in the ADD_WORKSPACE procedure.

Table 10-3 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 is assigned.

p_workspace

The name of the workspace to be added.

p_source_identifier

A short identifier for the workspace used when synchronizing feedback between different instances.

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.

p_rm_consumer_group

Resource Manager consumer group which is used when executing applications of 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 (
        p_workspace_id       => 8675309,
        p_workspace          => 'MY_WORKSPACE',
        p_primary_schema     => 'SCOTT',
        p_additional_schemas => '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 10-4 describes the parameters available in the GET_PARAMETER function.

Table 10-4 GET_PARAMETER Parameters

Parameter Description

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 10-5 describes the parameters available in the GET_SCHEMAS function.

Table 10-5 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_APPLICATION Procedure

The REMOVE_APPLICATION procedure removes the application specified from the Application Express instance.

Syntax

APEX_INSTANCE_ADMIN.REMOVE_APPLICATION (
    p_application_id IN NUMBER);

Parameters

Table 10-6 describes the REMOVE_APPLICATION procedure parameters.

Table 10-6 REMOVE_APPLICATION Parameters

Parameter Description

p_application_id

The ID of the application to remove.


Example

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;

REMOVE_SAVED_REPORT Procedure

The REMOVE_SAVED_REPORT procedure removes a specific user's saved interactive report settings for a particular application.

Syntax

APEX_INSTANCE_ADMIN.REMOVE_SAVED_REPORT(
    p_application_id     IN NUMBER,
    p_report_id          IN NUMBER);

Parameters

Table 10-7 describes the parameters available in the REMOVE_SAVED_REPORT procedure.

Table 10-7 REMOVE_SAVED_REPORT Parameters

Parameter Description

p_application_id

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.


Example

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;

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 10-8 describes the parameters available in the REMOVE_SAVED_REPORTS procedure.

Table 10-8 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 is 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 10-9 describes the parameters available in the REMOVE_SCHEMA procedure.

Table 10-9 REMOVE_SCHEMA Parameters

Parameter Description

p_workspace

The name of the workspace from which the schema mapping is 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_SUBSCRIPTION Procedure

The REMOVE_SUBSCRIPTION procedure removes a specific interactive report subscription.

Syntax

APEX_INSTANCE_ADMIN.REMOVE_SUBSCRIPTION(
    p_subscription_id     IN NUMBER);

Parameters

Table 10-10 describes the parameters available for the REMOVE_SUBSCRIPTION procedure.

Table 10-10 REMOVE_SUBSCRIPTION Procedure Parameters

Parameter Description

p_subscription_id

The ID of the interactive report subscription to be removed.


Example

The following example demonstrates how to use the REMOVE_SUBSCRIPTION procedure to remove interactive report subscription with the ID 12345. Use of APEX_APPLICATION_PAGE_IR_SUB view can help identifying the subscription ID to remove.

BEGIN
    APEX_INSTANCE_ADMIN.REMOVE_SUBSCRIPTION (
        p_subscription_id => 12345);
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 10-11 describes the parameters available in the REMOVE_WORKSPACE procedure.

Table 10-11 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_LOG_SWITCH_INTERVAL Procedure

Set the log switch interval for each of the logs maintained by Application Express.

Syntax

APEX_INSTANCE_ADMIN.SET_LOG_SWITCH_INTERVAL(
    p_log_name              IN VARCHAR2,
    p_log_switch_after_days IN NUMBER );

Parameters

Table 10-12 describes the parameters available in the SET_LOG_SWITCH_INTERVAL procedure.

Table 10-12 SET_LOG_SWITCH_INTERVAL Parameters

Parameters Description

p_log_name

Specifies the name of the log. Valid values include ACCESS, ACTIVITY, CLICKTHRU, and DEBUG.

p_log_switch_after_days

This interval must be a positive integer between 1 and 180.


Example

This example sets the log switch interval for the ACTIVITY log to 30 days.

begin
    apex_instance_admin.set_log_switch_interval( p_log_name in 'ACTIVITY', p_log_switch_after_days => 30 );
    commit;
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 10-13 describes the parameters available in the SET_PARAMETER procedure.

Table 10-13 SET_PARAMETER Parameters

Parameter Description

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.example.com');
END;

SET_WORKSPACE_CONSUMER_GROUP Procedure

The SET_WORKSPACE_CONSUMER_GROUP procedure sets a Resource Manager Consumer Group to a workspace.

Syntax

set_workspace_consumer_group( 
    p_workspace in varchar2, 
    p_rm_consumer_group in varchar2 ); 

Parameters

Table 10-14 describes the parameters available for the SET_WORKSPACE_CONSUMER_GROUP procedure.

Table 10-14 SET_WORKSPACE_CONSUMER_GROUP Parameters

Parameters Description

p_workspace

This is the name of the workspace for which the resource consumer group is to be set.

p_rm_consumer_group

The parameter P_RM_CONSUMER_GROUP is the Oracle Database Resource Manager Consumer Group name. The consumer group does not have to exist at the time this procedure is invoked. But if the Resource Manager Consumer Group is set for a workspace and the consumer group does not exist, then an error will be raised when anyone attempts to login to this workspace or execute any application in the workspace.

If the value of P_RM_CONSUMER_GROUP is null, then the Resource Manager consumer group associated with the specified workspace is cleared.


Example

The following example sets the workspace to the Resource Manager consumer group "CUSTOM_GROUP1":

begin
        apex_instance_admin.set_workspace_consumer_group(
        p_workspace => 'MY_WORKSPACE',
        p_rm_consumer_group => 'CUSTOM_GROUP1' );
    commit;
end;
/

TRUNCATE_LOG Procedure

The TRUNCATE_LOG procedure truncates the log entries specified by the input parameter.

Syntax

APEX_INSTANCE_ADMIN.TRUNCATE_LOG(
    p_log     IN VARCHAR2);

Parameters

Table 10-15 describes the parameters available in the TRUNCATE_LOG procedure.

Table 10-15 TRUNCATE_LOG Parameters

Parameter Description

p_log

This parameter can have one of the following values:

ACTIVITY - removes all entries that record page access.

USER_ACCESS - removes all entries that record user login.

MAIL - removes all entries that record mail sent.

DEVELOPER - removes all entries that record developer activity.

CLICKS - removes all entries that record clicks tracked to external sites.

LOCK_PAGE - removes all entries that record developer locking of pages.

WORKSPACE_HIST - removes all entries that record daily workspace summary.

PURGE - removes all entries that record automatic workspace purge activity.

FILE - removes all entries that record automatic file purge activity.

SCRIPT - removes all entries that record results of SQL scripts executed in SQL Workshop.

SQL - removes all entries that record the history of commands executed in SQL Workshop SQL Commands


Example

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;