Hello,
I'm using Apex 5.1 and Oracle 11.g on a Windows 10 platform.
I'm working with a page item P112_ATTACHMENTS
Type = File Browse
Allow Multiple Files = Yes
Storage Type = Table APEX_APPLICATION_TEMP_FILES
I'm storing the bulk of the page items in a table called AFF_EVENT. Those are standard VARCHAR2 and NUMBER formats and work fine.
I'd like the attachments stored in a new table AFF_ATTACH.
PRIM_KEY number
AFF_EVENT_FKEY number
FILE_NAME varchar2(512)
MIME_TYPE varchar2(512)
DOC_SIZE number
CHAR_SET varchar2(512)
LAST_UPDATE_DATE DATE
ATTACHMENT BLOB
The field AFF_EVENT_FKEY is a foreign key to the PRIM_KEY in the AFF_EVENT table.
I'm trying to create a page process to insert the uploaded files into AFF_ATTACH, but I can't see newly uploaded items using APEX_APPLICATION_FILES.
I can however see the newly inserted rows if I use SQL Developer and select Other Users (FLOWS_FILES) and view the table WWV_FLOW_FILE_OBJECT$. For example, I can see the value in the NAME field, which is a concatenation of the ID (slash) FILENAME fields.
If I run the following in Apex's SQL Workshop, I don't see the uploaded files:
Select * from apex_application_files
where created_by = :app_user
order by created_on desc;
However the following shows the uploaded files:
Select * from FLOWS_FILES.wwv_flow_file_objects$
Where created_by = :app_user
Order by created_on desc;
Different posts seem to indicate that I should use APEX_APPLICATION_FILES instead of accessing the FLOWS_FILE table directly. That makes a lot of sense to me.
Ultimately I'd like to use the following code as a page process to insert into the AFF_ATTACH table:
DECLARE
l_selected apex_application_global.vc_arr2;
l_filename VARCHAR2 (1024);
l_aff_event_fkey number;
BEGIN
l_selected := apex_util.string_to_table (:P112_SELECTED_FILES, ':');
l_aff_event_fkey := :P112_prim_key;
FOR i IN 1 .. l_selected.COUNT
LOOP
l_filename := l_selected (i);
BEGIN
INSERT INTO aff_attach
(file_id, file_name, mime_type, attachment, file_size, aff_event_fkey)
SELECT ID, l_filename, mime_type, blob_content, doc_size, l_aff_event_fkey
FROM apex_application_files
WHERE name = l_filename AND created_by = :app_user;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20585, 'Error in Insertion' || SQLERRM);
END;
DELETE FROM apex_application_files
WHERE name = l_filename AND created_by = :app_user;
COMMIT;
END LOOP;
END;
How can I access APEX_APPLICATION_FILES so that I avoid using FLOWS_FILES directly?
Thanks for looking at this.