APEX Orphaned Application Files

Search for a command to run...

No comments yet. Be the first to comment.
Oracle SQLcl is the command line tool to connect to an Oracle database. One neat feature it has is the ability to call host commands (i.e call terminal commands directly from SQLcl). You can use any of the following to trigger a host command: host ho...
How to use Text Messages for Constants
Last week SQL Developer for VS Code was released (direct link to VS Code extension here). If you're not using VS Code (VSC) I highly suggest you download and install as it's the de facto editor for developers (regardless of programming language). I'v...
I recently had to generate a sitemap.xml for an public facing APEX application. The official sitemap protocol has the following example: <?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"> <url> ...
Every APEX page item has a Server Side condition which controls whether the item is rendered on the page. They're various server side condition options and for the purpose of this article we'll just set it to Never (can do things like queries that re...
If you allow end users to upload files to your APEX application you may have a lot of "orphaned" files in apex_application_files and not even realize it.
Orphaned files are files that exist in APEX_APPLICATION_FILES that are not associated with an application. This can happen for several reasons, the most common are:
You can easily identify orphaned files using the APEX_APPLICATION_FILES view:
SELECT * FROM apex_application_files WHERE flow_id = 0 -- flow_id is the same as application_idAll 3 situations listed above will result in the file uploaded with flow_id = 0. The last 2 points, files uploaded from end users, can result in files that you may no longer need. I don't recommend that you keep uploaded files in the APEX_APPLICATION_FILES view. Instead you should move them immediately to a custom table. The main problem comes from the third point. When a user uploads a file and a validation fails. In this situation the file is uploaded to APEX_APPLICATION_FILES and then the validation fails. Even though the validation failed, the file still resides in APEX_APPLICATION_FILES. The following screen shot demonstrates this issue. [](http://2.bp.blogspot.com/_33EF80fk9sM/SwNsxYj7t_I/AAAAAAAADto/ALhY8Bl_mrU/s1600/validation_fail.bmp) To resolve this issue, I run the following application process which automatically "tags" uploaded files with a flow_id of -1\. By doing so you can run a nightly process to delete any files that have a flow_id of -1. Application Process: AP_TAG_APEX_FILES Sequence: -100 Point: On Submit: After Page Submission - Before Computations and Validations
-- AP_TAG_APEX_FILES
BEGIN
FOR x IN (SELECT v (item_name) item_name_value
FROM apex_application_page_items
WHERE application_id = :app_id
AND page_id = :app_page_id
AND display_as = 'File Browse...')
LOOP
UPDATE apex_application_files aaf
SET aaf.flow_id = -1
WHERE aaf.NAME = x.item_name_value;
END LOOP;
END;