CODEWITHSUNDEEP
oracle-apexapex
ss_beer
ss_beer

Reputation: 123

create apex user via plsql script

Run script from https://www.talkapex.com/2012/08/how-to-create-apex-session-in-plsql and get error

[Error] Execution (1: 1): ORA-20987: APEX - Application ID and current security group ID are not consistent. - Contact your application administrator.
Details about this incident are available via debug id "78243".
ORA-06512: at "APEX_190100.WWV_FLOW_ERROR", line 1381
ORA-06512: at "APEX_190100.WWV_FLOW_ERROR", line 1416
ORA-06512: at "APEX_190100.WWV_FLOW_CUSTOM_AUTH_STD", line 724
ORA-06512: at "APEX_190100.WWV_FLOW_CUSTOM_AUTH_STD", line 588
ORA-06512: at "APEX_190100.HTMLDB_CUSTOM_AUTH", line 260
ORA-06512: at "USER.SP_CREATE_APEX_SESSION", line 30
ORA-06512: at line 2

error raise from

apex_custom_auth.post_login(
    p_uname => p_app_user,
    p_session_id => APEX_CUSTOM_AUTH.GET_NEXT_SESSION_ID,
    p_app_page => apex_application.g_flow_id||':'||p_app_page_id);

how to properly create a user in need wokrspace? Apex version 19.1

and create user

BEGIN
    FOR C1 IN (SELECT WORKSPACE_ID
                 FROM APEX_APPLICATIONS
                WHERE APPLICATION_ID = 4050)
    LOOP
        APEX_UTIL.SET_SECURITY_GROUP_ID (
            P_SECURITY_GROUP_ID   => C1.WORKSPACE_ID);
    END LOOP;
    
    APEX_UTIL.CREATE_USER (
        P_USER_NAME                 => 'BOB',
        P_EMAIL_ADDRESS             => '[email protected]',
        P_DEFAULT_SCHEMA            => 'MY_SPACE',
        P_ALLOW_ACCESS_TO_SCHEMAS   => 'MY_SPACE',
        P_WEB_PASSWORD              => 'change_me',
        P_DEVELOPER_PRIVS           =>
            'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'); -- workspace administrator

    COMMIT;
END;

i have same error

[Error] Execution (2: 1): ORA-20987: APEX - Security Group ID (your workspace identity) is invalid. - Contact your application administrator.

declare
    l_workspace_id      number;
begin
    l_workspace_id := apex_util.find_security_group_id (p_workspace => 'INTERNAL');
    apex_util.set_security_group_id (p_security_group_id => l_workspace_id);    
    apex_util.create_user(
        p_user_name    => 'NEWUSER1',
        p_web_password => 'secret99');
    commit;
end;

Error at line 1 ORA-20001: Package variable g_security_group_id must be set. ORA-06512: at "APEX_190100.WWV_FLOW_API", line 1828 ORA-06512: at "APEX_190100.WWV_FLOW_API", line 1863 ORA-06512: at "APEX_190100.WWV_FLOW_FND_USER_INT", line 1977 ORA-06512: at "APEX_190100.HTMLDB_UTIL", line 1452 ORA-06512: at line 6

SELECT WORKSPACE,WORKSPACE_DISPLAY_NAME, OWNER, APPLICATION_GROUP_ID  FROM APEX_APPLICATIONS WHERE WORKSPACE = 'INTERNAL' AND APPLICATION_ID = 4050;

INTERNAL >INTERNAL
APEX_190100
9.1051E+16 1 row selected.

Upvotes: 1

Views: 3297

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18630

Ok, now this makes more sense. You're trying to create a user but it fails when trying to create a session prior to creating the user. You don't need a session to create a user, you just need the security group to be set. This can be done using APEX_UTIL.SET_SECURITY_GROUP_ID. I executed this code in my environment, connected via sqldeveloper:

DECLARE
    l_workspace_id      number;
BEGIN
    l_workspace_id := apex_util.find_security_group_id (p_workspace => '<myworkspacename>');
    apex_util.set_security_group_id (p_security_group_id => l_workspace_id);    
    APEX_UTIL.CREATE_USER(
        p_user_name    => 'NEWUSER1',
        p_web_password => 'secret99');
    COMMIT;
END;
/

and the user was created without errors.

Upvotes: 3

Related Questions