Store audio files in Oracle BLOB

Shiji Pan

11 months ago

Audio (MP3, WAV, Ogg, WMA, M4A, AAC, etc.) files can be stored as Oracle BLOB data.

1. Key Overview

Oracle Database supports BLOB (Binary Large Object) for storing mp3/wav/ogg audio files (up to 128 TB max with SecureFiles). Storing audio as BLOB integrates audio data with business records (e.g., voice notes linked to user profiles) and leverages Oracle’s advanced security, backup, and transactional features.

2. Prerequisites

Oracle Database (11g+ recommended, 12c+ for enhanced SecureFiles BLOB)
SQL*Plus/SQL Developer or DBBlobEditor (simplifies BLOB import/export)
CREATE TABLE, INSERT, SELECT privileges on target schema

3. Implementation Steps

3.1 Create Audio Table with BLOB Column

CREATE TABLE audio_oracle_blob (
    audio_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    audio_name VARCHAR2(255) NOT NULL,
    audio_format VARCHAR2(10) CHECK (audio_format IN ('mp3', 'wav', 'ogg')),
    file_size NUMBER NOT NULL,
    audio_blob BLOB NOT NULL,
    upload_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) LOB (audio_blob) STORE AS SECUREFILE; -- Optimize for audio files

3.2 Insert Audio

Use **SQL*Plus** (SQL-based) to Insert Audio

-- Create directory for audio files (DBA privilege required)
CREATE DIRECTORY AUDIO_DIR AS '/path/to/audio/files';
GRANT READ ON DIRECTORY AUDIO_DIR TO your_user;

-- Insert audio file to BLOB via PL/SQL
DECLARE
    v_blob BLOB;
    v_bfile BFILE := BFILENAME('AUDIO_DIR', 'customer_voice.mp3');
BEGIN
    INSERT INTO audio_oracle_blob (audio_name, audio_format, file_size, audio_blob)
    VALUES ('customer_voice_001', 'mp3', 200000, EMPTY_BLOB())
    RETURNING audio_blob INTO v_blob;
    
    DBMS_LOB.OPEN(v_bfile, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
    DBMS_LOB.CLOSE(v_bfile);
    COMMIT;
END;
/

Use DBBlobEditor (GUI/CLI) to Insert Audio

Batch import audio files into Oracle BLOB

3.3 Retrieve Audio

Use **SQL*Plus** (SQL-based) to Retrieve Audio

DECLARE
    v_blob BLOB;
    v_file UTL_FILE.FILE_TYPE;
    v_buffer RAW(32767);
    v_pos NUMBER := 1;
    v_blob_len NUMBER;
BEGIN
    SELECT audio_blob INTO v_blob FROM audio_oracle_blob WHERE audio_id = 1;
    v_blob_len := DBMS_LOB.GETLENGTH(v_blob);
    v_file := UTL_FILE.FOPEN('AUDIO_DIR', 'exported_audio.bin', 'WB', 32767);
    
    WHILE v_pos <= v_blob_len LOOP
        DBMS_LOB.READ(v_blob, 32767, v_pos, v_buffer);
        UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
        v_pos := v_pos + 32767;
    END LOOP;
    
    UTL_FILE.FCLOSE(v_file);
END;
/

Note: Rename the exported .bin file to .mp3/.wav/.ogg (matching stored format) for playback.

Use DBBlobEditor (GUI/CLI) to Retrieve Audio

Batch export Oracle BLOB to AUDIO files

4. Key Tips

Use SecureFiles (vs. BasicFiles) for BLOB to improve audio read/write performance.
Index metadata (audio_name/audio_format), never index BLOB columns.
Avoid audio files >100MB in BLOB (use Oracle Object Storage for large files).
DBBlobEditor CLI is suitable for automated scripts (cron jobs) to import/export audio files.