Monday, 2 February 2015

APEX 5 blob column uploads

Existing behaviour

In APEX 4.2, to upload a file into a BLOB column, there are two patterns for getting the file into your table. In settings, specify the storage type as:

  1. BLOB column specified in Item Source attribute
  2. Table WWV_FLOW_FILES 
I tend to use method 2, and will be focusing on that pattern.

This table can also be referred to with either:
Which you can see with the following query:

select *
from all_synonyms
where table_name = 'WWV_FLOW_FILES'

On your form page, whenever you have chosen a file to upload, and submit the page, the file will be uploaded into wwv_flow_files.

The name column is the value assigned to the page item, so to fetch that particular file upload you would have something like:

    l_filerow apex_application_files%rowtype;

    select *
    into l_filerow
    from apex_application_files
    where name = :Px_ITEM_NAME;
    --Do something with l_filerow
    --e.g. insert into my_Table (filename, mime_Type, file_contents) values (l_filerow.filename, l_filerow.mime_Type, l_filerow.blob_content);

This could be in a validation or page process. Unless you want your files to linger in wwv_flow_files, you also need to be sure to delete the files, after a process/validation and also being sure to delete them it if any exceptions occurred.

New behaviour

In APEX 5, you still have the two options, but WWV_FLOW_FILES has changed to: APEX_APPLICATION_TEMP_FILES.

A neat new feature is the option to purge the file (at). This gives us two options:

  1. End of session
  2. End of request
So no more worrying about deleting that row yourself! You will see that if you specify `End of request` after all page processing, the file will no long live in that table/view.

There are also a reduced set of columns in APEX_APPLICATION_TEMP_FILES:

  • NAME
  • ID
Making the following unavailable (that are in wwv_flow_files):
  • NAME
  • ID

wwv_flow_files is still around, it just means that uploads in your systems will no longer be going there. 

note: Any existing applications migrated that reference WWV_FLOW_FILES will still be using that, with a note that it is deprecated. After you change it and save it, it will be gone as an option.

This is also covered in the (currently beta) release notes:

File Browse Storage Type, Table WWV_FLOW_FILES - Any select, update or delete operations on WWV_FLOW_FILES should be changed to using the APEX_APPLICATION_TEMP_FILES table. Deletes are no longer necessary, as the file will automatically be purged after the request or when the session is purged.

disclaimer: This is based on the current early adopter (3) instance of apex