Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

BUG: Shared Files

634962Jul 9 2008 — edited Jul 11 2008
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.

Comments

60437
Thanks for reporting this. I'll be looking into it. In the meantime, please tell us your first name and update your forum handle and profile with it to help us. Thanks.

Scott
634962
Done.

Let me know what you find.

Thanks.

Gregg
60437
Gregg,

First, I'm not seeing the behavior you described when uploading duplicate file names, using 3.1.1. I get a validation error when I try that. So maybe that problem is fixed.

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$?

APEX_APPLICATION_FILES is a view on wwv_flow_file_objects$ constrained by your current workspace ID. APEX_WORKSPACE_FILES has more information than APEX_APPLICATION_FILES as it joins more tables. It also appears to have a bug, showing more rows than are actually there.

Why is there 'context security' on APEX_APPLICATION_FILES but not APEX_WORKSPACE_FILES?

I do not see that this is the case. Select the view definition from dba_views to see for yourself.

Can you just work with those views directly?

For selecting, yes.

How can/should I remove the 'orphaned' records?

You can delete from wwv_flow_file_objects$ as long as you are sure about what you are doing. This table is owned by FLOWS_FILES.

Scott
634962
Scott,

Thanks for your help with this.


If you are receiving a formal validation error, then I would agree that the issue was probably fixed in the newer version. I guess I will work to get our system updated. And in the meantime, I will just avoid the problem, now knowing what it is. (I think I stumbled into this problem by simply trying to upload a file again after having updated it offline, without deleting the version that was already in ApEx.)


If I understand you correctly, APEX_APPLICATION_FILES and APEX_WORKSPACE_FILES should have the same number of rows for a given workspace ID, correct? In my environment, APPLICATION has more records than WORKSPACE, which seems backwards to what you mentioned. Please clarify/confirm.


OK -- I see the security on WORKSPACE now. It obviously just works a little differently than the security on APPLICATION.


Unfortunately, I do not have access to WWV_FLOW_FILE_OBJECTS$ or the FLOWS_FILES user. But if I got access, I would just need to delete out the problematic records? Will there only be one record for each orphan? Any other cleanup work needed? Any other tables?

Obviously, I can just use another filename, but, well...that's annoying.


Thanks again.
60437
Gregg,

To access wwv_flow_file_objects$, you can connect as SYS and then

alter session set current_schema=flows_files;

Then query for the rows of interest and delete those you don't want. Pay particular attention to the security_group_id value (this is the workspace ID). As far as identifying "orphan" rows or knowing whether there are multiple rows that meet that criteria for a given value of filename+security_group_id, you'll just have to query the data to find out.

Be aware that any changes you make to this table can affect the operation of the entire Application Express instance (all workspaces), so be very careful.

Scott

P.S. If you'll put your first name in your OTN profile, responders will have ready access to it.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 8 2008
Added on Jul 9 2008
#apex-discussions
5 comments
1,941 views