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).
The following is a screencast to demonstrate:
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: