Skip Headers
Oracle® Application Express API Reference
Release 3.1.2

Part Number E12855-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

1 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, and also to get and set preferences for users.

Topics in this section include:


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 1-1 describes the parameters available in the CHANGE_CURRENT_USER_PW procedure.

Table 1-1 CHANGE_CURRENT_USER_PW Parameters

Parameter Description

p_new_password

The new password value in clear text


Example

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

CACHE_GET_DATE_OF_PAGE_CACHE Procedure

This procedure 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 1-2 describes the parameters available in the CACHE_GET_DATE_OF_PAGE_CACHE procedure.

Table 1-2 CACHE_GET_DATE_OF_PAGE_CACHE Parameters

Parameter Description

p_application

The identification number (ID) of the application.

p_page

The page number (ID).



CACHE_GET_DATE_OF_REGION_CACHE Procedure

This procedure 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 1-3 describes the parameters available in the CACHE_GET_DATE_OF_REGION_CACHE procedure.

Table 1-3 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



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 1-4 describes the parameters available in the CACHE_PURGE_BY_APPLICATION procedure.

Table 1-4 CACHE_PURGE_BY_APPLICATION Parameters

Parameter Description

p_application

The identification number (ID) of the application.



CACHE_PURGE_BY_PAGE Procedure

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

Syntax

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

Parameters

Table 1-5 describes the parameters available in the CACHE_PURGE_BY_PAGE procedure.

Table 1-5 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.



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 will no longer be used, thus removing those unusable pages or regions from the cache.

Syntax

APEX_UTIL.CACHE_PURGE_STALE (
    p_application  IN    NUMBER,

Parameters

Table 1-6 describes the parameters available in the CACHE_PURGE_STALE procedure.

Table 1-6 CACHE_PURGE_STALE Parameters

Parameter Description

p_application

The identification number (ID) of the application.



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. 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 1-7 describes the parameters available in the CHANGE_PASSWORD_ON_FIRST_USE function.

Table 1-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 Application Express user account (workspace administrator, developer, or end user) in the current workspace must 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 1-8 describes the parameters available in the CLEAR_APP_CACHE procedure.

Table 1-8 CLEAR_APP_CACHE Parameters

Parameter Description

p_app_id

The ID of the application for which session state will be cleared for current session


Example

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 1-9 describes the parameters available in the CLEAR_PAGE_CACHE procedure.

Table 1-9 CLEAR_PAGE_CACHE Parameters

Parameter Description

p_page

The ID of the page in the current application for which session state will be cleared for current session


Example

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

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

Table 1-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

BEGIN
htp.p('<a href=APEX_UTIL.COUNT_CLICK?p_url=http://yahoo.com&p_cat=yahoo&p_workspace=NNN> Click</a>'); end; 

Where NNN equals your workspace ID.


COUNT_STALE_REGIONS Function

Counts the number of expired regions.

Syntax

APEX_UTIL.COUNT_STALE_REGIONS (
     p_application IN NUMBER,
RETURN NUMBER;

Parameters

Table 1-11 describes the parameters available in COUNT_STALE_REGIONS.

Table 1-11 COUNT_STALE_REGIONS Parameters

Parameter Description

p_application

The identification number (ID) of the application.



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                      NUMBER                  IN     DEFAULT NULL,
    p_user_name                    VARCHAR2                IN,
    p_first_name                   VARCHAR2                IN     DEFAULT NULL,
    p_last_name                    VARCHAR2                IN     DEFAULT NULL,
    p_description                  VARCHAR2                IN     DEFAULT NULL,
    p_email_address                VARCHAR2                IN     DEFAULT NULL,
    p_web_password                 VARCHAR2                IN,
    p_web_password_format          VARCHAR2                IN     DEFAULT NULL,
    p_group_ids                    VARCHAR2                IN     DEFAULT NULL,
    p_attribute_01                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_02                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_03                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_04                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_05                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_06                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_07                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_08                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_09                 VARCHAR2                IN     DEFAULT NULL,
    p_attribute_10                 VARCHAR2                IN     DEFAULT NULL);

Parameters

Table 1-12 describes the parameters available in the CREATE_USER procedure.

Table 1-12 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_address

Clear text password

p_group_ID

Colon separated list of numeric group IDs

p_attribute_01

...

p_attribute_10

Arbitrary text accessible with an API


Example

BEGIN
APEX_UTIL.CREATE_USER( 
    P_USER_NAME    => 'NEWUSER1',
    P_WEB_PASSWORD => 'secret99'); 
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                       NUMBER                  IN,
    p_group_name               VARCHAR2                IN,
    p_security_group_id        NUMBER                  IN,
    p_group_desc               VARCHAR2                IN);

Parameter

Table 1-13 describes the parameters available in the CREATE_USER_GROUP procedure.

Table 1-13 CREATE_USER_GROUP Parameters

Parameter Description

p_id

Primary key of group

p_group_name

Arbitrary name

p_security_group_id

Workspace ID

p_group_desc

Descriptive text


Example

BEGIN
APEX_UTIL.CREATE_USER_GROUP (
    p_id                => 0 - trigger will assign 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 or not 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 1-14 describes the parameters available in the CURRENT_USER_IN_GROUP function.

Table 1-14 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

DECLARE VAL BOOLEAN;
BEGIN
  VAL := APEX_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers');
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

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

Parameters

Table 1-15 describes the parameters available in the EDIT_USER procedure.

Table 1-15 EDIT_USER Parameters

Parameter Description

p_user_id

Numeric primary key of the user account

p_user_name

Alphanumeric name used for login

p_first_name

Informational

p_last_name

Informational

p_web_password

Clear text password

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_privs

Colon-separated list of developer privileges (only ADMIN: has meaning to Oracle Application Express)

p_description

Informational

p_account_expiry

Date password was last updated.

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.

p_change_password_on_first_use

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

p_first_password_use_occurred

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



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 1-16 describes the parameters available in the END_USER_ACCOUNT_DAYS_LEFT function.

Table 1-16 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 will expire.

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) THEN
    htp.p('End User Account:'||c1.user_name||' will expire 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 1-18 describes the parameters available in the EXPIRE_END_USER_ACCOUNT procedure.

Table 1-17 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 with respect to its use by end users to authenticate to developed applications, but it may also expire the account with respect to 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 1-18 describes the parameters available in the EXPIRE_WORKSPACE_ACCOUNT procedure.

Table 1-18 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 with respect to its use by developers or administrators to log in to a workspace, but it may also expire the account with respect to 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 an 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 1-19 describes the parameters available in the EXPORT_USERS procedure.

Table 1-19 EXPORT_USERS Parameters

Parameter Description

p_export_format

Indicates how rows in the export file will be 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

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 1-20 describes the parameters available in the FETCH_APP_ITEM function.

Table 1-20 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

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

FETCH_USER Procedure

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.

Fetch_user Procedure Signature 1:

Syntax

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

Parameters

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

Table 1-21 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

p_first_name

Informational

p_last_name

Informational

p_web_password

Obfuscated account password

p_email_address

Email address

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

p_groups

List of groups of which user is a member

p_developer_role

Unused

p_description

Informational


Fetch_user Procedure Signature 2:

Syntax

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

Parameters

Table 1-22 describes the parameters available in the FETCH_USER procedure.

Table 1-22 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

p_first_name

Informational

p_last_name

Informational

p_web_password

Obfuscated account password

p_email_address

Email address

p_groups

List of groups of which user is a member

p_developer_role

Unused

p_description

Informational


FETCH_USER Procedure Signature 3

Syntax

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

Parameters

Table 1-23 describes the parameters available in the FETCH_USER procedure.

Table 1-23 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

p_first_name

Informational

p_last_name

Informational

p_web_password

Obfuscated account password

p_email_address

Email address

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

p_groups

List of groups of which user is a member

p_developer_role

Unused

p_description

Informational

p_account_expiry

Date account password was last reset

p_account_locked

Locked/Unlocked indicator Y or N

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



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 1-24 describes the parameters available in the FIND_SECURITY_GROUP_ID function.

Table 1-24 FIND_SECURITY_GROUP_ID Parameters

Parameter Description

p_workspace

The name of the workspace


Example

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 1-25 describes the parameters available in the FIND_WORKSPACE function.

Table 1-25 FIND_WORKSPACE Parameters

Parameter Description

p_security_group_id

The security group ID of a workspace


Example

DECLARE VAL NUMBER;
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 1-26 describes the parameters available in the GET_ACCOUNT_LOCKED_STATUS function.

Table 1-26 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.

Syntax

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

Parameters

Table 1-27 describes the parameters available in the GET_ATTRIBUTE function.

Table 1-27 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

DECLARE VAL VARCHAR2(30);
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

FUNCTION 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 1-28 describes the parameters available in GET_BLOB_FILE_SRC function.

Table 1-28 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_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

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

DECLARE VAL VARCHAR2;
BEGIN
  VAL := APEX_UTIL. GET_DEFAULT_SCHEMA;
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 1-29 describes the parameters available in GET_EMAIL function.

Table 1-29 GET_EMAIL Parameters

Parameter Description

p_username

The user name in the account


Example

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

GET_FILE Procedure

This procedure downloads files from the Oracle Application Express file repository.

Syntax

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

Parameters

Table 1-30 describes the parameters available in GET_FILE procedure.

Table 1-30 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_mime_type => 'text/xml',
              p_inline    => 'YES');  
END;

p_mime_type

Mime type of the file to download

p_inline

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


Example

BEGIN
        APEX_UTIL.GET_FILE(
              p_file_id   => '8675309', 
              p_mime_type => 'text/xml',
              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_fname    IN   VARCHAR2)
RETURN NUMBER;

Parameters

Table 1-31 describes the parameters available in GET_FILE_ID function.

Table 1-31 GET_FILE_ID Parameters

Parameter Description

p_fname

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

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_fname => 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 1-32 describes the parameters available in GET_FIRST_NAME function.

Table 1-32 GET_FIRST_NAME Parameters

Parameter Description

p_username

Identifies the user name in the account


Example

DECLARE VAL VARCHAR2;
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 1-33 describes the parameters available in GET_GROUPS_USER_BELONGS_TO function.

Table 1-33 GET_GROUPS_USER_BELONGS_TO Parameters

Parameter Description

p_username

Identifies the user name in the account


Example

DECLARE VAL VARCHAR2;
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)
RETURN VARCHAR2;

Parameters

Table 1-34 describes the parameters available in GET_GROUP_ID function.

Table 1-34 GET_GROUP_ID Parameters

Parameter Description

p_group_name

Identifies the user name in the account


Example

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)
RETURN NUMBER;

Parameters

Table 1-35 describes the parameters available in GET_GROUP_NAME function.

Table 1-35 GET_GROUP_NAME Parameters

Parameter Description

p_group_id

Identifies a numeric ID of a group in the workspace


Example

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

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 1-36 describes the parameters available in GET_LAST_NAME function.

Table 1-36 GET_LAST_NAME Parameters

Parameter Description

p_username

The user name in the user account record


Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := APEX_UTIL.GET_LAST_NAME(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)
RETURN VARCHAR2;

Parameters

Table 1-37 describes the parameters available in GET_USERNAME function.

Table 1-37 GET_USERNAME Parameters

Parameter Description

p_userid

Identifies the numeric ID of a user account in the workspace


Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := APEX_UTIL.GET_USERNAME(p_userid => 228922003);
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 1-38 describes the parameters available in GET_NUMERIC_SESSION_STATE function.

Table 1-38 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

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 1-39 describes the parameters available in the GET_PREFERENCE function.

Table 1-39 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

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

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 1-40 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 1-40 GET_PRINT_DOCUMENT 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 will be derived from preferences.



GET_PRINT_DOCUMENT Function

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_layout_name  IN VARCHAR2,
    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 1-41 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 1-41 GET_PRINT_DOCUMENT Parameters

Parameter Description

p_application_id

Defines the application ID of the report query

p_report_layout_name

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

p_report_query_name

Name of the report query (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 will be derived from preferences.



GET_PRINT_DOCUMENT Function

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 1-42 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 1-42 GET_PRINT_DOCUMENT 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 will be derived from preferences.



GET_PRINT_DOCUMENT Function

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 1-43 describes the parameters available in the GET_PRINT_DOCUMENT function.

Table 1-43 GET_PRINT_DOCUMENT 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 will be derived from preferences



DOWNLOAD_PRINT_DOCUMENT Procedure

This procedure initiates the download of a print document using XML based report data 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 1-44 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.

Table 1-44 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 will be derived from preferences.



DOWNLOAD_PRINT_DOCUMENT Procedure

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 1-45 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.

Table 1-45 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 will be derived from preferences.



DOWNLOAD_PRINT_DOCUMENT Procedure

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 1-46 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.

Table 1-46 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 will be derived from preferences.



DOWNLOAD_PRINT_DOCUMENT Procedure

This procedure initiates the download of a print document using XML based report data 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_query_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 1-46 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.

Table 1-47 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 will be derived from preferences.



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 1-48 describes the parameters available in GET_SESSION_STATE function.

Table 1-48 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

DECLARE
      l_item_value  VARCHAR2(255);
BEGIN
      l_item_value := APEX_UTIL.GET_SESSION_STATE('my_item');
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)
RETURN VARCHAR2;

Parameters

Table 1-49 describes the parameters available in GET_USER_ID function.

Table 1-49 GET_USER_ID Parameters

Parameter Description

p_username

Identifies the name of a user in the workspace


Example

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

GET_USER_ROLES Function

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

Syntax

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

Parameters

Table 1-50 describes the parameters available in GET_USER_ROLES function.

Table 1-50 GET_USER_ROLES Parameters

Parameter Description

p_username

Identifies a user name in the account


Example

DECLARE VAL VARCHAR2;
BEGIN
  VAL := APEX_UTIL.GET_USER_ROLES(p_username=>'FRANK');
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 1-51 describes the parameters available in the IS_LOGIN_PASSWORD_VALID function.

Table 1-51 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

DECLARE VAL BOOLEAN;
BEGIN
  VAL := APEX_UTIL. IS_LOGIN_PASSWORD_VALID (
             p_username=>'FRANK'
             p_password=>'tiger');
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 1-52 describes the parameters available in IS_USERNAME_UNIQUE function.

Table 1-52 IS_USERNAME_UNIQUE Parameters

Parameter Description

p_username

Identifies the user name to be tested


Example

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;

Parameters

Table 1-53 describes the parameters available in KEYVAL_NUM function.

Table 1-53 KEYVAL_NUM Parameters

Parameter Description

p_val

The numeric value previously saved


Example

DECLARE
VAL BOOLEAN;
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

p_val is the VARCHAR2 value previously saved.

Example

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 1-54 describes the parameters available in the LOCK_ACCOUNT procedure.

Table 1-54 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 1-55 describes the parameters available in the PASSWORD_FIRST_USE_OCCURRED procedure.

Table 1-55 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

Given a ready-to-render f?p relative URL, this function adds a Session State Protection checksum argument (&cs=) if one is required.

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 in order 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 1-56 describes the parameters available in the PREPARE_URL function.

Table 1-56 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

DECLARE
l_url varchar2(2000);
l_session number := v('APP_SESSION');
BEGIN
l_url := 
APEX_UTIL.PREPARE_URL('f?p=100:1:'||l_session||'::NO::P1_ITEM:xyz');
END;

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 1-57 describes the parameters available in PUBLIC_CHECK_AUTHORIZATION function.

Table 1-57 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

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 1-58 describes the parameters available in PURGE_REGIONS_BY_APP.

Table 1-58 PURGE_REGIONS_BY_APP Parameters

Parameter Description

p_application

The identification number (ID) of the application.


Example

APEX_UTILITIES.PURGE_REGIONS_BY_APP(p_application=>123);

PURGE_REGIONS_BY_ID Procedure

Deletes all cached values for a region.

Syntax

APEX_UTIL.PURGE_REGIONS_BY_ID (
     p_application IN NUMBER,
     p_region_id   IN NUMBER);

Parameters

Table 1-59 describes the parameters available in PURGE_REGIONS_BY_ID.

Table 1-59 PURGE_REGIONS_BY_ID Parameters

Parameter Description

p_application

The identification number (ID) of the application.

p_region_id

The identification number of the region for which cached values are deleted.



PURGE_REGIONS_BY_NAME Procedure

Deletes all cached regions identified by the application name and page number.

Syntax

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

Parameters

Table 1-60 describes the parameters available in PURGE_REGIONS_BY_NAME.

Table 1-60 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 to be deleted.



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 1-61 describes the parameters available in PURGE_REGIONS_BY_PAGE.

Table 1-61 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.



PURGE_STALE_REGIONS Procedure

Deletes all cached regions that have expired or are no longer useful.

Syntax

APEX_UTIL.PURGE_STALE_REGIONS (
     p_application IN NUMBER,

Parameters

Table 1-62 describes the parameters available in PURGE_STALE_REGIONS.

Table 1-62 PURGE_STALE_REGIONS Parameters

Parameter Description

p_application

The identification number (ID) of the application.



REMOVE_PREFERENCE Procedure

This function 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 1-63 describes the parameters available in the REMOVE_PREFERENCE procedure.

Table 1-63 REMOVE_PREFERENCE Parameters

Parameter Description

p_preference

Name of the preference to remove

p_user

User for whom the preference is defined


Example

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 1-64 describes the parameters available in REMOVE_SORT_PREFERENCES function.

Table 1-64 REMOVE_SORT_PREFERENCES Parameters

Parameter Description

p_user

Identifies the user for whom sorting preferences will be removed


Example

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 1-65 describes the parameters available in the REMOVE_USER procedure.

Table 1-65 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

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

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 1-66 describes the parameters available in the RESET_PW procedure.

Table 1-66 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

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);

Parameters

Table 1-67 describes the parameters available in the SAVEKEY_NUM procedure.

Table 1-67 SAVEKEY_NUM Parameters

Parameter Description

p_val

The numeric value to be saved


Example

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);

Parameters

Table 1-68 describes the parameters available in the SAVEKEY_VC2 function.

Table 1-68 SAVEKEY_VC2 Parameters

Parameter Description

p_val

The is the VARCHAR2 value to be saved


Example

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 1-69 describes the parameters available in the SET_ATTRIBUTE procedure.

Table 1-69 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

DECLARE VAL VARCHAR2(30); 
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 1-18 describes the parameters available in the SET_AUTHENTICATION_RESULT procedure.

Table 1-70 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_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 1-71 describes the parameters available in the SET_CUSTOM_AUTH_STATUS procedure.

Table 1-71 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_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 1-72 describes the parameters available in the SET_EMAIL procedure.

Table 1-72 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

BEGIN
APEX_UTIL.SET_EMAIL(
    p_userid  => '888883232',
    P_email   => 'frank.scott@oracle.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 1-73 describes the parameters available in the SET_FIRST_NAME procedure.

Table 1-73 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

BEGIN     
APEX_UTIL.SET_FIRST_NAME(
    p_userid       => '888883232',
    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 1-74 describes the parameters available in the SET_LAST_NAME procedure.

Table 1-74 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

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

SET_PREFERENCE Procedure

This procedure sets a preference that will persist 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 1-75 describes the parameters available in the SET_PREFERENCE procedure.

Table 1-75 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

BEGIN
       APEX_UTIL.SET_PREFERENCE(        
             p_preference => 'default_view',
             p_value      => 'WEEKLY',      
             p_user       => :APP_USER); 
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 1-76 describes the parameters available in the SET_SESSION_STATE procedure.

Table 1-76 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

BEGIN
APEX_UTIL.SET_SESSION_STATE('my_item','myvalue');
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 1-77 describes the parameters available in the SET_USERNAME procedure.

Table 1-77 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

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

STRONG_PASSWORD_CHECK Procedure

This procedure returns Boolean OUT values based on whether or not 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)
    ;

Parameters

Table 1-78 describes the parameters available in the STRONG_PASSWORD_CHECK procedure.

Table 1-78 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


Example

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;
BEGIN
    l_username := 'SOMEBODY';
    l_password := 'foo';
    l_old_password := 'foo';
    l_workspace_name := 'XYX_WS';

    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)

    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_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;

END;

STRONG_PASSWORD_VALIDATION Function

This function returns formatted HTML in a VARCHAR2 result based on whether or not 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 1-79 describes the parameters available in the STRONG_PASSWORD_VALIDATION function.

Table 1-79 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

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,

    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_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;

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 1-80 describes the parameters available in the STRING_TO_TABLE function.

Table 1-80 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

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;

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 1-81 describes the parameters available in the TABLE_TO_STRING function.

Table 1-81 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

DECLARE
       l_string     VARCHAR2(255);
       l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
       l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE('One:Two:Three');

       l_string := APEX_UTIL.TABLE_TO_STRING(l_vc_arr2);
END;

UNEXPIRE_END_USER_ACCOUNT Procedure

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

Syntax

APEX_UTIL.UNEXPIRE_END_USER_ACCOUNT (
    p_user_name IN VARCHAR2
    );

Parameters

Table 1-82 describes the parameters available in the UNEXPIRE_END_USER_ACCOUNT procedure.

Table 1-82 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 1-83 describes the parameters available in the UNEXPIRE_WORKSPACE_ACCOUNT procedure.

Table 1-83 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 with respect to 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 1-84 describes the parameters available in the UNLOCK_ACCOUNT procedure.

Table 1-84 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

This function encodes (into hexadecimal) all special characters that include spaces, question marks, and ampersands.

Syntax

APEX_UTIL.URL_ENCODE (
    p_url   IN    VARCHAR2) 
    RETURN VARCHAR2;

Parameters

Table 1-85 describes the parameters available in the URL_ENCODE function.

Table 1-85 URL_ENCODE Parameters

Parameter Description

p_url

The string to be encoded


Example

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

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 1-86 describes the parameters available in the WORKSPACE_ACCOUNT_DAYS_LEFT procedure.

Table 1-86 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) THEN
    htp.p('Workspace Account:'||c1.user_name||' will expire in '||l_days_left||' days.');    
  END LOOP;
END;