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


  1. Have you been through this? It's working fine, but the first load doesn't works. user test pwd test

    The file browse field has a commit inserted on dynamic action. After the second load action becomes almost functional, inserting correctly, but returning a non data found exception.

  2. Ola Gustavo, estou em uma fase anterior ao problema que encontrou, ou seja, nao estou nem conseguindo definir no form a chamada para fazer o upload do arquivo. Gostaria de verificar se vc pode me passar um exemplo de codigo para efetuar o upload. Obrigado