Static File not updating in runtime APEX environment
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.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.