FETCH_USER Procedure Signature 3

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

Note:

This procedure operates on the native Oracle APEX user accounts repository and is only applicable to applications configured with Oracle APEX Accounts authentication.

Syntax

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

Parameters

Table 59-1 Fetch_User Parameters Signature 3

Parameter	Description
`p_user_id`	Numeric primary key of the user account.
`p_workspace`	The name of the workspace.
`p_user_name`	Alphanumeric name used for login. See also GET_USERNAME Function
`p_first_name`	Informational. See also GET_FIRST_NAME Function
`p_last_name`	Informational. See also GET_LAST_NAME Function
`p_web_password`	Obfuscated account password.
`p_email_address`	Email address. See also GET_EMAIL Function
`p_start_date`	Unused.
`p_end_date`	Unused.
`p_employee_id`	Unused.
`p_allow_access_to_schemas`	A list of schemas assigned to the user's workspace to which user is restricted.
`p_person_type`	Unused.
`p_default_schema`	A database schema assigned to the user's workspace, used by default for browsing. See also GET_DEFAULT_SCHEMA Function
`p_groups`	List of groups of which user is a member. See also GET_GROUPS_USER_BELONGS_TO Function CURRENT_USER_IN_GROUP Function
`p_developer_role`	Colon-separated list of developer roles. The following are acceptable values for this parameter: `null` - Indicates an end user (a user who can only authenticate to developed applications). `CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL` - Indicates a user with developer privilege. `ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL` - Indicates a user with full workspace administrator and developer privilege. Note: Currently this parameter is named inconsistently between the `CREATE_USER`, `EDIT_USER` and `FETCH_USER` APIs, although they all relate to the `DEVELOPER_ROLE` field stored in the named user account record. `CREATE_USER` uses `p_developer_privs`, `EDIT_USER` uses `p_developer_roles` and `FETCH_USER` uses `p_developer_role`. See also GET_USER_ROLES Function
`p_description`	Informational.
`p_account_expiry`	Date account password was last reset. See also END_USER_ACCOUNT_DAYS_LEFT Function WORKSPACE_ACCOUNT_DAYS_LEFT Function
`p_account_locked`	Locked/Unlocked indicator `Y` or `N`. See also GET_ACCOUNT_LOCKED_STATUS Function
`p_failed_access_attempts`	Counter for consecutive login failures.
`p_change_password_on_first_use`	Setting to force password change on first use `Y` or `N`.
`p_first_password_use_occurred`	Indicates whether login with password occurred `Y` or `N`.

Example

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;