How to copy users to new workspace in Application Express

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.