42.31 FETCH_USER Procedure Signature 3

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

Syntax for Signature 3

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

Parameters for Signature 3

Table 42-29 Fetch_User Parameters Signature 3

Parameter Description

p_user_id

Numeric primary key of the user account.

p_workspace

The name of the workspace.

p_user_name

Alphanumeric name used for login.

See Also: "GET_USERNAME Function"

p_first_name

Informational.

See Also: "GET_FIRST_NAME Function"

p_last_name

Informational.

See Also: "GET_LAST_NAME Function"

p_web_password

Obfuscated account password.

p_email_address

Email address.

See Also: "GET_EMAIL Function"

p_start_date

Unused.

p_end_date

Unused.

p_employee_id

Unused.

p_allow_access_to_schemas

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

p_person_type

Unused.

p_default_schema

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

See Also: "GET_DEFAULT_SCHEMA Function"

p_groups

List of groups of which user is a member.

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

p_developer_role

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

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

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

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

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

See Also: "GET_USER_ROLES Function"

p_description

Informational.

p_account_expiry

Date account password was last reset.

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

p_account_locked

Locked/Unlocked indicator Y or N.

See Also: "GET_ACCOUNT_LOCKED_STATUS Function"

p_failed_access_attempts

Counter for consecutive login failures.

p_change_password_on_first_use

Setting to force password change on first use Y or N.

p_first_password_use_occurred

Indicates whether login with password occurred Y or N.

Example for Signature 3

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

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