In Application Express, there is no standard way of copying users in an existing workspace to a new workspace. Everytime you create a new workspace, all developers and users must be added manually.
Here’s a little script that does the work for you. Just replace the WORKSPACE with the name of your own workspace where you want to copy the users from, and execute in a SQL Command window in a new workspace. Might save you some time!
declare
cursor c_usr is
select t.user_name, t.first_name, t.last_name, t.email_address
, t.default_schema
, fd.developer_role
from APEX_040100.WWV_FLOW_FND_USER t
join apex_040100.wwv_flow_developers fd on fd.user_id = t.user_id
where t.security_group_id = ( select cp.PROVISIONING_COMPANY_ID
from apex_040100.wwv_flow_companies cp
where cp.short_name = 'WORKSPACE')
;
begin
for r_usr in c_usr loop
APEX_UTIL.CREATE_USER(
p_user_name => r_usr.user_name ,
p_first_name => r_usr.first_name,
p_last_name => r_usr.last_name,
p_description => null,
p_email_address => r_usr.email_address,
p_web_password => 'Welcome01',
p_developer_privs => r_usr.developer_role,
p_default_schema => r_usr.default_schema,
p_allow_access_to_schemas => null,
p_change_password_on_first_use => 'Y'
);
end loop;
end;
I never understood why this is not a standard function in Application Express, but the above snippet is easily converted into a function for reuse.
