Create an Application to Upload Files Using Oracle APEX, In Less Than 10 Minutes (Video)

A BLOB data type stores unstructured binary large objects. A table column with a BLOB data type can be used to store all types of files such a documents, spreadsheets, images and plain text. You can manage BLOB columns by easily adding file upload and download functionality to a form you create using Oracle Application Express (APEX).

APEX includes declarative BLOB support that enables you to declaratively upload files in forms, and download or display files in reports (ittichai wrote about it here).

The following is a screencast to demonstrate:

• how to create a simple APEX application to store the content of any file into a BLOB column and
• how to save the content of that BLOB into a file on the database server.

I recorded the screencast on Windows 7 and Oracle APEX 4.0 that comes pre-installed with Oracle Database Express Edition 11g Release 2.

But first, here is the code that I used in the screencast:

CREATE TABLE x_files
(
   ID              NUMBER PRIMARY KEY
  ,BLOB_CONTENT    BLOB
  ,MIME_TYPE       VARCHAR2 (255)
  ,FILENAME        VARCHAR2 (255)
  ,LAST_UPDATED    DATE
  ,CHARACTER_SET   VARCHAR2 (128)
);

CREATE SEQUENCE x_files_seq;

CREATE OR REPLACE TRIGGER x_tr_files_bi
   BEFORE INSERT
   ON x_files
   FOR EACH ROW
BEGIN
   :new.id := x_files_seq.NEXTVAL;
END;
/

In addition to the BLOB column, I made sure to include these columns: MIME_TYPE, FILENAME, CHARACTER_SET and LAST_UPDATED. They will be mapped to the attributes of the File Browse item type in APEX and are mostly useful when reading data out of the BLOB when needed. APEX automatically populates the corresponding values in these columns except the character set which is not automatically set on upload.

For writing the BLOB out to a file in a directory on the database server, I used this code (MOS 330146.1):

CREATE OR REPLACE DIRECTORY files AS 'C:\temp';

CREATE OR REPLACE PROCEDURE write_blob_to_file (
   p_file_id   IN NUMBER
  ,p_dir       IN VARCHAR2
)
IS
   l_blob            BLOB;
   l_blob_length     INTEGER;
   l_out_file        UTL_FILE.file_type;
   l_buffer          RAW (32767);
   l_chunk_size      BINARY_INTEGER := 32767;
   l_blob_position   INTEGER := 1;
   l_file_name       x_files.filename%TYPE;
BEGIN
   -- Retrieve the BLOB for reading
   SELECT blob_content, filename
     INTO l_blob, l_file_name
     FROM x_files
    WHERE id = p_file_id;

   -- Retrieve the SIZE of the BLOB
   l_blob_length := DBMS_LOB.getlength (l_blob);

   -- Open a handle to the location where you are going to write the BLOB 
   -- to file.
   -- NOTE: The 'wb' parameter means "write in byte mode" and is only
   --       available in the UTL_FILE package with Oracle 10g or later
   l_out_file :=
      UTL_FILE.fopen (
         p_dir
        ,l_file_name
        ,'wb' -- important. If ony w then extra carriage return/line brake
        ,l_chunk_size
      );

   -- Write the BLOB to file in chunks
   WHILE l_blob_position <= l_blob_length
   LOOP
      IF l_blob_position + l_chunk_size - 1 > l_blob_length
      THEN
         l_chunk_size := l_blob_length - l_blob_position + 1;
      END IF;

      DBMS_LOB.read (
         l_blob
        ,l_chunk_size
        ,l_blob_position
        ,l_buffer
      );
      UTL_FILE.put_raw (l_out_file, l_buffer, TRUE);
      l_blob_position := l_blob_position + l_chunk_size;
   END LOOP;

   -- Close the file handle
   UTL_FILE.fclose (l_out_file);
END write_blob_to_file;
/

Here is the screencast – Best viewed in 720p HD full screen:

Filed in Oracle on 20 Sep 11 | Tags: apex, howto, video

---