Bulk loading a template APEX application in a single workspace

I recently had a need to import the same application multiple times - 30+, so each person accessing the workspace could use their own application to play around with. And the user list was supplied to me in the format of an Excel spreadsheet. So, first in the Excel sheet, I had to make a new column to derive some sort of unique value - what better than the first letter of their first name and the surname. Like John Smith -> JSMITH.

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 :)