So, first I made a new column with the value as a formula, which was: =LEFT(A2,1). That would give me "J" for "John.
Then, for the actual account ID I come up with the formula =UPPER(CONCAT(C2, B2)) Where C2=J and B2 = SMITH, giving me JSMITH.
With that list, I can copy it over to my trusty text editor in order to manipulate to create an array.
The first part I wanted to do was create all the users, so for that I can use raw PL/SQL, and come up with a block of code like so:
declare type t_users is table of varchar2(200) index by PLS_INTEGER; l_all_users t_users; l_ws_id NUMBER; procedure append_list( p_emps in out t_users, p_new_person in varchar2 ) as begin p_emps(p_emps.count + 1) := p_new_person; end append_list; begin l_ws_id := apex_util.find_security_group_id('WORKSPACE_NAME'); apex_util.set_security_group_id(l_ws_id); append_list(l_all_employees, 'USER1'); append_list(l_all_employees, 'USER2'); for i in 1..l_all_employees.count loop apex_util.create_user( p_user_name => l_all_employees(i), p_web_password => l_all_employees(i), p_developer_privs => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL', p_change_password_on_first_use => 'N', p_allow_app_building_yn => 'Y', p_allow_sql_workshop_yn => 'Y', p_allow_team_development_yn => 'Y' ); end loop; end; /
Run that, and you should find all the users now have accounts in the workspace - I just assigned the password value as the same as the username to keep things simple.
So, the next part was loading the applications. APEX comes with the API APEX_APPLICATION_INSTALL, so if we look at the documentation, we can see a simple import script as:
begin apex_application_install.generate_application_id; apex_application_install.generate_offset; apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id ); end; / @f645.sql
So, we need to run an anonymous block, and then the actual application export script - which we can't put inside an anonymous block.
So, there comes in the scripting functionality of SQLcl. It support running JavaScript, so from the first step, I can make an Array of all the users, but this time in JavaScript. Then we can just loop over that structure and set the statement to run - which we do two separate statements, 1. the anonymous block; and 2. the application import script:
function runCmd(cmd){ sqlcl.setStmt(cmd); sqlcl.run(); } var people = [ 'USER1', 'USER2' ]; for (var i = 0; i < people.length; i++){ print ('Loading application for: ' + people[i]); runCmd([ "declare", " l_ws_id number;", "begin", " l_ws_id := apex_util.find_security_group_id('WS_NAME');", " apex_util.set_security_group_id(l_ws_id);", " apex_application_install.generate_application_id;", " apex_application_install.generate_offset;", " apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id);", " apex_application_install.set_application_name( '" + people[i] + " - Workshop Application' );", "end;", "/" ].join("\r\n") ); runCmd('@f237.sql'); print ('......next ->'); }
Then, I just connect to my database in SQLcl and run that script (named appLoader.js)
conn user/pass@DEV script appLoader.js
note: I could have just run everything (creating the user account and importing the app) in the SQLcl script, but this is just the process I followed as I was trying to figure things out :)