The UAT environment is a runtime Apex installation (4.2.4.00.08) and all deployments are done via SQL scripts. My application uses a small number of static files that for convenience we are serving from Apex (at least for now); to deploy changes to these static files, I export f100.sql and static_files.sql from Apex in dev and we run them in the target environment after setting a few variables, like this:
declare v_workspace CONSTANT VARCHAR2(100) := 'MYWORKSPACE'; v_workspace_id NUMBER; begin select workspace_id into v_workspace_id from apex_workspaces where workspace = v_workspace; apex_application_install.set_workspace_id (v_workspace_id); apex_util.set_security_group_id (p_security_group_id => apex_application_install.get_workspace_id); apex_application_install.set_schema('MYSCHEMA'); apex_application_install.set_application_id(100); end; / @f100.sql @static_file.sql
Many months after this application went live, and after multiple deployments in all the environments, we suddenly had an issue where the static files being served from one instance (UAT) were an older version. The logs showed the correct files had been deployed, and re-deploying into DEV seemed to work fine. I got the DBA to temporarily change the schema password in UAT so I could login to see what was going on.
When I ran this query in DEV, I got the expected two records:
select * from apex_workspace_files where file_name in ('myapp.css', 'myapp.js');
When I ran it in UAT, I got four records – two copies of each file, and the BLOB contents showed that the older copies were the ones being served to the clients. I have no idea how the extra copies got created in that environment. It must have been due to a failed deployment but the deployment logs didn’t seem to show any errors or anomalies.
Killing the Zombie Static File
I tried editing the static_file.sql script to remove the files (as below), but it only ever removed the new files that were created; re-running it never causes it to drop the old file copies.
... declare l_name varchar2(255); begin l_name := 'myapp.css'; wwv_flow_html_api.remove_html( p_html_name => l_name, p_flow_id => nvl(wwv_flow.g_flow_id, 0) ); end; / ...
Next thing I tried was something I picked up from here:
NOTE: run this at your own risk! It is not supported by Oracle.
declare v_workspace CONSTANT VARCHAR2(100) := 'MYWORKSPACE'; v_workspace_id NUMBER; begin *** WARNING: DO NOT RUN THIS UNLESS YOU KNOW WHAT YOU ARE DOING *** select workspace_id into v_workspace_id from apex_workspaces where workspace = v_workspace; apex_application_install.set_workspace_id (v_workspace_id); apex_util.set_security_group_id (p_security_group_id => apex_application_install.get_workspace_id); delete from wwv_flow_files where filename like 'myapp.%'; * commit; end; /
That seemed to do the trick. Thankfully this problem only occurred in a test environment – I would be uncomfortable running this in Prod.