Store video files in Oracle BLOB

Shiji Pan

11 months ago

Video files can be stored as Oracle BLOB data.

1. Core Compatible Type & Oracle BLOB Fundamentals

Oracle Database is an enterprise-grade relational database that natively supports the BLOB (Binary Large Object) data type, designed for storing large binary files such as videos. Oracle BLOB has a maximum storage capacity of 4GB per column, fully compatible with all mainstream video formats (AVI, MP4, WMV, MOV, MPEG, FLV, etc.).

Key characteristics of Oracle BLOB for video storage:

Tablespace dependency: Oracle BLOB columns must be assigned to a dedicated tablespace (separate from regular data) to optimize read/write performance for large video files.
NOLOGGING option: Enable NOLOGGING for BLOB columns during table creation to reduce redo log overhead when importing large video files.
BFILE integration: For video files exceeding 4GB, combine BLOB (metadata storage) with BFILE (external file pointer) to extend storage capacity.

2. Key Operational Steps for Video Storage

2.1 Table Creation

Create a dedicated table for video storage with a BLOB column and auxiliary metadata columns (optimize query and management efficiency):

Assign BLOB column to a dedicated tablespace (e.g., VIDEO_TBS).
Include metadata columns: file_name (VARCHAR2), file_format (VARCHAR2), file_size (NUMBER), upload_date (TIMESTAMP) etc.

SQL Code for Table Creation:

-- Create dedicated tablespace for video BLOB data (optional but recommended)
CREATE TABLESPACE VIDEO_TBS 
DATAFILE '/u01/app/oracle/oradata/ORCL/video_tbs01.dbf' 
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;

-- Create video storage table with BLOB column
CREATE TABLE video_storage (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    video_blob BLOB 
        STORE AS SECUREFILE (TABLESPACE VIDEO_TBS NOLOGGING COMPRESS HIGH),
    file_name VARCHAR2(255) NOT NULL,
    file_format VARCHAR2(50) NOT NULL,
    file_size NUMBER NOT NULL,
    upload_date TIMESTAMP DEFAULT SYSTIMESTAMP
);

2.2 Video File Storage (Write)

SQL Code to Insert Video BLOB Data (using PL/SQL):

-- PL/SQL script to insert video file into Oracle BLOB
DECLARE
    v_blob BLOB;
    v_file UTL_FILE.FILE_TYPE;
    v_buffer RAW(32767);
    v_bytes_read BINARY_INTEGER := 32767;
BEGIN
    -- Initialize empty BLOB
    INSERT INTO video_storage (video_blob, file_name, file_format, file_size)
    VALUES (EMPTY_BLOB(), 'sample_video.mp4', 'MP4', 104857600)
    RETURNING video_blob INTO v_blob;

    -- Open local video file (need DIRECTORY object permission)
    v_file := UTL_FILE.FOPEN('VIDEO_DIR', 'sample_video.mp4', 'rb', 32767);

    -- Read file and write to BLOB
    WHILE v_bytes_read = 32767 LOOP
        UTL_FILE.READ_RAW(v_file, v_buffer, v_bytes_read);
        DBMS_LOB.WRITEAPPEND(v_blob, v_bytes_read, v_buffer);
    END LOOP;

    -- Close file and commit
    UTL_FILE.FCLOSE(v_file);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        UTL_FILE.FCLOSE(v_file);
        RAISE;
END;
/

Use DBBlobEditor (GUI/CLI) to Batch import video files into Oracle BLOB.

2.3 Video File Retrieval (Read)

SQL Code to Extract BLOB Data:

-- Select BLOB data for specific video
SELECT 
    video_blob,
    file_name,
    file_format
FROM 
    video_storage
WHERE 
    id = 1; -- Target video ID

-- PL/SQL script to export BLOB to local file
DECLARE
    v_blob BLOB;
    v_file UTL_FILE.FILE_TYPE;
    v_buffer RAW(32767);
    v_pos INTEGER := 1;
    v_blob_len INTEGER;
BEGIN
    -- Get BLOB data from table
    SELECT video_blob INTO v_blob 
    FROM video_storage 
    WHERE id = 1;

    v_blob_len := DBMS_LOB.GETLENGTH(v_blob);
    v_file := UTL_FILE.FOPEN('VIDEO_DIR', 'exported_video.mp4', 'wb', 32767);

    -- Write BLOB to file
    WHILE v_pos <= v_blob_len LOOP
        DBMS_LOB.READ(v_blob, 32767, v_pos, v_buffer);
        UTL_FILE.WRITE_RAW(v_file, v_buffer);
        v_pos := v_pos + 32767;
    END LOOP;

    -- Close file and commit
    UTL_FILE.FCLOSE(v_file);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        UTL_FILE.FCLOSE(v_file);
        RAISE;
END;
/

Use DBBlobEditor (GUI/CLI) to Batch export Oracle BLOB to VIDEO files.

3. Key Best Practices

Tablespace Optimization: Store BLOB data in a separate tablespace (e.g., VIDEO_TBS) with autoextend enabled to avoid storage exhaustion for large video files.
4GB+ Video Handling: Use BFILE type to reference external video files (stored on file system) and store only metadata in BLOB columns.
Performance Tuning: Use SecureFiles (instead of BasicFiles) for BLOB columns to enable compression, deduplication, and encryption for video data.
Security: Restrict UTL_FILE directory access to authorized users and enable Transparent Data Encryption (TDE) for BLOB columns storing sensitive video content.

4. Common Issues & Solutions

Issue: UTL_FILE permission denied when accessing video files.
- Solution: Create a DIRECTORY object and grant READ/WRITE permissions (e.g., CREATE DIRECTORY VIDEO_DIR AS '/path/to/videos'; GRANT READ, WRITE ON DIRECTORY VIDEO_DIR TO your_user;).
Issue: BLOB insertion failed due to 4GB size limit.
- Solution: Use BFILE for external storage or split large video files into multiple BLOB chunks (not recommended for production).
Issue: Slow BLOB read/write for large video files.
- Solution: Enable CACHE for frequently accessed video BLOBs and increase DB_BLOCK_SIZE (if possible) to 8KB/16KB.