Mar 16 2011

File Browser in APEX 4 with BLOB column specified in item source attribute

Category: APEXittichai @ 12:45 pm

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:

File Browser Settings

  • 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.

File Browser Settings

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

File Browser Source

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

DML

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.

No DML 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.

DML Fecth

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.

Upload Test

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: , , , , ,


Oct 30 2007

Change column width of show parameter in SQL Plus

Category: SQLPlusittichai @ 2:40 am

You can change the width of the NAME, TYPE and VALUE of “show parameter” in SQL/Plus.

SQL> show parameter sga

NAME                           TYPE
------------------------------ ------------------------------
VALUE------------------------------
lock_sga                       boolean
FALSE
pre_page_sga                   boolean
FALSE
sga_max_size                   big integer
300M
sga_target                     big integer0

SQL> column NAME_COL_PLUS_SHOW_PARAM format A20
SQL> column TYPE format A20
SQL> column VALUE_COL_PLUS_SHOW_PARAM format A20

SQL> show parameter sga

NAME                 TYPE                 VALUE
-------------------- -------------------- --------------------
lock_sga             boolean              FALSE
pre_page_sga         boolean              FALSE
sga_max_size         big integer          300M
sga_target           big integer          0

Tags: , ,