21 APEX_UTIL

The APEX_UTIL package provides utilities you can use when programming in the Oracle Application Express environment. You can use the APEX_UTIL package to get and set session state, get files, check authorizations for users, reset different states for users, get and purge cache information and also to get and set preferences for users.

Topics:

CACHE_GET_DATE_OF_PAGE_CACHE Function

This function returns the date and time a specified application page was cached either for the user issuing the call, or for all users if the page was not set to be cached by user.

Syntax

APEX_UTIL.CACHE_GET_DATE_OF_PAGE_CACHE (
    p_application  IN    NUMBER,
    p_page         IN    NUMBER)
RETURN DATE;

Parameters

Table 21-1 describes the parameters available in the CACHE_GET_DATE_OF_PAGE_CACHE function.

Table 21-1 CACHE_GET_DATE_OF_PAGE_CACHE Parameters

Parameter Description

p_application

The identification number (ID) of the application.

p_page

The page number (ID).


Example

The following example demonstrates how to use the CACHE_GET_DATE_OF_PAGE_CACHE function to retrieve the cache date and time for page 9 of the currently executing application. If page 9 has been cached, the cache date and time is output using the HTP package. The page could have been cached either by the user issuing the call, or for all users if the page was not to be cached by the user.

DECLARE
    l_cache_date DATE DEFAULT NULL;
BEGIN
    l_cache_date := APEX_UTIL.CACHE_GET_DATE_OF_PAGE_CACHE(
                        p_application => :APP_ID,
                        p_page => 9);
    IF l_cache_date IS NOT NULL THEN
        HTP.P('Cached on ' || TO_CHAR(l_cache_date, 'DD-MON-YY HH24:MI:SS'));
    END IF;
END;

CACHE_GET_DATE_OF_REGION_CACHE Function

This function returns the date and time a specified region was cached either for the user issuing the call, or for all users if the page was not set to be cached by user.

Syntax

APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE (
    p_application  IN    NUMBER,
    p_page         IN    NUMBER,
    p_region_name  IN    VARCHAR2)
RETURN DATE;

Parameters

Table 21-2 describes the parameters available in the CACHE_GET_DATE_OF_REGION_CACHE function.

Table 21-2 CACHE_GET_DATE_OF_REGION_CACHE Parameters

Parameter Description

p_application

The identification number (ID) of the application

p_page

The page number (ID)

p_region_name

The region name


Example

The following example demonstrates how to use the CACHE_GET_DATE_OF_REGION_CACHE function to retrieve the cache date and time for the region named Cached Region on page 13 of the currently executing application. If the region has been cached, the cache date and time is output using the HTP package. The region could have been cached either by the user issuing the call, or for all users if the page was not to be cached by user.

DECLARE
    l_cache_date DATE DEFAULT NULL;
BEGIN
    l_cache_date := APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE(
        p_application => :APP_ID,
        p_page => 13,
        p_region_name => 'Cached Region');
    IF l_cache_date IS NOT NULL THEN
        HTP.P('Cached on ' || TO_CHAR(l_cache_date, 'DD-MON-YY HH24:MI:SS'));
    END IF;
END;

CACHE_PURGE_BY_APPLICATION Procedure

This procedure purges all cached pages and regions for a given application.

Syntax

APEX_UTIL.CACHE_PURGE_BY_APPLICATION (
    p_application  IN  NUMBER);

Parameters

Table 21-3 describes the parameters available in the CACHE_PURGE_BY_APPLICATION procedure.

Table 21-3 CACHE_PURGE_BY_APPLICATION Parameters

Parameter Description

p_application

The identification number (ID) of the application.


Example

The following example demonstrates how to use the CACHE_PURGE_BY_APPLICATION procedure to purge all the cached pages and regions for the application currently executing.

BEGIN
    APEX_UTIL.CACHE_PURGE_BY_APPLICATION(p_application => :APP_ID);
END;

CACHE_PURGE_BY_PAGE Procedure

This procedure purges the cache for a given application and page. If the page itself is not cached but contains one or more cached regions, then the cache for these is also purged.

Syntax

APEX_UTIL.CACHE_PURGE_BY_PAGE (
    p_application  IN    NUMBER,
    p_page         IN    NUMBER,
    p_user_name    IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 21-4 describes the parameters available in the CACHE_PURGE_BY_PAGE procedure.

Table 21-4 CACHE_PURGE_BY_PAGE Parameters

Parameter Description

p_application

The identification number (ID) of the application.

p_page

The page number (ID).

p_user_name

The user associated with cached pages and regions.


Example

The following example demonstrates how to use the CACHE_PURGE_BY_PAGE procedure to purge the cache for page 9 of the application currently executing. Additionally, if the p_user_name parameter is supplied, this procedure would be further restricted by a specific users cache (only relevant if the cache is set to be by user).

BEGIN
    APEX_UTIL.CACHE_PURGE_BY_PAGE(
        p_application => :APP_ID,
        p_page => 9);
END;

CACHE_PURGE_STALE Procedure

This procedure deletes all cached pages and regions for a specified application that have passed the defined active time period. When you cache a page or region, you specify an active time period (or Cache Timeout). Once that period has passed, the cache is no longer used, thus removing those unusable pages or regions from the cache.

Syntax

APEX_UTIL.CACHE_PURGE_STALE (
    p_application  IN    NUMBER);

Parameters

Table 21-5 describes the parameters available in the CACHE_PURGE_STALE procedure.

Table 21-5 CACHE_PURGE_STALE Parameters

Parameter Description

p_application

The identification number (ID) of the application.


Example

The following example demonstrates how to use the CACHE_PURGE_STALE procedure to purge all the stale pages and regions in the application currently executing.

BEGIN
    APEX_UTIL.CACHE_PURGE_STALE(p_application => :APP_ID);
END;

CHANGE_CURRENT_USER_PW Procedure

This procedure changes the password of the currently authenticated user, assuming Application Express user accounts are in use.

Syntax

APEX_UTIL.CHANGE_CURRENT_USER_PW(
    p_new_password IN VARCHAR2);

Parameters

Table 21-6 describes the parameters available in the CHANGE_CURRENT_USER_PW procedure.

Table 21-6 CHANGE_CURRENT_USER_PW Parameters

Parameter Description

p_new_password

The new password value in clear text


Example

The following example demonstrates how to use the CHANGE_CURRENT_USER_PW procedure to change the password for the user who is currently authenticated, assuming Application Express accounts are in use.

BEGIN
    APEX_UTIL.CHANGE_CURRENT_USER_PW ('secret99');
END;

CHANGE_PASSWORD_ON_FIRST_USE Function

Enables a developer to check whether this property is enabled or disabled for an end user account. This function returns true if the account password must be changed upon first use (after successful authentication) after the password is initially set and after it is changed on the Administration Service, Edit User page. This function returns false if the account does not have this property.

This function may be run in a page request context by any authenticated user.

Syntax

APEX_UTIL.CHANGE_PASSWORD_ON_FIRST_USE (
    p_user_name IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 21-7 describes the parameters available in the CHANGE_PASSWORD_ON_FIRST_USE function.

Table 21-7 CHANGE_PASSWORD_ON_FIRST_USE Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example demonstrates how to use the CHANGE_PASSWORD_ON_FIRST_USE function. Use this function to check if the password of an Application Express user account (workspace administrator, developer, or end user) in the current workspace must be changed by the user the first time it is used.

BEGIN
    FOR c1 IN (SELECT user_name FROM wwv_flow_users) LOOP
        IF APEX_UTIL.CHANGE_PASSWORD_ON_FIRST_USE(p_user_name => c1.user_name) THEN
            htp.p('User:'||c1.user_name||' requires password to be changed the first time it is used.');
        END IF;
    END LOOP;
END;

CLEAR_APP_CACHE Procedure

This procedure removes session state for a given application for the current session.

Syntax

APEX_UTIL.CLEAR_APP_CACHE (
    p_app_id    IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 21-8 describes the parameters available in the CLEAR_APP_CACHE procedure.

Table 21-8 CLEAR_APP_CACHE Parameters

Parameter Description

p_app_id

The ID of the application for which session state is cleared for current session


Example

The following example demonstrates how to use the CLEAR_APP_CACHE procedure to clear all the current sessions state for the application with an ID of 100.

BEGIN
    APEX_UTIL.CLEAR_APP_CACHE('100');
END;

CLEAR_PAGE_CACHE Procedure

This procedure removes session state for a given page for the current session.

Syntax

APEX_UTIL.CLEAR_PAGE_CACHE (
    p_page IN NUMBER DEFAULT NULL);

Parameters

Table 21-9 describes the parameters available in the CLEAR_PAGE_CACHE procedure.

Table 21-9 CLEAR_PAGE_CACHE Parameters

Parameter Description

p_page

The ID of the page in the current application for which session state is cleared for current session.


Example

The following example demonstrates how to use the CLEAR_PAGE_CACHE procedure to clear the current session s state for the page with an ID of 10.

BEGIN
    APEX_UTIL.CLEAR_PAGE_CACHE('10');
END;

CLEAR_USER_CACHE Procedure

This procedure removes session state and application system preferences for the current user's session. Run this procedure if you reuse session IDs and want to run applications without the benefit of existing session state.

Syntax

APEX_UTIL.CLEAR_USER_CACHE;

Parameters

None.

Example

The following example demonstrates how to use the CLEAR_USER_CACHE procedure to clear all session state and application system preferences for the current user's session.

BEGIN
    APEX_UTIL.CLEAR_USER_CACHE;
END;

COUNT_CLICK Procedure

This procedure counts clicks from an application built in Application Builder to an external site. You can also use the shorthand version, procedure Z, in place of APEX_UTIL.COUNT_CLICK.

Syntax

APEX_UTIL.COUNT_CLICK (
    p_url         IN    VARCHAR2,
    p_cat         IN    VARCHAR2,
    p_id          IN    VARCHAR2    DEFAULT NULL,
    p_user        IN    VARCHAR2    DEFAULT NULL,
    p_workspace   IN    VARCHAR2    DEFAULT NULL);

Parameters

Table 21-10 describes the parameters available in the COUNT_CLICK procedure.

Table 21-10 COUNT_CLICK Parameters

Parameter Description

p_url

The URL to which to redirect

p_cat

A category to classify the click

p_id

Secondary ID to associate with the click (optional)

p_user

The application user ID (optional)

p_workspace

The workspace associated with the application (optional)


Example

The following example demonstrates how to use the COUNT_CLICK procedure to log how many user's click on the http://yahoo.com link specified. Note that once this information is logged, you can view it by using the APEX_WORKSPACE_CLICKS view and in the reports on this view available to workspace and site administrators.

DECLARE
    l_url VARCHAR2(255);
    l_cat VARCHAR2(30);
    l_workspace_id VARCHAR2(30);
BEGIN
    l_url := 'http://yahoo.com';
    l_cat := 'yahoo';
    l_workspace_id := TO_CHAR(APEX_UTIL.FIND_SECURITY_GROUP_ID('MY_WORKSPACE'));
 
    HTP.P('<a href=APEX_UTIL.COUNT_CLICK?p_url=' || l_url || '&p_cat=' || l_cat || '&p_workspace=' || l_workspace_id || '>Click</a>');
END;

CREATE_USER Procedure

This procedure creates a new account record in the Application Express user account table. To execute this procedure, the current user must have administrative privileges.

Syntax

APEX_UTIL.CREATE_USER(
    p_user_id                       IN      NUMBER      DEFAULT NULL,
    p_user_name                     IN      VARCHAR2,
    p_first_name                    IN      VARCHAR2    DEFAULT NULL,
    p_last_name                     IN      VARCHAR2    DEFAULT NULL,
    p_description                   IN      VARCHAR2    DEFAULT NULL,
    p_email_address                 IN      VARCHAR2    DEFAULT NULL,
    p_web_password                  IN      VARCHAR2,
    p_web_password_format           IN      VARCHAR2    DEFAULT 'CLEAR_TEXT',
    p_group_ids                     IN      VARCHAR2    DEFAULT NULL,
    p_developer_privs               IN      VARCHAR2    DEFAULT NULL,
    p_default_schema                IN      VARCHAR2    DEFAULT NULL,
    p_allow_access_to_schemas       IN      VARCHAR2    DEFAULT NULL,
    p_account_expiry                IN      DATE        DEFAULT TRUNC(SYSDATE),
    p_account_locked                IN      VARCHAR2    DEFAULT 'N',
    p_failed_access_attempts        IN      NUMBER      DEFAULT 0,
    p_change_password_on_first_use  IN      VARCHAR2    DEFAULT 'Y',
    p_first_password_use_occurred   IN      VARCHAR2    DEFAULT 'N',
    p_attribute_01                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_02                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_03                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_04                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_05                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_06                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_07                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_08                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_09                  IN      VARCHAR2    DEFAULT NULL,
    p_attribute_10                  IN      VARCHAR2    DEFAULT NULL,
    p_allow_app_building_yn         IN      VARCHAR2    DEFAULT NULL,
    p_allow_sql_workshop_yn         IN      VARCHAR2    DEFAULT NULL,
    p_allow_websheet_dev_yn         IN      VARCHAR2    DEFAULT NULL,
    p_allow_team_development_yn     IN      VARCHAR2    DEFAULT NULL);

Parameters

Table 21-11 describes the parameters available in the CREATE_USER procedure.

Table 21-11 CREATE_USER Procedure Parameters

Parameter Description

p_user_id

Numeric primary key of user account

p_user_name

Alphanumeric name used for login

p_first_name

Informational

p_last_name

Informational

p_description

Informational

p_email_address

Email address

p_web_password

Clear text password

p_web_password_format

If the value your passing for the p_web_password parameter is in clear text format then use CLEAR_TEXT, otherwise use HEX_ENCODED_DIGEST_V2.

p_group_ids

Colon separated list of numeric group IDs

p_developer_privs

Colon separated list of developer privileges. If p_developer_privs is not null, the user is given access to Team Development. If p_developer_privs contains ADMIN, the user is given Application Builder and SQL Workshop access. If p_developer_privs does not contain ADMIN but contains EDIT, the user is given Application Builder Access. If p_developer_privs does not contain ADMIN but contains SQL, the user is given SQL Workshop access. The following are acceptable values for this parameter:

null - To create an end user (a user who can only authenticate to developed applications).

CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To create a user with developer privileges with access to Application Builder and SQL Workshop.

ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To create a user with full workspace administrator and developer privileges with access to Application Builder, SQL Workshop and Team Development.

Note: Currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field stored in the named user account record. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role.

p_default_schema

A database schema assigned to the user's workspace, used by default for browsing.

p_allow_access_to_schemas

Colon separated list of schemas assigned to the user's workspace to which the user is restricted (leave null for all).

p_account_expiry

Date password was last updated, which defaults to today's date on creation.

p_account_locked

'Y' or 'N' indicating if account is locked or unlocked.

p_failed_access_attempts

Number of consecutive login failures that have occurred, defaults to 0 on creation.

p_change_password_on_first_use

'Y' or 'N' to indicate whether password must be changed on first use, defaults to 'Y' on creation.

p_first_password_use_occurred

'Y' or 'N' to indicate whether login has occurred since password change, defaults to 'N' on creation.

p_attribute_01

...

p_attribute_10

Arbitrary text accessible with an API

p_allow_app_building_yn

'Y' or 'N' to indicate whether access is allowed to Application Builder.

p_allow_sql_workshop_yn

'Y' or 'N' to indicate whether access is allowed to SQL Workshop.

p_allow_websheet_dev_yn

'Y' or 'N' to indicate whether access is allowed to Websheet development.

p_allow_team_development_yn

'Y' or 'N' to indicate whether access is allowed to Team Development.


Example 1

The following simple example creates an 'End User' called 'NEWUSER1' with a password of 'secret99'. Note an 'End User' can only authenticate to developed applications.

BEGIN
    APEX_UTIL.CREATE_USER(
        p_user_name    => 'NEWUSER1',
        p_web_password => 'secret99');
END;

Example 2

The following example creates a 'Workspace Administrator' called 'NEWUSER2'. Where the user 'NEWUSER2':

  • Has full workspace administration and developer privilege (p_developer_privs parameter set to 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL').

  • Has access to 2 schemas, both their browsing default 'MY_SCHEMA' (p_default_schema parameter set to 'MY_SCHEMA') and also 'MY_SCHEMA2' (p_allow_access_to_schemas parameter set to 'MY_SCHEMA2').

  • Does not have to change their password when they first login (p_change_password_on_first_use parameter set to 'N').

  • Has their phone number stored in the first additional attribute (p_attribute_01 parameter set to '123 456 7890').

BEGIN
    APEX_UTIL.CREATE_USER(
        p_user_name                     => 'NEWUSER2',
        p_first_name                    => 'FRANK',
        p_last_name                     => 'SMITH',
        p_description                   => 'Description...',
        p_email_address                 => 'frank@smith.com',
        p_web_password                  => 'password',
        p_developer_privs               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
        p_default_schema                => 'MY_SCHEMA',
        p_allow_access_to_schemas       => 'MY_SCHEMA2',
        p_change_password_on_first_use  => 'N',
        p_attribute_01                  => '123 456 7890');
END;

CREATE_USER_GROUP Procedure

Assuming you are using Application Express authentication, this procedure creates a user group. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.CREATE_USER_GROUP(
    p_id                       IN                   NUMBER,
    p_group_name               IN                   VARCHAR2,
    p_security_group_id        IN                   NUMBER,
    p_group_desc               IN                   VARCHAR2);

Parameter

Table 21-12 describes the parameters available in the CREATE_USER_GROUP procedure.

Table 21-12 CREATE_USER_GROUP Parameters

Parameter Description

p_id

Primary key of group

p_group_name

Name of group

p_security_group_id

Workspace ID

p_group_desc

Descriptive text


Example

The following example demonstrates how to use the CREATE_USER_GROUP procedure to create a new group called 'Managers' with a description of 'text'. Pass null for the p_id parameter to allow the database trigger to assign the new primary key value. Pass null for the p_security_group_id parameter to default to the current workspace ID.

BEGIN
    APEX_UTIL.CREATE_USER_GROUP (
        p_id                => null,         -- trigger assigns PK
        p_group_name        => 'Managers',
        p_security_group_id => null,         -- defaults to current workspace ID
        p_group_desc        => 'text');
END;

CURRENT_USER_IN_GROUP Function

This function returns a Boolean result based on whether the current user is a member of the specified group. You can use the group name or group ID to identify the group.

Syntax

APEX_UTIL.CURRENT_USER_IN_GROUP(
    p_group_name    IN VARCHAR2)
RETURN BOOLEAN;

APEX_UTIL.CURRENT_USER_IN_GROUP(
    p_group_id    IN NUMBER)
RETURN BOOLEAN;

Parameters

Table 21-13 describes the parameters available in the CURRENT_USER_IN_GROUP function.

Table 21-13 CURRENT_USER_IN_GROUP Parameters

Parameter Description

p_group_name

Identifies the name of an existing group in the workspace

p_group_id

Identifies the numeric ID of an existing group in the workspace


Example

The following example demonstrates how to use the CURRENT_USER_IN_GROUP function to check if the user currently authenticated belongs to the group 'Managers'.

DECLARE 
    VAL BOOLEAN;
BEGIN
    VAL := APEX_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers');
END;

CUSTOM_CALENDAR Procedure

Use this procedure to change the existing calendar view to Custom Calendar.

Syntax

APEX_UTIL.CUSTOM_CALENDAR(
    p_date_type_field IN VARCHAR2);

Parameters

Table 21-14 describes the parameters available in the CUSTOM_CALENDAR procedure.

Table 21-14 CUSTOM_CALENDAR Parameters

Parameter Description

p_date_type_field

Identifies the item name used to define the type of calendar to be displayed.


Example 1

The following example defines a custom calendar based on the hidden calendar type field. Assuming the Calendar is created in Page 9, the following example hides the column called P9_CALENDAR_TYPE.

APEX_UTIL.CUSTOM_CALENDAR(
    'P9_CALENDAR_TYPE');

DELETE_USER_GROUP Procedure Signature 1

Assuming you are using Application Express authentication, this procedure deletes a user group by providing the primary key of the group. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.DELETE_USER_GROUP(
    p_group_id IN NUMBER);

Parameter

Table 21-12 describes the parameters available in the DELETE_USER_GROUP procedure signature 1.

Table 21-15 DELETE_USER_GROUP Procedure Signature 1 Parameters

Parameter Description

p_group_id

Primary key of group


Example

The following example demonstrates how to use the DELETE_USER_GROUP procedure signature 1 to remove the user group called 'Managers', by providing the user group's primary key.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.GET_GROUP_ID (
        p_group_name => 'Managers');
    APEX_UTIL.DELETE_USER_GROUP (
        p_group_id => VAL);
END;

DELETE_USER_GROUP Procedure Signature 2

Assuming you are using Application Express authentication, this procedure deletes a user group by providing the name of the group. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.DELETE_USER_GROUP(
    p_group_name IN VARCHAR2);

Parameter

Table 21-12 describes the parameters available in the DELETE_USER_GROUP procedure signature 2.

Table 21-16 DELETE_USER_GROUP Procedure Signature 2 Parameters

Parameter Description

p_group_name

Name of group


Example

The following example demonstrates how to use the DELETE_USER_GROUP procedure signature 2 to remove the user group called 'Managers', by providing the name of the user group.

BEGIN
    APEX_UTIL.DELETE_USER_GROUP (
        p_group_name => 'Managers');
END;

DOWNLOAD_PRINT_DOCUMENT Procedure Signature 1

This procedure initiates the download of a print document using XML based report data (as a BLOB) and RTF or XSL-FO based report layout.

Syntax

APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
    p_file_name           IN VARCHAR,
    p_content_disposition IN VARCHAR,
    p_report_data         IN BLOB,
    p_report_layout       IN CLOB,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default null);

Parameters

Table 21-17 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT procedure for Signature 1.

Table 21-17 DOWNLOAD_PRINT_DOCUMENT Parameters

Parameter Description

p_file_name

Defines the filename of the print document

p_content_disposition

Specifies whether to download the print document or display inline ("attachment", "inline")

p_report_data

XML based report data

p_report_layout

Report layout in XSL-FO or RTF format

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


DOWNLOAD_PRINT_DOCUMENT Procedure Signature 2

This procedure initiates the download of a print document using pre-defined report query and RTF and XSL-FO based report layout.

Syntax

APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
    p_file_name           IN VARCHAR,
    p_content_disposition IN VARCHAR,
    p_application_id      IN NUMBER,
    p_report_query_name   IN VARCHAR2,
    p_report_layout       IN CLOB,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default null);

Parameters

Table 21-18 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.

Table 21-18 DOWNLOAD_PRINT_DOCUMENT Parameters

Parameter Description

p_file_name

Defines the filename of the print document

p_content_disposition

Specifies whether to download the print document or display inline ("attachment", "inline")

p_application_id

Defines the application ID of the report query

p_report_query_name

Name of the report query (stored under application's Shared Components)

p_report_layout

Report layout in XSL-FO or RTF format

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


Example for Signature 2

The following example shows how to use the DOWNLOAD_PRINT_DOCUMENT using Signature 2 (Pre-defined report query and RTF or XSL-FO based report layout.). In this example, the data for the report is taken from a Report Query called 'ReportQueryAndXSL' stored in the current application's Shared Components > Report Queries. The report layout is taken from a value stored in a page item (P1_XSL).

BEGIN
    APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
        p_file_name           => 'mydocument',
        p_content_disposition => 'attachment',
        p_application_id      => :APP_ID,
        p_report_query_name   => 'ReportQueryAndXSL',
        p_report_layout       => :P1_XSL,
        p_report_layout_type  => 'xsl-fo',
        p_document_format     => 'pdf');
END;

DOWNLOAD_PRINT_DOCUMENT Procedure Signature 3

This procedure initiates the download of a print document using pre-defined report query and pre-defined report layout.

Syntax

APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
    p_file_name           IN VARCHAR,
    p_content_disposition IN VARCHAR,
    p_application_id      IN NUMBER,
    p_report_query_name   IN VARCHAR2,
    p_report_layout_name  IN VARCHAR2,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default null);

Parameters

Table 21-19 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT procedure for Signature 3.

Table 21-19 DOWNLOAD_PRINT_DOCUMENT Parameters

Parameter Description

p_file_name

Defines the filename of the print document

p_content_disposition

Specifies whether to download the print document or display inline ("attachment", "inline")

p_application_id

Defines the application ID of the report query

p_report_query_name

Name of the report query (stored under application's Shared Components)

p_report_layout_name

Name of the report layout (stored under application's Shared Components)

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


Example for Signature 3

The following example shows how to use the DOWNLOAD_PRINT_DOCUMENT using Signature 3 (Pre-defined report query and pre-defined report layout). In this example, the data for the report is taken from a Report Query called 'ReportQuery' stored in the current application's Shared Components > Report Queries. The report layout is taken from a Report Layout called 'ReportLayout' stored in the current application's Shared Components > Report Layouts. Note that if you want to provision dynamic layouts, instead of specifying 'ReportLayout' for the p_report_layout_name parameter, you could reference a page item that allowed the user to select one of multiple saved Report Layouts. This example also provides a way for the user to specify how they want to receive the document (as an attachment or inline), through passing the value of P1_CONTENT_DISP to the p_content_disposition parameter. P1_CONTENT_DISP is a page item of type 'Select List' with the following List of Values Definition:

STATIC2:In Browser;inline,Save / Open in separate Window;attachment

BEGIN
    APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
        p_file_name           => 'myreport123',
        p_content_disposition => :P1_CONTENT_DISP,
        p_application_id      => :APP_ID,
        p_report_query_name   => 'ReportQuery',
        p_report_layout_name  => 'ReportLayout',
        p_report_layout_type  => 'rtf',
        p_document_format     => 'pdf');
END;

DOWNLOAD_PRINT_DOCUMENT Procedure Signature 4

This procedure initiates the download of a print document using XML based report data (as a CLOB) and RTF or XSL-FO based report layout.

Syntax

APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
    p_file_name           IN VARCHAR,
    p_content_disposition IN VARCHAR,
    p_report_data         IN CLOB,
    p_report_layout       IN CLOB,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default null);

Parameters

Table 21-19 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT procedure for Signature 4.

Table 21-20 DOWNLOAD_PRINT_DOCUMENT Parameters

Parameter Description

p_file_name

Defines the filename of the print document

p_content_disposition

Specifies whether to download the print document or display inline ("attachment", "inline")

p_report_data

XML based report data, must be encoded in UTF-8

p_report_layout

Report layout in XSL-FO or RTF format

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


Example for Signature 4

The following example shows how to use the DOWNLOAD_PRINT_DOCUMENT using Signature 4 (XML based report data (as a CLOB) and RTF or XSL-FO based report layout). In this example both the report data (XML) and report layout (XSL-FO) are taken from values stored in page items.

BEGIN
    APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT (
        p_file_name           => 'mydocument',
        p_content_disposition => 'attachment',
        p_report_data         => :P1_XML,
        p_report_layout       => :P1_XSL,
        p_report_layout_type  => 'xsl-fo',
        p_document_format     => 'pdf');
END;

EDIT_USER Procedure

This procedure enables a user account record to be altered. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.EDIT_USER (
    p_user_id                      IN                   NUMBER,
    p_user_name                    IN                   VARCHAR2,
    p_first_name                   IN                   VARCHAR2    DEFAULT NULL,
    p_last_name                    IN                   VARCHAR2    DEFAULT NULL,
    p_web_password                 IN                   VARCHAR2    DEFAULT NULL,
    p_new_password                 IN                   VARCHAR2    DEFAULT NULL,
    p_email_address                IN                   VARCHAR2    DEFAULT NULL,
    p_start_date                   IN                   VARCHAR2    DEFAULT NULL,
    p_end_date                     IN                   VARCHAR2    DEFAULT NULL,
    p_employee_id                  IN                   VARCHAR2    DEFAULT NULL,
    p_allow_access_to_schemas      IN                   VARCHAR2    DEFAULT NULL,
    p_person_type                  IN                   VARCHAR2    DEFAULT NULL,
    p_default_schema               IN                   VARCHAR2    DEFAULT NULL,
    p_group_ids                    IN                   VARCHAR2    DEFAULT NULL,
    p_developer_roles              IN                   VARCHAR2    DEFAULT NULL,
    p_description                  IN                   VARCHAR2    DEFAULT NULL,
    p_account_expiry               IN                   DATE        DEFAULT NULL,
    p_account_locked               IN                   VARCHAR2    DEFAULT 'N',
    p_failed_access_attempts       IN                   NUMBER      DEFAULT 0,
    p_change_password_on_first_use IN                   VARCHAR2    DEFAULT 'Y',
    p_first_password_use_occurred  IN                   VARCHAR2    DEFAULT 'N');

Parameters

Table 21-21 describes the parameters available in the EDIT_USER procedure.

Table 21-21 EDIT_USER Parameters

Parameter Description

p_user_id

Numeric primary key of the user account

p_user_name

Alphanumeric name used for login.

See Also: "SET_USERNAME Procedure"

p_first_name

Informational.

See Also: "SET_FIRST_NAME Procedure"

p_last_name

Informational.

See Also: "SET_LAST_NAME Procedure"

p_web_password

Clear text password. If using this procedure to update the password for the user, values for both p_web_password and p_new_password must not be null and must be identical.

p_new_password

Clear text new password. If using this procedure to update the password for the user, values for both p_web_password and p_new_password must not be null and must be identical.

p_email_address

Informational.

See Also: "SET_EMAIL Procedure"

p_start_date

Unused

p_end_date

Unused

p_employee_id

Unused

p_allow_access_to_schemas

A list of schemas assigned to the user's workspace to which the user is restricted

p_person_type

Unused

p_default_schema

A database schema assigned to the user's workspace, used by default for browsing

p_group_ids

Colon-separated list of numeric group IDs

p_developer_roles

Colon-separated list of developer privileges. The following are acceptable values for this parameter:

· null - To update the user to be an end user (a user who can only authenticate to developed applications)

· CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To update the user to have developer privilege

· ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To update the user to have full workspace administrator and developer privilege

Note: Currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field stored in the named user account record. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role.

See Also: "GET_USER_ROLES Function"

p_description

Informational

p_account_expiry

Date password was last updated.

See Also: "EXPIRE_END_USER_ACCOUNT Procedure", "EXPIRE_WORKSPACE_ACCOUNT Procedure", "UNEXPIRE_END_USER_ACCOUNT Procedure", "UNEXPIRE_WORKSPACE_ACCOUNT Procedure"

p_account_locked

'Y' or 'N' indicating if account is locked or unlocked.

See Also: "LOCK_ACCOUNT Procedure", "UNLOCK_ACCOUNT Procedure"

p_failed_access_attempts

Number of consecutive login failures that have occurred.

p_change_password_on_first_use

'Y' or 'N' to indicate whether password must be changed on first use.

See Also: "CHANGE_PASSWORD_ON_FIRST_USE Function"

p_first_password_use_occurred

'Y' or 'N' to indicate whether login has occurred since password change.

See Also: "PASSWORD_FIRST_USE_OCCURRED Function"


Example

The following example shows how to use the EDIT_USER procedure to update a user account. This example shows how you can use the EDIT_USER procedure to change the user 'FRANK' from a user with just developer privilege to a user with workspace administrator and developer privilege. Firstly, the FETCH_USER procedure is called to assign account details for the user 'FRANK' to local variables. These variables are then used in the call to EDIT_USER to preserve the details of the account, with the exception of the value for the p_developer_roles parameter, which is set to 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'.

DECLARE
    l_user_id                       NUMBER;
    l_workspace                     VARCHAR2(255);
    l_user_name                     VARCHAR2(100);
    l_first_name                    VARCHAR2(255);
    l_last_name                     VARCHAR2(255);
    l_web_password                  VARCHAR2(255);
    l_email_address                 VARCHAR2(240);
    l_start_date                    DATE;
    l_end_date                      DATE;
    l_employee_id                   NUMBER(15,0);
    l_allow_access_to_schemas       VARCHAR2(4000);
    l_person_type                   VARCHAR2(1);
    l_default_schema                VARCHAR2(30);
    l_groups                        VARCHAR2(1000);
    l_developer_role                VARCHAR2(60);
    l_description                   VARCHAR2(240);
    l_account_expiry                DATE;
    l_account_locked                VARCHAR2(1);
    l_failed_access_attempts        NUMBER;
    l_change_password_on_first_use  VARCHAR2(1);
    l_first_password_use_occurred   VARCHAR2(1);
BEGIN
    l_user_id := APEX_UTIL.GET_USER_ID('FRANK');

APEX_UTIL.FETCH_USER(
    p_user_id                       => l_user_id,
    p_workspace                     => l_workspace,
    p_user_name                     => l_user_name,
    p_first_name                    => l_first_name,
    p_last_name                     => l_last_name,
    p_web_password                  => l_web_password,
    p_email_address                 => l_email_address,
    p_start_date                    => l_start_date,
    p_end_date                      => l_end_date,
    p_employee_id                   => l_employee_id,
    p_allow_access_to_schemas       => l_allow_access_to_schemas,
    p_person_type                   => l_person_type,
    p_default_schema                => l_default_schema,
    p_groups                        => l_groups,
    p_developer_role                => l_developer_role,
    p_description                   => l_description,
    p_account_expiry                => l_account_expiry,
    p_account_locked                => l_account_locked,
    p_failed_access_attempts        => l_failed_access_attempts,
    p_change_password_on_first_use  => l_change_password_on_first_use,
    p_first_password_use_occurred   => l_first_password_use_occurred);
APEX_UTIL.EDIT_USER (
    p_user_id                       => l_user_id,
    p_user_name                     => l_user_name,
    p_first_name                    => l_first_name,
    p_last_name                     => l_last_name,
    p_web_password                  => l_web_password,
    p_new_password                  => l_web_password,
    p_email_address                 => l_email_address,
    p_start_date                    => l_start_date,
    p_end_date                      => l_end_date,
    p_employee_id                   => l_employee_id,
    p_allow_access_to_schemas       => l_allow_access_to_schemas,
    p_person_type                   => l_person_type,
    p_default_schema                => l_default_schema,
    p_group_ids                     => l_groups,
    p_developer_roles               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
    p_description                   => l_description,
    p_account_expiry                => l_account_expiry,
    p_account_locked                => l_account_locked,
    p_failed_access_attempts        => l_failed_access_attempts,
    p_change_password_on_first_use  => l_change_password_on_first_use,
    p_first_password_use_occurred   => l_first_password_use_occurred);
END;

END_USER_ACCOUNT_DAYS_LEFT Function

Returns the number of days remaining before a end user account password expires. This function may be run in a page request context by any authenticated user.

Syntax

APEX_UTIL.END_USER_ACCOUNT_DAYS_LEFT (
    p_user_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Table 21-22 describes the parameters available in the END_USER_ACCOUNT_DAYS_LEFT function.

Table 21-22 END_USER_ACCOUNT_DAYS_LEFT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the END_USER_ACCOUNT_DAYS_LEFT function. Use this function to determine the number of days remaining before an Application Express end user account in the current workspace expires.

DECLARE
    l_days_left NUMBER;
BEGIN
    FOR c1 IN (SELECT user_name from wwv_flow_users) LOOP
        l_days_left := APEX_UTIL.END_USER_ACCOUNT_DAYS_LEFT(p_user_name => c1.user_name);
        htp.p('End User Account:'||c1.user_name||' expires in '||l_days_left||' days.');
    END LOOP;
END;

EXPIRE_END_USER_ACCOUNT Procedure

Expires the login account for use as a workspace end user. Must be run by an authenticated workspace administrator in a page request context.

Syntax

APEX_UTIL.EXPIRE_END_USER_ACCOUNT (
    p_user_name IN VARCHAR2
    );

Parameters

Table 21-24 describes the parameters available in the EXPIRE_END_USER_ACCOUNT procedure.

Table 21-23 EXPIRE_END_USER_ACCOUNT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the EXPIRE_END_USER_ACCOUNT procedure. Use this procedure to expire an Oracle Application Express account (workspace administrator, developer, or end user) in the current workspace. This action specifically expires the account for its use by end users to authenticate to developed applications, but it may also expire the account for its use by developers or administrators to log in to a workspace.

Note that this procedure must be run by a user having administration privileges in the current workspace.

BEGIN
    FOR c1 IN (select user_name from wwv_flow_users) LOOP
        APEX_UTIL.EXPIRE_END_USER_ACCOUNT(p_user_name => c1.user_name);
        htp.p('End User Account:'||c1.user_name||' is now expired.');    
    END LOOP;
END;    

EXPIRE_WORKSPACE_ACCOUNT Procedure

Expires developer or workspace administrator login accounts. Must be run by an authenticated workspace administrator in a page request context.

Syntax

APEX_UTIL.EXPIRE_WORKSPACE_ACCOUNT (
    p_user_name IN VARCHAR2
    );

Parameters

Table 21-24 describes the parameters available in the EXPIRE_WORKSPACE_ACCOUNT procedure.

Table 21-24 EXPIRE_WORKSPACE_ACCOUNT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the EXPIRE_WORKSPACE_ACCOUNT procedure. Use this procedure to expire an Application Express account (workspace administrator, developer, or end user) in the current workspace. This action specifically expires the account for its use by developers or administrators to log in to a workspace, but it may also expire the account for its use by end users to authenticate to developed applications.

BEGIN
    FOR c1 IN (SELECT user_name FROM wwv_flow_users) LOOP
        APEX_UTIL.EXPIRE_WORKSPACE_ACCOUNT(p_user_name => c1.user_name);
        htp.p('Workspace Account:'||c1.user_name||' is now expired.');  
    END LOOP;
END;

EXPORT_USERS Procedure

When called from a page, this procedure produces an export file of the current workspace definition, workspace users, and workspace groups. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

APEX_UTIL.EXPORT_USERS(
    p_export_format IN VARCHAR2 DEFAULT 'UNIX');

Parameters

Table 21-25 describes the parameters available in the EXPORT_USERS procedure.

Table 21-25 EXPORT_USERS Parameters

Parameter Description

p_export_format

Indicates how rows in the export file are formatted. Specify 'UNIX' to have the resulting file contain rows delimited by line feeds. Specify 'DOS' to have the resulting file contain rows delimited by carriage returns and line feeds


Example

The following example shows how to use the EXPORT_USERS procedure. Call this procedure from a page to produce an export file containing the current workspace definition, list of workspace users and list of workspace groups. The file is formatted with rows delimited by line feeds.

BEGIN
    APEX_UTIL.EXPORT_USERS;
END;

FETCH_APP_ITEM Function

This function fetches session state for the current or specified application in the current or specified session.

Syntax

APEX_UTIL.FETCH_APP_ITEM(
    p_item    IN VARCHAR2,
    p_app     IN NUMBER DEFAULT NULL,
    p_session IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 21-26 describes the parameters available in the FETCH_APP_ITEM function.

Table 21-26 FETCH_APP_ITEM Parameters

Parameter Description

p_item

The name of an application-level item (not a page item) whose current value is to be fetched

p_app

The ID of the application that owns the item (leave null for the current application)

p_session

The session ID from which to obtain the value (leave null for the current session)


Example

The following example shows how to use the FETCH_APP_ITEM function to obtain the value of the application item 'F300_NAME' in application 300. As no value is passed for p_session, this defaults to the current session state value.

DECLARE
    VAL VARCHAR2(30);
BEGIN
    VAL := APEX_UTIL.FETCH_APP_ITEM(
        p_item => 'F300_NAME',
        p_app => 300);
END;

FETCH_USER Procedure Signature 1

This procedure fetches a user account record. To execute this procedure, the current user must have administrative privileges in the workspace. Three overloaded versions of this procedure exist, each with a distinct set of allowed parameters or signatures.

Syntax for Signature 1

APEX_UTIL.FETCH_USER (
    p_user_id                       IN                    NUMBER,
    p_workspace                     OUT                   VARCHAR2,
    p_user_name                     OUT                   VARCHAR2,
    p_first_name                    OUT                   VARCHAR2,
    p_last_name                     OUT                   VARCHAR2,
    p_web_password                  OUT                   VARCHAR2,
    p_email_address                 OUT                   VARCHAR2,
    p_start_date                    OUT                   VARCHAR2,
    p_end_date                      OUT                   VARCHAR2,
    p_employee_id                   OUT                   VARCHAR2,
    p_allow_access_to_schemas       OUT                   VARCHAR2,
    p_person_type                   OUT                   VARCHAR2,
    p_default_schema                OUT                   VARCHAR2,
    p_groups                        OUT                   VARCHAR2,
    p_developer_role                OUT                   VARCHAR2,
    p_description                   OUT                   VARCHAR2 );

Parameters for Signature 1

Table 21-27 describes the parameters available in the FETCH_USER procedure for signature 1.

Table 21-27 Fetch_User Parameters Signature 1

Parameter Description

p_user_id

Numeric primary key of the user account

p_workspace

The name of the workspace

p_user_name

Alphanumeric name used for login.

See Also: "GET_USERNAME Function"

p_first_name

Informational.

See Also: "GET_FIRST_NAME Function"

p_last_name

Informational.

See Also: "GET_LAST_NAME Function"

p_web_password

Obfuscated account password

p_email_address

Email address.

See Also: "GET_EMAIL Function"

p_start_date

Unused

p_end_date

Unused

p_employee_id

Unused

p_allow_access_to_schemas

A list of schemas assigned to the user's workspace to which user is restricted

p_person_type

Unused

p_default_schema

A database schema assigned to the user's workspace, used by default for browsing.

See Also: "GET_DEFAULT_SCHEMA Function"

p_groups

List of groups of which user is a member.

See Also: "GET_GROUPS_USER_BELONGS_TO Function" and "CURRENT_USER_IN_GROUP Function"

p_developer_role

Colon-separated list of developer roles. The following are acceptable values for this parameter:

null - Indicates an end user (a user who can only authenticate to developed applications).

CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - Indicates a user with developer privilege.

ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - Indicates a user with full workspace administrator and developer privilege.

Note: Currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field stored in the named user account record. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role.

See Also: "GET_USER_ROLES Function"

p_description

Informational


Example for Signature 1

The following example shows how to use the FETCH_USER procedure with Signature 1. This procedure is passed the ID of the currently authenticated user for the only IN parameter p_user_id. The code then stores all the other OUT parameter values in local variables.

DECLARE
    l_workspace                 VARCHAR2(255);
    l_user_name                 VARCHAR2(100);
    l_first_name                VARCHAR2(255);
    l_last_name                 VARCHAR2(255);
    l_web_password              VARCHAR2(255);
    l_email_address             VARCHAR2(240);
    l_start_date                DATE;
    l_end_date                  DATE;
    l_employee_id               NUMBER(15,0);
    l_allow_access_to_schemas   VARCHAR2(4000);
    l_person_type               VARCHAR2(1);
    l_default_schema            VARCHAR2(30);
    l_groups                    VARCHAR2(1000);
    l_developer_role            VARCHAR2(60);
    l_description               VARCHAR2(240);
BEGIN
    APEX_UTIL.FETCH_USER(
        p_user_id                   => APEX_UTIL.GET_CURRENT_USER_ID,
        p_workspace                 => l_workspace,
        p_user_name                 => l_user_name,
        p_first_name                => l_first_name,
        p_last_name                 => l_last_name,
        p_web_password              => l_web_password,
        p_email_address             => l_email_address,
        p_start_date                => l_start_date,
        p_end_date                  => l_end_date,
        p_employee_id               => l_employee_id,
        p_allow_access_to_schemas   => l_allow_access_to_schemas,
        p_person_type               => l_person_type,
        p_default_schema            => l_default_schema,
        p_groups                    => l_groups,
        p_developer_role            => l_developer_role,
        p_description               => l_description);
END;

FETCH_USER Procedure Signature 2

This procedure fetches a user account record. To execute this procedure, the current user must have administrative privileges in the workspace. Three overloaded versions of this procedure exist, each with a distinct set of allowed parameters or signatures.

Syntax for Signature 2

APEX_UTIL.FETCH_USER (
    p_user_id                     IN                 NUMBER,
    p_user_name                   OUT                VARCHAR2,
    p_first_name                  OUT                VARCHAR2,
    p_last_name                   OUT                VARCHAR2,
    p_email_address               OUT                VARCHAR2,
    p_groups                      OUT                VARCHAR2,
    p_developer_role              OUT                VARCHAR2,
    p_description                 OUT                VARCHAR2 );

Parameters for Signature 2

Table 21-28 describes the parameters available in the FETCH_USER procedure for signature 2.

Table 21-28 Fetch_User Parameters Signature 2

Parameter Description

p_user_id

Numeric primary key of the user account

p_user_name

Alphanumeric name used for login.

See Also: "GET_USERNAME Function"

p_first_name

Informational.

See Also: "GET_FIRST_NAME Function"

p_last_name

Informational.

See Also: "GET_LAST_NAME Function"

p_email_address

Email address.

See Also: "GET_EMAIL Function"

p_groups

List of groups of which user is a member.

See Also: "GET_GROUPS_USER_BELONGS_TO Function" and "CURRENT_USER_IN_GROUP Function"

p_developer_role

Colon-separated list of developer roles. The following are acceptable values for this parameter:

null - Indicates an end user (a user who can only authenticate to developed applications).

CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - Indicates a user with developer privilege.

ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - Indicates a user with full workspace administrator and developer privilege.

Note: Currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field stored in the named user account record. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role.

See Also: "GET_USER_ROLES Function"

p_description

Informational


Example for Signature 2

The following example shows how to use the FETCH_USER procedure with Signature 2. This procedure is passed the ID of the currently authenticated user for the only IN parameter p_user_id. The code then stores all the other OUT parameter values in local variables.

DECLARE
    l_user_name         VARCHAR2(100);
    l_first_name        VARCHAR2(255);
    l_last_name         VARCHAR2(255);
    l_email_address     VARCHAR2(240);
    l_groups            VARCHAR2(1000);
    l_developer_role    VARCHAR2(60);
    l_description       VARCHAR2(240);
BEGIN
    APEX_UTIL.FETCH_USER(
        p_user_id           => APEX_UTIL.GET_CURRENT_USER_ID,
        p_user_name         => l_user_name,
        p_first_name        => l_first_name,
        p_last_name         => l_last_name,
        p_email_address     => l_email_address,
        p_groups            => l_groups,
        p_developer_role    => l_developer_role,
        p_description       => l_description);
END;

FETCH_USER Procedure Signature 3

This procedure fetches a user account record. To execute this procedure, the current user must have administrative privileges in the workspace. Three overloaded versions of this procedure exist, each with a distinct set of allowed parameters or signatures.

Syntax for Signature 3

APEX_UTIL.FETCH_USER (
    p_user_id                           IN                   NUMBER,
    p_workspace                         OUT                  VARCHAR2,
    p_user_name                         OUT                  VARCHAR2,
    p_first_name                        OUT                  VARCHAR2,
    p_last_name                         OUT                  VARCHAR2,
    p_web_password                      OUT                  VARCHAR2,
    p_email_address                     OUT                  VARCHAR2,
    p_start_date                        OUT                  VARCHAR2,
    p_end_date                          OUT                  VARCHAR2,
    p_employee_id                       OUT                  VARCHAR2,
    p_allow_access_to_schemas           OUT                  VARCHAR2,
    p_person_type                       OUT                  VARCHAR2,
    p_default_schema                    OUT                  VARCHAR2,
    p_groups                            OUT                  VARCHAR2,
    p_developer_role                    OUT                  VARCHAR2,
    p_description                       OUT                  VARCHAR2,
    p_account_expiry                    OUT                  DATE,
    p_account_locked                    OUT                  VARCHAR2,
    p_failed_access_attempts            OUT                  NUMBER,
    p_change_password_on_first_use      OUT                  VARCHAR2,
    p_first_password_use_occurred       OUT                  VARCHAR2 );

Parameters for Signature 3

Table 21-29 describes the parameters available in the FETCH_USER procedure.

Table 21-29 Fetch_User Parameters Signature 3

Parameter Description

p_user_id

Numeric primary key of the user account

p_workspace

The name of the workspace

p_user_name

Alphanumeric name used for login.

See Also: "GET_USERNAME Function"

p_first_name

Informational.

See Also: "GET_FIRST_NAME Function"

p_last_name

Informational.

See Also: "GET_LAST_NAME Function"

p_web_password

Obfuscated account password

p_email_address

Email address.

See Also: "GET_EMAIL Function"

p_start_date

Unused

p_end_date

Unused

p_employee_id

Unused

p_allow_access_to_schemas

A list of schemas assigned to the user's workspace to which user is restricted

p_person_type

Unused

p_default_schema

A database schema assigned to the user's workspace, used by default for browsing.

See Also: "GET_DEFAULT_SCHEMA Function"

p_groups

List of groups of which user is a member.

See Also: "GET_GROUPS_USER_BELONGS_TO Function" and "CURRENT_USER_IN_GROUP Function"

p_developer_role

Colon-separated list of developer roles. The following are acceptable values for this parameter:

null - Indicates an end user (a user who can only authenticate to developed applications).

CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - Indicates a user with developer privilege.

ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - Indicates a user with full workspace administrator and developer privilege.

Note: Currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field stored in the named user account record. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role.

See Also: "GET_USER_ROLES Function"

p_description

Informational

p_account_expiry

Date account password was last reset.

See Also: "END_USER_ACCOUNT_DAYS_LEFT Function" and "WORKSPACE_ACCOUNT_DAYS_LEFT Function"

p_account_locked

Locked/Unlocked indicator Y or N.

See Also: "GET_ACCOUNT_LOCKED_STATUS Function"

p_failed_access_attempts

Counter for consecutive login failures

p_change_password_on_first_use

Setting to force password change on first use Y or N

p_first_password_use_occurred

Indicates whether login with password occurred Y or N


Example for Signature 3

The following example shows how to use the FETCH_USER procedure with Signature 3. This procedure is passed the ID of the currently authenticated user for the only IN parameter p_user_id. The code then stores all the other OUT parameter values in local variables.

DECLARE
    l_workspace                     VARCHAR2(255);
    l_user_name                     VARCHAR2(100);
    l_first_name                    VARCHAR2(255);
    l_last_name                     VARCHAR2(255);
    l_web_password                  VARCHAR2(255);
    l_email_address                 VARCHAR2(240);
    l_start_date                    DATE;
    l_end_date                      DATE;
    l_employee_id                   NUMBER(15,0);
    l_allow_access_to_schemas       VARCHAR2(4000);
    l_person_type                   VARCHAR2(1);
    l_default_schema                VARCHAR2(30);
    l_groups                        VARCHAR2(1000);
    l_developer_role                VARCHAR2(60);
    l_description                   VARCHAR2(240);
    l_account_expiry                DATE;
    l_account_locked                VARCHAR2(1);
    l_failed_access_attempts        NUMBER;
    l_change_password_on_first_use  VARCHAR2(1);
    l_first_password_use_occurred   VARCHAR2(1);
BEGIN
    APEX_UTIL.FETCH_USER(
        p_user_id                       => APEX_UTIL.GET_CURRENT_USER_ID,
        p_workspace                     => l_workspace,
        p_user_name                     => l_user_name,
        p_first_name                    => l_first_name,
        p_last_name                     => l_last_name,
        p_web_password                  => l_web_password,
        p_email_address                 => l_email_address,
        p_start_date                    => l_start_date,
        p_end_date                      => l_end_date,
        p_employee_id                   => l_employee_id,
        p_allow_access_to_schemas       => l_allow_access_to_schemas,
        p_person_type                   => l_person_type,
        p_default_schema                => l_default_schema,
        p_groups                        => l_groups,
        p_developer_role                => l_developer_role,
        p_description                   => l_description,
        p_account_expiry                => l_account_expiry,
        p_account_locked                => l_account_locked,
        p_failed_access_attempts        => l_failed_access_attempts,
        p_change_password_on_first_use  => l_change_password_on_first_use,
        p_first_password_use_occurred   => l_first_password_use_occurred);
END;

FIND_SECURITY_GROUP_ID Function

This function returns the numeric security group ID of the named workspace.

Syntax

APEX_UTIL.FIND_SECURITY_GROUP_ID(
    p_workspace    IN VARCHAR2)
RETURN NUMBER;

Parameters

Table 21-30 describes the parameters available in the FIND_SECURITY_GROUP_ID function.

Table 21-30 FIND_SECURITY_GROUP_ID Parameters

Parameter Description

p_workspace

The name of the workspace


Example

The following example demonstrates how to use the FIND_SECURITY_GROUP_ID function to return the security group ID for the workspace called 'DEMOS'.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.FIND_SECURITY_GROUP_ID (p_workspace=>'DEMOS');
END;

FIND_WORKSPACE Function

This function returns the workspace name associated with a security group ID.

Syntax

APEX_UTIL.FIND_WORKSPACE(
    p_security_group_id    IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-31 describes the parameters available in the FIND_WORKSPACE function.

Table 21-31 FIND_WORKSPACE Parameters

Parameter Description

p_security_group_id

The security group ID of a workspace


Example

The following example demonstrates how to use the FIND_WORKSPACE function to return the workspace name for the workspace with a security group ID of 20.

DECLARE
    VAL VARCHAR2(255);
BEGIN
    VAL := APEX_UTIL.FIND_WORKSPACE (p_security_group_id =>'20');
END;

GET_ACCOUNT_LOCKED_STATUS Function

Returns TRUE if the account is locked and FALSE if the account is unlocked. Must be run by an authenticated workspace administrator in a page request context.

Syntax

APEX_UTIL.GET_ACCOUNT_LOCKED_STATUS (
     p_user_name IN VARCHAR2
     ) RETURN BOOLEAN;

Parameters

Table 21-32 describes the parameters available in the GET_ACCOUNT_LOCKED_STATUS function.

Table 21-32 GET_ACCOUNT_LOCKED_STATUS Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the GET_ACCOUNT_LOCKED_STATUS function. Use this function to check if an Application Express user account (workspace administrator, developer, or end user) in the current workspace is locked.

BEGIN
    FOR c1 IN (SELECT user_name FROM wwv_flow_users) loop
        IF APEX_UTIL.GET_ACCOUNT_LOCKED_STATUS(p_user_name => c1.user_name) THEN
            HTP.P('User Account:'||c1.user_name||' is locked.'); 
        END IF;   
    END LOOP;
END;

GET_ATTRIBUTE Function

This function returns the value of one of the attribute values (1 through 10) of a named user in the Application Express accounts table. Please note these are only accessible by using the APIs.

Syntax

APEX_UTIL.GET_ATTRIBUTE(
    p_username                IN VARCHAR2,
    p_attribute_number        IN NUMBER)
RETURN VARCHAR2;

Parameters

Table 21-33 describes the parameters available in the GET_ATTRIBUTE function.

Table 21-33 GET_ATTRIBUTE Parameters

Parameter Description

p_username

User name in the account.

p_attribute_number

Number of attributes in the user record (1 through 10)


Example

The following example shows how to use the GET_ATTTIBUTE function to return the value for the 1st attribute for the user 'FRANK'.

DECLARE
    VAL VARCHAR2(4000);
BEGIN
    VAL := APEX_UTIL.GET_ATTRIBUTE (
        p_username => 'FRANK',
        p_attribute_number => 1);
END;

GET_AUTHENTICATION_RESULT Function

Use this function to retrieve the authentication result of the current session. Any authenticated user can call this function in a page request context.

Syntax

APEX_UTIL.GET_AUTHENTICATION_RESULT
RETURN NUMBER;

Parameters

None.

Example

The following example demonstrates how to use the post-authentication process of an application's authentication scheme to retrieve the authentication result code set during authentication.

APEX_UTIL.SET_SESSION_STATE('MY_AUTH_STATUS',
    'Authentication result:'||APEX_UTIL.GET_AUTHENTICATION_RESULT);

GET_BLOB_FILE_SRC Function

As an alternative to using the built-in methods of providing a download link, you can use the APEX_UTIL.GET_BLOB_FILE_SRC function. One advantage of this approach, is the ability to more specifically format the display of the image (with height and width tags). Please note that this approach is only valid if called from a valid Oracle Application Express session. Also, this method requires that the parameters that describe the BLOB to be listed as the format of a valid item within the application. That item is then referenced by the function.

Syntax

APEX_UTIL.GET_BLOB_FILE_SRC (
    p_item_name           IN VARCHAR2 DEFAULT NULL,
    p_v1                  IN VARCHAR2 DEFAULT NULL,
    p_v2                  IN VARCHAR2 DEFAULT NULL,
    p_content_disposition IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 21-34 describes the parameters available in GET_BLOB_FILE_SRC function.

Table 21-34 GET_BLOB_FILE_SRC Parameters

Parameter Description

p_item_name

Name of valid application page ITEM that with type FILE that contains the source type of DB column.

p_v1

Value of primary key column 1.

p_v2

Value of primary key column 2.

p_content_disposition

Specify inline or attachment, all other values ignored


Example

As a PLSQL Function Body:

RETURN '<img src="'||APEX_UTIL.GET_BLOB_FILE_SRC('P2_ATTACHMENT',:P2_EMPNO)||'" />';

As a Region Source of type SQL:

SELECT ID, NAME,CASE WHEN NVL(dbms_lob.getlength(document),0) = 0
    THEN NULL 
    ELSE CASE WHEN attach_mimetype like 'image%'
    THEN '<img src="'||apex_util.get_blob_file_src('P4_DOCUMENT',id)||'" />' 
    ELSE 
    '<a href="'||apex_util.get_blob_file_src('P4_DOCUMENT',id)||'">Download</a>'
    end 
    END new_img
    FROM TEST_WITH_BLOB

The previous example illustrates how to display the BLOB within the report, if it can be displayed, and provide a download link, if it cannot be displayed.

GET_BUILD_OPTION_STATUS Function Signature 1

Use this function to get the build option status of a specified application by providing the ID of the application build option.

Syntax

APEX_UTIL.GET_BUILD_OPTION_STATUS(
    p_application_id  IN NUMBER
    p_id              IN NUMBER;

Parameters

Table 21-35 describes the parameters available in the GET_BUILD_OPTION_STATUS function signature 1.

Table 21-35 GET_BUILD_OPTION_STATUS Function Signature 1 Paremeters

Parameters Description

p_application_id

The ID of the application that owns the build option under shared components.

p_id

The ID of the build option in the application.


Example

The following code retrieves the current status of the specified build option that is identified by ID.

DECLARE
    l_status VARCHAR2(255);
BEGIN
    l_status := APEX_UTIL.GET_BUILD_OPTION_STATUS(
                    P_APPLICATION_ID => 101,
                    P_ID => 245935500311121039);
END;
/

GET_BUILD_OPTION_STATUS Function Signature 2

Use this function to get the build option status of a specified application by providing the name of the application build option.

Syntax

APEX_UTIL.GET_BUILD_OPTION_STATUS(
    p_application_id     IN NUMBER
    p_build_option_name IN VARCHAR2);

Parameters

Table 21-36 describes the parameters available in the GET_BUILD_OPTION_STATUS function signature 2.

Table 21-36 GET_BUILD_OPTION_STATUS Function Signature 2 Parameters

Parameters Description

p_application_id

The ID of the application that owns the build option under shared components.

p_build_option_name

The name of the build option in the application.


Example

The following code retrieves the current status of the specified build option that is identified by name.

DECLARE
    l_status VARCHAR2(255);
BEGIN
    l_status := APEX_UTIL.GET_BUILD_OPTION_STATUS(
                    P_APPLICATION_ID => 101,
                    P_BUILD_OPTION_NAME => 'EXCLUDE_FROM_PRODUCTION');
END;
/

GET_CURRENT_USER_ID Function

This function returns the numeric user ID of the current user.

Syntax

APEX_UTIL.GET_CURRENT_USER_ID
RETURN NUMBER;

Parameters

None.

Example

This following example shows how to use the GET_CURRENT_USER_ID function. It returns the numeric user ID of the current user into a local variable.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.GET_CURRENT_USER_ID;
END;

GET_DEFAULT_SCHEMA Function

This function returns the default schema name associated with the current user.

Syntax

APEX_UTIL.GET_DEFAULT_SCHEMA
RETURN VARCHAR2;

Parameters

None.

Example

The following example shows how to use the GET_DEFAULT_SCHEMA function. It returns the default schema name associated with the current user into a local variable.

DECLARE
    VAL VARCHAR2(30);
BEGIN
    VAL := APEX_UTIL.GET_DEFAULT_SCHEMA;
END;

GET_EDITION Function

This function returns the edition for the current page view.

Syntax

APEX_UTIL.GET_EDITION
RETURN VARCHAR2;

Parameters

None.

Example

The following example shows how to use the GET_EDITION function. It returns the edition name for the current page view into a local variable.

DECLARE
    VAL VARCHAR2(30);
BEGIN
    VAL := APEX_UTIL.GET_EDITION;
END;

GET_EMAIL Function

This function returns the email address associated with the named user.

Syntax

APEX_UTIL.GET_EMAIL(
   p_username IN VARCHAR2);
RETURN VARCHAR2;

Parameters

Table 21-37 describes the parameters available in GET_EMAIL function.

Table 21-37 GET_EMAIL Parameters

Parameter Description

p_username

The user name in the account


Example

The following example shows how to use the GET_EMAIL function to return the email address of the user 'FRANK'.

DECLARE
    VAL VARCHAR2(240);
BEGIN
    VAL := APEX_UTIL.GET_EMAIL(p_username => 'FRANK');
END;

GET_FEEDBACK_FOLLOW_UP Function

Use this function to retrieve any remaining follow up associated with a specific feedback.

Syntax

APEX_UTIL.GET_FEEDBACK_FOLLOW_UP (
    p_feedback_id    IN NUMBER,
    p_row            IN NUMBER DEFAULT 1,
    p_template       IN VARCHAR2 DEFAULT '<br />#CREATED_ON# (#CREATED_BY#) #FOLLOW_UP#')
RETURN VARCHAR2;

Parameters

Table 21-38 describes the parameters available in GET_FEEDBACK_FOLLOW_UP function.

Table 21-38 GET_FEEDBACK_FOLLOW_UP Parameters

Parameter Description

p_feedback_id

The unique identifier of the feedback item.

p_row

Identifies which follow-up to retrieve and is ordered by created_on_desc.

p_template

The template to use to return the follow up. Given the <br/> in the default template, the function can be used in a loop to return all the follow up to a feedback.


Example

The following example displays all the remaining follow-up for feedback with the ID of 123.

declare
   l_feedback_count  number;
begin
   select count(*)
     into l_feedback_count
     from apex_team_feedback_followup
    where feedback_id = 123;
 
   for i in 1..l_feedback_count loop
      htp.p(apex_util.get_feedback_follow_up (
               p_feedback_id => 123,
               p_row         => i,
               p_template    => '<br />#FOLLOW_UP# was created on #CREATED_ON# by #CREATED_BY#') );
   end loop;
end;
/

GET_FILE Procedure

This procedure downloads files from the Oracle Application Express file repository. Please note if you are invoking this procedure during page processing, you must ensure that no page branch is invoked under the same condition, as it interferes with the file retrieval. This means that branches with any of the following conditions should not be set to fire:

  • Branches with a 'When Button Pressed' attribute equal to the button that invokes the procedure.

  • Branches with conditional logic defined that would succeed during page processing when the procedure is being invoked.

  • As unconditional.

Syntax

APEX_UTIL.GET_FILE (
    p_file_id    IN   VARCHAR2,
    p_inline     IN   VARCHAR2 DEFAULT 'NO');

Parameters

Table 21-39 describes the parameters available in GET_FILE procedure.

Table 21-39 GET_FILE Parameters

Parameter Description

p_file_id

ID in APEX_APPLICATION_FILES of the file to be downloaded. APEX_APPLICATION_FILES is a view on all files uploaded to your workspace. The following example demonstrates how to use APEX_APPLICATION_FILES:

DECLARE
    l_file_id NUMBER;
BEGIN
    SELECT id
        INTO l_file_id
        FROM APEX_APPLICATION_FILES
        WHERE filename = 'myxml';
        --
        APEX_UTIL.GET_FILE(
            p_file_id   => l_file_id, 
            p_inline    => 'YES');  
END;

p_inline

Valid values include YES and NO. YES to display inline in a browser. NO to download as attachment


Example

The following example shows how to use the GET_FILE function to return the file identified by the ID 8675309. This is displayed inline in the browser.

BEGIN
    APEX_UTIL.GET_FILE(
        p_file_id   => '8675309',
        p_inline    => 'YES');
END;

GET_FILE_ID Function

This function obtains the primary key of a file in the Oracle Application Express file repository.

Syntax

APEX_UTIL.GET_FILE_ID (
    p_name    IN   VARCHAR2)
RETURN NUMBER;

Parameters

Table 21-40 describes the parameters available in GET_FILE_ID function.

Table 21-40 GET_FILE_ID Parameters

Parameter Description

p_name

The NAME in APEX_APPLICATION_FILES of the file to be downloaded. APEX_APPLICATION_FILES is a view on all files uploaded to your workspace.


Example

The following example shows how to use the GET_FILE_ID function to retrieve the database ID of the file with a filename of 'F125.sql'.

DECLARE
    l_name VARCHAR2(255);
    l_file_id NUMBER;
BEGIN
    SELECT name
        INTO l_name
        FROM APEX_APPLICATION_FILES
        WHERE filename = 'F125.sql';
        --
        l_file_id := APEX_UTIL.GET_FILE_ID(p_name => l_name);
END;

GET_FIRST_NAME Function

This function returns the FIRST_NAME field stored in the named user account record.

Syntax

APEX_UTIL.GET_FIRST_NAME
    p_username IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-41 describes the parameters available in GET_FIRST_NAME function.

Table 21-41 GET_FIRST_NAME Parameters

Parameter Description

p_username

Identifies the user name in the account


Example

The following example shows how to use the GET_FIRST_NAME function to return the FIRST_NAME of the user 'FRANK'.

DECLARE
    VAL VARCHAR2(255);
BEGIN
    VAL := APEX_UTIL.GET_FIRST_NAME(p_username => 'FRANK');
END;

GET_GROUPS_USER_BELONGS_TO Function

This function returns a comma then a space separated list of group names to which the named user is a member.

Syntax

APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(
   p_username IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-42 describes the parameters available in GET_GROUPS_USER_BELONGS_TO function.

Table 21-42 GET_GROUPS_USER_BELONGS_TO Parameters

Parameter Description

p_username

Identifies the user name in the account


Example

The following example shows how to use the GET_GROUPS_USER_BELONGS_TO to return the list of groups to which the user 'FRANK' is a member.

DECLARE
    VAL VARCHAR2(32765);
BEGIN
    VAL := APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'FRANK');
END;

GET_GROUP_ID Function

This function returns the numeric ID of a named group in the workspace.

Syntax

APEX_UTIL.GET_GROUP_ID(
    p_group_name IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-43 describes the parameters available in GET_GROUP_ID function.

Table 21-43 GET_GROUP_ID Parameters

Parameter Description

p_group_name

Identifies the user name in the account


Example

The following example shows how to use the GET_GROUP_ID function to return the ID for the group named 'Managers'.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.GET_GROUP_ID(p_group_name => 'Managers');
END;

GET_GROUP_NAME Function

This function returns the name of a group identified by a numeric ID.

Syntax

APEX_UTIL.GET_GROUP_NAME(
    p_group_id IN NUMBER)
RETURN VARCHAR2;

Parameters

Table 21-44 describes the parameters available in GET_GROUP_NAME function.

Table 21-44 GET_GROUP_NAME Parameters

Parameter Description

p_group_id

Identifies a numeric ID of a group in the workspace


Example

The following example shows how to use the GET_GROUP_NAME function to return the name of the group with the ID 8922003.

DECLARE
    VAL VARCHAR2(255);
BEGIN
    VAL := APEX_UTIL.GET_GROUP_NAME(p_group_id => 8922003);
END;

GET_HIGH_CONTRAST_MODE_TOGGLE Function

This function returns a link to the current page that enables you to turn on or off, toggle, the mode. For example, if you are in standard mode, this function displays a link that when clicked switches high contrast mode on.

Syntax

APEX_UTIL.GET_HIGH_CONTRAST_MODE_TOGGLE (
    p_on_message  IN VARCHAR2 DEFAULT NULL,
    p_off_message IN VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

Parameters

Table 21-45 describes the parameters available in GET_HIGH_CONTRAST_MODE_TOGGLE function.

Table 21-45 GET_HIGH_CONTRAST_MODE_TOGGLE Prameters

Parameter Description

p_on_message

Optional text used for the link to switch to high contrast mode, when you are in standard mode. If this parameter is not passed, the default 'Set High Contrast Mode On' text is returned in the link.

p_off_message

Optional text used for the link to switch to standard mode, when you are in high contrast mode. If this parameter is not passed, the default 'Set High Contrast Mode Off' text is returned in the link.


Example

When running in standard mode, this function returns a link with the text 'Set High Contrast Mode On'. When the link is clicked the current page is refreshed and high contrast mode is switched on. When running in high contrast mode, a link 'Set High Contrast Mode Off' is returned. When the link is clicked the current page is refreshed and switched back to standard mode.

BEGIN
    htp.p(apex_util.get_high_contrast_mode_toggle);
END;

Note:

There are also 2 translatable system messages that can be overridden at application level to change the default link text that is returned for this toggle. They include:
  • APEX.SET_HIGH_CONTRAST_MODE_OFF - Default text = Set High Contrast Mode Off

  • APEX.SET_HIGH_CONTRAST_MODE_ON - Default text = Set High Contrast Mode On

GET_LAST_NAME Function

This function returns the LAST_NAME field stored in the named user account record.

Syntax

APEX_UTIL.GET_LAST_NAME(
    p_username IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-46 describes the parameters available in GET_LAST_NAME function.

Table 21-46 GET_LAST_NAME Parameters

Parameter Description

p_username

The user name in the user account record


Example

The following example shows how to use the function to return the LAST_NAME for the user 'FRANK'.

DECLARE
    VAL VARCHAR2(255);
BEGIN
    VAL := APEX_UTIL.GET_LAST_NAME(p_username => 'FRANK');
END;

GET_NUMERIC_SESSION_STATE Function

This function returns a numeric value for a numeric item. You can use this function in Oracle Application Express applications wherever you can use PL/SQL or SQL. You can also use the shorthand, function NV, in place of APEX_UTIL.GET_NUMERIC_SESSION_STATE.

Syntax

APEX_UTIL.GET_NUMERIC_SESSION_STATE (
    p_item     IN VARCHAR2) 
RETURN NUMBER;

Parameters

Table 21-47 describes the parameters available in GET_NUMERIC_SESSION_STATE function.

Table 21-47 GET_NUMERIC_SESSION_STATE Parameters

Parameter Description

p_item

The case insensitive name of the item for which you want to have the session state fetched


Example

The following example shows how to use the function to return the numeric value stored in session state for the item 'my_item'.

DECLARE
    l_item_value    NUMBER;
BEGIN
    l_item_value := APEX_UTIL.GET_NUMERIC_SESSION_STATE('my_item');
END;

GET_PREFERENCE Function

This function retrieves the value of a previously saved preference for a given user.

Syntax

APEX_UTIL.GET_PREFERENCE (
    p_preference  IN    VARCHAR2 DEFAULT NULL,
    p_user        IN    VARCHAR2 DEFAULT V('USER')) 
RETURN VARCHAR2;

Parameters

Table 21-48 describes the parameters available in the GET_PREFERENCE function.

Table 21-48 GET_PREFERENCE Parameters

Parameter Description

p_preference

Name of the preference to retrieve the value

p_value

Value of the preference

p_user

User for whom the preference is being retrieved


Example

The following example shows how to use the GET_PREFERENCE function to return the value for the currently authenticated user's preference named default_view.

DECLARE
    l_default_view    VARCHAR2(255);
BEGIN
    l_default_view := APEX_UTIL.GET_PREFERENCE(      
        p_preference => 'default_view',
        p_user       => :APP_USER);
END;

GET_PRINT_DOCUMENT Function Signature 1

This function returns a document as BLOB using XML based report data and RTF or XSL-FO based report layout.

Syntax

APEX_UTIL.GET_PRINT_DOCUMENT (
    p_report_data         IN BLOB,
    p_report_layout       IN CLOB,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default NULL)
RETURN BLOB;

Parameters

Table 21-49 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 21-49 GET_PRINT_DOCUMENT Signature 1 Parameters

Parameter Description

p_report_data

XML based report data

p_report_layout

Report layout in XSL-FO or RTF format

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


For a GET_PRINT_DOCUMENT example see "GET_PRINT_DOCUMENT Function Signature 4".

GET_PRINT_DOCUMENT Function Signature 2

This function returns a document as BLOB using pre-defined report query and pre-defined report layout.

Syntax

APEX_UTIL.GET_PRINT_DOCUMENT (
    p_application_id      IN NUMBER,
    p_report_query_name   IN VARCHAR2,
    p_report_layout_name  IN VARCHAR2 default null,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default null)
RETURN BLOB;

Parameters

Table 21-50 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 21-50 GET_PRINT_DOCUMENT Signature 2 Parameters

Parameter Description

p_application_id

Defines the application ID of the report query

p_report_query_name

Name of the report query (stored under application's shared components)

p_report_layout_name

Name of the report layout (stored under application's Shared Components)

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


For a GET_PRINT_DOCUMENT example see "GET_PRINT_DOCUMENT Function Signature 4".

GET_PRINT_DOCUMENT Function Signature 3

This function returns a document as BLOB using a pre-defined report query and RTF or XSL-FO based report layout.

Syntax

APEX_UTIL.GET_PRINT_DOCUMENT (
    p_application_id      IN NUMBER,
    p_report_query_name   IN VARCHAR2,
    p_report_layout       IN CLOB,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default null)
RETURN BLOB;

Parameters

Table 21-51 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 21-51 GET_PRINT_DOCUMENT Signature 3 Parameters

Parameter Description

p_application_id

Defines the application ID of the report query

p_report_query_name

Name of the report query (stored under application's shared components)

p_report_layout

Defines the report layout in XSL-FO or RTF format

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences.


For a GET_PRINT_DOCUMENT example see "GET_PRINT_DOCUMENT Function Signature 4".

GET_PRINT_DOCUMENT Function Signature 4

This function returns a document as BLOB using XML based report data and RTF or XSL-FO based report layout.

Syntax

APEX_UTIL.GET_PRINT_DOCUMENT (
    p_report_data         IN CLOB,
    p_report_layout       IN CLOB,
    p_report_layout_type  IN VARCHAR2 default 'xsl-fo',
    p_document_format     IN VARCHAR2 default 'pdf',
    p_print_server        IN VARCHAR2 default NULL)
RETURN BLOB;

Parameters

Table 21-52 describes the parameters available in the GET_PRINT_DOCUMENT function. for Signature 4

Table 21-52 GET_PRINT_DOCUMENT Signature 4 Parameters

Parameter Description

p_report_data

XML based report data, must be encoded in UTF-8

p_report_layout

Report layout in XSL-FO or RTF format

p_report_layout_type

Defines the report layout type, that is "xsl-fo" or "rtf"

p_document_format

Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml"

p_print_server

URL of the print server. If not specified, the print server is derived from preferences


Example for Signature 4

The following example shows how to use the GET_PRINT_DOCUMENT using Signature 4 (Document returns as a BLOB using XML based report data and RTF or XSL-FO based report layout). In this example, GET_PRINT_DOCUMENT is used with APEX_MAIL.SEND and APEX_MAIL.ADD_ATTACHMENT to send an email with an attachment of the file returned by GET_PRINT_DOCUMENT. Both the report data and layout are taken from values stored in page items (P1_XML and P1_XSL).

DECLARE
    l_id number;
    l_document BLOB;
BEGIN
    l_document := APEX_UTIL.GET_PRINT_DOCUMENT (
        p_report_data         => :P1_XML,
        p_report_layout       => :P1_XSL,
        p_report_layout_type  => 'xsl-fo',
        p_document_format     => 'pdf');
 
   l_id := APEX_MAIL.SEND(
       p_to        => :P35_MAIL_TO,
       p_from      => 'noreplies@oracle.com',
       p_subj      => 'sending PDF by using print API',
       p_body      => 'Please review the attachment.',
       p_body_html => 'Please review the attachment');
 
   APEX_MAIL.ADD_ATTACHMENT (
       p_mail_id    => l_id,
       p_attachment => l_document,
       p_filename   => 'mydocument.pdf',
       p_mime_type  => 'application/pdf');
END;

GET_SCREEN_READER_MODE_TOGGLE Function

This function returns a link to the current page to turn on or off, toggle, the mode. For example, if you are in standard mode, this function displays a link that when clicked switches screen reader mode on.

Syntax

APEX_UTIL.GET_SCREEN_READER_MODE_TOGGLE (
    p_on_message  IN VARCHAR2 DEFAULT NULL,
    p_off_message IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 21-53 describes the parameters available in GET_SCREEN_READER_MODE_TOGGLE function.

Table 21-53 GET_SCREEN_READER_MODE_TOGGLE Parameters

Parameter Description

p_on_message

Optional text used for the link to switch to screen reader mode, when you are in standard mode. If this parameter is not passed, the default 'Set Screen Reader Mode On' text is returned in the link.

p_off_message

Optional text used for the link to switch to standard mode, when you are in screen reader mode. If this parameter is not passed, the default 'Set Screen Reader Mode Off' text is returned in the link.


Example

When running in standard mode, this function returns a link with the text 'Set Screen Reader Mode On'. When the link is clicked the current page is refreshed and screen reader mode is switched on. When running in screen reader mode, a link 'Set Screen Reader Mode Off' is returned. When the link is clicked the current page is refreshed and switched back to standard mode.

BEGIN
    htp.p(apex_util.get_screen_reader_mode_toggle);
END;

GET_SESSION_LANG Function

This function returns the language setting for the current user in the current Application Express session.

Syntax

APEX_UTIL.GET_SESSION_LANG
RETURN VARCHAR2;

Parameters

None.

Example

The following example shows how to use the GET_SESSION_LANG function. It returns the session language for the current user in the current Application Express session into a local variable.

DECLARE
    VAL VARCHAR2(5);
BEGIN
    VAL := APEX_UTIL.GET_SESSION_LANG;
END;

GET_SESSION_STATE Function

This function returns the value for an item. You can use this function in your Oracle Application Express applications wherever you can use PL/SQL or SQL. You can also use the shorthand, function V, in place of APEX_UTIL.GET_SESSION_STATE.

Syntax

APEX_UTIL.GET_SESSION_STATE (
    p_item    IN   VARCHAR2) 
RETURN VARCHAR2;

Parameters

Table 21-54 describes the parameters available in GET_SESSION_STATE function.

Table 21-54 GET_SESSION_STATE Parameters

Parameter Description

p_item

The case insensitive name of the item for which you want to have the session state fetched


Example

The following example shows how to use the GET_SESSION_STATE function to return the value stored in session state for the item 'my_item'.

DECLARE
    l_item_value  VARCHAR2(255);
BEGIN
    l_item_value := APEX_UTIL.GET_SESSION_STATE('my_item');
END;

GET_SESSION_TERRITORY Function

This function returns the territory setting for the current user in the current Application Express session.

Syntax

APEX_UTIL.GET_SESSION_TERRITORY
RETURN VARCHAR2;

Parameters

None.

Example

The following example shows how to use the GET_SESSION_TERRITORY function. It returns the session territory setting for the current user in the current Application Express session into a local variable.

DECLARE
    VAL VARCHAR2(30);
BEGIN
    VAL := APEX_UTIL.GET_SESSION_TERRITORY;
END;

GET_SESSION_TIME_ZONE Function

This function returns the time zone for the current user in the current Application Express session. This value is null if the time zone is not explicitly set by using APEX_UTIL.SET_SESSION_TIME_ZONE or if an application's automatic time zone attribute is enabled.

Syntax

APEX_UTIL.GET_SESSION_TIME_ZONE
RETURN VARCHAR2;

Parameters

None.

Example

The following example shows how to use the GET_SESSION_TIME_ZONE function. It returns the session time zone for the current user in the current Application Express session into a local variable.

BEGIN
    VAL := APEX_UTIL.GET_SESSION_TIME_ZONE;
END;

GET_USER_ID Function

This function returns the numeric ID of a named user in the workspace.

Syntax

APEX_UTIL.GET_USER_ID(
    p_username   IN VARCHAR2)
RETURN NUMBER;

Parameters

Table 21-55 describes the parameters available in GET_USER_ID function.

Table 21-55 GET_USER_ID Parameters

Parameter Description

p_username

Identifies the name of a user in the workspace


Example

The following example shows how to use the GET_USER_ID function to return the ID for the user named 'FRANK'.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.GET_USER_ID(p_username => 'FRANK');
END;

GET_USER_ROLES Function

This function returns the DEVELOPER_ROLE field stored in the named user account record. Please note that currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role.

Syntax

APEX_UTIL.GET_USER_ROLES(
   p_username IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-56 describes the parameters available in GET_USER_ROLES function.

Table 21-56 GET_USER_ROLES Parameters

Parameter Description

p_username

Identifies a user name in the account


Example

The following example shows how to use the GET_USER_ROLES function to return colon separated list of roles stored in the DEVELOPER_ROLE field for the user 'FRANK'.

DECLARE
    VAL VARCHAR2(4000);
BEGIN
    VAL := APEX_UTIL.GET_USER_ROLES(p_username=>'FRANK');
END;

GET_USERNAME Function

This function returns the user name of a user account identified by a numeric ID.

Syntax

APEX_UTIL.GET_USERNAME(
    p_userid IN NUMBER)
RETURN VARCHAR2;

Parameters

Table 21-57 describes the parameters available in GET_USERNAME function.

Table 21-57 GET_USERNAME Parameters

Parameter Description

p_userid

Identifies the numeric ID of a user account in the workspace


Example

The following example shows how to use the GET_USERNAME function to return the user name for the user with an ID of 228922003.

DECLARE
    VAL VARCHAR2(100);
BEGIN
    VAL := APEX_UTIL.GET_USERNAME(p_userid => 228922003);
END;

HOST_URL Function

This function returns the URL to the Application Express instance, depending on the option passed.

Syntax

APEX_UTIL.HOST_URL (
    p_option IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 21-58 describes the parameters available in the HOST_URL function.

Table 21-58 HOST_URL Parameters

Parameter Description

p_option

Specifies the parts of the URL to include.

Possible values for p_option include:

  • NULL - Return URL up to port number. For example:

    http://myserver.com:7778
    
  • SCRIPT - Return URL to include script name. For example:

    https://myserver.com:7778/pls/apex/
    
  • IMGPRE - Return URL to include image prefix. For example:

    https://myserver.com:7778/i/
    

Example

The following example demonstrates how to use the HOST_URL function to return the URL, including the script name, to the current Application Express instance.

declare
    l_host_url      varchar2(4000);
    l_url           varchar2(4000);
    l_application   varchar2(30) := 'f?p=100:1';
    l_email_body    varchar2(32000);
begin
    l_host_url := apex_util.host_url('SCRIPT');
    l_url := l_host_url||l_application;
    l_email_body := 'The URL to the application is: '||l_url;
end;

HTML_PCT_GRAPH_MASK Function

Use this function to scale a graph. This function can also be used by classic and interactive reports with format mask of GRAPH. This generates a <div> tag with inline styles.

Syntax

APEX_UTIL.HTML_PCT_GRAPH_MASK (
    p_number         IN NUMBER    DEFAULT NULL,
    p_size           IN NUMBER    DEFAULT 100,
    p_background     IN VARCHAR2  DEFAULT NULL,
    p_bar_background IN VARCHAR2  DEFAULT NULL,
    p_format         IN VARCHAR2  DEFAULT NULL)
RETURN VARCHAR2;

Parameters

Table 21-59 describes the parameters available in HTML_PCT_GRAPH_MASK function.

Table 21-59 HTML_PCT_GRAPH_MASK Parameters

Parameter Description

p_number

Number between 0 and 100.

p_size

Width of graph in pixels.

p_background

Six character hexadecimal background color of chart bar (not bar color)

p_bar_background

Six character hexadecimal background color of chart bar (bar color)

p_format

If this parameter is supplied, p_size, p_background and p_bar_background are ignored.

This parameter uses the following format: PCT_GRAPH:<BACKGROUND>:<FOREGROUND>:<CHART_WIDTH> position 1: PCT_GRAPH format mask indicator position 2: Background color in hexadecimal, 6 characters (optional) position 3: Foreground "bar" color in hexadecimal, 6 characters (optional) position 4: Chart width in pixels. Numeric and defaults to 100. p_number is automatically scaled so that 50 is half of chart_width (optional)


Example

The following is an SQL example.

select apex_util.html_pct_graph_mask(33) from dual

The following is a report numeric column format mask example.

PCT_GRAPH:777777:111111:200

INCREMENT_CALENDAR Procedure

Use this procedure to navigate to the next set of days in the calendar. Depending on what the calendar view is, this procedure navigates to the next month, week or day. If it is a Custom Calendar the total number of days between the start date and end date are navigated.

Syntax

APEX_UTIL.INCREMENT_CALENDAR;

Parameter

None.

Example

In this example, if you create a button called NEXT in the Calendar page and create a process that fires when the create button is clicked the following code navigates the calendar.

APEX_UTIL.INCREMENT_CALENDAR

IR_CLEAR Procedure

This procedure clears report settings.

Note:

This procedure should be used only in a page submit process.

Syntax

APEX_UTIL.IR_CLEAR(
    p_page_id IN NUMBER,
    p_report_alias IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-60 describes the parameters available in IR_CLEAR procedure.

Table 21-60 IR_CLEAR Parameters

Parameter Description

p_page_id

Page of the current Application Express application that contains an interactive report.

p_report_alias

Identifies the saved report alias within the current application page. To clear a Primary report, p_report_alias must be 'PRIMARY' or leave as NULL. To clear a saved report, p_report_alias must be the name of the saved report. For example, to clear report '1234', p_report_alias must be '1234'.


Example

The following example shows how to use the IR_CLEAR procedure to clear Interactive report settings with alias of '8101021' in page 1 of the current application.

BEGIN
    APEX_UTIL.IR_CLEAR(
        p_page_id      => 1,
        p_report_alias => '8101021'
        );
END;

IR_DELETE_REPORT Procedure

This procedure deletes saved Interactive reports. It deletes all saved reports except the Primary Default report.

Syntax

APEX_UTIL.IR_DELETE_REPORT(
    p_report_id IN NUMBER);

Parameters

Table 21-61 describes the parameters available in IR_DELETE_REPORT procedure.

Table 21-61 IR_DELETE_REPORT Parameters

Parameter Description

p_report_id

Report ID to delete within the current Application Express application.


Example

The following example shows how to use the IR_DELETE_REPORT procedure to delete the saved Interactive report with ID of '880629800374638220' in the current application.

BEGIN
    APEX_UTIL.IR_DELETE_REPORT(
        p_report_id => '880629800374638220');
END;

IR_DELETE_SUBSCRIPTION Procedure

This procedure deletes Interactive subscriptions.

Syntax

APEX_UTIL.IR_DELETE_SUBSCRIPTION(
    p_subscription_id IN NUMBER);

Parameters

Table 21-61 describes the parameters available in IR_DELETE_SUBSCRIPTION procedure.

Table 21-62 IR_DELETE_SUBSCRIPTION Parameters

Parameter Description

p_subscription_id

Subscription ID to delete within the current workspace.


Example

The following example shows how to use the IR_DELETE_SUBSCRIPTION procedure to delete the subscription with ID of ' 880629800374638220 ' in the current workspace.

BEGIN
    APEX_UTIL.IR_DELETE_SUBSCRIPTION(
        p_subscription_id => '880629800374638220');
END;

IR_FILTER Procedure

This procedure creates a filter on an interactive report.

Note:

This procedure should be used only in a page submit process.

Syntax

APEX_UTIL.IR_FILTER(
    p_page_id       IN NUMBER,
    p_report_column IN VARCHAR2,
    p_operator_abbr IN VARCHAR2 DEFAULT NULL,
    p_filter_value  IN VARCHAR2,
    p_report_alias  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-63 describes the parameters available in IR_FILTER procedure.

Table 21-63 IR_FILTER Parameters

Parameter Description

p_page_id

Page of the current Application Express application that contains an interactive report.

p_report_column

Name of the report SQL column, or column alias, to be filtered.

p_operator_abbr

Filter type. Valid values are as follows:

  • EQ = Equals

  • NEQ = Not Equals

  • LT = Less than

  • LTE = Less then or equal to

  • GT = Greater Than

  • GTE = Greater than or equal to

  • LIKE = SQL Like operator

  • N = Null

  • NN = Not Null

  • C = Contains

  • NC = Not Contains

  • IN = SQL In Operator

  • NIN = SQL Not In Operator

p_filter_value

Filter value. This value is not used for 'N' and 'NN'.

p_report_alias

Identifies the saved report alias within the current application page. To create a filter on a Primary report, p_report_alias must be 'PRIMARY' or leave as NULL. To create a filter on a saved report, p_report_alias must be the name of the saved report. For example, to create a filter on report '1234', p_report_alias must be '1234'.


Example

The following example shows how to use the IR_FILTER procedure to filter Interactive report with alias of '8101021' in page 1 of the current application with DEPTNO equals 30.

BEGIN
    APEX_UTIL.IR_FILTER (   
        p_page_id        => 1,
        p_report_column  => 'DEPTNO',
        p_operator_abbr  => 'EQ',
        p_filter_value   => '30'
        p_report_alias   => '8101021'
        );
END;

IR_RESET Procedure

This procedure resets report settings back to the default report settings. Resetting a report removes any customizations you have made.

Note:

This procedure should be used only in a page submit process.

Syntax

APEX_UTIL.IR_RESET(
    p_page_id IN NUMBER,
    p_report_alias IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-64 describes the parameters available in IR_RESET procedure.

Table 21-64 IR_RESET Parameters

Parameter Description

p_page_id

Page of the current Application Express application that contains an interactive report.

p_report_alias

Identifies the saved report alias within the current application page. To reset a Primary report, p_report_alias must be 'PRIMARY' or leave as NULL. To reset a saved report, p_report_alias must be the name of the saved report. For example, to reset report '1234', p_report_alias must be '1234'.


Example

The following example shows how to use the IR_RESET procedure to reset Interactive report settings with alias of '8101021' in page 1 of the current application.

BEGIN
    APEX_UTIL.IR_RESET(
        p_page_id      => 1,
        p_report_alias => '8101021'
        );
END;

IS_HIGH_CONTRAST_SESSION Function

This function returns a boolean true if the session is in high contrast mode and returns a boolean false if not in high contrast mode.

Syntax

APEX_UTIL.IS_HIGH_CONTRAST_SESSION
RETURN BOOLEAN;

Parameters

None.

Example

In this example, if the current session is running in high contrast mode, a high contrast specific CSS file 'my_app_hc.css' is added to the HTML output of the page.

BEGIN
    IF apex_util.is_high_contrast_session THEN
        apex_css.add_file (
            p_name => 'my_app_hc');
    END IF;
END;

IS_HIGH_CONTRAST_SESSION_YN Function

This function returns Y if the session is in high contrast mode and N if not in high contrast mode.

Syntax

APEX_UTIL.IS_HIGH_CONTRAST_SESSION_YN
RETURN VARCHAR2;

Parameters

None.

Example

In this example, if the current session is running in high contrast mode, a high contrast specific CSS file, my_app_hc.css, is added to the HTML output of the page.

BEGIN
    IF apex_util.is_high_contrast_session_yn = 'Y' THEN
        apex_css.add_file (
            p_name => 'my_app_hc');
    END IF;
END;

IS_LOGIN_PASSWORD_VALID Function

This function returns a Boolean result based on the validity of the password for a named user account in the current workspace. This function returns true if the password matches and it returns false if the password does not match.

Syntax

APEX_UTIL.IS_LOGIN_PASSWORD_VALID(
    p_username IN VARCHAR2,
    p_password IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 21-65 describes the parameters available in the IS_LOGIN_PASSWORD_VALID function.

Table 21-65 IS_LOGIN_PASSWORD_VALID Parameters

Parameter Description

p_username

User name in account

p_password

Password to be compared with password stored in the account


Example

The following example shows how to use the IS_LOGIN_PASSWORD_VALID function to check if the user 'FRANK' has the password 'tiger'. TRUE is returned if this is a valid password for 'FRANK', FALSE is returned if not.

DECLARE
    VAL BOOLEAN;
BEGIN
    VAL := APEX_UTIL.IS_LOGIN_PASSWORD_VALID (
        p_username=>'FRANK',
        p_password=>'tiger');
END;

IS_SCREEN_READER_SESSION Function

This function returns a boolean true if the session is in screen reader mode and returns a boolean false if not in screen reader mode.

Syntax

APEX_UTIL.IS_SCREEN_READER_SESSION
RETURN BOOLEAN;

Parameters

None

Example

BEGIN
    IF apex_util.is_screen_reader_session then
        htp.p('Screen Reader Mode');
    END IF;
END;

IS_SCREEN_READER_SESSION_YN Function

This function returns 'Y' if the session is in screen reader mode and 'N' if not in screen reader mode.

Syntax

APEX_UTIL.IS_SCREEN_READER_SESSION_YN
RETURN VARCHAR2;

Parameters

None

Example

BEGIN
    IF apex_util.is_screen_reader_session_yn = 'Y' then
        htp.p('Screen Reader Mode');
    END IF;
END;

IS_USERNAME_UNIQUE Function

This function returns a Boolean result based on whether the named user account is unique in the workspace.

Syntax

APEX_UTIL.IS_USERNAME_UNIQUE(
    p_username IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 21-66 describes the parameters available in IS_USERNAME_UNIQUE function.

Table 21-66 IS_USERNAME_UNIQUE Parameters

Parameter Description

p_username

Identifies the user name to be tested


Example

The following example shows how to use the IS_USERNAME_UNIQUE function. If the user 'FRANK' already exists in the current workspace, FALSE is returned, otherwise TRUE is returned.

DECLARE
    VAL BOOLEAN;
BEGIN
    VAL := APEX_UTIL.IS_USERNAME_UNIQUE(
        p_username=>'FRANK');
END;

KEYVAL_NUM Function

This function gets the value of the package variable (wwv_flow_utilities.g_val_num) set by APEX_UTIL.SAVEKEY_NUM.

Syntax

APEX_UTIL.KEYVAL_NUM
RETURN NUMBER;

Parameters

None

Example

The following example shows how to use the KEYVAL_NUM function to return the current value of the package variable wwv_flow_utilities.g_val_num.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.KEYVAL_NUM;
END;

KEYVAL_VC2 Function

This function gets the value of the package variable (wwv_flow_utilities.g_val_vc2) set by APEX_UTIL.SAVEKEY_VC2.

Syntax

APEX_UTIL.KEYVAL_VC2;

Parameters

None.

Example

The following example shows how to use the KEYVAL_VC2 function to return the current value of the package variable wwv_flow_utilities.g_val_vc2.

DECLARE
    VAL VARCHAR2(4000);
BEGIN
    VAL := APEX_UTIL.KEYVAL_VC2;
END;

LOCK_ACCOUNT Procedure

Sets a user account status to locked. Must be run by an authenticated workspace administrator in the context of a page request.

Syntax

APEX_UTIL.LOCK_ACCOUNT (
     p_user_name IN VARCHAR2); 

Parameters

Table 21-67 describes the parameters available in the LOCK_ACCOUNT procedure.

Table 21-67 LOCK_ACCOUNT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the LOCK_ACCOUNT procedure. Use this procedure to lock an Application Express account (workspace administrator, developer, or end user) in the current workspace. This action locks the account for use by administrators, developers, and end users.

BEGIN
    FOR c1 IN (SELECT user_name from wwv_flow_users) LOOP
        APEX_UTIL.LOCK_ACCOUNT(p_user_name => c1.user_name);
        htp.p('End User Account:'||c1.user_name||' is now locked.');    
    END LOOP;
END;

PASSWORD_FIRST_USE_OCCURRED Function

Returns true if the account's password has changed since the account was created, an Oracle Application Express administrator performs a password reset operation that results in a new password being emailed to the account holder, or a user has initiated password reset operation. This function returns false if the account's password has not been changed since either of the events just described.

This function may be run in a page request context by any authenticated user.

Syntax

APEX_UTIL.PASSWORD_FIRST_USE_OCCURRED (
    p_user_name IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 21-68 describes the parameters available in the PASSWORD_FIRST_USE_OCCURRED procedure.

Table 21-68 PASSWORD_FIRST_USE_OCCURRED Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the PASSWORD_FIRST_USE_OCCURRED function. Use this function to check if the password for an Application Express user account (workspace administrator, developer, or end user) in the current workspace has been changed by the user the first time the user logged in after the password was initially set during account creation, or was changed by one of the password reset operations described above.This is meaningful only with accounts for which the CHANGE_PASSWORD_ON_FIRST_USE attribute is set to Yes.

BEGIN
    FOR c1 IN (SELECT user_name from wwv_flow_users) LOOP
        IF APEX_UTIL.PASSWORD_FIRST_USE_OCCURRED(p_user_name => c1.user_name) THEN
            htp.p('User:'||c1.user_name||' has logged in and updated the password.');
        END IF;
    END LOOP;
END;    

PREPARE_URL Function

The PREPARE_URL function serves two purposes:

  1. To return an f?p URL with the Session State Protection checksum argument (&cs=) if one is required.

  2. To return an f?p URL with the session ID component replaced with zero (0) if the zero session ID feature is in use and other criteria are met.

Note:

The PREPARE_URL functions returns the f?p URL with &cs=<large hex value> appended. If you use this returned value, for example in JavaScript, it may be necessary to escape the ampersand in the URL to conform with syntax rules of the particular context. One place you may encounter this is in SVG chart SQL queries which might include PREPARE_URL calls.

Syntax

APEX_UTIL.PREPARE_URL (
    p_url           IN VARCHAR2,
    p_url_charset   IN VARCHAR2 default null,
    p_checksum_type IN VARCHAR2 default null)
RETURN VARCHAR2;

Parameters

Table 21-69 describes the parameters available in the PREPARE_URL function.

Table 21-69 PREPARE_URL Parameters

Parameter Description

p_url

An f?p relative URL with all substitutions resolved

p_url_charset

The character set name (for example, UTF-8) to use when escaping special characters contained within argument values

p_checksum_type

Null or any of the following six values, SESSION or 3, PRIVATE_BOOKMARK or 2, or PUBLIC_BOOKMARK or 1


Example 1

The following example shows how to use the PREPARE_URL function to return a URL with a valid 'SESSION' level checksum argument. This URL sets the value of P1_ITEM page item to xyz.

DECLARE
    l_url varchar2(2000);
    l_app number := v('APP_ID');
    l_session number := v('APP_SESSION');
BEGIN
    l_url := APEX_UTIL.PREPARE_URL(
        p_url => 'f?p=' || l_app || ':1:'||l_session||'::NO::P1_ITEM:xyz',
        p_checksum_type => 'SESSION');
END;

Example 2

The following example shows how to use the PREPARE_URL function to return a URL with a zero session ID. In a PL/SQL Dynamic Content region that generates f?p URLs (anchors), call PREPARE_URL to ensure that the session ID is set to zero when the zero session ID feature is in use, when the user is a public user (not authenticated), and when the target page is a public page in the current application:

htp.p(APEX_UTIL.PREPARE_URL(p_url => 'f?p=' || :APP_ID || ':10:'|| :APP_SESSION
||'::NO::P10_ITEM:ABC');

When using PREPARE_URL for this purpose, the p_url_charset and p_checksum_type arguments can be omitted. However, it is permissible to use them when both the Session State Protection and Zero Session ID features are applicable.

PUBLIC_CHECK_AUTHORIZATION Function

Given the name of a security scheme, this function determines if the current user passes the security check.

Syntax

APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION (
    p_security_scheme    IN    VARCHAR2) 
RETURN BOOLEAN;

Parameters

Table 21-70 describes the parameters available in PUBLIC_CHECK_AUTHORIZATION function.

Table 21-70 PUBLIC_CHECK_AUTHORIZATION Parameters

Parameter Description

p_security_name

The name of the security scheme that determines if the user passes the security check


Example

The following example shows how to use the PUBLIC_CHECK_AUTHORIZATION function to check if the current user passes the check defined in the my_auth_scheme authorization scheme.

DECLARE
    l_check_security  BOOLEAN;
BEGIN
    l_check_security := APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION('my_auth_scheme');
END;

PURGE_REGIONS_BY_APP Procedure

Deletes all cached regions for an application.

Syntax

APEX_UTIL.PURGE_REGIONS_BY_APP (
    p_application IN NUMBER);

Parameters

Table 21-71 describes the parameters available in PURGE_REGIONS_BY_APP.

Table 21-71 PURGE_REGIONS_BY_APP Parameters

Parameter Description

p_application

The identification number (ID) of the application.


Example

The following example show how to use APEX_UTIL.PURGE_REGIONS_BY_APP to delete all cached regions for application #123.

BEGIN
    APEX_UTIL.PURGE_REGIONS_BY_APP(p_application=>123);
END;

PURGE_REGIONS_BY_NAME Procedure

Deletes all cached values for a region identified by the application ID, page number and region name.

Syntax

APEX_UTIL.PURGE_REGIONS_BY_NAME (
     p_application IN NUMBER,
     p_page        IN NUMBER,
     p_region_name IN VARCHAR2);

Parameters

Table 21-72 describes the parameters available in PURGE_REGIONS_BY_NAME.

Table 21-72 PURGE_REGIONS_BY_NAME Parameters

Parameter Description

p_application

The identification number (ID) of the application.

p_page

The number of the page containing the region to be deleted.

p_region_name

The region name to be deleted.


Example

The following example shows how to use the PURGE_REGIONS_BY_NAME procedure to delete all the cached values for the region 'my_cached_region' on page 1 of the current application.

BEGIN
    APEX_UTIL.PURGE_REGIONS_BY_NAME(
        p_application => :APP_ID,
        p_page => 1,
        p_region_name => 'my_cached_region');
END;

PURGE_REGIONS_BY_PAGE Procedure

Deletes all cached regions by application and page.

Syntax

APEX_UTIL.PURGE_REGIONS_BY_PAGE (
     p_application IN NUMBER,
     p_page     IN NUMBER);

Parameters

Table 21-73 describes the parameters available in PURGE_REGIONS_BY_PAGE.

Table 21-73 PURGE_REGIONS_BY_PAGE Parameters

Parameter Description

p_application

The identification number (ID) of the application.

p_page

The identification number of page containing the region.


Example

The following example shows how to use the PURGE_REGIONS_BY_PAGE procedure to delete all the cached values for regions on page 1 of the current application.

BEGIN
    APEX_UTIL.PURGE_REGIONS_BY_PAGE(
        p_application => :APP_ID,
        p_page => 1);
END;

REDIRECT_URL Procedure

This procedure calls owa_util.redirect_url to tell the browser to redirect to a new URL. Afterwards, it automatically calls apex_application.stop_apex_engine to abort further processing of the Application Express application.

Syntax

APEX_UTIL.REDIRECT_URL (
    p_url              in varchar2,
    p_reset_htp_buffer in boolean default true );

Parameters

Table 21-74 describes the parameters available in the REDIRECT_URL procedure.

Table 21-74 REDIRECT_URL Parameters

Parameter Description

p_url

The URL the browser requests.

p_reset_htp_buffer

Set to TRUE to reset the HTP buffer to make sure the browser understands the redirect to the new URL and is not confused by data that is already written to the HTP buffer. Set to FALSE if the application has it's own cookie to use in the response.


Example

The following example tells the browser to redirect to http://www.oracle.com and immediately stops further processing.

apex_util.redirect_url (
    p_url => 'http://www.oracle.com/' );

REMOVE_PREFERENCE Procedure

This procedure removes the preference for the supplied user.

Syntax

APEX_UTIL.REMOVE_PREFERENCE(
    p_preference    IN    VARCHAR2 DEFAULT NULL,
    p_user          IN    VARCHAR2 DEFAULT V('USER'));

Parameters

Table 21-75 describes the parameters available in the REMOVE_PREFERENCE procedure.

Table 21-75 REMOVE_PREFERENCE Parameters

Parameter Description

p_preference

Name of the preference to remove

p_user

User for whom the preference is defined


Example

The following example shows how to use the REMOVE_PREFERENCE procedure to remove the preference default_view for the currently authenticated user.

BEGIN
    APEX_UTIL.REMOVE_PREFERENCE(
        p_preference => 'default_view',
        p_user       => :APP_USER);    
END;

REMOVE_SORT_PREFERENCES Procedure

This procedure removes the user's column heading sorting preference value.

Syntax

APEX_UTIL.REMOVE_SORT_PREFERENCES (
    p_user  IN   VARCHAR2 DEFAULT V('USER'));

Parameters

Table 21-76 describes the parameters available in REMOVE_SORT_PREFERENCES function.

Table 21-76 REMOVE_SORT_PREFERENCES Parameters

Parameter Description

p_user

Identifies the user for whom sorting preferences are removed


Example

The following example shows how to use the REMOVE_SORT_PREFERENCES procedure to remove the currently authenticated user's column heading sorting preferences.

BEGIN
    APEX_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER);
END;

REMOVE_USER Procedure

This procedure removes the user account identified by the primary key or a user name. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

APEX_UTIL.REMOVE_USER(
    p_user_id   IN NUMBER,
    p_user_name IN VARCHAR2);

Parameters

Table 21-77 describes the parameters available in the REMOVE_USER procedure.

Table 21-77 REMOVE_USER Parameters

Parameter Description

p_user_id

The numeric primary key of the user account record

p_user_name

The user name of the user account


Example

The following examples show how to use the REMOVE_USER procedure to remove a user account. Firstly, by the primary key (using the p_user_id parameter) and secondly by user name (using the p_user_name parameter).

BEGIN
    APEX_UTIL.REMOVE_USER(p_user_id=> 99997);
END;

BEGIN
    APEX_UTIL.REMOVE_USER(p_user_name => 'FRANK');
END;

RESET_AUTHORIZATIONS Procedure

To increase performance, Oracle Application Express caches the results of authorization schemes after they have been evaluated. You can use this procedure to undo caching, requiring each authorization scheme be revalidated when it is next encountered during page show or accept processing. You can use this procedure if you want users to have the ability to change their responsibilities (their authorization profile) within your application.

Syntax

APEX_UTIL.RESET_AUTHORIZATIONS; 

Parameters

None.

Example

The following example shows how to use the RESET_AUTHORIZATIONS procedure to clear the authorization scheme cache.

BEGIN
    APEX_UTIL.RESET_AUTHORIZATIONS;
END;

RESET_PW Procedure

This procedure resets the password for a named user and emails it in a message to the email address located for the named account in the current workspace. To execute this procedure, the current user must have administrative privilege in the workspace.

Syntax

APEX_UTIL.RESET_PW(
    p_user IN VARCHAR2,
    p_msg  IN VARCHAR2);

Parameters

Table 21-78 describes the parameters available in the RESET_PW procedure.

Table 21-78 RESET_PW Parameters

Parameter Description

p_user

The user name of the user account

p_msg

Message text to be mailed to a user


Example

The following example shows how to use the RESET_PW procedure to reset the password for the user 'FRANK'.

BEGIN
    APEX_UTIL.RESET_PW(
        p_user => 'FRANK',
        p_msg => 'Contact help desk at 555-1212 with questions');
END;

SAVEKEY_NUM Function

This function sets a package variable (wwv_flow_utilities.g_val_num) so that it can be retrieved using the function KEYVAL_NUM.

Syntax

APEX_UTIL.SAVEKEY_NUM(
    p_val IN NUMBER)
RETURN NUMBER;

Parameters

Table 21-79 describes the parameters available in the SAVEKEY_NUM procedure.

Table 21-79 SAVEKEY_NUM Parameters

Parameter Description

p_val

The numeric value to be saved


Example

The following example shows how to use the SAVEKEY_NUM function to set the wwv_flow_utilities.g_val_num package variable to the value of 10.

DECLARE
    VAL NUMBER;
BEGIN
    VAL := APEX_UTIL.SAVEKEY_NUM(p_val => 10);
END;

SAVEKEY_VC2 Function

This function sets a package variable (wwv_flow_utilities.g_val_vc2) so that it can be retrieved using the function KEYVAL_VC2.

Syntax

APEX_UTIL.SAVEKEY_VC2(
    p_val IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-80 describes the parameters available in the SAVEKEY_VC2 function.

Table 21-80 SAVEKEY_VC2 Parameters

Parameter Description

p_val

The is the VARCHAR2 value to be saved


Example

The following example shows how to use the SAVEKEY_VC2 function to set the wwv_flow_utilities.g_val_vc2 package variable to the value of 'XXX'.

DECLARE
    VAL VARCHAR2(4000);
BEGIN
    VAL := APEX_UTIL.SAVEKEY_VC2(p_val => 'XXX');
END;

SET_ATTRIBUTE Procedure

This procedure sets the value of one of the attribute values (1 through 10) of a user in the Application Express accounts table.

Syntax

APEX_UTIL.SET_ATTRIBUTE( 
    p_userid           IN NUMBER, 
    p_attribute_number IN NUMBER,
    p_attribute_value  IN VARCHAR2); 

Parameters

Table 21-81 describes the parameters available in the SET_ATTRIBUTE procedure.

Table 21-81 SET_ATTRIBUTE Parameters

Parameter Description

p_userid

The numeric ID of the user account

p_attribute_number

Attribute number in the user record (1 through 10)

p_attribute_value

Value of the attribute located by p_attribute_number to be set in the user record


Example

The following example shows how to use the SET_ATTRIBUTE procedure to set the number 1 attribute for user 'FRANK' with the value 'foo'.

DECLARE  
    VAL VARCHAR2(4000);
BEGIN 
    APEX_UTIL.SET_ATTRIBUTE ( 
        p_userid => apex_util.get_user_id(p_username => 'FRANK'), 
        p_attribute_number => 1, 
        p_attribute_value => 'foo'); 
END; 

SET_AUTHENTICATION_RESULT Procedure

This procedure can be called from an application's custom authentication function (that is, credentials verification function). The status passed to this procedure is logged in the Login Access Log.

Syntax

APEX_UTIL.SET_AUTHENTICATION_RESULT(
    p_code IN NUMBER);

Parameters

Table 21-24 describes the parameters available in the SET_AUTHENTICATION_RESULT procedure.

Table 21-82 SET_AUTHENTICATION_RESULT Parameters

Parameter Description

p_code

Any numeric value the developer chooses. After this value is set in the session using this procedure, it can be retrieved using the APEX_UTIL.GET_AUTHENTICATION_RESULT function.


Example

One way to use this procedure is to include it in the application authentication scheme. This example demonstrates how text and numeric status values can be registered for logging. In this example, no credentials verification is performed, it just demonstrates how text and numeric status values can be registered for logging.Note that the status set using this procedure is visible in the apex_user_access_log view and in the reports on this view available to workspace and site administrators.

CREATE OR REPLACE FUNCTION MY_AUTH(
    p_username IN VARCHAR2,
    p_password IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
    APEX_UTIL.SET_CUSTOM_AUTH_STATUS(p_status=>'User:'||p_username||' is back.');
    IF UPPER(p_username) = 'GOOD' THEN
        APEX_UTIL.SET_AUTHENTICATION_RESULT(24567);
        RETURN TRUE;
    ELSE
        APEX_UTIL.SET_AUTHENTICATION_RESULT(-666);
        RETURN FALSE;
    END IF;
END;

SET_BUILD_OPTION_STATUS Procedure

Use this procedure to change the build option status of a specified application.

Syntax

apex_util.set_build_option_status(p_application_id IN NUMBER,
                                  p_id IN NUMBER,
                                  p_build_status IN VARCHAR2);

Parameters

Table 21-83 describes the parameters available in the SET_BUILD_OPTION_STATUS procedure.

Table 21-83 SET_BUILD_OPTION_STATUS Parameters

Parameter Description

p_application_id

The ID of the application that owns the build option under shared components.

p_id

The ID of the build option in the application.

p_build_status

The new status of the build option. Possible values are INCLUDE, EXCLUDE both upper case.


Example

The following example demonstrates how to use the SET_BUILD_OPTION_STATUS procedure to change the current status of build option.

BEGIN
APEX_UTIL.SET_BUILD_OPTION_STATUS(
    P_APPLICATION_ID => 101,
    P_ID => 245935500311121039, P_BUILD_STATUS=>'INCLUDE');
 
END;

SET_CUSTOM_AUTH_STATUS Procedure

This procedure can be called from an application's custom authentication function (that is, credentials verification function). The status passed to this procedure is logged in the Login Access Log.

Syntax

APEX_UTIL.SET_CUSTOM_AUTH_STATUS(
    p_status  IN VARCHAR2);

Parameters

Table 21-84 describes the parameters available in the SET_CUSTOM_AUTH_STATUS procedure.

Table 21-84 SET_CUSTOM_AUTH_STATUS Parameters

Parameter Description

p_status

Any text the developer chooses to denote the result of the authentication attempt (up to 4000 characters).


Example

One way to use the SET_CUSTOM_AUTH_STATUS procedure is to include it in the application authentication scheme. This example demonstrates how text and numeric status values can be registered for logging. Note that no credentials verification is performed. The status set using this procedure is visible in the apex_user_access_log view and in the reports on this view available to workspace and site administrators.

CREATE OR REPLACE FUNCTION MY_AUTH(
    p_username IN VARCHAR2, 
    p_password IN VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
    APEX_UTIL.SET_CUSTOM_AUTH_STATUS(p_status=>'User:'||p_username||' is back.');
    IF UPPER(p_username) = 'GOOD' THEN
        APEX_UTIL.SET_AUTHENTICATION_RESULT(24567);
        RETURN TRUE;
    ELSE
        APEX_UTIL.SET_AUTHENTICATION_RESULT(-666);
        RETURN FALSE;
    END IF;
END;

SET_EDITION Procedure

This procedure sets the name of the edition to be used in all application SQL parsed in the current page view or page submission.

Syntax

APEX_UTIL.SET_EDITION(
    p_edition IN VARCHAR2);

Parameters

Table 21-84 describes the parameters available in the SET_EDITION procedure.

Table 21-85 SET_EDITION Parameters

Parameter Description

p_edition

Edition name.


Example

The following example shows how to use the SET_EDITION procedure. It sets the edition name for the database session of the current page view.

BEGIN
    APEX_UTIL.SET_EDITION( P_EDITION => 'Edition1' );
END;

Note:

Support for Edition-Based Redefinition is only available in database version 11.2.0.1 or higher.

SET_EMAIL Procedure

This procedure updates a user account with a new email address. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.SET_EMAIL(
    p_userid IN NUMBER,
    p_email  IN VARCHAR2);

Parameters

Table 21-86 describes the parameters available in the SET_EMAIL procedure.

Table 21-86 SET_EMAIL Parameters

Parameter Description

p_userid

The numeric ID of the user account

p_email

The email address to be saved in user account


Example

The following example shows how to use the SET_EMAIL procedure to set the value of EMAIL to 'frank.scott@somewhere.com' for the user 'FRANK'.

BEGIN
    APEX_UTIL.SET_EMAIL(
        p_userid  => APEX_UTIL.GET_USER_ID('FRANK'),
        p_email   => 'frank.scott@somewhere.com');
END;

SET_FIRST_NAME Procedure

This procedure updates a user account with a new FIRST_NAME value. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.SET_FIRST_NAME(
    p_userid      IN NUMBER,
    p_first_name  IN VARCHAR2);

Parameters

Table 21-87 describes the parameters available in the SET_FIRST_NAME procedure.

Table 21-87 SET_FIRST_NAME Parameters

Parameter Description

p_userid

The numeric ID of the user account

p_first_name

FIRST_NAME value to be saved in user account


Example

The following example shows how to use the SET_FIRST_NAME procedure to set the value of FIRST_NAME to 'FRANK' for the user 'FRANK'.

BEGIN     
    APEX_UTIL.SET_FIRST_NAME(
        p_userid       => APEX_UTIL.GET_USER_ID('FRANK'),
        p_first_name   => 'FRANK');
END;

SET_LAST_NAME Procedure

This procedure updates a user account with a new LAST_NAME value. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.SET_LAST_NAME(
    p_userid      IN NUMBER,
    p_last_name   IN VARCHAR2);

Parameters

Table 21-88 describes the parameters available in the SET_LAST_NAME procedure.

Table 21-88 SET_LAST_NAME Parameters

Parameter Description

p_userid

The numeric ID of the user account

p_last_name

LAST_NAME value to be saved in the user account


Example

The following example shows how to use the SET_LAST_NAME procedure to set the value of LAST_NAME to 'SMITH' for the user 'FRANK'.

BEGIN     
    APEX_UTIL.SET_LAST_NAME(
        p_userid       => APEX_UTIL.GET_USER_ID('FRANK'),
        p_last_name   => 'SMITH');
END;

SET_PREFERENCE Procedure

This procedure sets a preference that persists beyond the user's current session.

Syntax

APEX_UTIL.SET_PREFERENCE (
    p_preference   IN    VARCHAR2 DEFAULT NULL,
    p_value        IN    VARCHAR2 DEFAULT NULL,
    p_user         IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 21-89 describes the parameters available in the SET_PREFERENCE procedure.

Table 21-89 SET_PREFERENCE Parameters

Parameter Description

p_preference

Name of the preference (case-sensitive)

p_value

Value of the preference

p_user

User for whom the preference is being set


Example

The following example shows how to use the SET_PREFERENCE procedure to set a preference called 'default_view' to the value 'WEEKLY' that persists beyond session for the currently authenticated user.

BEGIN
    APEX_UTIL.SET_PREFERENCE(        
        p_preference => 'default_view',
        p_value      => 'WEEKLY',      
        p_user       => :APP_USER); 
END;

SET_SECURITY_GROUP_ID Procedure

Use this procedure with apex_util.find_security_group_id to ease the use of the mail package in batch mode. This procedure is especially useful when a schema is associated with more than one workspace. For example, you might want to create a procedure that is run by a nightly job to email all outstanding tasks.

Syntax

APEX_UTIL.SET_SECURITY_GROUP_ID ( 
    p_security_group_id  IN NUMBER);

Parameters

Table 21-90 describes the parameters available in the SET_SECURITY_GROUP_ID procedure.

Table 21-90 SET_SECURITY_GROUP_ID Parameters

Parameter Description

p_security_group_id

This is the security group id of the workspace you are working in.


Example

The following example sends an alert to each user that has had a task assigned within the last day.

create or replace procedure new_tasks
is
    l_workspace_id      number;
    l_subject           varchar2(2000);
    l_body              clob;
    l_body_html         clob;
begin
    l_workspace_id := apex_util.find_security_group_id (p_workspace => 'PROJECTS');
    apex_util.set_security_group_id (p_security_group_id => l_workspace_id);
 
    l_body := ' ';
    l_subject := 'You have new tasks';
    for c1 in (select distinct(p.email_address) email_address, p.user_id
                 from teamsp_user_profile p, teamsp_tasks t
                where p.user_id = t.assigned_to_user_id
                  and t.created_on > sysdate - 1
                  and p.email_address is not null ) loop
        l_body_html := '<p />The following tasks have been added.';
        for c2 in (select task_name, due_date
                     from teamsp_tasks
                    where assigned_to_user_id = c1.user_id
                      and created_on > sysdate - 1 ) loop
            l_body_html := l_body_html || '<p />Task: '||c2.task_name||', due '||c2.due_date;
        end loop;
apex_mail.send (
            p_to        => c1.email_address,
            p_from      => c1.email_address,
            p_body      => l_body,
            p_body_html => l_body_html,
            p_subj      => l_subject );
    end loop;
end;

SET_SESSION_HIGH_CONTRAST_OFF Procedure

This procedure switches off high contrast mode for the current session.

Syntax

APEX_UTIL.SET_SESSION_HIGH_CONTRAST_OFF;

Parameters

None.

Example

In this example, high contrast mode is switched off for the current session.

BEGIN
    apex_util.set_session_high_contrast_off;
END;

SET_SESSION_HIGH_CONTRAST_ON Procedure

This procedure switches on high contrast mode for the current session.

Syntax

APEX_UTIL.SET_SESSION_HIGH_CONTRAST_ON;

Parameters

None.

Example

In this example, the current session is put into high contrast mode.

BEGIN
    apex_util.set_session_high_contrast_on;
END;

SET_SESSION_LANG Procedure

This procedure sets the language to be used for the current user in the current Application Express session. The language must be a valid IANA language name.

Syntax

APEX_UTIL.SET_SESSION_LANG(
    p_lang IN VARCHAR2);

Parameters

Table 21-91 describes the parameters available in the SET_SESSION_LANG procedure.

Table 21-91 SET_SESSION_LANG Parameters

Parameter Description

p_lang

This is an IANA language code. Some examples include: en, de, de-at, zh-cn, and pt-br.


Example

The following example shows how to use the SET_SESSION_LANG procedure. It sets the language for the current user for the duration of the Application Express session.

BEGIN
    APEX_UTIL.SET_SESSION_LANG( P_LANG => 'en');
END;

SET_SESSION_LIFETIME_SECONDS Procedure

This procedure sets the current session's Maximum Session Length in Seconds value. overriding the corresponding application attribute. This allows developers to dynamically shorten or lengthen the session life based on criteria determined after the user authenticates.

Syntax

APEX_UTIL.SET_SESSION_LIFETIME_SECONDS (
    p_seconds  IN    NUMEBER,
    p_scope    IN    VARCHAR2 DEFAULT 'SESSION');

Parameters

Table 21-92 describes the parameters available in the SET_SESSION_LIFETIME_SECONDS procedure.

Table 21-92 SET_SESSION_LIFETIME_SECONDS Parameters

Parameter Description

p_seconds

A positive integer indicating the number of seconds the session used by this application is allowed to exist.

p_scope

This parameter is obsolete. The procedure always sets the lifetime for the whole session.


Example 1

The following example shows how to use the SET_SESSION_LIFETIME_SECONDS procedure to set the current application's Maximum Session Length in Seconds attribute to 7200 seconds (two hours).

By allowing the p_scope input parameter to use the default value of 'SESSION', the following example would actually apply to all applications using the current session. This would be the most common use case when multiple Application Express applications use a common authentication scheme and are designed to operate as a suite in a common session.

BEGIN
   APEX_UTIL.SET_SESSION_LIFETIME_SECONDS(p_seconds => 7200);
END;

Example 2

The following example shows how to use the SET_SESSION_LIFETIME_SECONDS procedure to set the current application's Maximum Session Length in Seconds attribute to 3600 seconds (one hour).

BEGIN
    APEX_UTIL.SET_SESSION_LIFETIME_SECONDS(p_seconds => 3600); 
END;

SET_SESSION_MAX_IDLE_SECONDS Procedure

Sets the current application's Maximum Session Idle Time in Seconds value for the current session, overriding the corresponding application attribute. This allows developers to dynamically shorten or lengthen the maximum idle time allowed between page requests based on criteria determined after the user authenticates.

Syntax

APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS (
    p_seconds  IN    NUMEBER,
    p_scope    IN    VARCHAR2 DEFAULT 'SESSION');

Parameters

Table 21-93 describes the parameters available in the SET_SESSION_MAX_IDLE_SECONDS procedure.

Table 21-93 SET_SESSION_MAX_IDLE_SECONDS Parameters

Parameter Description

p_seconds

A positive integer indicating the number of seconds allowed between page requests.

p_scope

This parameter is obsolete. The procedure always sets the lifetime for the whole session


Example 1

The following example shows how to use the SET_SESSION_MAX_IDLE_SECONDS procedure to set the current application's Maximum Session Idle Time in Seconds attribute to 1200 seconds (twenty minutes). The following example applies to all applications using the current session.

BEGIN
   APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 1200);
END;

Example 2

The following example shows how to use the SET_SESSION_MAX_IDLE_SECONDS procedure to set the current application's Maximum Session Idle Time in Seconds attribute to 600 seconds (ten minutes). This example applies to all applications using the current session.

BEGIN
    APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 600); 
END;

SET_SESSION_SCREEN_READER_OFF Procedure

This procedure switches off screen reader mode for the current session.

Syntax

APEX_UTIL.SET_SESSION_SCREEN_READER_OFF;

Parameters

None

Example

In this example, the current session is put into standard mode.

BEGIN
    apex_util.set_session_screen_reader_off;
END;

SET_SESSION_SCREEN_READER_ON Procedure

This procedure puts the current session into screen reader mode.

Syntax

APEX_UTIL.SET_SESSION_SCREEN_READER_ON;

Parameters

None

Example

In this example, the current session is put into screen reader mode.

BEGIN
    apex_util.set_session_screen_reader_on;
END;

SET_SESSION_STATE Procedure

This procedure sets session state for a current Oracle Application Express session.

Syntax

APEX_UTIL.SET_SESSION_STATE (
    p_name     IN    VARCHAR2 DEFAULT NULL,
    p_value    IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 21-94 describes the parameters available in the SET_SESSION_STATE procedure.

Table 21-94 SET_SESSION_STATE Parameters

Parameter Description

p_name

Name of the application-level or page-level item for which you are setting sessions state

p_value

Value of session state to set


Example

The following example shows how to use the SET_SESSION_STATE procedure to set the value of the item 'my_item' to 'myvalue' in the current session.

BEGIN
    APEX_UTIL.SET_SESSION_STATE('my_item','myvalue');
END;

SET_SESSION_TERRITORY Procedure

This procedure sets the territory to be used for the current user in the current Application Express session. The territory name must be a valid Oracle territory.

Syntax

APEX_UTIL.SET_SESSION_TERRITORY(
    p_territory IN VARCHAR2);

Parameters

Table 21-95 describes the parameters available in the SET_SESSION_TERRITORY procedure.

Table 21-95 SET_SESSION_TERRITORY Parameters

Parameter Description

p_territory

A valid Oracle territory name. Examples include: AMERICA, UNITED KINGDOM, ISRAEL, AUSTRIA, and UNITED ARAB EMIRATES.


Example

The following example shows how to use the SET_SESSION_TERRITORY procedure. It sets the territory for the current user for the duration of the Application Express session.

BEGIN
    APEX_UTIL.SET_SESSION_TERRITORY( P_TERRITORY => 'UNITED KINGDOM');
END;

SET_SESSION_TIME_ZONE Procedure

This procedure sets the time zone to be used for the current user in the current Application Express session.

Syntax

APEX_UTIL.SET_SESSION_TIME_ZONE(
    p_time_zone IN VARCHAR2);

Parameters

Table 21-96 describes the parameters available in the SET_SESSION_TIME_ZONE procedure.

Table 21-96 SET_SESSION_TIME_ZONE Parameters

Parameter Description

p_timezone

A time zone value in the form of hours and minutes. Examples include: +09:00, 04:00, -05:00.


Example

The following example shows how to use the SET_SESSION_TIME_ZONE procedure. It sets the time zone for the current user for the duration of the Application Express session.

BEGIN
    APEX_UTIL.SET_SESSION_TIME_ZONE( P_TIME_ZONE => '-05:00');
END;

SET_USERNAME Procedure

This procedure updates a user account with a new USER_NAME value. To execute this procedure, the current user must have administrative privileges in the workspace.

Syntax

APEX_UTIL.SET_USERNAME(
    p_userid   IN NUMBER,
    p_username IN VARCHAR2);

Parameters

Table 21-97 describes the parameters available in the SET_USERNAME procedure.

Table 21-97 SET_USERNAME Parameters

Parameter Description

p_userid

The numeric ID of the user account

p_username

USER_NAME value to be saved in the user account


Example

The following example shows how to use the SET_USERNAME procedure to set the value of USERNAME to 'USER-XRAY' for the user 'FRANK'.

BEGIN     
    APEX_UTIL.SET_USERNAME(
        p_userid     => APEX_UTIL.GET_USER_ID('FRANK'),
        P_username   => 'USER-XRAY');
END;

SHOW_HIGH_CONTRAST_MODE_TOGGLE Procedure

This procedure displays a link to the current page to turn on or off, toggle, the mode. For example, if you are in standard mode, this function displays a link that when clicked switches the high contrast mode on.

Syntax

APEX_UTIL.SHOW_HIGH_CONTRAST_MODE_TOGGLE (
    p_on_message  in varchar2 default null,
    p_off_message in varchar2 default null);

Parameters

Table 21-98 describes the parameters available in the SHOW_HIGH_CONTRAST_MODE_TOGGLE procedure.

Table 21-98 SHOW_HIGH_CONTRAST_MODE_TOGGLE Parameters

Parameters Description

p_on_message

Optional text used for the link to switch to high contrast mode, when you are in standard mode. If this parameter is not passed, the default 'Set High Contrast Mode On' text is displayed.

p_off_message

Optional text used for the link to switch to standard mode, when you are in high contrast mode. If this parameter is not passed, the default 'Set High Contrast Mode Off' text is displayed.


Example

When running in standard mode, this procedure displays a link, Set High Contrast Mode On, that when clicked refreshes the current page and switches on high contrast mode. When running in high contrast mode, a link, Set High Contrast Mode Off, is displayed, that refreshes the current page and switches back to standard mode when clicked.

BEGIN
    apex_util.show_high_contrast_mode_toggle;
END;

Note:

There are also 2 translatable system messages that can be overridden at application level to change the default link text that is returned for this toggle. They include:
  • APEX.SET_HIGH_CONTRAST_MODE_OFF - Default text = Set High Contrast Mode Off

  • APEX.SET_HIGH_CONTRAST_MODE_ON - Default text = Set High Contrast Mode On

SHOW_SCREEN_READER_MODE_TOGGLE Procedure

This procedure displays a link to the current page to turn on or off, toggle, the mode. For example, if you are in standard mode, this function displays a link that when clicked switches the screen reader mode on.

Syntax

APEX_UTIL.SHOW_SCREEN_READER_MODE_TOGGLE (
    p_on_message  IN VARCHAR2 DEFAULT NULL,
    p_off_message IN VARCHAR2 DEFAULT NULL)

Parameters

Table 21-99 describes the parameters available in SHOW_SCREEN_READER_MODE_TOGGLE function.

Table 21-99 SHOW_SCREEN_READER_MODE_TOGGLE Parameters

Parameter Description

p_on_message

Optional text used for the link to switch to screen reader mode, when you are in standard mode. If this parameter is not passed, the default 'Set Screen Reader Mode On' text is displayed.

p_off_message

Optional text used for the link to switch to standard mode, when you are in screen reader mode. If this parameter is not passed, the default 'Set Screen Reader Mode Off' text is displayed.


Example

When running in standard mode, this procedure displays a link 'Set Screen Reader Mode On', that when clicked refreshes the current page and switches on screen reader mode. When running in screen reader mode, a link 'Set Screen Reader Mode Off' is displayed, that when clicked refreshes the current page and switches back to standard mode.

BEGIN
    apex_util.show_screen_reader_mode_toggle;
END;

STRING_TO_TABLE Function

Given a string, this function returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2. This array is a VARCHAR2(32767) table.

Syntax

APEX_UTIL.STRING_TO_TABLE (
    p_string       IN VARCHAR2,
    p_separator    IN VARCHAR2 DEFAULT ':') 
    RETURN APEX_APPLICATION_GLOBAL.VC_ARR2;

Parameters

Table 21-100 describes the parameters available in the STRING_TO_TABLE function.

Table 21-100 STRING_TO_TABLE Parameters

Parameter Description

p_string

String to be converted into a PL/SQL table of type APEX_APPLICATION_GLOBAL.VC_ARR2

p_separator

String separator. The default is a colon


Example

The following example shows how to use the STRING_TO_TABLE function. The function is passed the string 'One:Two:Three' in the p_string parameter and it returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2 containing 3 elements, the element at position 1 contains the value 'One', position 2 contains the value 'Two' and position 3 contains the value 'Three'. This is then output using the HTP.P function call.

DECLARE
    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
    l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE('One:Two:Three');
    FOR z IN 1..l_vc_arr2.count LOOP
        htp.p(l_vc_arr2(z));
    END LOOP;
END;

STRONG_PASSWORD_CHECK Procedure

This procedure returns Boolean OUT values based on whether a proposed password meets the password strength requirements as defined by the Oracle Application Express site administrator.

Syntax

APEX_UTIL.STRONG_PASSWORD_CHECK(
    p_username                    IN  VARCHAR2,
    p_password                    IN  VARCHAR2,
    p_old_password                IN  VARCHAR2,
    p_workspace_name              IN  VARCHAR2,
    p_use_strong_rules            IN  BOOLEAN,
    p_min_length_err              OUT BOOLEAN,
    p_new_differs_by_err          OUT BOOLEAN,
    p_one_alpha_err               OUT BOOLEAN,
    p_one_numeric_err             OUT BOOLEAN,
    p_one_punctuation_err         OUT BOOLEAN,
    p_one_upper_err               OUT BOOLEAN,
    p_one_lower_err               OUT BOOLEAN,
    p_not_like_username_err       OUT BOOLEAN,
    p_not_like_workspace_name_err OUT BOOLEAN,
    p_not_like_words_err          OUT BOOLEAN,
    p_not_reusable_err            OUT BOOLEAN);

Parameters

Table 21-101 describes the parameters available in the STRONG_PASSWORD_CHECK procedure.

Table 21-101 STRONG_PASSWORD_CHECK Parameters

Parameter Description

p_username

Username that identifies the account in the current workspace

p_password

Password to be checked against password strength rules

p_old_password

Current password for the account. Used only to enforce "new password must differ from old" rule

p_workspace_name

Current workspace name, used only to enforce "password must not contain workspace name" rule

p_use_strong_rules

Pass FALSE when calling this API

p_min_length_err

Result returns True or False depending upon whether the password meets minimum length requirement

p_new_differs_by_err

Result returns True or False depending upon whether the password meets "new password must differ from old" requirements

p_one_alpha_err

Result returns True or False depending upon whether the password meets requirement to contain at least one alphabetic character

p_one_numeric_err

Result returns True or False depending upon whether the password meets requirements to contain at least one numeric character

p_one_punctuation_err

Result returns True or False depending upon whether the password meets requirements to contain at least one punctuation character

p_one_upper_err

Result returns True or False depending upon whether the password meets requirements to contain at least one upper-case character

p_one_lower_err

Result returns True or False depending upon whether the password meets requirements to contain at least one lower-case character

p_not_like_username_err

Result returns True or False depending upon whether the password meets requirements that it not contain the username

p_not_like_workspace_name_err

Result returns True or False whether upon whether the password meets requirements that it not contain the workspace name

p_not_like_words_err

Result returns True or False whether the password meets requirements that it not contain specified simple words

p_not_reusable_err

Result returns True or False whether the password can be reused based on password history rules


Example

The following example shows how to use the STRONG_PASSWORD_CHECK procedure. It checks the new password 'foo' for the user 'SOMEBODY' meets all the password strength requirements defined by the Oracle Application Express site administrator. If any of the checks fail (the associated OUT parameter returns TRUE), then the example outputs a relevant message. For example, if the Oracle Application Express site administrator has defined that passwords must have at least one numeric character and the password 'foo' was checked, then the p_one_numeric_err OUT parameter would return TRUE and the message 'Password must contain at least one numeric character' would be output.

DECLARE
    l_username                    varchar2(30);
    l_password                    varchar2(30);
    l_old_password                varchar2(30);
    l_workspace_name              varchar2(30);
    l_min_length_err              boolean;
    l_new_differs_by_err          boolean;
    l_one_alpha_err               boolean;
    l_one_numeric_err             boolean;
    l_one_punctuation_err         boolean;
    l_one_upper_err               boolean;
    l_one_lower_err               boolean;
    l_not_like_username_err       boolean;
    l_not_like_workspace_name_err boolean;
    l_not_like_words_err          boolean;
    l_not_reusable_err            boolean;
    l_password_history_days       pls_integer;
BEGIN
    l_username := 'SOMEBODY';
    l_password := 'foo';
    l_old_password := 'foo';
    l_workspace_name := 'XYX_WS';
    l_password_history_days := 
        apex_instance_admin.get_parameter ('PASSWORD_HISTORY_DAYS');
 
    APEX_UTIL.STRONG_PASSWORD_CHECK(
        p_username                    => l_username,
        p_password                    => l_password,
        p_old_password                => l_old_password,
        p_workspace_name              => l_workspace_name,
        p_use_strong_rules            => false,
        p_min_length_err              => l_min_length_err,
        p_new_differs_by_err          => l_new_differs_by_err,
        p_one_alpha_err               => l_one_alpha_err,
        p_one_numeric_err             => l_one_numeric_err,
        p_one_punctuation_err         => l_one_punctuation_err,
        p_one_upper_err               => l_one_upper_err,
        p_one_lower_err               => l_one_lower_err,
        p_not_like_username_err       => l_not_like_username_err,
        p_not_like_workspace_name_err => l_not_like_workspace_name_err,
        p_not_like_words_err          => l_not_like_words_err,
        p_not_reusable_err            => l_not_reusable_err);

    IF l_min_length_err THEN
        htp.p('Password is too short');
    END IF;
 
    IF l_new_differs_by_err THEN
        htp.p('Password is too similar to the old password');
    END IF;
 
    IF l_one_alpha_err THEN
        htp.p('Password must contain at least one alphabetic character');
    END IF;
 
    IF l_one_numeric_err THEN
        htp.p('Password  must contain at least one numeric character');
    END IF;
 
    IF l_one_punctuation_err THEN
        htp.p('Password  must contain at least one punctuation character');
    END IF;
 
    IF l_one_upper_err THEN
        htp.p('Password must contain at least one upper-case character');
    END IF;
 
    IF l_one_lower_err THEN
        htp.p('Password must contain at least one lower-case character');
    END IF;
 
    IF l_not_like_username_err THEN
        htp.p('Password may not contain the username');
    END IF;
 
    IF l_not_like_workspace_name_err THEN
        htp.p('Password may not contain the workspace name');
    END IF;
 
    IF l_not_like_words_err THEN
        htp.p('Password contains one or more prohibited common words');
    END IF;

    IF l_not_reusable_err THEN
        htp.p('Password cannot be used because it has been used for the account within the last '||l_password_history_days||' days.');
    END IF;
END;

STRONG_PASSWORD_VALIDATION Function

This function returns formatted HTML in a VARCHAR2 result based on whether a proposed password meets the password strength requirements as defined by the Oracle Application Express site administrator.

Syntax

FUNCTION STRONG_PASSWORD_VALIDATION(
    p_username                    IN  VARCHAR2,
    p_password                    IN  VARCHAR2,
    P_OLD_PASSWORD                IN  VARCHAR2 DEFAULT NULL,
    P_WORKSPACE_NAME              IN  VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 21-102 describes the parameters available in the STRONG_PASSWORD_VALIDATION function.

Table 21-102 STRONG_PASSWORD_VALIDATION Parameters

Parameter Description

p_username

Username that identifies the account in the current workspace

p_password

Password to be checked against password strength rules

p_old_password

Current password for the account. Used only to enforce "new password must differ from old" rule

p_workspace_name

Current workspace name, used only to enforce "password must not contain workspace name" rule


Example

The following example shows how to use the STRONG_PASSWORD_VALIDATION procedure. It checks the new password 'foo' for the user 'SOMEBODY' meets all the password strength requirements defined by the Oracle Application Express site administrator. If any of the checks fail, then the example outputs formatted HTML showing details of where the new password fails to meet requirements.

DECLARE
      l_username                    varchar2(30);
      l_password                    varchar2(30);
      l_old_password                varchar2(30);
      l_workspace_name              varchar2(30);
BEGIN
    l_username := 'SOMEBODY';
    l_password := 'foo';
    l_old_password := 'foo';
    l_workspace_name := 'XYX_WS';

    HTP.P(APEX_UTIL.STRONG_PASSWORD_VALIDATION(
        p_username                    => l_username,
        p_password                    => l_password,
        p_old_password                => l_old_password,
        p_workspace_name              => l_workspace_name));
END;

SUBMIT_FEEDBACK Procedure

This procedure enables you to write a procedure to submit feedback, rather than using the page that can be generated by create page of type feedback.

Syntax

APEX_UTIL.SUBMIT_FEEDBACK (
    p_comment          IN VARCHAR2 DEFAULT NULL,
    p_type             IN NUMBER   DEFAULT '1',
    p_application_id   IN VARCHAR2 DEFAULT NULL,
    p_page_id          IN VARCHAR2 DEFAULT NULL,
    p_email            IN VARCHAR2 DEFAULT NULL,
    p_screen_width     IN VARCHAR2 DEFAULT NULL,
    p_screen_height    IN VARCHAR2 DEFAULT NULL,
    p_attribute_01     IN VARCHAR2 DEFAULT NULL,
    p_attribute_02     IN VARCHAR2 DEFAULT NULL,
    p_attribute_03     IN VARCHAR2 DEFAULT NULL,
    p_attribute_04     IN VARCHAR2 DEFAULT NULL,
    p_attribute_05     IN VARCHAR2 DEFAULT NULL,
    p_attribute_06     IN VARCHAR2 DEFAULT NULL,
    p_attribute_07     IN VARCHAR2 DEFAULT NULL,
    p_attribute_08     IN VARCHAR2 DEFAULT NULL,
    p_label_01         IN VARCHAR2 DEFAULT NULL,
    p_label_02         IN VARCHAR2 DEFAULT NULLl,
    p_label_03         IN VARCHAR2 DEFAULT NULL,
    p_label_04         IN VARCHAR2 DEFAULT NULL,
    p_label_05         IN VARCHAR2 DEFAULT NULL,
    p_label_06         IN VARCHAR2 DEFAULT NULL,
    p_label_07         IN VARCHAR2 DEFAULT NULL,
    p_label_08         IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-103 describes the parameters available in the SUBMIT_FEEDBACK procedure.

Table 21-103 SUBMIT_FEEDBACK Parameters

Parameter Description

p_comment

Comment to be submitted

p_type

Type of feedback (1 is General Comment, 2 is Enhancement Request, 3 is Bug)

p_application_id

ID of application related to the feedback

p_page_id

ID of page related to the feedback

p_email

Email of the user providing the feedback

p_screen_width

Width of screen at time feedback was provided

p_screen_height

Height of screen at time feedback was provided

p_attribute_01

Custom attribute for collecting feedback

p_attribute_02

Custom attribute for collecting feedback

p_attribute_03

Custom attribute for collecting feedback

p_attribute_04

Custom attribute for collecting feedback

p_attribute_05

Custom attribute for collecting feedback

p_attribute_06

Custom attribute for collecting feedback

p_attribute_07

Custom attribute for collecting feedback

p_attribute_08

Custom attribute for collecting feedback

p_label_01

Label for corresponding custom attribute

p_label_02

Label for corresponding custom attribute

p_label_03

Label for corresponding custom attribute

p_label_04

Label for corresponding custom attribute

p_label_05

Label for corresponding custom attribute

p_label_06

Label for corresponding custom attribute

p_label_07

Label for corresponding custom attribute

p_label_08

Label for corresponding custom attribute


Example

The following example submits a bug about page 22 within application 283.

begin
    apex_util.submit_feedback (
       p_comment        => 'This page does not render properly for me',
       p_type           => 3,
       p_application_id => 283,
       p_page_id        => 22,
       p_email          => 'user@xyz.corp',
       p_attribute_01   => 'Charting',
       p_label_01       => 'Component' );
end;
/

SUBMIT_FEEDBACK_FOLLOWUP Procedure

This procedure enables you to submit follow up to a feedback.

Syntax

APEX_UTIL.SUBMIT_FEEDBACK_FOLLOWUP (
    p_feedback_id      IN NUMBER,
    p_follow_up        IN VARCHAR2 DEFAULT NULL,
    p_email            IN VARCHAR2 DEFAULT NULL);

Parameters

Table 21-104 describes the parameters available in the SUBMIT_FEEDBACK_FOLLOWUP procedure.

Table 21-104 SUBMIT_FEEDBACK_FOLLOWUP Parameters

Parameter Description

p_feedback_followup

ID of feedback that this is a follow up to

p_follow_up

Text of follow up

p_email

Email of user providing the follow up


Example

The following example submits follow up to a previously filed feedback.

begin
   apex_util.submit_feedback_followup (
       p_feedback_id    => 12345,
       p_follow_up      => 'I tried this on another instance and it does not work there either',
       p_email          => 'user@xyz.corp' );
end;
/

TABLE_TO_STRING Function

Given a a PL/SQL table of type APEX_APPLICATION_GLOBAL.VC_ARR2, this function returns a delimited string separated by the supplied separator, or by the default separator, a colon (:).

Syntax

APEX_UTIL.TABLE_TO_STRING (
    p_table       IN     APEX_APPLICATION_GLOBAL.VC_ARR2,
    p_string      IN     VARCHAR2 DEFAULT ':') 
RETURN VARCHAR2;

Parameters

Table 21-105 describes the parameters available in the TABLE_TO_STRING function.

Table 21-105 TABLE_TO_STRING Parameters

Parameter Description

p_string

String separator. Default separator is a colon (:)

p_table

PL/SQL table that is to be converted into a delimited string


Example

The following function returns a comma delimited string of contact names that are associated with the provided cust_id.

create or replace function get_contacts ( 
    p_cust_id  in  number ) 
    return varchar2 
is 
    l_vc_arr2   apex_application_global.vc_arr2; 
    l_contacts  varchar2(32000); 
begin 
 
    select contact_name 
        bulk collect 
        into l_vc_arr2 
        from contacts 
    where cust_id = p_cust_id 
        order by contact_name; 
 
    l_contacts :=  apex_util.table_to_string ( 
                       p_table => l_vc_arr2, 
                       p_string => ', '); 
 
   return l_contacts; 
 
end get_contacts;

UNEXPIRE_END_USER_ACCOUNT Procedure

Makes expired end users accounts and the associated passwords usable, enabling a end user to log in to developed applications.

Syntax

APEX_UTIL.UNEXPIRE_END_USER_ACCOUNT (
    p_user_name IN VARCHAR2);

Parameters

Table 21-106 describes the parameters available in the UNEXPIRE_END_USER_ACCOUNT procedure.

Table 21-106 UNEXPIRE_END_USER_ACCOUNT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the UNEXPIRE_END_USER_ACCOUNT procedure. Use this procedure to renew (unexpire) an Application Express end user account in the current workspace. This action specifically renews the account for use by end users to authenticate to developed applications and may also renew the account for use by developers or administrators to log in to a workspace.

This procedure must be run by a user having administration privileges in the current workspace.

BEGIN
    FOR c1 IN (SELECT user_name from wwv_flow_users) LOOP
        APEX_UTIL.UNEXPIRE_END_USER_ACCOUNT(p_user_name => c1.user_name);
        htp.p('End User Account:'||c1.user_name||' is now valid.');   
    END LOOP;
END;   

UNEXPIRE_WORKSPACE_ACCOUNT Procedure

Unexpires developer and workspace administrator accounts and the associated passwords, enabling the developer or administrator to log in to a workspace.

Syntax

APEX_UTIL.UNEXPIRE_WORKSPACE_ACCOUNT (
    p_user_name IN VARCHAR2);

Parameters

Table 21-107 describes the parameters available in the UNEXPIRE_WORKSPACE_ACCOUNT procedure.

Table 21-107 UNEXPIRE_WORKSPACE_ACCOUNT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the UNEXPIRE_WORKSPACE_ACCOUNT procedure. Use this procedure to renew (unexpire) an Application Express workspace administrator account in the current workspace. This action specifically renews the account for use by developers or administrators to login to a workspace and may also renew the account for its use by end users to authenticate to developed applications.

This procedure must be run by a user having administration privileges in the current workspace.

BEGIN
    FOR c1 IN (select user_name from wwv_flow_users) loop
        APEX_UTIL.UNEXPIRE_WORKSPACE_ACCOUNT(p_user_name => c1.user_name);
        htp.p('Workspace Account:'||c1.user_name||' is now valid.'); 
    END LOOP;
END;   

UNLOCK_ACCOUNT Procedure

Sets a user account status to unlocked. Must be run by an authenticated workspace administrator in a page request context.

Syntax

APEX_UTIL.UNLOCK_ACCOUNT (
     p_user_name IN VARCHAR2); 

Parameters

Table 21-108 describes the parameters available in the UNLOCK_ACCOUNT procedure.

Table 21-108 UNLOCK_ACCOUNT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the UNLOCK_ACCOUNT procedure. Use this procedure to unlock an Application Express account in the current workspace. This action unlocks the account for use by administrators, developers, and end users.This procedure must be run by a user who has administration privileges in the current workspace

BEGIN
    FOR c1 IN (SELECT user_name from wwv_flow_users) LOOP
        APEX_UTIL.UNLOCK_ACCOUNT(p_user_name => c1.user_name);
        htp.p('End User Account:'||c1.user_name||' is now unlocked.');    
    END LOOP;
END;

URL_ENCODE Function

The following special characters are encoded as follows:

 Special       After 
Characters    Encoding
    %          %25
    +          %2B
  space         +
    .          %2E
    *          %2A
    ?          %3F
    \          %5C
    /          %2F
    >          %3E
    <          %3C
    }          %7B
    {          %7D
    ~          %7E
    [          %5B
    ]          %5D
    '          %60
    ;          %3B
    ?          %3F
    @          %40
    &          %26
    #          %23
    |          %7C
    ^          %5E
    :          %3A
    =          %3D
    $          %24

Syntax

APEX_UTIL.URL_ENCODE (
    p_url   IN    VARCHAR2) 
    RETURN VARCHAR2;

Parameters

Table 21-109 describes the parameters available in the URL_ENCODE function.

Table 21-109 URL_ENCODE Parameters

Parameter Description

p_url

The string to be encoded


Example

The following example shows how to use the URL_ENCODE function.

DECLARE
    l_url  VARCHAR2(255);
BEGIN
    l_url := APEX_UTIL.URL_ENCODE('http://www.myurl.com?id=1&cat=foo');
END;

In this example, the following URL:

http://www.myurl.com?id=1&cat=foo

Would be returned as:

http%3A%2F%2Fwww%2Emyurl%2Ecom%3Fid%3D1%26cat%3Dfoo

WORKSPACE_ACCOUNT_DAYS_LEFT Function

Returns the number of days remaining before the developer or workspace administrator account password expires. This function may be run in a page request context by any authenticated user.

Syntax

APEX_UTIL.WORKSPACE_ACCOUNT_DAYS_LEFT (
    p_user_name IN VARCHAR2)
    RETURN NUMBER;

Parameters

Table 21-110 describes the parameters available in the WORKSPACE_ACCOUNT_DAYS_LEFT procedure.

Table 21-110 WORKSPACE_ACCOUNT_DAYS_LEFT Parameters

Parameter Description

p_user_name

The user name of the user account


Example

The following example shows how to use the WORKSPACE_ACCOUNT_DAYS_LEFT function. It can be used in to find the number of days remaining before an Application Express administrator or developer account in the current workspace expires.

DECLARE
    l_days_left NUMBER;
BEGIN
    FOR c1 IN (SELECT user_name from wwv_flow_users) LOOP
        l_days_left := APEX_UTIL.WORKSPACE_ACCOUNT_DAYS_LEFT(p_user_name => 
c1.user_name);
        htp.p('Workspace Account:'||c1.user_name||' expires in '||l_days_left||' days.');    
    END LOOP;
END;