APEX

Versions:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

Application Express 3.0.1.00.08



Steps To Reproduce:
1. Upload file via Shared Components page in Application Builder.
2. Attempt to upload file with same filename as file uploaded in Step 1, again using the Shared Components page in Application Builder for the same application (basically do Step 1 twice in a row).

Upon clicking the "Upload" button during Step 2, the following error is produced:

"ORA-00001: unique constraint (FLOWS_030000.WWV_FLOW_IMAGE_REPO_IDX1) violated

Error processing row.

Return to application."


3. Click "Return to application." link.
4. Click "Cancel" button.

Upon clicking the "Cancel" button during Step 4, a row for the duplicate file from Step 2 is placed in both APEX_APPLICATION_FILES and APEX_WORKSPACE_FILES.



The real problem then is that you cannot delete the file from Step 2 from the system. No problems with the file from Step 1 -- you can delete it from Application Builder or using wwv_flow_api.create_or_remove_file(). But the file from Step 2 cannot be deleted -- in fact, it does not even appear in Application Builder, only in the two APEX__FILES views.

This happens for both Cascading Style Sheets and Images. I assume Static Files would also exhibit the same behavior.

I stumbled upon this when I uploaded a new stylesheet, but my application kept using the old version. After much digging, I finally figured out what was happening.



A couple requests then:

1. Can someone please provide some kind of overview of APEX_APPLICATION_FILES, APEX_WORKSPACE_FILES, WWV_FLOW_FILE_OBJECTS$, and any APIs that interact with them? Some questions: What is the difference between APEX_APPLICATION_FILES and APEX_WORKSPACE_FILES? How do they then relate to WWV_FLOW_FILE_OBJECTS$? Why is there 'context security' on APEX_APPLICATION_FILES but not APEX_WORKSPACE_FILES? Can you just work with those views directly? Or should you only use APIs? What are the APIs? etc, etc, etc... (I have not been able to find a definitive source for this information -- either in the documentation or in the forum -- just bits and pieces, here and there.)

2. How can/should I remove the 'orphaned' records? Is there an API that I should use (wwv_flow_api.create_or_remove_file() does not work for the 'orphaned' records)? Or just do a DELETE on the APEX__FILES views (would this cause more problems?)?



Please let me know if any more information is needed.

Thanks much.