Sometimes I need a new APEX workspace, but I'm already connected to the database. Instead of opening Administration Services and going through the wizard, I can create the workspace and its admin user directly with PL/SQL.
For this example, DEMO_APP is an existing database schema. I'll create DEMO_WORKSPACE and add DEMO_ADMIN as its administrator.
Creating the workspace and administrator
Run this in the database where APEX is installed, using an account with APEX instance administration privileges:
DECLARE
l_workspace_id NUMBER;
BEGIN
apex_instance_admin.add_workspace(
p_workspace => 'DEMO_WORKSPACE',
p_primary_schema => 'DEMO_APP',
p_additional_schemas => NULL
);
l_workspace_id := apex_util.find_security_group_id(
p_workspace => 'DEMO_WORKSPACE'
);
apex_util.set_security_group_id(
p_security_group_id => l_workspace_id
);
apex_util.create_user(
p_user_name => 'DEMO_ADMIN',
p_email_address => 'admin@example.com',
p_web_password => 'REPLACE_WITH_A_TEMPORARY_PASSWORD',
p_default_schema => 'DEMO_APP',
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_change_password_on_first_use => 'Y'
);
COMMIT;
END;
/ADD_WORKSPACE creates the workspace and maps it to the existing schema. APEX generates the workspace ID automatically.
The part that matters most is SET_SECURITY_GROUP_ID. It sets the workspace context before CREATE_USER runs. The ADMIN privilege gives the new account Workspace Administrator access.
Replace the example email and password before running the script. Don't store real passwords in a script committed to Git.
Checking the result
I can check the new administrator through the APEX metadata view:
SELECT workspace_name, user_name, is_admin
FROM apex_workspace_apex_users
WHERE workspace_name = 'DEMO_WORKSPACE'
AND user_name = 'DEMO_ADMIN';The IS_ADMIN column should show Yes.
One detail worth knowing: APEX_UTIL.CREATE_USER creates an account in the native APEX user repository, not a database user. If the development environment authenticates through Database Accounts, the user also needs a database account.
This is a simple one-time setup script. If I wanted to use it repeatedly for environment provisioning, I'd add checks for an existing workspace and user.