Tuesday, 3 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:
  • WWV_FLOW_FILES
  • APEX_APPLICATION_FILES
  • HTMLDB_APPLICATION_FILES
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:

declare
    l_filerow apex_application_files%rowtype;
begin

    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);
end;

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:

  • CREATED_ON
  • MIME_TYPE
  • FILENAME
  • NAME
  • APPLICATION_ID
  • ID
  • BLOB_CONTENT
Making the following unavailable (that are in wwv_flow_files):
  • LANGUAGE
  • CONTENT_TYPE
  • LAST_UPDATED
  • UPDATED_ON
  • UPDATED_BY
  • CREATED_ON
  • CREATED_BY
  • MIME_TYPE
  • FILENAME
  • 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: http://docs.oracle.com/cd/E59726_01/doc.50/e39143/toc.htm#BAJJDJBA

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

2 comments:

  1. Have you been through this? It's working fine, but the first load doesn't works. https://apex.oracle.com/pls/apex/f?p=65673:3 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.

    ReplyDelete
  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

    ReplyDelete