Export static workspace files in APEX5

In APEX 5 the functionality for the workspace- and application files is improved tremendously.

The most important changes are:
1. It is possible to upload zip files which will be extracted in the database.
2. The files can be organized within directories.

With this new functionality it is a lot easier to push your static files from development to other environments like acceptance and production.

Although it is here where a problem arises. At our site we deliver our applications via SQL scripts. When APEX exports an application it does include the static application files nicely into the export file. But what if you want to deliver your static workspace files as well? There is no check-box or what so ever to tell APEX to include these files in the export. I have searched but could not find functionality in APEX to achieve this. Normally google and twitter are your best friends in this situations but to my disappointment there was also no solution available on the internet. I had to figure this one out for myself.

In the wwv_flow_api package in the apex schema is a procedure to create a static workspace file.

procedure create_workspace_static_file (
p_id in number default null,
p_file_name in varchar2,
p_mime_type in varchar2,
p_file_charset in varchar2 default null,
p_file_content in blob );

With this procedure you can upload a BLOB to the APEX static workspace files. All we need to do is write a script which will take all the static workspace files and create a PL/SQL block for each of them with the above procedure. There are several ways to create a script. In this example we will use dbms_output to get it done. After running the script, save the dbms output data to create the script.

The script looks as follows, in this script it is explained what is done and why.

declare
-- workspace to export the static file from
l_workspace varchar2(255) := 'APEX5';

-- cursor for all static workspace files
cursor c_file is
select w.id
, w.security_group_id
, w.file_name
, w.mime_type
, w.file_charset
, w.file_content
from apex_050000.wwv_flow_company_static_files w
join apex_050000.wwv_flow_companies c
on c.provisioning_company_id = w.security_group_id
where c.display_name = l_workspace;

begin
-- loop throug all files
for r_file in c_file loop
-- start pl/sql block
dbms_output.put_line('begin');
-- set the security group id to make it possible to use the script outside APEX (toad,sqldeveloper)
dbms_output.put_line(' wwv_flow_api.set_security_group_id(p_security_group_id=> '||r_file.security_group_id||');');
-- create varchar table to hold the data for the BLOB
dbms_output.put_line(' wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;');
-- loop through LOB in blocks of 200 to improve readabiliy of the end script
for i in 1 .. trunc(dbms_lob.getlength(r_file.file_content)/200)+sign(mod(dbms_lob.getlength(r_file.file_content),200))
loop
-- save to raw file data from the blob to a varchar table
dbms_output.put_line(' wwv_flow_api.g_varchar2_table('||i||') := '''
||utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(dbms_lob.substr(r_file.file_content,200,((i-1)*200)+1)))
||''''||';');
end loop;
-- create the create_workspace_static_file procedure with the correct parameters
dbms_output.put_line(' wwv_flow_api.create_workspace_static_file('
dbms_output.put_line(' p_file_name => '''||r_file.file_name||'''');
dbms_output.put_line(' , p_mime_type => '''||r_file.mime_type||'''');
dbms_output.put_line(' , p_file_charset => '''||r_file.file_charset||'''');
-- convert the varchar table to a blob as input for the procedure
dbms_output.put_line(' , p_file_content => wwv_flow_api.varchar2_to_blob(wwv_flow_api.g_varchar2_table)');
dbms_output.put_line(' );');
dbms_output.put_line('end;');
end loop;
-- commit your work
dbms_output.put_line('commit;');
end;

This script will deliver output that looks like this:

begin
wwv_flow_api.set_security_group_id(p_security_group_id=> 5325975770000001);
wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;
wwv_flow_api.g_varchar2_table(1) := '406368617273657420225554462D38223B0A0A756C2E64726F70646F776E2C0A756C2E64726F70646F776E206C692C0A756C2E64726F70646F776E20756C207B0A206C6973742D7374796C653A206E6F6E653B0A206D617267696E3A20303B0A2070616464696E673A20303B0A7D0A0A756C2E64726F70646F776E207B0A20706F736974696F6E3A2072656C61746976653B0A207A2D696E6465783A203539373B0A20666C6F61743A206C6566743B0A7D0A0A756C2E64726F70646F776E206C69207B0A20666C6F';
wwv_flow_api.g_varchar2_table(2) := '61743A206C6566743B0A206D696E2D6865696768743A203170783B0A206C696E652D6865696768743A20312E33656D3B0A20766572746963616C2D616C69676E3A206D6964646C653B0A7D0A0A756C2E64726F70646F776E206C692E686F7665722C0A756C2E64726F70646F776E206C693A686F766572207B0A20706F736974696F6E3A2072656C61746976653B0A207A2D696E6465783A203539393B0A20637572736F723A2064656661111111232334555532212111126F70646F776E20756C207B0A20766973';
wwv_flow_api.g_varchar2_table(3) := '6962696C6974793A2068696464656E3B0A20706F736974696F6E3A206162736F6C7574653B0A20746F703A20313030253B0A206C6566743A20303B0A207A2D696E6465783A203539383B0A7D0A0A756C2E64726F70646F776E20756C206C69207B0A20666C6F61743A206E6F6E653B0A7D0A0A756C2E64726F70646F776E20756C20756C207B0A20746F703A203170783B0A206C6566743A203939253B0A7D0A0A756C2E64726F70646F776E206C693A686F766572203E20756C207B0A207669736962696C697479';
wwv_flow_api.g_varchar2_table(4) := '3A2076697369626C653B0A7D';
wwv_flow_api.create_workspace_static_file(p_file_name => 'css/xxxxx.css'
, p_mime_type => 'text/css'
, p_file_charset => 'utf-8'
, p_file_content => wwv_flow_api.varchar2_to_blob(wwv_flow_api.g_varchar2_table)
);
end;
begin
wwv_flow_api.set_security_group_id(p_security_group_id=> 5325975770000001);
wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;
wwv_flow_api.g_varchar2_table(1) := '406368617273657420225554462D38223B0D0A0D0A756C2E64726F70646F776E2C0D0A756C2E64726F70646F776E206C692C0D0A756C2E64726F70646F776E20756C207B0D0A20206865696768743A206175746F3B0D0A20206261636B67726F756E642D636F6C6F723A20236638663866383B0D0A7D0D0A0D0A756C2E64726F70646F776E207B0D0A2020666F6E742D7765696768743A20626F6C643B0D0A2020666F6E742D73697A653A20313270783B0D0A2020706F736974696F6E3A2072656C61746976653B';
wwv_flow_api.g_varchar2_table(2) := '0D111110746F703A203070783B0D0A20206C6566743A203070783B0D0A2020626F726465722D72696768743A2031707820736F6C6964207267626128302C302C302C302E3135293B0D0A20206D617267696E3A203020302031367078203670783B0D0A7D0D0A0D0A756C2E64726F70646F776E206C69207B0D0A202070616464696E673A20303B0D0A7D0D0A0D0A756C2E64726F70646F776E206C693A686F766572207B0D0A20206261636B67726F756E642D636F6C6F723A20236565653B0D0A7D0D0A0D0A756C';
wwv_flow_api.g_varchar2_table(3) := '2E64726F70646F776E206C692061207B0D0A20646973706C61793A20626C6F636B3B0D0A2070616464696E673A203670782031307078203470783B0D0A2077686974652D73706163653A206E6F777261703B0D0A7D0D0A0D0A756C2E64726F70646AFFAF12aA5A6C696E6B2C0D0A756C2E64726F70646F776E20613A76697369746564207B0D0A2020636F6C6F723A20233535353B0D0A2020746578742D6465636F726174696F6E3A206E6F6E653B0D0A7D0D0A0D0A756C2E64726F70646F776E20613A686F7665';
wwv_flow_api.g_varchar2_table(4) := '722C0D0A756C2E64726F70646F776E20613A616374697665207B0D0A2020636F6C6F723A20233535353B0D0A7D0D0A0D0A756C2E64726F70646F776E20756C207B0D0A2020626F726465723A2031707820736F6C6964207267626128302C302C302C302E3135293B0D0A7D0D0A0D0A2E6E61762D6172726F77207B0D0A2020666C6F61743A2072696768742021696D706F7274616E743B0D0A2020706F736974696F6E3A2072656C61746976653B0D0A20206C6566743A203870783B0D0A2020746F703A20307078';
wwv_flow_api.g_varchar2_table(5) := '3B0D0A7D0D0A0D0A236E61762D6C332C0D0A236E61762D6C34207B0D0A2020626F726465722D6C6566743A2031707820736F6C6964207267626128302C302C302C302E3135293B0D0A2020626F726465722D626F74746F6D3A2031707820736F6C6964207267626128302C302C302C302E3135293B0D0A7D0D0A0D0A';
wwv_flow_api.create_workspace_static_file(p_file_name => 'js/xxxxx.js'
, p_mime_type => 'application/x-javascript'
, p_file_charset => 'utf-8'
, p_file_content => wwv_flow_api.varchar2_to_blob(wwv_flow_api.g_varchar2_table)
);
end;
commit;

It is obvious that all settings like the directory, mime-type and character set are unchanged. Save the output as a script and add this script to your other install scripts like the application export SQL.

I hope in the future there will be a check-box in APEX which makes it possible to export the static workspace files with the application export. That would make this script unnecessary and life just a bit easier ;-).