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.
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:
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.
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:
- You must log in to the scheme on which the APEX workspace stands (you can also do it from SQL Workshop inside APEX).
- Run this script:
Oracle PL/SQL12345678910declarev_workspace_id apex_workspaces.workspace_id%type;beginselect workspace_idinto v_workspace_idfrom apex_workspaces wwhere workspace = upper(:workspace_name);apex_util.set_security_group_id(v_workspace_id);end;
- Then, to find the hidden file versions of the file run following query:
Oracle PL/SQL1select * from wwv_flow_files where filename = :fname;
- 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).
- Delete selected files using this query:
Oracle PL/SQL1delete from wwv_flow_files where id = :id;
If you want to check which files could potentially cause problems with the update, you can run this query:
select FLOW_ID, FILENAME, count(1) from wwv_flow_files group by FLOW_ID, FILENAME having count(1) > 1
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.