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:
This procedure changes the password of the currently authenticated user, assuming Application Express user accounts are in use.
APEX_UTIL.CHANGE_CURRENT_USER_PW(
p_new_password IN VARCHAR2);
Table 1-1 describes the parameters available in the CHANGE_CURRENT_USER_PW procedure.
Table 1-1 CHANGE_CURRENT_USER_PW Parameters
| Parameter | Description |
|---|---|
|
|
The new password value in clear text |
BEGIN
APEX_UTIL.CHANGE_CURRENT_USER_PW ('secret99');
END;
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.
APEX_UTIL.CACHE_GET_DATE_OF_PAGE_CACHE (
p_application IN NUMBER,
p_page IN NUMBER,
RETURN DATE;
Table 1-2 describes the parameters available in the CACHE_GET_DATE_OF_PAGE_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.
APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE (
p_application IN NUMBER,
p_page IN NUMBER,
p_region_name IN VARCHAR2)
RETURN DATE;
Table 1-3 describes the parameters available in the CACHE_GET_DATE_OF_REGION_CACHE procedure.
This procedure purges all cached pages and regions for a given application.
APEX_UTIL.CACHE_PURGE_BY_APPLICATION (
p_application IN NUMBER;
Table 1-4 describes the parameters available in the CACHE_PURGE_BY_APPLICATION procedure.
This procedure purges all cached pages and regions for a given application and page.
APEX_UTIL.CACHE_PURGE_BY_PAGE (
p_application IN NUMBER,
p_page IN NUMBER,
p_user_name IN VARCHAR2 DEFAULT NULL);
Table 1-5 describes the parameters available in the CACHE_PURGE_BY_PAGE 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.
APEX_UTIL.CACHE_PURGE_STALE (
p_application IN NUMBER,
Table 1-6 describes the parameters available in the CACHE_PURGE_STALE procedure.
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.
APEX_UTIL.CHANGE_PASSWORD_ON_FIRST_USE (
p_user_name IN VARCHAR2
) RETURN BOOLEAN
;
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 |
|---|---|
|
|
The user name of the user account |
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;
See Also:
"PASSWORD_FIRST_USE_OCCURRED Function"This procedure removes session state for a given application for the current session.
APEX_UTIL.CLEAR_APP_CACHE (
p_app_id IN VARCHAR2 DEFAULT NULL);
Table 1-8 describes the parameters available in the CLEAR_APP_CACHE procedure.
Table 1-8 CLEAR_APP_CACHE Parameters
| Parameter | Description |
|---|---|
|
|
The ID of the application for which session state will be cleared for current session |
BEGIN
APEX_UTIL.CLEAR_APP_CACHE('100');
END;
This procedure removes session state for a given page for the current session.
APEX_UTIL.CLEAR_PAGE_CACHE (
p_page IN NUMBER DEFAULT NULL);
Table 1-9 describes the parameters available in the CLEAR_PAGE_CACHE procedure.
Table 1-9 CLEAR_PAGE_CACHE Parameters
| Parameter | Description |
|---|---|
|
|
The ID of the page in the current application for which session state will be cleared for current session |
BEGIN
APEX_UTIL.CLEAR_PAGE_CACHE('10');
END;
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.
APEX_UTIL.CLEAR_USER_CACHE;
None.
BEGIN
APEX_UTIL.CLEAR_USER_CACHE;
END;
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.
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);
Table 1-10 describes the parameters available in the COUNT_CLICK procedure.
Table 1-10 COUNT_CLICK Parameters
| Parameter | Description |
|---|---|
|
|
The URL to which to redirect |
|
|
A category to classify the click |
|
|
Secondary ID to associate with the click (optional) |
|
|
The application user ID (optional) |
|
|
The workspace associated with the application (optional) |
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.
Counts the number of expired regions.
APEX_UTIL.COUNT_STALE_REGIONS (
p_application IN NUMBER,
RETURN NUMBER;
Table 1-11 describes the parameters available in COUNT_STALE_REGIONS.
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.
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);
Table 1-12 describes the parameters available in the CREATE_USER procedure.
Table 1-12 CREATE_USER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
Numeric primary key of user account |
|
|
Alphanumeric name used for login |
|
|
Informational |
|
|
Informational |
|
|
Informational |
|
|
Email address |
|
|
Clear text password |
|
|
Colon separated list of numeric group IDs |
|
|
Arbitrary text accessible with an API |
BEGIN
APEX_UTIL.CREATE_USER(
P_USER_NAME => 'NEWUSER1',
P_WEB_PASSWORD => 'secret99');
END;
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.
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);
Table 1-13 describes the parameters available in the CREATE_USER_GROUP procedure.
Table 1-13 CREATE_USER_GROUP Parameters
| Parameter | Description |
|---|---|
|
|
Primary key of group |
|
|
Arbitrary name |
|
|
Workspace ID |
|
|
Descriptive text |
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;
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.
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;
Table 1-14 describes the parameters available in the CURRENT_USER_IN_GROUP function.
Table 1-14 CURRENT_USER_IN_GROUP Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the name of an existing group in the workspace |
|
|
Identifies the numeric ID of an existing group in the workspace |
DECLARE VAL BOOLEAN; BEGIN VAL := APEX_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers'); END;
This procedure enables a user account record to be altered. To execute this procedure, the current user must have administrative privileges in the workspace.
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');
Table 1-15 describes the parameters available in the EDIT_USER procedure.
Table 1-15 EDIT_USER Parameters
| Parameter | Description |
|---|---|
|
|
Numeric primary key of the user account |
|
|
Alphanumeric name used for login |
|
|
Informational |
|
|
Informational |
|
|
Clear text password |
|
|
Unused |
|
|
Unused |
|
|
Unused |
|
|
A list of schemas assigned to the user's workspace to which the user is restricted |
|
|
Unused |
|
|
A database schema assigned to the user's workspace, used by default for browsing |
|
|
Colon-separated list of numeric group IDs |
|
|
Colon-separated list of developer privileges (only ADMIN: has meaning to Oracle Application Express) |
|
|
Informational |
|
|
Date password was last updated. |
|
|
'Y' or 'N' indicating if account is locked or unlocked. |
|
|
Number of consecutive login failures that have occurred. |
|
|
'Y' or 'N' to indicate whether password must be changed on first use. |
|
|
'Y' or 'N' to indicate whether login has occurred since password change |
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.
APEX_UTIL.END_USER_ACCOUNT_DAYS_LEFT (
p_user_name IN VARCHAR2
)RETURN NUMBER
;
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 |
|---|---|
|
|
The user name of the user account |
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;
Expires the login account for use as a workspace end user. Must be run by an authenticated workspace administrator in a page request context.
APEX_UTIL.EXPIRE_END_USER_ACCOUNT (
p_user_name IN VARCHAR2
);
Table 1-18 describes the parameters available in the EXPIRE_END_USER_ACCOUNT procedure.
Table 1-17 EXPIRE_END_USER_ACCOUNT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
Expires developer or workspace administrator login accounts. Must be run by an authenticated workspace administrator in a page request context.
APEX_UTIL.EXPIRE_WORKSPACE_ACCOUNT (
p_user_name IN VARCHAR2
);
Table 1-18 describes the parameters available in the EXPIRE_WORKSPACE_ACCOUNT procedure.
Table 1-18 EXPIRE_WORKSPACE_ACCOUNT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
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.
APEX_UTIL.EXPORT_USERS(
p_export_format in VARCHAR2 DEFAULT 'UNIX')
Table 1-19 describes the parameters available in the EXPORT_USERS procedure.
Table 1-19 EXPORT_USERS Parameters
| Parameter | Description |
|---|---|
|
|
Indicates how rows in the export file will be formatted. Specify |
BEGIN APEX_UTIL.EXPORT_USERS; END;
This function fetches session state for the current or specified application in the current or specified session.
APEX_UTIL.FETCH_APP_ITEM(
p_item IN VARCHAR2,
p_app IN NUMBER DEFAULT NULL,
p_session IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
Table 1-20 describes the parameters available in the FETCH_APP_ITEM function.
Table 1-20 FETCH_APP_ITEM Parameters
| Parameter | Description |
|---|---|
|
|
The name of an application-level item (not a page item) whose current value is to be fetched |
|
|
The ID of the application that owns the item (leave null for the current application) |
|
|
The session ID from which to obtain the value (leave null for the current session) |
DECLARE VAL VARCHAR2(30); BEGIN VAL := APEX_UTIL.FETCH_APP_ITEM (p_item=>'F300_NAME',p_app=>300); END;
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:
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 );
Table 1-21 describes the parameters available in the FETCH_USER procedure.
Table 1-21 Fetch_User Parameters Signature 1
| Parameter | Description |
|---|---|
|
|
Numeric primary key of the user account |
|
|
The name of the workspace |
|
|
Alphanumeric name used for login |
|
|
Informational |
|
|
Informational |
|
|
Obfuscated account password |
|
|
Email address |
|
|
Unused |
|
|
Unused |
|
|
Unused |
|
|
A list of schemas assigned to the user's workspace to which user is restricted |
|
|
Unused |
|
|
A database schema assigned to the user's workspace, used by default for browsing |
|
|
List of groups of which user is a member |
|
|
Unused |
|
|
Informational |
Fetch_user Procedure Signature 2:
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 );
Table 1-22 describes the parameters available in the FETCH_USER procedure.
Table 1-22 Fetch_User Parameters Signature 2
| Parameter | Description |
|---|---|
|
|
Numeric primary key of the user account |
|
|
Alphanumeric name used for login |
|
|
Informational |
|
|
Informational |
|
|
Obfuscated account password |
|
|
Email address |
|
|
List of groups of which user is a member |
|
|
Unused |
|
|
Informational |
FETCH_USER Procedure Signature 3
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);
Table 1-23 describes the parameters available in the FETCH_USER procedure.
Table 1-23 Fetch_User Parameters Signature 3
| Parameter | Description |
|---|---|
|
|
Numeric primary key of the user account |
|
|
The name of the workspace |
|
|
Alphanumeric name used for login |
|
|
Informational |
|
|
Informational |
|
|
Obfuscated account password |
|
|
Email address |
|
|
Unused |
|
|
Unused |
|
|
Unused |
|
|
A list of schemas assigned to the user's workspace to which user is restricted |
|
|
Unused |
|
|
A database schema assigned to the user's workspace, used by default for browsing |
|
|
List of groups of which user is a member |
|
|
Unused |
|
|
Informational |
|
|
Date account password was last reset |
|
|
Locked/Unlocked indicator |
|
|
Counter for consecutive login failures |
|
|
Setting to force password change on first use |
|
|
Indicates whether login with password occurred |
This function returns the numeric security group ID of the named workspace.
APEX_UTIL.FIND_SECURITY_GROUP_ID(
p_workspace IN VARCHAR2
RETURN NUMBER;
Table 1-24 describes the parameters available in the FIND_SECURITY_GROUP_ID function.
Table 1-24 FIND_SECURITY_GROUP_ID Parameters
| Parameter | Description |
|---|---|
|
|
The name of the workspace |
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.FIND_SECURITY_GROUP_ID (p_workspace=>'DEMOS'); END;
This function returns the workspace name associated with a security group ID.
APEX_UTIL.FIND_WORKSPACE(
p_security_group_id IN VARCHAR2)
RETURN VARCHAR2;
Table 1-25 describes the parameters available in the FIND_WORKSPACE function.
Table 1-25 FIND_WORKSPACE Parameters
| Parameter | Description |
|---|---|
|
|
The security group ID of a workspace |
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.FIND_WORKSPACE (p_security_group_id =>'20'); END;
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.
APEX_UTIL.GET_ACCOUNT_LOCKED_STATUS (
p_user_name IN VARCHAR2
) return boolean
;
Table 1-26 describes the parameters available in the GET_ACCOUNT_LOCKED_STATUS function.
Table 1-26 GET_ACCOUNT_LOCKED_STATUS Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
This function returns the value of one of the attribute values (1 through 10) of a named user in the Application Express accounts table.
APEX_UTIL.GET_ATTRIBUTE(
p_username IN VARCHAR2,
p_attribute_number IN NUMBER)
RETURN VARCHAR2;
Table 1-27 describes the parameters available in the GET_ATTRIBUTE function.
Table 1-27 GET_ATTRIBUTE Parameters
| Parameter | Description |
|---|---|
|
|
User name in the account. |
|
|
Number of attributes in the user record (1 through 10) |
DECLARE VAL VARCHAR2(30);
BEGIN
VAL := APEX_UTIL.GET_ATTRIBUTE (
p_username => 'FRANK',
p_attribute_number => 1);
END;
Use this function to retrieve the authentication result of the current session. Any authenticated user can call this function in a page request context.
APEX_UTIL.GET_AUTHENTICATION_RESULT
RETURN NUMBER
;
None.
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);
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.
See Also:
"About BLOB Support in Forms and Reports" in Oracle Application Express Application Builder User's Guide
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
;
Table 1-28 describes the parameters available in GET_BLOB_FILE_SRC function.
Table 1-28 GET_BLOB_FILE_SRC Parameters
| Parameter | Description |
|---|---|
|
|
Name of valid application page ITEM that with type FILE that contains the source type of DB column. |
|
|
Value of primary key column 1. |
|
|
Value of primary key column 2. |
|
|
Specify |
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.
This function returns the numeric user ID of the current user.
APEX_UTIL.GET_CURRENT_USER_ID RETURN NUMBER;
None.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.GET_CURRENT_USER_ID; END;
This function returns the default schema name associated with the current user.
APEX_UTIL.GET_DEFAULT_SCHEMA RETURN VARCHAR2;
None.
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL. GET_DEFAULT_SCHEMA; END;
This function returns the email address associated with the named user.
APEX_UTIL.GET_EMAIL( p_username IN VARCHAR2); RETURN VARCHAR2;
Table 1-29 describes the parameters available in GET_EMAIL function.
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_EMAIL(p_username => 'FRANK'); END;
This procedure downloads files from the Oracle Application Express file repository.
APEX_UTIL.GET_FILE (
p_file_id IN VARCHAR2,
p_mime_type IN VARCHAR2 DEFAULT NULL,
p_inline IN VARCHAR2 DEFAULT 'NO');
Table 1-30 describes the parameters available in GET_FILE procedure.
Table 1-30 GET_FILE Parameters
| Parameter | Description |
|---|---|
|
|
ID in
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;
|
|
|
Mime type of the file to download |
|
|
Valid values include |
BEGIN
APEX_UTIL.GET_FILE(
p_file_id => '8675309',
p_mime_type => 'text/xml',
p_inline => 'YES');
END;
This function obtains the primary key of a file in the Oracle Application Express file repository.
APEX_UTIL.GET_FILE_ID (
p_fname IN VARCHAR2)
RETURN NUMBER;
Table 1-31 describes the parameters available in GET_FILE_ID function.
Table 1-31 GET_FILE_ID Parameters
| Parameter | Description |
|---|---|
|
|
The NAME in |
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;
This function returns the FIRST_NAME field stored in the named user account record.
APEX_UTIL.GET_FIRST_NAME p_username IN VARCHAR2) RETURN VARCHAR2;
Table 1-32 describes the parameters available in GET_FIRST_NAME function.
Table 1-32 GET_FIRST_NAME Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the user name in the account |
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_FIRST_NAME(p_username => 'FRANK'); END;
This function returns a comma then a space separated list of group names to which the named user is a member.
APEX_UTIL.GET_GROUPS_USER_BELONGS_TO( p_username IN VARCHAR2); RETURN VARCHAR2;
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 |
|---|---|
|
|
Identifies the user name in the account |
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'FRANK'); END;
This function returns the numeric ID of a named group in the workspace.
APEX_UTIL.GET_GROUP_ID( p_group_name) RETURN VARCHAR2;
Table 1-34 describes the parameters available in GET_GROUP_ID function.
Table 1-34 GET_GROUP_ID Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the user name in the account |
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.GET_GROUP_ID(p_group_name => 'Managers'); END;
This function returns the name of a group identified by a numeric ID.
APEX_UTIL.GET_GROUP_NAME( p_group_id) RETURN NUMBER;
Table 1-35 describes the parameters available in GET_GROUP_NAME function.
Table 1-35 GET_GROUP_NAME Parameters
| Parameter | Description |
|---|---|
|
|
Identifies a numeric ID of a group in the workspace |
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_GROUP_NAME(p_group_id => 8922003); END;
This function returns the LAST_NAME field stored in the named user account record.
APEX_UTIL.GET_LAST_NAME( p_username IN VARCHAR2) RETURN VARCHAR2;
Table 1-36 describes the parameters available in GET_LAST_NAME function.
Table 1-36 GET_LAST_NAME Parameters
| Parameter | Description |
|---|---|
|
|
The user name in the user account record |
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_LAST_NAME(p_username => 'FRANK'); END;
This function returns the user name of a user account identified by a numeric ID.
APEX_UTIL.GET_USERNAME( p_userid) RETURN VARCHAR2;
Table 1-37 describes the parameters available in GET_USERNAME function.
Table 1-37 GET_USERNAME Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the numeric ID of a user account in the workspace |
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_USERNAME(p_userid => 228922003); END;
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.
APEX_UTIL.GET_NUMERIC_SESSION_STATE (
p_item IN VARCHAR2)
RETURN NUMBER;
Table 1-38 describes the parameters available in GET_NUMERIC_SESSION_STATE function.
Table 1-38 GET_NUMERIC_SESSION_STATE Parameters
| Parameter | Description |
|---|---|
|
|
The case insensitive name of the item for which you want to have the session state fetched |
DECLARE
l_item_value Number;
BEGIN
l_item_value := APEX_UTIL.GET_NUMERIC_SESSION_STATE('my_item');
END;
This function retrieves the value of a previously saved preference for a given user.
APEX_UTIL.GET_PREFERENCE (
p_preference IN VARCHAR2 DEFAULT NULL,
p_user IN VARCHAR2 DEFAULT V('USER'))
RETURN VARCHAR2;
Table 1-39 describes the parameters available in the GET_PREFERENCE function.
Table 1-39 GET_PREFERENCE Parameters
| Parameter | Description |
|---|---|
|
|
Name of the preference to retrieve the value |
|
|
Value of the preference |
|
|
User for whom the preference is being retrieved |
DECLARE
l_default_view VARCHAR2(255);
BEGIN
l_default_view := APEX_UTIL.GET_PREFERENCE(
p_preference => 'default_view',
p_user => :APP_USER);
END;
This function returns a document as BLOB using XML based report data and RTF or XSL-FO based report layout.
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;
Table 1-40 describes the parameters available in the GET_PRINT_DOCUMENT function.
Table 1-40 GET_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
XML based report data |
|
|
Report layout in XSL-FO or RTF format |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
This function returns a document as BLOB using pre-defined report query and pre-defined report layout.
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;
Table 1-41 describes the parameters available in the GET_PRINT_DOCUMENT function.
Table 1-41 GET_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
Defines the application ID of the report query |
|
|
Name of the report layout (stored under application's Shared Components) |
|
|
Name of the report query (stored under application's shared components) |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
This function returns a document as BLOB using a pre-defined report query and RTF or XSL-FO based report layout.
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;
Table 1-42 describes the parameters available in the GET_PRINT_DOCUMENT function.
Table 1-42 GET_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
Defines the application ID of the report query |
|
|
Name of the report query (stored under application's shared components) |
|
|
Defines the report layout in XSL-FO or RTF format |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
This function returns a document as BLOB using XML based report data and RTF or XSL-FO based report layout.
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;
Table 1-43 describes the parameters available in the GET_PRINT_DOCUMENT function.
Table 1-43 GET_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
XML based report data, must be encoded in UTF-8 |
|
|
Report layout in XSL-FO or RTF format |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences |
This procedure initiates the download of a print document using XML based report data and RTF or XSL-FO based report layout.
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;
Table 1-44 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.
Table 1-44 DOWNLOAD_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
Defines the filename of the print document |
|
|
Specifies whether to download the print document or display inline ("attachment", "inline") |
|
|
XML based report data |
|
|
Report layout in XSL-FO or RTF format |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
This procedure initiates the download of a print document using pre-defined report query and RTF and XSL-FO based report layout.
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;
Table 1-45 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.
Table 1-45 DOWNLOAD_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
Defines the filename of the print document |
|
|
Specifies whether to download the print document or display inline ("attachment", "inline") |
|
|
Defines the application ID of the report query |
|
|
Name of the report query (stored under application's Shared Components) |
|
|
Report layout in XSL-FO or RTF format |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
This procedure initiates the download of a print document using pre-defined report query and pre-defined report layout.
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;
Table 1-46 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.
Table 1-46 DOWNLOAD_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
Defines the filename of the print document |
|
|
Specifies whether to download the print document or display inline ("attachment", "inline") |
|
|
Defines the application ID of the report query |
|
|
Name of the report query (stored under application's Shared Components) |
|
|
Name of the report layout (stored under application's Shared Components) |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
This procedure initiates the download of a print document using XML based report data and RTF or XSL-FO based report layout.
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;
Table 1-46 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT function.
Table 1-47 DOWNLOAD_PRINT_DOCUMENT Parameters
| Parameter | Description |
|---|---|
|
|
Defines the filename of the print document |
|
|
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 |
|
|
Report layout in XSL-FO or RTF format |
|
|
Defines the report layout type, that is "xsl-fo" or "rtf" |
|
|
Defines the document format, that is "pdf", "rtf", "xls", "htm", or "xml" |
|
|
URL of the print server. If not specified, the print server will be derived from preferences. |
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.
APEX_UTIL.GET_SESSION_STATE (
p_item IN VARCHAR2)
RETURN VARCHAR2;
Table 1-48 describes the parameters available in GET_SESSION_STATE function.
Table 1-48 GET_SESSION_STATE Parameters
| Parameter | Description |
|---|---|
|
|
The case insensitive name of the item for which you want to have the session state fetched |
DECLARE
l_item_value VARCHAR2(255);
BEGIN
l_item_value := APEX_UTIL.GET_SESSION_STATE('my_item');
END;
This function returns the numeric ID of a named user in the workspace.
APEX_UTIL.GET_USER_ID( p_username) RETURN VARCHAR2;
Table 1-49 describes the parameters available in GET_USER_ID function.
Table 1-49 GET_USER_ID Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the name of a user in the workspace |
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.GET_USER_ID(p_username => 'Managers'); END;
This function returns the DEVELOPER_ROLE field stored in the named user account record.
APEX_UTIL.GET_USER_ROLES( p_username IN VARCHAR2); RETURN VARCHAR2;
Table 1-50 describes the parameters available in GET_USER_ROLES function.
Table 1-50 GET_USER_ROLES Parameters
| Parameter | Description |
|---|---|
|
|
Identifies a user name in the account |
DECLARE VAL VARCHAR2; BEGIN VAL := APEX_UTIL.GET_USER_ROLES(p_username=>'FRANK'); END;
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.
APEX_UTIL.IS_LOGIN_PASSWORD_VALID( p_username IN VARCHAR2, p_password IN VARCHAR2); RETURN BOOLEAN;
Table 1-51 describes the parameters available in the IS_LOGIN_PASSWORD_VALID function.
Table 1-51 IS_LOGIN_PASSWORD_VALID Parameters
| Parameter | Description |
|---|---|
|
|
User name in account |
|
|
Password to be compared with password stored in the account |
DECLARE VAL BOOLEAN;
BEGIN
VAL := APEX_UTIL. IS_LOGIN_PASSWORD_VALID (
p_username=>'FRANK'
p_password=>'tiger');
END;
This function returns a Boolean result based on whether the named user account is unique in the workspace.
APEX_UTIL.IS_USERNAME_UNIQUE( p_username IN VARCHAR2) RETURN BOOLEAN;
Table 1-52 describes the parameters available in IS_USERNAME_UNIQUE function.
Table 1-52 IS_USERNAME_UNIQUE Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the user name to be tested |
DECLARE VAL BOOLEAN;
BEGIN
VAL := APEX_UTIL.IS_USERNAME_UNIQUE(
p_username=>'FRANK');
END;
This function gets the value of the package variable (wwv_flow_utilities.g_val_num) set by APEX_UTIL.SAVEKEY_NUM.
APEX_UTIL.KEYVAL_NUM;
Table 1-53 describes the parameters available in KEYVAL_NUM function.
DECLARE
VAL BOOLEAN;
BEGIN
VAL := APEX_UTIL.KEYVAL_NUM;
END;
See Also:
"SAVEKEY_NUM Function"This function gets the value of the package variable (wwv_flow_utilities.g_val_vc2) set by APEX_UTIL.SAVEKEY_VC2.
APEX_UTIL.KEYVAL_VC2;
p_val is the VARCHAR2 value previously saved.
DECLARE
VAL VARCHAR2(4000);
BEGIN
VAL := APEX_UTIL.KEYVAL_VC2;
END;
See Also:
"SAVEKEY_VC2 Function"Sets a user account status to locked. Must be run by an authenticated workspace administrator in the context of a page request.
APEX_UTIL.LOCK_ACCOUNT (
p_user_name IN VARCHAR2
);
Table 1-54 describes the parameters available in the LOCK_ACCOUNT procedure.
Table 1-54 LOCK_ACCOUNT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
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.
APEX_UTIL.PASSWORD_FIRST_USE_OCCURRED (
p_user_name IN VARCHAR2
) RETURN BOOLEAN
;
Table 1-55 describes the parameters available in the PASSWORD_FIRST_USE_OCCURRED procedure.
Table 1-55 PASSWORD_FIRST_USE_OCCURRED Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
See Also:
"CHANGE_PASSWORD_ON_FIRST_USE 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:
ThePREPARE_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.APEX_UTIL.PREPARE_URL ( p_url IN VARCHAR2, p_url_charset IN VARCHAR2 default null, p_checksum_type IN VARCHAR2 default null) RETURN VARCHAR2;
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, |
|
p_checksum type |
Null or any of the following six values, |
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;
Given the name of a security scheme, this function determines if the current user passes the security check.
APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION (
p_security_scheme IN VARCHAR2)
RETURN BOOLEAN;
Table 1-57 describes the parameters available in PUBLIC_CHECK_AUTHORIZATION function.
Table 1-57 PUBLIC_CHECK_AUTHORIZATION Parameters
| Parameter | Description |
|---|---|
|
|
The name of the security scheme that determines if the user passes the security check |
DECLARE
l_check_security BOOLEAN;
BEGIN
l_check_security := APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION('my_auth_scheme');
END;
Deletes all cached regions for an application.
APEX_UTIL.PURGE_REGIONS_BY_APP (
p_application IN NUMBER,
Table 1-58 describes the parameters available in PURGE_REGIONS_BY_APP.
Table 1-58 PURGE_REGIONS_BY_APP Parameters
| Parameter | Description |
|---|---|
|
|
The identification number (ID) of the application. |
APEX_UTILITIES.PURGE_REGIONS_BY_APP(p_application=>123);
Deletes all cached values for a region.
APEX_UTIL.PURGE_REGIONS_BY_ID (
p_application IN NUMBER,
p_region_id IN NUMBER);
Table 1-59 describes the parameters available in PURGE_REGIONS_BY_ID.
Deletes all cached regions identified by the application name and page number.
APEX_UTIL.PURGE_REGIONS_BY_NAME (
p_application IN NUMBER,
p_page IN NUMBER,
p_region_name IN VARCHAR2);
Table 1-60 describes the parameters available in PURGE_REGIONS_BY_NAME.
Deletes all cached regions by application and page.
APEX_UTIL.PURGE_REGIONS_BY_PAGE (
p_application IN NUMBER,
p_page IN NUMBER);
Table 1-61 describes the parameters available in PURGE_REGIONS_BY_PAGE.
Deletes all cached regions that have expired or are no longer useful.
APEX_UTIL.PURGE_STALE_REGIONS (
p_application IN NUMBER,
Table 1-62 describes the parameters available in PURGE_STALE_REGIONS.
This function removes the preference for the supplied user.
APEX_UTIL.REMOVE_PREFERENCE(
p_preference IN VARCHAR2 DEFAULT NULL,
p_user IN VARCHAR2 DEFAULT V('USER'));
Table 1-63 describes the parameters available in the REMOVE_PREFERENCE procedure.
Table 1-63 REMOVE_PREFERENCE Parameters
| Parameter | Description |
|---|---|
|
|
Name of the preference to remove |
|
|
User for whom the preference is defined |
BEGIN
APEX_UTIL.REMOVE_PREFERENCE(
p_preference => 'default_view',
p_user => :APP_USER);
END;
This procedure removes the user's column heading sorting preference value.
APEX_UTIL.REMOVE_SORT_PREFERENCES (
p_user IN VARCHAR2 DEFAULT V('USER'));
Table 1-64 describes the parameters available in REMOVE_SORT_PREFERENCES function.
Table 1-64 REMOVE_SORT_PREFERENCES Parameters
| Parameter | Description |
|---|---|
|
|
Identifies the user for whom sorting preferences will be removed |
BEGIN
APEX_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER);
END;
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.
APEX_UTIL.REMOVE_USER(
p_user_id IN NUMBER,
p_user_name IN VARCHAR2);
Table 1-65 describes the parameters available in the REMOVE_USER procedure.
Table 1-65 REMOVE_USER Parameters
| Parameter | Description |
|---|---|
|
|
The numeric primary key of the user account record |
|
|
The user name of the user account |
BEGIN APEX_UTIL.REMOVE_USER(p_user_id=>'99997'); END; BEGIN APEX_UTIL.REMOVE_USER(p_user_name => 'FRANK'); END;
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.
APEX_UTIL.RESET_AUTHORIZATIONS;
None.
BEGIN APEX_UTIL.RESET_AUTHORIZATIONS; END;
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.
APEX_UTIL.RESET_PW(
p_user IN VARCHAR2,
p_msg IN VARCHAR2);
Table 1-66 describes the parameters available in the RESET_PW procedure.
Table 1-66 RESET_PW Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
|
|
Message text to be mailed to a user |
BEGIN
APEX_UTIL.RESET_PW(
p_user => 'FRANK',
p_msg => 'Contact help desk at 555-1212 with questions');
END;
This function sets a package variable (wwv_flow_utilities.g_val_num) so that it can be retrieved using the function KEYVAL_NUM.
APEX_UTIL.SAVEKEY_NUM(
p_val IN NUMBER);
Table 1-67 describes the parameters available in the SAVEKEY_NUM procedure.
DECLARE
VAL NUMBER;
BEGIN
VAL := APEX_UTIL.SAVEKEY_NUM(
p_val => 10);
END;
See Also:
"KEYVAL_NUM Function"This function sets a package variable (wwv_flow_utilities.g_val_vc2) so that it can be retrieved using the function KEYVAL_VC2.
APEX_UTIL.SAVEKEY_VC2
p_val IN VARCHAR2);
Table 1-68 describes the parameters available in the SAVEKEY_VC2 function.
DECLARE
VAL VARCHAR2(4000);
BEGIN
VAL := APEX_UTIL.SAVEKEY_VC2(
p_val => 'XXX');
END;
See Also:
"KEYVAL_VC2 Function"This procedure sets the value of one of the attribute values (1 through 10) of a user in the Application Express accounts table.
APEX_UTIL.SET_ATTRIBUTE(
p_userid IN NUMBER,
p_attribute_number IN NUMBER,
p_attribute_value IN VARCHAR2);
Table 1-69 describes the parameters available in the SET_ATTRIBUTE procedure.
Table 1-69 SET_ATTRIBUTE Parameters
| Parameter | Description |
|---|---|
|
|
The numeric ID of the user account |
|
|
Attribute number in the user record (1 through 10) |
|
|
Value of the attribute located by |
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;
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.
See Also:
"Monitoring Activity within a Workspace" in Oracle Application Express Administration Guide
APEX_UTIL.SET_AUTHENTICATION_RESULT(
p_code IN NUMBER
);
Table 1-18 describes the parameters available in the SET_AUTHENTICATION_RESULT procedure.
Table 1-70 SET_AUTHENTICATION_RESULT Parameters
| Parameter | Description |
|---|---|
|
|
Any numeric value the developer chooses. After this value is set in the session using this procedure, it can be retrieved using the |
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;
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.
See Also:
"Monitoring Activity within a Workspace" in Oracle Application Express Administration Guide
APEX_UTIL.SET_CUSTOM_AUTH_STATUS(
p_status IN VARCHAR2
);
Table 1-71 describes the parameters available in the SET_CUSTOM_AUTH_STATUS procedure.
Table 1-71 SET_CUSTOM_AUTH_STATUS Parameters
| Parameter | Description |
|---|---|
|
|
Any text the developer chooses to denote the result of the authentication attempt (up to 4000 characters). |
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;
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.
APEX_UTIL.SET_EMAIL(
p_userid IN NUMBER,
p_email IN VARCHAR2);
Table 1-72 describes the parameters available in the SET_EMAIL procedure.
Table 1-72 SET_EMAIL Parameters
| Parameter | Description |
|---|---|
|
|
The numeric ID of the user account |
|
|
The email address to be saved in user account |
BEGIN
APEX_UTIL.SET_EMAIL(
p_userid => '888883232',
P_email => 'frank.scott@oracle.com');
END;
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.
APEX_UTIL.SET_FIRST_NAME(
p_userid IN NUMBER,
p_first_name IN VARCHAR2);
Table 1-73 describes the parameters available in the SET_FIRST_NAME procedure.
Table 1-73 SET_FIRST_NAME Parameters
| Parameter | Description |
|---|---|
|
|
The numeric ID of the user account |
|
|
|
BEGIN
APEX_UTIL.SET_FIRST_NAME(
p_userid => '888883232',
P_first_name => 'FRANK');
END;
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.
APEX_UTIL.SET_LAST_NAME(
p_userid IN NUMBER,
p_last_name IN VARCHAR2);
Table 1-74 describes the parameters available in the SET_LAST_NAME procedure.
Table 1-74 SET_LAST_NAME Parameters
| Parameter | Description |
|---|---|
|
|
The numeric ID of the user account |
|
|
|
BEGIN
APEX_UTIL.SET_LAST_NAME(
p_userid => '888883232',
p_last_name => 'SMITH');
END;
This procedure sets a preference that will persist beyond the user's current session.
APEX_UTIL.SET_PREFERENCE (
p_preference IN VARCHAR2 DEFAULT NULL,
p_value IN VARCHAR2 DEFAULT NULL,
p_user IN VARCHAR2 DEFAULT NULL);
Table 1-75 describes the parameters available in the SET_PREFERENCE procedure.
Table 1-75 SET_PREFERENCE Parameters
| Parameter | Description |
|---|---|
|
|
Name of the preference (case-sensitive) |
|
|
Value of the preference |
|
|
User for whom the preference is being set |
BEGIN
APEX_UTIL.SET_PREFERENCE(
p_preference => 'default_view',
p_value => 'WEEKLY',
p_user => :APP_USER);
END;
This procedure sets session state for a current Oracle Application Express session.
APEX_UTIL.SET_SESSION_STATE (
p_name IN VARCHAR2 DEFAULT NULL,
p_value IN VARCHAR2 DEFAULT NULL);
Table 1-76 describes the parameters available in the SET_SESSION_STATE procedure.
Table 1-76 SET_SESSION_STATE Parameters
| Parameter | Description |
|---|---|
|
|
Name of the application-level or page-level item for which you are setting sessions state |
|
|
Value of session state to set |
BEGIN
APEX_UTIL.SET_SESSION_STATE('my_item','myvalue');
END;
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.
APEX_UTIL.SET_USERNAME( p_userid IN NUMBER, p_username IN VARCHAR2);
Table 1-77 describes the parameters available in the SET_USERNAME procedure.
Table 1-77 SET_USERNAME Parameters
| Parameter | Description |
|---|---|
|
|
The numeric ID of the user account |
|
|
|
BEGIN
APEX_UTIL.SET_USERNAME(
p_userid => '888883232',
P_username => 'USER-XRAY');
END;
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.
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) ;
Table 1-78 describes the parameters available in the STRONG_PASSWORD_CHECK procedure.
Table 1-78 STRONG_PASSWORD_CHECK Parameters
| Parameter | Description |
|---|---|
|
|
Username that identifies the account in the current workspace |
|
|
Password to be checked against password strength rules |
|
|
Current password for the account. Used only to enforce "new password must differ from old" rule |
|
|
Current workspace name, used only to enforce "password must not contain workspace name" rule |
|
|
Pass |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
|
|
Result returns |
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;
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.
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
;
Table 1-79 describes the parameters available in the STRONG_PASSWORD_VALIDATION function.
Table 1-79 STRONG_PASSWORD_VALIDATION Parameters
| Parameter | Description |
|---|---|
|
|
Username that identifies the account in the current workspace |
|
|
Password to be checked against password strength rules |
|
|
Current password for the account. Used only to enforce "new password must differ from old" rule |
|
|
Current workspace name, used only to enforce "password must not contain workspace name" rule |
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;
Given a string, this function returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2. This array is a VARCHAR2(32767) table.
APEX_UTIL.STRING_TO_TABLE (
p_string IN VARCHAR2,
p_separator IN VARCHAR2 DEFAULT ':')
RETURN APEX_APPLICATION_GLOBAL.VC_ARR2;
Table 1-80 describes the parameters available in the STRING_TO_TABLE function.
Table 1-80 STRING_TO_TABLE Parameters
| Parameter | Description |
|---|---|
|
|
String to be converted into a PL/SQL table of type |
|
|
String separator. The default is a colon |
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;
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 (:).
APEX_UTIL.TABLE_TO_STRING (
p_table IN APEX_APPLICATION_GLOBAL.VC_ARR2,
p_string IN VARCHAR2 DEFAULT ':')
RETURN VARCHAR2;
Table 1-81 describes the parameters available in the TABLE_TO_STRING function.
Table 1-81 TABLE_TO_STRING Parameters
| Parameter | Description |
|---|---|
|
|
String separator. Default separator is a colon (:) |
|
|
PL/SQL table that is to be converted into a delimited string |
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;
Makes expired end users accounts and the associated passwords usable, enabling a end user to log in to a workspace.
APEX_UTIL.UNEXPIRE_END_USER_ACCOUNT (
p_user_name IN VARCHAR2
);
Table 1-82 describes the parameters available in the UNEXPIRE_END_USER_ACCOUNT procedure.
Table 1-82 UNEXPIRE_END_USER_ACCOUNT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
See Also:
"EXPIRE_END_USER_ACCOUNT Parameters"Unexpires developer and workspace administrator accounts and the associated passwords, enabling the developer or administrator to log in to a workspace.
APEX_UTIL.UNEXPIRE_WORKSPACE_ACCOUNT (
p_user_name IN VARCHAR2
);
Table 1-83 describes the parameters available in the UNEXPIRE_WORKSPACE_ACCOUNT procedure.
Table 1-83 UNEXPIRE_WORKSPACE_ACCOUNT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
See Also:
"EXPIRE_WORKSPACE_ACCOUNT Procedure" andSets a user account status to unlocked. Must be run by an authenticated workspace administrator in a page request context.
APEX_UTIL.UNLOCK_ACCOUNT (
p_user_name IN VARCHAR2
);
Table 1-84 describes the parameters available in the UNLOCK_ACCOUNT procedure.
Table 1-84 UNLOCK_ACCOUNT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;
This function encodes (into hexadecimal) all special characters that include spaces, question marks, and ampersands.
APEX_UTIL.URL_ENCODE (
p_url IN VARCHAR2)
RETURN VARCHAR2;
Table 1-85 describes the parameters available in 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;
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.
APEX_UTIL.WORKSPACE_ACCOUNT_DAYS_LEFT (
p_user_name IN VARCHAR2
RETURN NUMBER
;
Table 1-86 describes the parameters available in the WORKSPACE_ACCOUNT_DAYS_LEFT procedure.
Table 1-86 WORKSPACE_ACCOUNT_DAYS_LEFT Parameters
| Parameter | Description |
|---|---|
|
|
The user name of the user account |
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;