This manifested itself as follows: after deleting old version of a file (ex. javascript library) and uploading new one with some fixes, APEX stubbornly refused to refresh the source and serves the old version of the file. I could not stop wondering how was it even possible though I removed the previous version of the file. Refreshing and clearing browser cache didn’t help. The most effective way to solve this problem was loading the file under a different name and changing the link in the page template.

One day, when once again I had to go through all this, I decided that I would find the cause of the problem and fix it.

Private investigation

I began by determining whether the problem lies with the client (browser), or on the side of database (Oracle APEX). Since you’ve already noticed that the browser cache cleaning does not help, my first suspicion fell on APEX. However, in order to eliminate the risk of confusion, to verify my thesis I used a browser other than that I use every day to work. To verify this, i called manually get_file procedure. If you still don’t know it, you can do so by using a link that looks something like this:

Of course, instead of #WORKSPACE_ID# and #filename#, you should substitute respectively APEX workspace ID and name of the file you want to download. Part &p_flow_id=#APP_ID# occurs if the file is assigned to the application. This URL can be easily obtained using the console. For example, in Chrome, you can use Resources tab to find the interesting file (for javascript this will be Frames -> (f) -> Script). For more information about file management in Oracle APEX, check out this entry.

Calling such an URL returned to me the old version of the script. So I was right – the fault is on the side of Oracle Application Express!

The next step was to search the Internet in search of evidence that I am not the first to have this problem. It wasn’t hard to find records of people who were asking how to deal with it. It was harder to find some answers. After digging through a ton of posts in various forums in the end I came across something that gave hope to solve the puzzle. On the OTN forum I came accross a posting from 2008 for APEX 3.0.1 version. It turns out that the same problem persists Version 4.2.1. ! You can find the posting here.

In short: the problem is that when trying to load a file with the same name as the previously uploaded file, we’ll get the unique constraint violation error. Everything would be OK if it weren’t that, after all the file is loaded to the APEX! It can’t be deleted because it is not visible from APEX level. Even if we remove the old version of the file (the one because of which we could not previously properly load a new file) and load the new version of the file, it is likely that APEX will start referring to the “invisible” file. And even if the next time we start by removing the old version of the file and then loading new, APEX will be serving “invisible” file in the old version all the time.

Solution

I found solution of this problem on this blog. As you can guess, it involves manual “cleaning up” of this mess. To be able to access the files stored by APEX, you must first set the context. To do this, follow these steps:

1. You must log in to the scheme on which the APEX workspace stands (you can also do it from SQL Workshop inside APEX).
2. Run this script:
   

    

   Oracle PL/SQL

   <br /><br /> declare<br /><br /> v_workspace_id apex_workspaces.workspace_id%type;<br /><br /> begin<br /><br /> select workspace_id<br /><br /> into v_workspace_id<br /><br /> from apex_workspaces w<br /><br /> where workspace = upper(:workspace_name);</p><br /> <p>apex_util.set_security_group_id(v_workspace_id);<br /><br /> end;

   1 2 3 4 5 6 7 8 9 10

   declare v_workspace_id apex_workspaces.workspace_id%type; begin select workspace_id into v_workspace_id from apex_workspaces w where workspace = upper(:workspace_name); apex_util.set_security_group_id(v_workspace_id); end;

   Set variable :workspace_name to the name of the workspace, which includes problematic files.

3. Then, to find the hidden file versions of the file run following query:
   

    

   Oracle PL/SQL

   <br /><br /> select * from wwv_flow_files where filename = :fname;

   1	select * from wwv_flow_files where filename = :fname;

   If you see duplicated records, which differ only in that some have a null value in the FILE_TYPE column, you have found candidates for removal.

4. You have to make sure that these are correct files. You must verify the file name and FLOW_ID (id of applications to which they are assigned – 0 means WORKSPACE_IMAGES).
5. Delete selected files using this query:
   

    

   Oracle PL/SQL

   <br /><br /> delete from wwv_flow_files where id = :id;

   1	delete from wwv_flow_files where id = :id;

   where :id stands for file ID from the previous point.

If you want to check which files could potentially cause problems with the update, you can run this query:

 Summary

It is true that I could not fix the root cause of the problem (it is on the creators of the Oracle Application Express), but I managed to locate the cause of this strange behavior. Additionally, now when I know the nature of this bug, I’m able to take precautions against the unpleasant consequences. And even when it happens, I know how to deal with it. However, you should be careful when cleaning the table wwv_flow_files because you can accidentally delete an important file, without which some application may stop working. If you are not sure which file you should erase, you can do a backup before deleting , just in case.

This problem doesn’t occur with files stored on the server (in the /i/ directory), but only in the case of files stored in the database. I checked this scenario also in APEX 5 and using the steps outlined in the post, it can’t be reproduced. It seems that in the latest version Oracle managed to handle this case.