The APEX_UTIL
package provides utilities you can use when programming in the Oracle Application Express environment. You can use the APEX_UTIL
package to get and set session state, get files, check authorizations for users, reset different states for users, get and purge cache information and also to get and set preferences for users.
Topics in this section include:
This function returns the date and time a specified application page was cached either for the user issuing the call, or for all users if the page was not set to be cached by user.
APEX_UTIL.CACHE_GET_DATE_OF_PAGE_CACHE ( p_application IN NUMBER, p_page IN NUMBER) RETURN DATE;
Table 1-1 describes the parameters available in the CACHE_GET_DATE_OF_PAGE_CACHE
procedure.
Table 1-1 CACHE_GET_DATE_OF_PAGE_CACHE Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
|
The page number (ID). |
The following example demonstrates how to use the CACHE_GET_DATE_OF_PAGE_CACHE
function to retrieve the cache date and time for page 9 of the currently executing application. If page 9 has been cached, the cache date and time is output using the HTP package. The page could have been cached either by the user issuing the call, or for all users if the page was not to be cached by the user.
DECLARE l_cache_date DATE DEFAULT NULL; BEGIN l_cache_date := APEX_UTIL.CACHE_GET_DATE_OF_PAGE_CACHE( p_application => :APP_ID, p_page => 9); IF l_cache_date IS NOT NULL THEN HTP.P('Cached on ' || TO_CHAR(l_cache_date, 'DD-MON-YY HH24:MI:SS')); END IF; END;
This function returns the date and time a specified region was cached either for the user issuing the call, or for all users if the page was not set to be cached by user.
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-2 describes the parameters available in the CACHE_GET_DATE_OF_REGION_CACHE
function.
Table 1-2 CACHE_GET_DATE_OF_REGION_CACHE Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application |
|
The page number (ID) |
|
The region name |
The following example demonstrates how to use the CACHE_GET_DATE_OF_REGION_CACHE
function to retrieve the cache date and time for the region named Cached Region on page 13 of the currently executing application. If the region has been cached, the cache date and time is output using the HTP package. The region could have been cached either by the user issuing the call, or for all users if the page was not to be cached by user.
DECLARE l_cache_date DATE DEFAULT NULL; BEGIN l_cache_date := APEX_UTIL.CACHE_GET_DATE_OF_REGION_CACHE( p_application => :APP_ID, p_page => 13, p_region_name => 'Cached Region'); IF l_cache_date IS NOT NULL THEN HTP.P('Cached on ' || TO_CHAR(l_cache_date, 'DD-MON-YY HH24:MI:SS')); END IF; END;
This procedure purges all cached pages and regions for a given application.
APEX_UTIL.CACHE_PURGE_BY_APPLICATION ( p_application IN NUMBER);
Table 1-3 describes the parameters available in the CACHE_PURGE_BY_APPLICATION
procedure.
Table 1-3 CACHE_PURGE_BY_APPLICATION Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
The following example demonstrates how to use the CACHE_PURGE_BY_APPLICATION
procedure to purge all the cached pages and regions for the application currently executing.
BEGIN APEX_UTIL.CACHE_PURGE_BY_APPLICATION(p_application => :APP_ID); END;
This procedure purges the cache for a given application and page. If the page itself is not cached but contains one or more cached regions, then the cache for these will also be purged.
APEX_UTIL.CACHE_PURGE_BY_PAGE ( p_application IN NUMBER, p_page IN NUMBER, p_user_name IN VARCHAR2 DEFAULT NULL);
Table 1-4 describes the parameters available in the CACHE_PURGE_BY_PAGE
procedure.
Table 1-4 CACHE_PURGE_BY_PAGE Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
|
The page number (ID). |
|
The user associated with cached pages and regions. |
The following example demonstrates how to use the CACHE_PURGE_BY_PAGE
procedure to purge the cache for page 9 of the application currently executing. Additionally, if the p_user_name
parameter is supplied, this procedure would be further restricted by a specific users cache (only relevant if the cache is set to be by user).
BEGIN APEX_UTIL.CACHE_PURGE_BY_PAGE( p_application => :APP_ID, p_page => 9); END;
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-5 describes the parameters available in the CACHE_PURGE_STALE
procedure.
Table 1-5 CACHE_PURGE_STALE Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
The following example demonstrates how to use the CACHE_PURGE_STALE procedure to purge all the stale pages and regions in the application currently executing.
BEGIN APEX_UTIL.CACHE_PURGE_STALE(p_application => :APP_ID); END;
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-6 describes the parameters available in the CHANGE_CURRENT_USER_PW
procedure.
Table 1-6 CHANGE_CURRENT_USER_PW Parameters
Parameter | Description |
---|---|
|
The new password value in clear text |
The following example demonstrates how to use the CHANGE_CURRENT_USER_PW
procedure to change the password for the user who is currently authenticated, assuming Application Express accounts are in use.
BEGIN APEX_UTIL.CHANGE_CURRENT_USER_PW ('secret99'); END;
See Also:
"RESET_PW 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. This function returns false if the account does not have this property.
This function may be run in a page request context by any authenticated user.
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 an Application Express user account (workspace administrator, developer, or end user) in the current workspace must be changed by the user the first time it is used.
BEGIN FOR c1 IN (SELECT user_name FROM wwv_flow_users) LOOP IF APEX_UTIL.CHANGE_PASSWORD_ON_FIRST_USE(p_user_name => c1.user_name) THEN htp.p('User:'||c1.user_name||' requires password to be changed the first time it is used.'); END IF; END LOOP; END;
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 |
The following example demonstrates how to use the CLEAR_APP_CACHE procedure to clear all the current sessions state for the application with an ID of 100.
BEGIN APEX_UTIL.CLEAR_APP_CACHE('100'); END;
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. |
The following example demonstrates how to use the CLEAR_PAGE_CACHE procedure to clear the current session s state for the page with an ID of 10.
BEGIN APEX_UTIL.CLEAR_PAGE_CACHE('10'); END;
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.
The following example demonstrates how to use the CLEAR_USER_CACHE procedure to clear all session state and application system preferences for the current user's session.
BEGIN APEX_UTIL.CLEAR_USER_CACHE; END;
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) |
The following example demonstrates how to use the COUNT_CLICK procedure to log how many user's click on the http://yahoo.com
link specified. Note that once this information is logged, you can view it via the APEX_WORKSPACE_CLICKS view and in the reports on this view available to workspace and site administrators.
DECLARE l_url VARCHAR2(255); l_cat VARCHAR2(30); l_workspace_id VARCHAR2(30); BEGIN l_url := 'http://yahoo.com'; l_cat := 'yahoo'; l_workspace_id := TO_CHAR(APEX_UTIL.FIND_SECURITY_GROUP_ID('MY_WORKSPACE')); HTP.P('<a href=APEX_UTIL.COUNT_CLICK?p_url=' || l_url || '&p_cat=' || l_cat || '&p_workspace=' || l_workspace_id || '>Click</a>'); END;
See Also:
"FIND_SECURITY_GROUP_ID Function" in this document and "Purging the External Click Count Log" in Oracle Application Express Administration GuideThis 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 IN NUMBER DEFAULT NULL, p_user_name IN VARCHAR2, p_first_name IN VARCHAR2 DEFAULT NULL, p_last_name IN VARCHAR2 DEFAULT NULL, p_description IN VARCHAR2 DEFAULT NULL, p_email_address IN VARCHAR2 DEFAULT NULL, p_web_password IN VARCHAR2, p_web_password_format IN VARCHAR2 DEFAULT 'CLEAR_TEXT', p_group_ids IN VARCHAR2 DEFAULT NULL, p_developer_privs IN VARCHAR2 DEFAULT NULL, p_default_schema IN VARCHAR2 DEFAULT NULL, p_allow_access_to_schemas IN VARCHAR2 DEFAULT NULL, p_account_expiry IN DATE DEFAULT TRUNC(SYSDATE), p_account_locked IN VARCHAR2 DEFAULT 'N', p_failed_access_attempts IN NUMBER DEFAULT 0, p_change_password_on_first_use IN VARCHAR2 DEFAULT 'Y', p_first_password_use_occurred IN VARCHAR2 DEFAULT 'N', p_attribute_01 IN VARCHAR2 DEFAULT NULL, p_attribute_02 IN VARCHAR2 DEFAULT NULL, p_attribute_03 IN VARCHAR2 DEFAULT NULL, p_attribute_04 IN VARCHAR2 DEFAULT NULL, p_attribute_05 IN VARCHAR2 DEFAULT NULL, p_attribute_06 IN VARCHAR2 DEFAULT NULL, p_attribute_07 IN VARCHAR2 DEFAULT NULL, p_attribute_08 IN VARCHAR2 DEFAULT NULL, p_attribute_09 IN VARCHAR2 DEFAULT NULL, p_attribute_10 IN VARCHAR2 DEFAULT NULL);
Table 1-11 describes the parameters available in the CREATE_USER
procedure.
Table 1-11 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 |
|
If the value your passing for the |
|
Colon separated list of numeric group IDs |
|
Colon separated list of developer privileges. The following are acceptable values for this parameter: null - To create an end user (a user who can only authenticate to developed applications). CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To create a user with developer privilege. ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To create a user with full workspace administrator and developer privilege. Note: Currently this parameter is named inconsistently between the |
|
A database schema assigned to the user's workspace, used by default for browsing. |
|
Colon separated list of schemas assigned to the user's workspace to which the user is restricted (leave null for all). |
|
Date password was last updated, which will default to today's date on creation. |
|
'Y' or 'N' indicating if account is locked or unlocked. |
|
Number of consecutive login failures that have occurred, defaults to 0 on creation. |
|
'Y' or 'N' to indicate whether password must be changed on first use, defaults to 'Y' on creation. |
|
'Y' or 'N' to indicate whether login has occurred since password change, defaults to 'N' on creation. |
|
Arbitrary text accessible with an API |
The following simple example creates an 'End User' called 'NEWUSER1' with a password of 'secret99'. Note an 'End User' can only authenticate to developed applications.
BEGIN APEX_UTIL.CREATE_USER( p_user_name => 'NEWUSER1', p_web_password => 'secret99'); END;
The following example creates a 'Workspace Administrator' called 'NEWUSER2'. Where the user 'NEWUSER2':
Has full workspace administration and developer privilege (p_developer_privs
parameter set to 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL
').
Has access to 2 schemas, both their browsing default 'MY_SCHEMA
' (p_default_schema
parameter set to 'MY_SCHEMA
') and also 'MY_SCHEMA2
' (p_allow_access_to_schemas
parameter set to 'MY_SCHEMA2
').
Does not have to change their password when they first login (p_change_password_on_first_use
parameter set to 'N').
Has their phone number stored in the first additional attribute (p_attribute_01
parameter set to '123 456 7890').
BEGIN APEX_UTIL.CREATE_USER( p_user_name => 'NEWUSER2', p_first_name => 'FRANK', p_last_name => 'SMITH', p_description => 'Description...', p_email_address => 'frank@smith.com', p_web_password => 'password', p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL', p_default_schema => 'MY_SCHEMA', p_allow_access_to_schemas => 'MY_SCHEMA2', p_change_password_on_first_use => 'N', p_attribute_01 => '123 456 7890'); END;
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 IN NUMBER, p_group_name IN VARCHAR2, p_security_group_id IN NUMBER, p_group_desc IN VARCHAR2);
Table 1-12 describes the parameters available in the CREATE_USER_GROUP
procedure.
Table 1-12 CREATE_USER_GROUP Parameters
Parameter | Description |
---|---|
|
Primary key of group |
|
Name of group |
|
Workspace ID |
|
Descriptive text |
The following example demonstrates how to use the CREATE_USER_GROUP
procedure to create a new group called 'Managers' with a description of 'text'. Pass null for the p_id
parameter to allow the database trigger to assign the new primary key value. Pass null for the p_security_group_id
parameter to default to the current workspace ID.
BEGIN APEX_UTIL.CREATE_USER_GROUP ( p_id => null, -- trigger 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-13 describes the parameters available in the CURRENT_USER_IN_GROUP
function.
Table 1-13 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 |
The following example demonstrates how to use the CURRENT_USER_IN_GROUP
function to check if the user currently authenticated belongs to the group 'Managers'.
DECLARE VAL BOOLEAN; BEGIN VAL := APEX_UTIL.CURRENT_USER_IN_GROUP(p_group_name=>'Managers'); END;
This procedure initiates the download of a print document using XML based report data (as a BLOB) and RTF or XSL-FO based report layout.
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-14 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT
procedure for Signature 1.
Table 1-14 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-15 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT
function.
Table 1-15 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. |
The following example shows how to use the DOWNLOAD_PRINT_DOCUMENT
using Signature 2 (Pre-defined report query and RTF or XSL-FO based report layout.). In this example, the data for the report is taken from a Report Query called 'ReportQueryAndXSL
' stored in the current application's Shared Components > Report Queries. The report layout is taken from a value stored in a page item (P1_XSL
).
BEGIN APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT ( p_file_name => 'mydocument', p_content_disposition => 'attachment', p_application_id => :APP_ID, p_report_query_name => 'ReportQueryAndXSL', p_report_layout => :P1_XSL, p_report_layout_type => 'xsl-fo', p_document_format => 'pdf'); END;
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-16 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT
procedure for Signature 3.
Table 1-16 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. |
The following example shows how to use the DOWNLOAD_PRINT_DOCUMENT
using Signature 3 (Pre-defined report query and pre-defined report layout). In this example, the data for the report is taken from a Report Query called 'ReportQuery
' stored in the current application's Shared Components > Report Queries. The report layout is taken from a Report Layout called 'ReportLayout
' stored in the current application's Shared Components > Report Layouts. Note that if you wish to provision dynamic layouts, instead of specifying 'ReportLayout
' for the p_report_layout_name
parameter, you could reference a page item that allowed the user to select one of multiple saved Report Layouts. This example also provides a way for the user to specify how they wish to receive the document (as an attachment or inline), through passing the value of P1_CONTENT_DISP
to the p_content_disposition
parameter. P1_CONTENT_DISP
is a page item of type 'Select
List
' with the following List of Values Definition:
STATIC2:In Browser;inline,Save / Open in separate Window;attachment BEGIN APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT ( p_file_name => 'myreport123', p_content_disposition => :P1_CONTENT_DISP, p_application_id => :APP_ID, p_report_query_name => 'ReportQuery', p_report_layout_name => 'ReportLayout', p_report_layout_type => 'rtf', p_document_format => 'pdf'); END;
This procedure initiates the download of a print document using XML based report data (as a CLOB) and RTF or XSL-FO based report layout.
APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT ( p_file_name IN VARCHAR, p_content_disposition IN VARCHAR, p_report_data IN CLOB, p_report_layout IN CLOB, p_report_layout_type IN VARCHAR2 default 'xsl-fo', p_document_format IN VARCHAR2 default 'pdf', p_print_server IN VARCHAR2 default null);
Table 1-16 describes the parameters available in the DOWNLOAD_PRINT_DOCUMENT
procedure for Signature 4.
Table 1-17 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, 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. |
The following example shows how to use the DOWNLOAD_PRINT_DOCUMENT using Signature 4 (XML based report data (as a CLOB) and RTF or XSL-FO based report layout). In this example both the report data (XML) and report layout (XSL-FO) are taken from values stored in page items.
BEGIN APEX_UTIL.DOWNLOAD_PRINT_DOCUMENT ( p_file_name => 'mydocument', p_content_disposition => 'attachment', p_report_data => :P1_XML, p_report_layout => :P1_XSL, p_report_layout_type => 'xsl-fo', p_document_format => 'pdf'); END;
This procedure enables a user account record to be altered. To execute this procedure, the current user must have administrative privileges in the workspace.
APEX_UTIL.EDIT_USER ( p_user_id IN NUMBER, p_user_name IN VARCHAR2, p_first_name IN VARCHAR2 DEFAULT NULL, p_last_name IN VARCHAR2 DEFAULT NULL, p_web_password IN VARCHAR2 DEFAULT NULL, p_new_password IN VARCHAR2 DEFAULT NULL, p_email_address IN VARCHAR2 DEFAULT NULL, p_start_date IN VARCHAR2 DEFAULT NULL, p_end_date IN VARCHAR2 DEFAULT NULL, p_employee_id IN VARCHAR2 DEFAULT NULL, p_allow_access_to_schemas IN VARCHAR2 DEFAULT NULL, p_person_type IN VARCHAR2 DEFAULT NULL, p_default_schema IN VARCHAR2 DEFAULT NULL, p_group_ids IN VARCHAR2 DEFAULT NULL, p_developer_roles IN VARCHAR2 DEFAULT NULL, p_description IN VARCHAR2 DEFAULT NULL, p_account_expiry IN DATE DEFAULT NULL, p_account_locked IN VARCHAR2 DEFAULT 'N', p_failed_access_attempts IN NUMBER DEFAULT 0, p_change_password_on_first_use IN VARCHAR2 DEFAULT 'Y', p_first_password_use_occurred IN VARCHAR2 DEFAULT 'N');
Table 1-18 describes the parameters available in the EDIT_USER
procedure.
Table 1-18 EDIT_USER Parameters
Parameter | Description |
---|---|
|
Numeric primary key of the user account |
|
Alphanumeric name used for login. See Also: "SET_USERNAME Procedure" |
|
Informational. See Also: "SET_FIRST_NAME Procedure" |
|
Informational. See Also: "SET_LAST_NAME Procedure" |
|
Clear text password. If using this procedure to update the password for the user, values for both |
|
Clear text new password. If using this procedure to update the password for the user, values for both |
|
Informational. See Also: "SET_EMAIL Procedure" |
|
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. The following are acceptable values for this parameter: · null - To update the user to be an end user (a user who can only authenticate to developed applications) · CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To update the user to have developer privilege · ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL - To update the user to have full workspace administrator and developer privilege Note: Currently this parameter is named inconsistently between the CREATE_USER, EDIT_USER and FETCH_USER APIs, although they all relate to the DEVELOPER_ROLE field stored in the named user account record. CREATE_USER uses p_developer_privs, EDIT_USER uses p_developer_roles and FETCH_USER uses p_developer_role. See Also: "GET_USER_ROLES Function" |
|
Informational |
|
Date password was last updated. See Also: "EXPIRE_END_USER_ACCOUNT Procedure", "EXPIRE_WORKSPACE_ACCOUNT Procedure", "UNEXPIRE_END_USER_ACCOUNT Procedure", "UNEXPIRE_WORKSPACE_ACCOUNT Procedure" |
|
'Y' or 'N' indicating if account is locked or unlocked. See Also: "LOCK_ACCOUNT Procedure", "UNLOCK_ACCOUNT Procedure" |
|
Number of consecutive login failures that have occurred. |
|
'Y' or 'N' to indicate whether password must be changed on first use. See Also: "CHANGE_PASSWORD_ON_FIRST_USE Function" |
|
'Y' or 'N' to indicate whether login has occurred since password change. See Also: "PASSWORD_FIRST_USE_OCCURRED Function" |
The following example shows how to use the EDIT_USER
procedure to update a user account. This example shows how you can use the EDIT_USER
procedure to change the user 'FRANK' from a user with just developer privilege to a user with workspace administrator and developer privilege. Firstly, the FETCH_USER
procedure is called to assign account details for the user 'FRANK' to local variables. These variables are then used in the call to EDIT_USER
to preserve the details of the account, with the exception of the value for the p_developer_roles
parameter, which is set to 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL
'.
DECLARE l_user_id NUMBER; l_workspace VARCHAR2(255); l_user_name VARCHAR2(100); l_first_name VARCHAR2(255); l_last_name VARCHAR2(255); l_web_password VARCHAR2(255); l_email_address VARCHAR2(240); l_start_date DATE; l_end_date DATE; l_employee_id NUMBER(15,0); l_allow_access_to_schemas VARCHAR2(4000); l_person_type VARCHAR2(1); l_default_schema VARCHAR2(30); l_groups VARCHAR2(1000); l_developer_role VARCHAR2(60); l_description VARCHAR2(240); l_account_expiry DATE; l_account_locked VARCHAR2(1); l_failed_access_attempts NUMBER; l_change_password_on_first_use VARCHAR2(1); l_first_password_use_occurred VARCHAR2(1); BEGIN l_user_id := APEX_UTIL.GET_USER_ID('FRANK'); APEX_UTIL.FETCH_USER( p_user_id => l_user_id, p_workspace => l_workspace, p_user_name => l_user_name, p_first_name => l_first_name, p_last_name => l_last_name, p_web_password => l_web_password, p_email_address => l_email_address, p_start_date => l_start_date, p_end_date => l_end_date, p_employee_id => l_employee_id, p_allow_access_to_schemas => l_allow_access_to_schemas, p_person_type => l_person_type, p_default_schema => l_default_schema, p_groups => l_groups, p_developer_role => l_developer_role, p_description => l_description, p_account_expiry => l_account_expiry, p_account_locked => l_account_locked, p_failed_access_attempts => l_failed_access_attempts, p_change_password_on_first_use => l_change_password_on_first_use, p_first_password_use_occurred => l_first_password_use_occurred); APEX_UTIL.EDIT_USER ( p_user_id => l_user_id, p_user_name => l_user_name, p_first_name => l_first_name, p_last_name => l_last_name, p_web_password => l_web_password, p_new_password => l_web_password, p_email_address => l_email_address, p_start_date => l_start_date, p_end_date => l_end_date, p_employee_id => l_employee_id, p_allow_access_to_schemas => l_allow_access_to_schemas, p_person_type => l_person_type, p_default_schema => l_default_schema, p_group_ids => l_groups, p_developer_roles => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL', p_description => l_description, p_account_expiry => l_account_expiry, p_account_locked => l_account_locked, p_failed_access_attempts => l_failed_access_attempts, p_change_password_on_first_use => l_change_password_on_first_use, p_first_password_use_occurred => l_first_password_use_occurred); END;
See Also:
"FETCH_USER Procedure Signature 3"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-19 describes the parameters available in the END_USER_ACCOUNT_DAYS_LEFT
function.
Table 1-19 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); 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-21 describes the parameters available in the EXPIRE_END_USER_ACCOUNT
procedure.
Table 1-20 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;
See Also:
"UNEXPIRE_END_USER_ACCOUNT Procedure"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-21 describes the parameters available in the EXPIRE_WORKSPACE_ACCOUNT
procedure.
Table 1-21 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;
See Also:
"UNEXPIRE_WORKSPACE_ACCOUNT Procedure"When called from a page, this procedure produces an export file of the current workspace definition, workspace users, and workspace groups. To execute this procedure, the current user must have administrative privilege in the workspace.
APEX_UTIL.EXPORT_USERS( p_export_format IN VARCHAR2 DEFAULT 'UNIX');
Table 1-22 describes the parameters available in the EXPORT_USERS
procedure.
Table 1-22 EXPORT_USERS Parameters
Parameter | Description |
---|---|
|
Indicates how rows in the export file will be formatted. Specify |
The following example shows how to use the EXPORT_USERS
procedure. Call this procedure from a page to produce an export file containing the current workspace definition, list of workspace users and list of workspace groups. The file will be formatted with rows delimited by line feeds.
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-23 describes the parameters available in the FETCH_APP_ITEM
function.
Table 1-23 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) |
The following example shows how to use the FETCH_APP_ITEM
function to obtain the value of the application item 'F300_NAME
' in application 300. As no value is passed for p_session
, this defaults to the current session state value.
DECLARE VAL VARCHAR2(30); BEGIN VAL := APEX_UTIL.FETCH_APP_ITEM( p_item => 'F300_NAME', p_app => 300); END;
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.
APEX_UTIL.FETCH_USER ( p_user_id IN NUMBER, p_workspace OUT VARCHAR2, p_user_name OUT VARCHAR2, p_first_name OUT VARCHAR2, p_last_name OUT VARCHAR2, p_web_password OUT VARCHAR2, p_email_address OUT VARCHAR2, p_start_date OUT VARCHAR2, p_end_date OUT VARCHAR2, p_employee_id OUT VARCHAR2, p_allow_access_to_schemas OUT VARCHAR2, p_person_type OUT VARCHAR2, p_default_schema OUT VARCHAR2, p_groups OUT VARCHAR2, p_developer_role OUT VARCHAR2, p_description OUT VARCHAR2 );
Table 1-24 describes the parameters available in the FETCH_USER
procedure for signature 1.
Table 1-24 Fetch_User Parameters Signature 1
Parameter | Description |
---|---|
|
Numeric primary key of the user account |
|
The name of the workspace |
|
Alphanumeric name used for login. See Also: "GET_USERNAME Function" |
|
Informational. See Also: "GET_FIRST_NAME Function" |
|
Informational. See Also: "GET_LAST_NAME Function" |
|
Obfuscated account password |
|
Email address. See Also: "GET_EMAIL Function" |
|
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. See Also: "GET_DEFAULT_SCHEMA Function" |
|
List of groups of which user is a member. See Also: "GET_GROUPS_USER_BELONGS_TO Function" and "CURRENT_USER_IN_GROUP Function" |
|
Colon-separated list of developer roles. The following are acceptable values for this parameter:
Note: Currently this parameter is named inconsistently between the See Also: "GET_USER_ROLES Function" |
|
Informational |
The following example shows how to use the FETCH_USER
procedure with Signature 1. This procedure is passed the ID of the currently authenticated user for the only IN
parameter p_user_id
. The code then stores all the other OUT
parameter values in local variables.
DECLARE l_workspace VARCHAR2(255); l_user_name VARCHAR2(100); l_first_name VARCHAR2(255); l_last_name VARCHAR2(255); l_web_password VARCHAR2(255); l_email_address VARCHAR2(240); l_start_date DATE; l_end_date DATE; l_employee_id NUMBER(15,0); l_allow_access_to_schemas VARCHAR2(4000); l_person_type VARCHAR2(1); l_default_schema VARCHAR2(30); l_groups VARCHAR2(1000); l_developer_role VARCHAR2(60); l_description VARCHAR2(240); BEGIN APEX_UTIL.FETCH_USER( p_user_id => APEX_UTIL.GET_CURRENT_USER_ID, p_workspace => l_workspace, p_user_name => l_user_name, p_first_name => l_first_name, p_last_name => l_last_name, p_web_password => l_web_password, p_email_address => l_email_address, p_start_date => l_start_date, p_end_date => l_end_date, p_employee_id => l_employee_id, p_allow_access_to_schemas => l_allow_access_to_schemas, p_person_type => l_person_type, p_default_schema => l_default_schema, p_groups => l_groups, p_developer_role => l_developer_role, p_description => l_description); END;
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.
APEX_UTIL.FETCH_USER ( p_user_id IN NUMBER, p_user_name OUT VARCHAR2, p_first_name OUT VARCHAR2, p_last_name OUT VARCHAR2, p_email_address OUT VARCHAR2, p_groups OUT VARCHAR2, p_developer_role OUT VARCHAR2, p_description OUT VARCHAR2 );
Table 1-25 describes the parameters available in the FETCH_USER
procedure for signature 2.
Table 1-25 Fetch_User Parameters Signature 2
Parameter | Description |
---|---|
|
Numeric primary key of the user account |
|
Alphanumeric name used for login. See Also: "GET_USERNAME Function" |
|
Informational. See Also: "GET_FIRST_NAME Function" |
|
Informational. See Also: "GET_LAST_NAME Function" |
|
Email address. See Also: "GET_EMAIL Function" |
|
List of groups of which user is a member. See Also: "GET_GROUPS_USER_BELONGS_TO Function" and "CURRENT_USER_IN_GROUP Function" |
|
Colon-separated list of developer roles. The following are acceptable values for this parameter:
Note: Currently this parameter is named inconsistently between the See Also: "GET_USER_ROLES Function" |
|
Informational |
The following example shows how to use the FETCH_USER
procedure with Signature 2. This procedure is passed the ID of the currently authenticated user for the only IN
parameter p_user_id
. The code then stores all the other OUT
parameter values in local variables.
DECLARE l_user_name VARCHAR2(100); l_first_name VARCHAR2(255); l_last_name VARCHAR2(255); l_email_address VARCHAR2(240); l_groups VARCHAR2(1000); l_developer_role VARCHAR2(60); l_description VARCHAR2(240); BEGIN APEX_UTIL.FETCH_USER( p_user_id => APEX_UTIL.GET_CURRENT_USER_ID, p_user_name => l_user_name, p_first_name => l_first_name, p_last_name => l_last_name, p_email_address => l_email_address, p_groups => l_groups, p_developer_role => l_developer_role, p_description => l_description); END;
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.
APEX_UTIL.FETCH_USER ( p_user_id IN NUMBER, p_workspace OUT VARCHAR2, p_user_name OUT VARCHAR2, p_first_name OUT VARCHAR2, p_last_name OUT VARCHAR2, p_web_password OUT VARCHAR2, p_email_address OUT VARCHAR2, p_start_date OUT VARCHAR2, p_end_date OUT VARCHAR2, p_employee_id OUT VARCHAR2, p_allow_access_to_schemas OUT VARCHAR2, p_person_type OUT VARCHAR2, p_default_schema OUT VARCHAR2, p_groups OUT VARCHAR2, p_developer_role OUT VARCHAR2, p_description OUT VARCHAR2, p_account_expiry OUT DATE, p_account_locked OUT VARCHAR2, p_failed_access_attempts OUT NUMBER, p_change_password_on_first_use OUT VARCHAR2, p_first_password_use_occurred OUT VARCHAR2 );
Table 1-26 describes the parameters available in the FETCH_USER
procedure.
Table 1-26 Fetch_User Parameters Signature 3
Parameter | Description |
---|---|
|
Numeric primary key of the user account |
|
The name of the workspace |
|
Alphanumeric name used for login. See Also: "GET_USERNAME Function" |
|
Informational. See Also: "GET_FIRST_NAME Function" |
|
Informational. See Also: "GET_LAST_NAME Function" |
|
Obfuscated account password |
|
Email address. See Also: "GET_EMAIL Function" |
|
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. See Also: "GET_DEFAULT_SCHEMA Function" |
|
List of groups of which user is a member. See Also: "GET_GROUPS_USER_BELONGS_TO Function" and "CURRENT_USER_IN_GROUP Function" |
|
Colon-separated list of developer roles. The following are acceptable values for this parameter:
Note: Currently this parameter is named inconsistently between the See Also: "GET_USER_ROLES Function" |
|
Informational |
|
Date account password was last reset. See Also: "END_USER_ACCOUNT_DAYS_LEFT Function" and "WORKSPACE_ACCOUNT_DAYS_LEFT Function" |
|
Locked/Unlocked indicator See Also: "GET_ACCOUNT_LOCKED_STATUS Function" |
|
Counter for consecutive login failures |
|
Setting to force password change on first use |
|
Indicates whether login with password occurred |
The following example shows how to use the FETCH_USER
procedure with Signature 3. This procedure is passed the ID of the currently authenticated user for the only IN
parameter p_user_id
. The code then stores all the other OUT
parameter values in local variables.
DECLARE l_workspace VARCHAR2(255); l_user_name VARCHAR2(100); l_first_name VARCHAR2(255); l_last_name VARCHAR2(255); l_web_password VARCHAR2(255); l_email_address VARCHAR2(240); l_start_date DATE; l_end_date DATE; l_employee_id NUMBER(15,0); l_allow_access_to_schemas VARCHAR2(4000); l_person_type VARCHAR2(1); l_default_schema VARCHAR2(30); l_groups VARCHAR2(1000); l_developer_role VARCHAR2(60); l_description VARCHAR2(240); l_account_expiry DATE; l_account_locked VARCHAR2(1); l_failed_access_attempts NUMBER; l_change_password_on_first_use VARCHAR2(1); l_first_password_use_occurred VARCHAR2(1); BEGIN APEX_UTIL.FETCH_USER( p_user_id => APEX_UTIL.GET_CURRENT_USER_ID, p_workspace => l_workspace, p_user_name => l_user_name, p_first_name => l_first_name, p_last_name => l_last_name, p_web_password => l_web_password, p_email_address => l_email_address, p_start_date => l_start_date, p_end_date => l_end_date, p_employee_id => l_employee_id, p_allow_access_to_schemas => l_allow_access_to_schemas, p_person_type => l_person_type, p_default_schema => l_default_schema, p_groups => l_groups, p_developer_role => l_developer_role, p_description => l_description, p_account_expiry => l_account_expiry, p_account_locked => l_account_locked, p_failed_access_attempts => l_failed_access_attempts, p_change_password_on_first_use => l_change_password_on_first_use, p_first_password_use_occurred => l_first_password_use_occurred); END;
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-27 describes the parameters available in the FIND_SECURITY_GROUP_ID
function.
Table 1-27 FIND_SECURITY_GROUP_ID Parameters
Parameter | Description |
---|---|
|
The name of the workspace |
The following example demonstrates how to use the FIND_SECURITY_GROUP_ID
function to return the security group ID for the workspace called 'DEMOS'.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.FIND_SECURITY_GROUP_ID (p_workspace=>'DEMOS'); END;
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-28 describes the parameters available in the FIND_WORKSPACE
function.
Table 1-28 FIND_WORKSPACE Parameters
Parameter | Description |
---|---|
|
The security group ID of a workspace |
The following example demonstrates how to use the FIND_WORKSPACE
function to return the workspace name for the workspace with a security group ID of 20.
DECLARE VAL VARCHAR2(255); BEGIN VAL := APEX_UTIL.FIND_WORKSPACE (p_security_group_id =>'20'); END;
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-29 describes the parameters available in the GET_ACCOUNT_LOCKED_STATUS
function.
Table 1-29 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. Please note these are only accessible via the APIs.
APEX_UTIL.GET_ATTRIBUTE( p_username IN VARCHAR2, p_attribute_number IN NUMBER) RETURN VARCHAR2;
Table 1-30 describes the parameters available in the GET_ATTRIBUTE
function.
Table 1-30 GET_ATTRIBUTE Parameters
Parameter | Description |
---|---|
|
User name in the account. |
|
Number of attributes in the user record (1 through 10) |
The following example shows how to use the GET_ATTTIBUTE
function to return the value for the 1st attribute for the user 'FRANK'.
DECLARE VAL VARCHAR2(4000); BEGIN VAL := APEX_UTIL.GET_ATTRIBUTE ( p_username => 'FRANK', p_attribute_number => 1); END;
See Also:
"SET_ATTRIBUTE Procedure"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 GuideAPEX_UTIL.GET_BLOB_FILE_SRC ( p_item_name IN VARCHAR2 DEFAULT NULL, p_v1 IN VARCHAR2 DEFAULT NULL, p_v2 IN VARCHAR2 DEFAULT NULL, p_content_disposition IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
Table 1-31 describes the parameters available in GET_BLOB_FILE_SRC
function.
Table 1-31 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.
This following example shows how to use the GET_CURRENT_USER_ID
function. It returns the numeric user ID of the current user into a local variable.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.GET_CURRENT_USER_ID; END;
This function returns the default schema name associated with the current user.
APEX_UTIL.GET_DEFAULT_SCHEMA RETURN VARCHAR2;
None.
The following example shows how to use the GET_DEFAULT_SCHEMA
function. It returns the default schema name associated with the current user into a local variable.
DECLARE VAL VARCHAR2(30); BEGIN VAL := APEX_UTIL.GET_DEFAULT_SCHEMA; END;
This function returns the email address associated with the named user.
APEX_UTIL.GET_EMAIL( p_username IN VARCHAR2); RETURN VARCHAR2;
Table 1-32 describes the parameters available in GET_EMAIL
function.
The following example shows how to use the GET_EMAIL
function to return the email address of the user 'FRANK'.
DECLARE VAL VARCHAR2(240); BEGIN VAL := APEX_UTIL.GET_EMAIL(p_username => 'FRANK'); END;
See Also:
"SET_EMAIL Procedure"This procedure downloads files from the Oracle Application Express file repository. Please note if you are invoking this procedure during page processing, you must ensure that no page branch will be invoked under the same condition, as it will interfere with the file retrieval. This means that branches with any of the following conditions should not be set to fire:
Branches with a 'When Button Pressed' attribute equal to the button that invokes the procedure.
Branches with conditional logic defined that would succeed during page processing when the procedure is being invoked.
As unconditional.
APEX_UTIL.GET_FILE ( p_file_id IN VARCHAR2, p_inline IN VARCHAR2 DEFAULT 'NO');
Table 1-33 describes the parameters available in GET_FILE
procedure.
Table 1-33 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_inline => 'YES'); END; |
|
Valid values include |
The following example shows how to use the GET_FILE
function to return the file identified by the ID 8675309. This will be displayed inline in the browser.
BEGIN APEX_UTIL.GET_FILE( p_file_id => '8675309', p_inline => 'YES'); END;
See Also:
"GET_FILE_ID Function"This function obtains the primary key of a file in the Oracle Application Express file repository.
APEX_UTIL.GET_FILE_ID ( p_name IN VARCHAR2) RETURN NUMBER;
Table 1-34 describes the parameters available in GET_FILE_ID
function.
Table 1-34 GET_FILE_ID Parameters
Parameter | Description |
---|---|
|
The NAME in |
The following example shows how to use the GET_FILE_ID
function to retrieve the database ID of the file with a filename of 'F125.sql'.
DECLARE l_name VARCHAR2(255); l_file_id NUMBER; BEGIN SELECT name INTO l_name FROM APEX_APPLICATION_FILES WHERE filename = 'F125.sql'; -- l_file_id := APEX_UTIL.GET_FILE_ID(p_name => l_name); END;
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-35 describes the parameters available in GET_FIRST_NAME
function.
Table 1-35 GET_FIRST_NAME Parameters
Parameter | Description |
---|---|
|
Identifies the user name in the account |
The following example shows how to use the GET_FIRST_NAME
function to return the FIRST_NAME
of the user 'FRANK'.
DECLARE VAL VARCHAR2(255); BEGIN VAL := APEX_UTIL.GET_FIRST_NAME(p_username => 'FRANK'); END;
See Also:
"SET_FIRST_NAME Procedure"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-36 describes the parameters available in GET_GROUPS_USER_BELONGS_TO
function.
Table 1-36 GET_GROUPS_USER_BELONGS_TO Parameters
Parameter | Description |
---|---|
|
Identifies the user name in the account |
The following example shows how to use the GET_GROUPS_USER_BELONGS_TO
to return the list of groups to which the user 'FRANK' is a member.
DECLARE VAL VARCHAR2(32765); BEGIN VAL := APEX_UTIL.GET_GROUPS_USER_BELONGS_TO(p_username => 'FRANK'); END;
See Also:
"EDIT_USER Procedure"This function returns the numeric ID of a named group in the workspace.
APEX_UTIL.GET_GROUP_ID( p_group_name IN VARCHAR2) RETURN VARCHAR2;
Table 1-37 describes the parameters available in GET_GROUP_ID
function.
Table 1-37 GET_GROUP_ID Parameters
Parameter | Description |
---|---|
|
Identifies the user name in the account |
The following example shows how to use the GET_GROUP_ID
function to return the ID for the group named 'Managers'.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.GET_GROUP_ID(p_group_name => 'Managers'); END;
This function returns the name of a group identified by a numeric ID.
APEX_UTIL.GET_GROUP_NAME( p_group_id IN NUMBER) RETURN NUMBER;
Table 1-38 describes the parameters available in GET_GROUP_NAME
function.
Table 1-38 GET_GROUP_NAME Parameters
Parameter | Description |
---|---|
|
Identifies a numeric ID of a group in the workspace |
The following example shows how to use the GET_GROUP_NAME
function to return the name of the group with the ID 8922003.
DECLARE VAL VARCHAR2(255); BEGIN VAL := APEX_UTIL.GET_GROUP_NAME(p_group_id => 8922003); END;
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-39 describes the parameters available in GET_LAST_NAME
function.
Table 1-39 GET_LAST_NAME Parameters
Parameter | Description |
---|---|
|
The user name in the user account record |
The following example shows how to use the function to return the LAST_NAME
for the user 'FRANK'.
DECLARE VAL VARCHAR2(255); BEGIN VAL := APEX_UTIL.GET_LAST_NAME(p_username => 'FRANK'); END;
See Also:
"SET_LAST_NAME Procedure"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-40 describes the parameters available in GET_NUMERIC_SESSION_STATE
function.
Table 1-40 GET_NUMERIC_SESSION_STATE Parameters
Parameter | Description |
---|---|
|
The case insensitive name of the item for which you want to have the session state fetched |
The following example shows how to use the function to return the numeric value stored in session state for the item 'my_item
'.
DECLARE l_item_value NUMBER; BEGIN l_item_value := APEX_UTIL.GET_NUMERIC_SESSION_STATE('my_item'); END;
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-41 describes the parameters available in the GET_PREFERENCE
function.
Table 1-41 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 |
The following example shows how to use the GET_PREFERENCE
function to return the value for the currently authenticated user's preference named 'default_view
'.
DECLARE l_default_view VARCHAR2(255); BEGIN l_default_view := APEX_UTIL.GET_PREFERENCE( p_preference => 'default_view', p_user => :APP_USER); END;
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-42 describes the parameters available in the GET_PRINT_DOCUMENT
function.
Table 1-42 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. |
For a GET_PRINT_DOCUMENT
example see "GET_PRINT_DOCUMENT Function Signature 4".
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_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-43 describes the parameters available in the GET_PRINT_DOCUMENT
function.
Table 1-43 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) |
|
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. |
For a GET_PRINT_DOCUMENT
example see "GET_PRINT_DOCUMENT Function Signature 4".
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-44 describes the parameters available in the GET_PRINT_DOCUMENT
function.
Table 1-44 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. |
For a GET_PRINT_DOCUMENT
example see "GET_PRINT_DOCUMENT Function Signature 4".
This function returns a document as BLOB
using XML based report data and RTF or XSL-FO based report layout.
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-45 describes the parameters available in the GET_PRINT_DOCUMENT
function. for Signature 4
Table 1-45 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 |
The following example shows how to use the GET_PRINT_DOCUMENT
using Signature 4 (Document returns as a BLOB using XML based report data and RTF or XSL-FO based report layout). In this example, GET_PRINT_DOCUMENT
is used in conjunction with APEX_MAIL
.SEND
and APEX_MAIL.ADD_ATTACHMENT
to send an email with an attachment of the file returned by GET_PRINT_DOCUMENT
. Both the report data and layout are taken from values stored in page items (P1_XML
and P1_XSL
).
DECLARE l_id number; l_document BLOB; BEGIN l_document := APEX_UTIL.GET_PRINT_DOCUMENT ( p_report_data => :P1_XML, p_report_layout => :P1_XSL, p_report_layout_type => 'xsl-fo', p_document_format => 'pdf'); l_id := APEX_MAIL.SEND( p_to => :P35_MAIL_TO, p_from => 'noreplies@oracle.com', p_subj => 'sending PDF via print API', p_body => 'Please review the attachment.', p_body_html => 'Please review the attachment'); APEX_MAIL.ADD_ATTACHMENT ( p_mail_id => l_id, p_attachment => l_document, p_filename => 'mydocument.pdf', p_mime_type => 'application/pdf'); END;
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-46 describes the parameters available in GET_SESSION_STATE
function.
Table 1-46 GET_SESSION_STATE Parameters
Parameter | Description |
---|---|
|
The case insensitive name of the item for which you want to have the session state fetched |
The following example shows how to use the GET_SESSION_STATE
function to return the value stored in session state for the item 'my_item'.
DECLARE l_item_value VARCHAR2(255); BEGIN l_item_value := APEX_UTIL.GET_SESSION_STATE('my_item'); END;
This function returns the numeric ID of a named user in the workspace.
APEX_UTIL.GET_USER_ID( p_username IN VARCHAR2) RETURN NUMBER;
Table 1-47 describes the parameters available in GET_USER_ID
function.
Table 1-47 GET_USER_ID Parameters
Parameter | Description |
---|---|
|
Identifies the name of a user in the workspace |
The following example shows how to use the GET_USER_ID
function to return the ID for the user named 'FRANK'.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.GET_USER_ID(p_username => 'FRANK'); END;
This function returns the DEVELOPER_ROLE
field stored in the named user account record. Please note that currently this parameter is named inconsistently between the CREATE_USER
, EDIT_USER
and FETCH_USER
APIs, although they all relate to the DEVELOPER_ROLE
field. CREATE_USER
uses p_developer_privs
, EDIT_USER
uses p_developer_roles
and FETCH_USER
uses p_developer_role
.
APEX_UTIL.GET_USER_ROLES( p_username IN VARCHAR2) RETURN VARCHAR2;
Table 1-48 describes the parameters available in GET_USER_ROLES
function.
Table 1-48 GET_USER_ROLES Parameters
Parameter | Description |
---|---|
|
Identifies a user name in the account |
The following example shows how to use the GET_USER_ROLES
function to return colon separated list of roles stored in the DEVELOPER_ROLE
field for the user 'FRANK'.
DECLARE VAL VARCHAR2(4000); BEGIN VAL := APEX_UTIL.GET_USER_ROLES(p_username=>'FRANK'); END;
This function returns the user name of a user account identified by a numeric ID.
APEX_UTIL.GET_USERNAME( p_userid IN NUMBER) RETURN VARCHAR2;
Table 1-49 describes the parameters available in GET_USERNAME
function.
Table 1-49 GET_USERNAME Parameters
Parameter | Description |
---|---|
|
Identifies the numeric ID of a user account in the workspace |
The following example shows how to use the GET_USERNAME
function to return the user name for the user with an ID of 228922003.
DECLARE VAL VARCHAR2(100); BEGIN VAL := APEX_UTIL.GET_USERNAME(p_userid => 228922003); END;
See Also:
"SET_USERNAME Procedure"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-50 describes the parameters available in the IS_LOGIN_PASSWORD_VALID
function.
Table 1-50 IS_LOGIN_PASSWORD_VALID Parameters
Parameter | Description |
---|---|
|
User name in account |
|
Password to be compared with password stored in the account |
The following example shows how to use the IS_LOGIN_PASSWORD_VALID
function to check if the user 'FRANK' has the password 'tiger'. TRUE
will be returned if this is a valid password for 'FRANK', FALSE
if not.
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-51 describes the parameters available in IS_USERNAME_UNIQUE
function.
Table 1-51 IS_USERNAME_UNIQUE Parameters
Parameter | Description |
---|---|
|
Identifies the user name to be tested |
The following example shows how to use the IS_USERNAME_UNIQUE
function. If the user 'FRANK' already exists in the current workspace, FALSE
will be returned, otherwise TRUE
is returned.
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 RETURN NUMBER;
None
The following example shows how to use the KEYVAL_NUM
function to return the current value of the package variable wwv_flow_utilities.g_val_num
.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.KEYVAL_NUM; END;
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;
None.
The following example shows how to use the KEYVAL_VC2
function to return the current value of the package variable wwv_flow_utilities.g_val_vc2
.
DECLARE VAL VARCHAR2(4000); BEGIN VAL := APEX_UTIL.KEYVAL_VC2; END;
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-52 describes the parameters available in the LOCK_ACCOUNT
procedure.
Table 1-52 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-53 describes the parameters available in the PASSWORD_FIRST_USE_OCCURRED
procedure.
Table 1-53 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"The PREPARE_URL function serves two purposes:
To return an f?p URL with the Session State Protection checksum argument (&cs=) if one is required.
To return an f?p URL with the session ID component replaced with zero (0) if the zero session ID feature is in use and other criteria are met.
Note:
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-54 describes the parameters available in the PREPARE_URL function.
Table 1-54 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, |
The following example shows how to use the PREPARE_URL
function to return a URL with a valid 'SESSION' level checksum argument. This URL sets the value of P1_ITEM
page item to xyz
.
DECLARE l_url varchar2(2000); l_app number := v('APP_ID'); l_session number := v('APP_SESSION'); BEGIN l_url := APEX_UTIL.PREPARE_URL( p_url => 'f?p=' || l_app || ':1:'||l_session||'::NO::P1_ITEM:xyz', p_checksum_type => 'SESSION'); END;
The following example shows how to use the PREPARE_URL
function to return a URL with a zero session ID. In a PL/SQL Dynamic Content region that generates f?p
URLs (anchors), call PREPARE_URL
to ensure that the session ID will set to zero when the zero session ID feature is in use, when the user is a public user (not authenticated), and when the target page is a public page in the current application:
htp.p(APEX_UTIL.PREPARE_URL(p_url => 'f?p=' || :APP_ID || ':10:'|| :APP_SESSION ||'::NO::P10_ITEM:ABC');
When using PREPARE_URL
for this purpose, the p_url_charset
and p_checksum_type
arguments can be omitted. However, it is permissible to use them when both the Session State Protection and Zero Session ID features are applicable.
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-55 describes the parameters available in PUBLIC_CHECK_AUTHORIZATION
function.
Table 1-55 PUBLIC_CHECK_AUTHORIZATION Parameters
Parameter | Description |
---|---|
|
The name of the security scheme that determines if the user passes the security check |
The following example shows how to use the PUBLIC_CHECK_AUTHORIZATION
function to check if the current user passes the check defined in the my_auth_scheme
authorization scheme.
DECLARE l_check_security BOOLEAN; BEGIN l_check_security := APEX_UTIL.PUBLIC_CHECK_AUTHORIZATION('my_auth_scheme'); END;
Deletes all cached regions for an application.
APEX_UTIL.PURGE_REGIONS_BY_APP ( p_application IN NUMBER);
Table 1-56 describes the parameters available in PURGE_REGIONS_BY_APP
.
Table 1-56 PURGE_REGIONS_BY_APP Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
The following example show how to use APEX_UTIL.PURGE_REGIONS_BY_APP
to delete all cached regions for application #123.
BEGIN APEX_UTILITIES.PURGE_REGIONS_BY_APP(p_application=>123); END;
Deletes all cached values for a region identified by the application ID, page number and region name.
APEX_UTIL.PURGE_REGIONS_BY_NAME ( p_application IN NUMBER, p_page IN NUMBER, p_region_name IN VARCHAR2);
Table 1-57 describes the parameters available in PURGE_REGIONS_BY_NAME
.
Table 1-57 PURGE_REGIONS_BY_NAME Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
|
The number of the page containing the region to be deleted. |
|
The region name to be deleted. |
The following example shows how to use the PURGE_REGIONS_BY_NAME
procedure to delete all the cached values for the region 'my_cached_region
' on page 1 of the current application.
BEGIN APEX_UTIL.PURGE_REGIONS_BY_NAME( p_application => :APP_ID, p_page => 1, p_region_name => 'my_cached_region'); END;
Deletes all cached regions by application and page.
APEX_UTIL.PURGE_REGIONS_BY_PAGE ( p_application IN NUMBER, p_page IN NUMBER);
Table 1-58 describes the parameters available in PURGE_REGIONS_BY_PAGE
.
Table 1-58 PURGE_REGIONS_BY_PAGE Parameters
Parameter | Description |
---|---|
|
The identification number (ID) of the application. |
|
The identification number of page containing the region. |
The following example shows how to use the PURGE_REGIONS_BY_PAGE
procedure to delete all the cached values for regions on page 1 of the current application.
BEGIN APEX_UTIL.PURGE_REGIONS_BY_PAGE( p_application => :APP_ID, p_page => 1); END;
This procedure 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-59 describes the parameters available in the REMOVE_PREFERENCE
procedure.
Table 1-59 REMOVE_PREFERENCE Parameters
Parameter | Description |
---|---|
|
Name of the preference to remove |
|
User for whom the preference is defined |
The following example shows how to use the REMOVE_PREFERENCE
procedure to remove the preference default_view
for the currently authenticated user.
BEGIN APEX_UTIL.REMOVE_PREFERENCE( p_preference => 'default_view', p_user => :APP_USER); END;
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-60 describes the parameters available in REMOVE_SORT_PREFERENCES
function.
Table 1-60 REMOVE_SORT_PREFERENCES Parameters
Parameter | Description |
---|---|
|
Identifies the user for whom sorting preferences will be removed |
The following example shows how to use the REMOVE_SORT_PREFERENCES
procedure to remove the currently authenticated user's column heading sorting preferences.
BEGIN APEX_UTIL.REMOVE_SORT_PREFERENCES(:APP_USER); END;
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-61 describes the parameters available in the REMOVE_USER
procedure.
Table 1-61 REMOVE_USER Parameters
Parameter | Description |
---|---|
|
The numeric primary key of the user account record |
|
The user name of the user account |
The following examples show how to use the REMOVE_USER
procedure to remove a user account. Firstly, by the primary key (using the p_user_id
parameter) and secondly by user name (using the p_user_name
parameter).
BEGIN APEX_UTIL.REMOVE_USER(p_user_id=> 99997); END; BEGIN APEX_UTIL.REMOVE_USER(p_user_name => 'FRANK'); END;
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.
The following example shows how to use the RESET_AUTHORIZATIONS
procedure to clear the authorization scheme cache.
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-62 describes the parameters available in the RESET_PW
procedure.
Table 1-62 RESET_PW Parameters
Parameter | Description |
---|---|
|
The user name of the user account |
|
Message text to be mailed to a user |
The following example shows how to use the RESET_PW
procedure to reset the password for the user 'FRANK'.
BEGIN APEX_UTIL.RESET_PW( p_user => 'FRANK', p_msg => 'Contact help desk at 555-1212 with questions'); END;
See Also:
"CHANGE_CURRENT_USER_PW Procedure"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) RETURN NUMBER;
Table 1-63 describes the parameters available in the SAVEKEY_NUM
procedure.
The following example shows how to use the SAVEKEY_NUM
function to set the wwv_flow_utilities.g_val_num
package variable to the value of 10
.
DECLARE VAL NUMBER; BEGIN VAL := APEX_UTIL.SAVEKEY_NUM(p_val => 10); END;
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) RETURN VARCHAR2;
Table 1-64 describes the parameters available in the SAVEKEY_VC2
function.
The following example shows how to use the SAVEKEY_VC2
function to set the wwv_flow_utilities.g_val_vc2
package variable to the value of 'XXX'.
DECLARE VAL VARCHAR2(4000); BEGIN VAL := APEX_UTIL.SAVEKEY_VC2(p_val => 'XXX'); END;
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-65 describes the parameters available in the SET_ATTRIBUTE
procedure.
Table 1-65 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 |
The following example shows how to use the SET_ATTRIBUTE
procedure to set the number 1 attribute for user 'FRANK' with the value 'foo'.
DECLARE VAL VARCHAR2(4000); BEGIN APEX_UTIL.SET_ATTRIBUTE ( p_userid => apex_util.get_user_id(p_username => 'FRANK'), p_attribute_number => 1, p_attribute_value => 'foo'); END;
See Also:
"GET_ATTRIBUTE Function"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 GuideAPEX_UTIL.SET_AUTHENTICATION_RESULT( p_code IN NUMBER);
Table 1-21 describes the parameters available in the SET_AUTHENTICATION_RESULT
procedure.
Table 1-66 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 GuideAPEX_UTIL.SET_CUSTOM_AUTH_STATUS( p_status IN VARCHAR2);
Table 1-67 describes the parameters available in the SET_CUSTOM_AUTH_STATUS
procedure.
Table 1-67 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-68 describes the parameters available in the SET_EMAIL
procedure.
Table 1-68 SET_EMAIL Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
The email address to be saved in user account |
The following example shows how to use the SET_EMAIL
procedure to set the value of EMAIL
to 'frank.scott@oracle.com' for the user 'FRANK'.
BEGIN APEX_UTIL.SET_EMAIL( p_userid => APEX_UTIL.GET_USER_ID('FRANK'), 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-69 describes the parameters available in the SET_FIRST_NAME
procedure.
Table 1-69 SET_FIRST_NAME Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
|
The following example shows how to use the SET_FIRST_NAME
procedure to set the value of FIRST_NAME
to 'FRANK' for the user 'FRANK'.
BEGIN APEX_UTIL.SET_FIRST_NAME( p_userid => APEX_UTIL.GET_USER_ID('FRANK'), p_first_name => 'FRANK'); END;
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-70 describes the parameters available in the SET_LAST_NAME
procedure.
Table 1-70 SET_LAST_NAME Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
|
The following example shows how to use the SET_LAST_NAME
procedure to set the value of LAST_NAME
to 'SMITH' for the user 'FRANK'.
BEGIN APEX_UTIL.SET_LAST_NAME( p_userid => APEX_UTIL.GET_USER_ID('FRANK'), p_last_name => 'SMITH'); END;
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-71 describes the parameters available in the SET_PREFERENCE
procedure.
Table 1-71 SET_PREFERENCE Parameters
Parameter | Description |
---|---|
|
Name of the preference (case-sensitive) |
|
Value of the preference |
|
User for whom the preference is being set |
The following example shows how to use the SET_PREFERENCE
procedure to set a preference called 'default_view
' to the value 'WEEKLY' that will persist beyond session for the currently authenticated user.
BEGIN APEX_UTIL.SET_PREFERENCE( p_preference => 'default_view', p_value => 'WEEKLY', p_user => :APP_USER); END;
This procedure sets the current application's Maximum Session Length in Seconds value for the current session, overriding the corresponding application attribute. This allows developers to dynamically shorten or lengthen the session life based on criteria determined after the user authenticates.
Note:
In order for this procedure to have any effect, the application's Maximum Session Length in Seconds attribute must have been set to a non-zero value in the application definition. This procedure will have no effect if that attribute was not set by the developer.APEX_UTIL.SET_SESSION_LIFETIME_SECONDS ( p_seconds IN NUMEBER, p_scope IN VARCHAR2 DEFAULT 'SESSION');
Table 1-74 describes the parameters available in the SET_SESSION_LIFETIME_SECONDS
procedure.
Table 1-72 SET_SESSION_LIFETIME_SECONDS Parameters
Parameter | Description |
---|---|
|
A positive integer indicating the number of seconds the session used by this application is allowed to exist. |
|
Defaults to 'SESSION' and may also be set to 'APPLICATION'. If 'SESSION', all applications using this session are affected. If 'APPLICATION', only the current application using the current session is affected. |
The following example shows how to use the SET_SESSION_LIFETIME_SECONDS procedure to set the current application's Maximum Session Length in Seconds attribute to 7200 seconds (two hours). This API call will have no effect if the application's Maximum Session Length in Seconds attribute was not set by the developer to a non-zero value in the application definition.By allowing the p_scope input parameter to use the default value of 'SESSION', the following example would actually apply to all applications using the current session. This would be the most common use case when multiple Application Express applications use a common authentication scheme and are designed to operate as a suite in a common session.
BEGIN APEX_UTIL.SET_SESSION_LIFETIME_SECONDS(p_seconds => 7200); END;
The following example shows how to use the SET_SESSION_LIFETIME_SECONDS procedure to set the current application's Maximum Session Length in Seconds attribute to 3600 seconds (one hour). This API call will have no effect if the application's Maximum Session Length in Seconds attribute was not set by the developer to a non-zero value in the application definition.By overriding the p_scope input parameter's default value and setting it to 'APPLICATION', the following example would actually apply to only to the current application using the current session even if other applications are using the same session.
BEGIN APEX_UTIL.SET_SESSION_LIFETIME_SECONDS(p_seconds => 3600, p_scope => 'APPLICATION'); END;
Sets the current application's Maximum Session Idle Time in Seconds value for the current session, overriding the corresponding application attribute. This allows developers to dynamically shorten or lengthen the maximum idle time allowed between page requests based on criteria determined after the user authenticates.
Note:
In order for this procedure to have any effect, the application's Maximum Session Idle Time in Seconds attribute must have been set to a non-zero value in the application definition. This procedure will have no effect if that attribute was not set by the developer.APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS ( p_seconds IN NUMEBER, p_scope IN VARCHAR2 DEFAULT 'SESSION');
Table 1-73 describes the parameters available in the SET_SESSION_MAX_IDLE_SECONDS
procedure.
Table 1-73 SET_SESSION_MAX_IDLE_SECONDS Parameters
Parameter | Description |
---|---|
|
A positive integer indicating the number of seconds allowed between page requests. |
|
Defaults to 'SESSION' and may also be set to 'APPLICATION'. If 'SESSION', this idle time applies to all applications using this session. If 'APPLICATION', this idle time only applies to the current application using the current session. |
The following example shows how to use the SET_SESSION_MAX_IDLE_SECONDS
procedure to set the current application's Maximum Session Idle Time in Seconds attribute to 1200 seconds (twenty minutes). This API call will have no effect if the application's Maximum Session Idle Time in Seconds attribute was not set by the developer to a non-zero value in the application definition.By allowing the p_scope
input parameter to use the default value of 'SESSION
', the following example would actually apply to all applications using the current session. This would be the most common use case when multiple Application Express applications use a common authentication scheme and are designed to operate as a suite in a common session.
BEGIN APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 1200); END;
The following example shows how to use the SET_SESSION_MAX_IDLE_SECONDS
procedure to set the current application's Maximum Session Idle Time in Seconds attribute to 600 seconds (ten minutes). This API call will have no effect if the application's Maximum Session Idle Time in Seconds attribute was not set by the developer to a non-zero value in the application definition.By overriding the p_scope
input parameter's default value and setting it to 'APPLICATION
', the following example would actually apply to only to the current application using the current session even if other applications are using the same session.
BEGIN APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => 600, p_scope => 'APPLICATION'); 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-74 describes the parameters available in the SET_SESSION_STATE
procedure.
Table 1-74 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 |
The following example shows how to use the SET_SESSION_STATE
procedure to set the value of the item 'my_item
' to 'myvalue
' in the current session.
BEGIN APEX_UTIL.SET_SESSION_STATE('my_item','myvalue'); END;
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-75 describes the parameters available in the SET_USERNAME
procedure.
Table 1-75 SET_USERNAME Parameters
Parameter | Description |
---|---|
|
The numeric ID of the user account |
|
|
The following example shows how to use the SET_USERNAME
procedure to set the value of USERNAME
to 'USER-XRAY' for the user 'FRANK'.
BEGIN APEX_UTIL.SET_USERNAME( p_userid => APEX_UTIL.GET_USER_ID('FRANK'), P_username => 'USER-XRAY'); END;
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, p_not_reusable_err OUT BOOLEAN);
Table 1-76 describes the parameters available in the STRONG_PASSWORD_CHECK
procedure.
Table 1-76 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 |
|
Result returns |
The following example shows how to use the STRONG_PASSWORD_CHECK
procedure. It checks the new password 'foo
' for the user 'SOMEBODY
' meets all the password strength requirements defined by the Oracle Application Express site administrator. If any of the checks fail (the associated OUT parameter returns TRUE
), then the example outputs a relevant message. For example, if the Oracle Application Express site administrator has defined that passwords must have at least one numeric character and the password 'foo
' was checked, then the p_one_numeric_err
OUT parameter would return TRUE
and the message 'Password must contain at least one numeric character' would be output.
DECLARE l_username varchar2(30); l_password varchar2(30); l_old_password varchar2(30); l_workspace_name varchar2(30); l_min_length_err boolean; l_new_differs_by_err boolean; l_one_alpha_err boolean; l_one_numeric_err boolean; l_one_punctuation_err boolean; l_one_upper_err boolean; l_one_lower_err boolean; l_not_like_username_err boolean; l_not_like_workspace_name_err boolean; l_not_like_words_err boolean; l_not_reusable_err boolean; l_password_history_days pls_integer; BEGIN l_username := 'SOMEBODY'; l_password := 'foo'; l_old_password := 'foo'; l_workspace_name := 'XYX_WS'; l_password_history_days := apex_instance_admin.get_parameter ('PASSWORD_HISTORY_DAYS'); APEX_UTIL.STRONG_PASSWORD_CHECK( p_username => l_username, p_password => l_password, p_old_password => l_old_password, p_workspace_name => l_workspace_name, p_use_strong_rules => false, p_min_length_err => l_min_length_err, p_new_differs_by_err => l_new_differs_by_err, p_one_alpha_err => l_one_alpha_err, p_one_numeric_err => l_one_numeric_err, p_one_punctuation_err => l_one_punctuation_err, p_one_upper_err => l_one_upper_err, p_one_lower_err => l_one_lower_err, p_not_like_username_err => l_not_like_username_err, p_not_like_workspace_name_err => l_not_like_workspace_name_err, p_not_like_words_err => l_not_like_words_err, p_not_reusable_err => l_not_reusable_err); IF l_min_length_err THEN htp.p('Password is too short'); END IF; IF l_new_differs_by_err THEN htp.p('Password is too similar to the old password'); END IF; IF l_one_alpha_err THEN htp.p('Password must contain at least one alphabetic character'); END IF; IF l_one_numeric_err THEN htp.p('Password must contain at least one numeric character'); END IF; IF l_one_punctuation_err THEN htp.p('Password must contain at least one punctuation character'); END IF; IF l_one_upper_err THEN htp.p('Password must contain at least one upper-case character'); END IF; IF l_one_lower_err THEN htp.p('Password must contain at least one lower-case character'); END IF; IF l_not_like_username_err THEN htp.p('Password may not contain the username'); END IF; IF l_not_like_workspace_name_err THEN htp.p('Password may not contain the workspace name'); END IF; IF l_not_like_words_err THEN htp.p('Password contains one or more prohibited common words'); END IF; IF l_not_reusable_err THEN htp.p('Password cannot be used because it has been used for the account within the last '||l_password_history_days||' days.'); END IF; END;
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-77 describes the parameters available in the STRONG_PASSWORD_VALIDATION
function.
Table 1-77 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 |
The following example shows how to use the STRONG_PASSWORD_VALIDATION
procedure. It checks the new password 'foo
' for the user 'SOMEBODY
' meets all the password strength requirements defined by the Oracle Application Express site administrator. If any of the checks fail, then the example outputs formatted HTML showing details of where the new password fails to meet requirements.
DECLARE l_username varchar2(30); l_password varchar2(30); l_old_password varchar2(30); l_workspace_name varchar2(30); BEGIN l_username := 'SOMEBODY'; l_password := 'foo'; l_old_password := 'foo'; l_workspace_name := 'XYX_WS'; HTP.P(APEX_UTIL.STRONG_PASSWORD_VALIDATION( p_username => l_username, p_password => l_password, p_old_password => l_old_password, p_workspace_name => l_workspace_name)); END;
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-78 describes the parameters available in the STRING_TO_TABLE
function.
Table 1-78 STRING_TO_TABLE Parameters
Parameter | Description |
---|---|
|
String to be converted into a PL/SQL table of type |
|
String separator. The default is a colon |
The following example shows how to use the STRING_TO_TABLE function. The function is passed the string 'One:Two:Three' in the p_string parameter and it returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2 containing 3 elements, the element at position 1 contains the value 'One', position 2 contains the value 'Two' and position 3 contains the value 'Three'. This is then output using the HTP.P function call.
DECLARE l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2; BEGIN l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE('One:Two:Three'); FOR z IN 1..l_vc_arr2.count LOOP htp.p(l_vc_arr2(z)); END LOOP; END;
See Also:
"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 (:).
APEX_UTIL.TABLE_TO_STRING ( p_table IN APEX_APPLICATION_GLOBAL.VC_ARR2, p_string IN VARCHAR2 DEFAULT ':') RETURN VARCHAR2;
Table 1-79 describes the parameters available in the TABLE_TO_STRING
function.
Table 1-79 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 |
The following example shows how to use the TABLE_TO_STRING
function. The example first calls STRING_TO_TABLE
which is passed the string 'One:Two:Three' in the p_string
parameter, and returns a PL/SQL array of type APEX_APPLICATION_GLOBAL.VC_ARR2
containing 3 elements, the element at position 1 contains the value 'One', position 2 contains the value 'Two' and position 3 contains the value 'Three'. This array is then passed in to the TABLE_TO_STRING
function in the p_string
parameter, which then returns back the original string 'One:Two:Three'.
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;
See Also:
"STRING_TO_TABLE Function"Makes expired end users accounts and the associated passwords usable, enabling a end user to log in to developed applications.
APEX_UTIL.UNEXPIRE_END_USER_ACCOUNT ( p_user_name IN VARCHAR2);
Table 1-80 describes the parameters available in the UNEXPIRE_END_USER_ACCOUNT
procedure.
Table 1-80 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;
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-81 describes the parameters available in the UNEXPIRE_WORKSPACE_ACCOUNT
procedure.
Table 1-81 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;
Sets 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-82 describes the parameters available in the UNLOCK_ACCOUNT
procedure.
Table 1-82 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;
The following special characters are encoded as follows:
Special After Characters Encoding % %25 + %2B space + . %2E * %2A ? %3F \ %5C / %2F > %3E < %3C } %7B { %7D ~ %7E [ %5B ] %5D ' %60 ; %3B ? %3F @ %40 & %26 # %23 | %7C ^ %5E : %3A = %3D $ %24
APEX_UTIL.URL_ENCODE ( p_url IN VARCHAR2) RETURN VARCHAR2;
Table 1-83 describes the parameters available in the URL_ENCODE
function.
The following example shows how to use the URL_ENCODE
function.
DECLARE l_url VARCHAR2(255); BEGIN l_url := APEX_UTIL.URL_ENCODE('http://www.myurl.com?id=1&cat=foo'); END;
In this example, the following URL:
http://www.myurl.com?id=1&cat=foo
Would be returned as:
http%3A%2F%2Fwww%2Emyurl%2Ecom%3Fid%3D1%26cat%3Dfoo
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-84 describes the parameters available in the WORKSPACE_ACCOUNT_DAYS_LEFT
procedure.
Table 1-84 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) htp.p('Workspace Account:'||c1.user_name||' will expire in '||l_days_left||' days.'); END LOOP; END;