Store PDF files in Oracle BLOB

by

Posted on by

Oracle BLOB Type Overview

Oracle BLOB (Binary Large Object) stores up to 4 GB of binary data. Differentiate from CLOB (character data) and BFILE (external file pointer).

Step-by-Step Implementation

Method 1: Manual PL/SQL Statements (Primary Method)

1. Create Table

CREATE TABLE pdf_storage (
    id NUMBER PRIMARY KEY,
    pdf_name VARCHAR2(255),
    pdf_data BLOB
);

2. Insert PDF Data

DECLARE
    v_blob BLOB;
    v_file UTL_FILE.FILE_TYPE;
    v_buffer RAW(32767);
    v_amount BINARY_INTEGER := 32767;
    v_pos INTEGER := 1;
BEGIN
    INSERT INTO pdf_storage (id, pdf_name, pdf_data)
    VALUES (1, 'sample.pdf', EMPTY_BLOB())
    RETURNING pdf_data INTO v_blob;
    
    v_file := UTL_FILE.FOPEN('PDF_DIR', 'sample.pdf', 'RB');
    LOOP
        UTL_FILE.READ_RAW(v_file, v_buffer, v_amount);
        DBMS_LOB.WRITE(v_blob, v_amount, v_pos, v_buffer);
        v_pos := v_pos + v_amount;
    END LOOP;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        UTL_FILE.FCLOSE(v_file);
        COMMIT;
END;
/

Method 2: Using DBBlobEditor (Optional Visual Tool)

DBBlobEditor supports seamless switching between BFILE and BLOB in Oracle, auto-handles directory permissions, and allows direct preview of PDF data stored in BLOB fields.

Oracle BFILE/BLOB Issues & Solutions

  • BFILE vs BLOB: Use BFILE for large external PDFs, BLOB for embedded storage
  • Tablespace management: Allocate dedicated tablespace for BLOB columns

Related Guides