Store images in Oracle BLOB

by

Posted on by

Oracle BLOB Type Features

  • Oracle-native large object type for binary data storage
  • Supports image sizes up to 4GB per BLOB (Oracle 11g+)
  • Distinct from BFILE (stores pointer to external files, not binary data)
  • Integrated with Oracle’s Advanced LOB Compression and Encryption

Step 1: Create a Table with BLOB Column

CREATE TABLE image_storage (
    image_id NUMBER PRIMARY KEY,
    image_name VARCHAR2(100),
    image_blob BLOB,
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE users; -- Specify dedicated tablespace for LOB data

Optional: Create separate LOB segment for better performance

CREATE TABLE image_storage (
    image_id NUMBER PRIMARY KEY,
    image_name VARCHAR2(100),
    image_blob BLOB,
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) 
LOB (image_blob) STORE AS SECUREFILE (
    TABLESPACE lob_ts -- Dedicated tablespace for BLOB data
    ENABLE STORAGE IN ROW
    CACHE READS
);

Step 2: Insert Images into Oracle BLOB

Method 1: Using PL/SQL (Oracle SQL Developer/Command Line)

DECLARE
    v_blob BLOB;
    v_file BFILE := BFILENAME('IMAGE_DIR', 'product_photo.jpg'); -- IMAGE_DIR = Oracle directory object
BEGIN
    -- Initialize empty BLOB
    INSERT INTO image_storage (image_id, image_name, image_blob)
    VALUES (1, 'product_photo.jpg', EMPTY_BLOB())
    RETURNING image_blob INTO v_blob;

    -- Open and load file into BLOB
    DBMS_LOB.OPEN(v_file, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LOADFROMFILE(v_blob, v_file, DBMS_LOB.GETLENGTH(v_file));
    DBMS_LOB.CLOSE(v_file);
    
    COMMIT;
END;
/

Prerequisite: Create Oracle directory object first

CREATE DIRECTORY IMAGE_DIR AS '/path/to/your/image/folder';
GRANT READ, WRITE ON DIRECTORY IMAGE_DIR TO your_username;

Method 2: Using DBBlobEditor

Insert Single Image into Oracle BLOB

  1. Connect to your Oracle database in DBBlobEditor
  2. Navigate to the “image_storage” table in the target schema
  3. Click “+” button to add a record, and then Edit image stored in Oracle BLOB field

Batch Insert Images into Oracle BLOB

Step 3: Retrieve and Restore Oracle BLOB to Image

Method 1: PL/SQL to Export BLOB to File

DECLARE
    v_blob BLOB;
    v_file BFILE := BFILENAME('EXPORT_DIR', 'exported_photo.jpg');
    v_output UTL_FILE.FILE_TYPE;
    v_buffer RAW(32767);
    v_amount BINARY_INTEGER := 32767;
    v_pos INTEGER := 1;
BEGIN
    -- Get BLOB data from table
    SELECT image_blob INTO v_blob FROM image_storage WHERE image_id = 1;

    -- Export BLOB to file
    v_output := UTL_FILE.FOPEN('EXPORT_DIR', 'exported_photo.jpg', 'wb', 32767);
    WHILE v_pos <= DBMS_LOB.GETLENGTH(v_blob) LOOP
        DBMS_LOB.READ(v_blob, v_amount, v_pos, v_buffer);
        UTL_FILE.PUT_RAW(v_output, v_buffer, TRUE);
        v_pos := v_pos + v_amount;
    END LOOP;
    UTL_FILE.FCLOSE(v_output);
    
    COMMIT;
END;
/

Method 2: DBBlobEditor Export

Retrieve and Restore Single Image

  1. Connect to Oracle database in DBBlobEditor
  2. Query the “image_storage” table and locate the target image_blob column
  3. Double click to View image stored in Oracle BLOB field
  4. Click menu “File” -> “Save” to save BLOB data as a local image file

Batch Export Oracle BLOB to Images

Oracle Specific Notes

  • Use SECUREFILE LOBs (Oracle 11g+) for better performance with large images (replace BASICFILE)
  • Assign dedicated LOB tablespaces to avoid performance impact on regular tables
  • Grant necessary privileges: CREATE DIRECTORY, UTL_FILE, DBMS_LOB
  • Avoid using BLOB columns in indexes (use image_id or image_name for filtering)
  • For high concurrency, use LOB caching (CACHE READS) and disable logging temporarily

Related Guides