OraExplorer

In APEX 4, we now have the option to place where the uploaded file will be stored within the development workspace interface. The Storage Type setting of the File Browser item has two options:

• Table WWV_FLOW_FILES stores the uploaded file in the table wwv_flow_files (or APEX_APPLICATION_FILES).  This is the default way in the previous versions of APEX. Note that if you still decide to store the uploaded files this way, it is suggested to move record to another custom table after upload, and then clean it up in this table because this is the shared default area (table) where every application will upload files to.

• BLOB column specified in item source attribute will store the uploaded file in a custom table identified in the Automatic Row Processing (DML) process and the column specified in the item source attribute. This column has to be of data type BLOB.

To configure the File Browser with the BLOB column specified in item source attribute.

1. Create a table for storing uploaded files. One of the columns must be BLOB data type.

CREATE TABLE tbl_attach_file
(
  attach_id          NUMBER PRIMARY KEY,
  attach_data        BLOB,
  attach_mimetype    VARCHAR2(255),
  attach_filename    VARCHAR2(255),
  attach_last_update DATE ,
  attach_charset     VARCHAR2(128),
  attach_user        VARCHAR(10)
);

CREATE SEQUENCE SQ_ATTACH_FILE start with 1;

CREATE OR REPLACE TRIGGER tr_attach_file_BI
   BEFORE INSERT
   ON tbl_attach_file
   FOR EACH ROW
BEGIN
   SELECT SQ_ATTACH_FILE.NEXTVAL
     INTO :NEW.attach_id
     FROM DUAL;
END;
/

2. Create a File Browser item with the following settings.

If this is just simply for file upload, select No on Display Download Link. Otherwise, it will require additional Automated Row Fetch (DML). See note and comment below.

3.  The item’s source points to the BLOB column.

4. Create an Automatic Row Processing  (DML) process.

5. Create a button called Upload with Database Action – SQL Insert Action.

Initially I thought this should be sufficient, but when I ran the page, I got this error.

Apparently I got this error because I checked Yes to Display Download Link thus it requires a DML to fetch data. If this is set to No, it should be okay without the following Automated Row Fetch (DML). Thanks Patrick for pointing that out.

To workaround this, I created another DML but for Automated Row Fetch.

For a purpose of blogging, we’re not going to run any row fetch, so the DML Fetch will have it disabled (its condition to Never). I’m still not sure why the Automated Row Fetch (DML) is needed (even being disabled) if we just want to upload files. But if it is not there, I got the ORA-2001: No Corresponding DML process shown in step 5.

A quick test shows that the file is uploaded to our custom table.

According to Oracle APEX document, the character set of the BLOB is not automatically set on upload. We will have to create an additional page item which is bound to the character set column. This will be where users can specify the character set for the file they are uploading.

Tags: APEX, APEX4, BLOB, column, File Browser, source attribute