declare
l_workspace_id number;
l_workspace_base constant varchar2(30) := 'MY_WORKSPACE_NAME';
l_db_user_base constant varchar2(30) := 'MY_WORKSPACE_USER';
l_password_base constant varchar2(30) := 'MyTempPassword_01';
i integer;
begin
execute immediate 'create user '||l_db_user_base||' identified by "'||l_password_base||i||
'" default tablespace DATA quota unlimited on DATA';
dbms_output.put_line('create user '||l_db_user_base);
--grant all the privileges that a db user would get if provisioned by APEX
for c1 in (select privilege
from sys.dba_sys_privs
where grantee = 'APEX_GRANTS_FOR_NEW_USERS_ROLE' ) loop
execute immediate 'grant '||c1.privilege||' to '||l_db_user_base;
dbms_output.put_line('grant '||c1.privilege||' to '||l_db_user_base);
end loop;
apex_instance_admin.add_workspace(
p_workspace => l_workspace_base,
p_primary_schema => l_db_user_base);
dbms_output.put_line('add_workspace '||l_workspace_base||' in '||l_db_user_base);
apex_util.set_workspace(
p_workspace => l_workspace_base);
dbms_output.put_line('set_workspace '||l_workspace_base);
apex_util.create_user(
p_user_name => l_db_user_base,
p_web_password => l_password_base,
p_developer_privs => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
p_email_address => l_db_user_base||'@example.com',
p_default_schema => l_db_user_base,
p_change_password_on_first_use => 'N' );
dbms_output.put_line('create_user '||l_db_user_base);
apex_util.set_workspace(
p_workspace => 'PROVISION');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('EXCEPTION OTHERS: '||SQLCODE||' - '||SQLERRM);
end;