Store RTF in Oracle CLOB

by

Posted on by

RTF(Rich Text Format) can be stored as Oracle CLOB data.

1. Oracle CLOB Type Overview

Oracle CLOB (Character Large Object) is a native large character field supporting up to 4GB, designed for large text data.

Requires initialization with EMPTY_CLOB() before writing large content via PL/SQL.

2. Pure SQL/PLSQL Implementation Steps

Step 1: Create Table with CLOB Field

CREATE TABLE RTF_DOCS (
    DOC_ID NUMBER PRIMARY KEY,
    DOC_NAME VARCHAR2(100) NOT NULL,
    RTF_CONTENT CLOB NOT NULL,
    CREATE_TIME DATE DEFAULT SYSDATE
);

Step 2: Insert RTF Text into CLOB Field

-- Method 1: Insert short RTF text directly
INSERT INTO RTF_DOCS (DOC_ID, DOC_NAME, RTF_CONTENT)
VALUES (
    1,
    'test.rtf',
    '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Calibri;}} \\pard\\f0\\fs22 Oracle RTF Test\\par}'
);
COMMIT;

-- Method 2: Insert large RTF text with PL/SQL (recommended)
DECLARE
    v_clob CLOB;
BEGIN
    -- Initialize CLOB
    INSERT INTO RTF_DOCS (DOC_ID, DOC_NAME, RTF_CONTENT)
    VALUES (2, 'large.rtf', EMPTY_CLOB())
    RETURNING RTF_CONTENT INTO v_clob;
    
    -- Write RTF content (supports concatenating long text)
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH('{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Arial;}} '), '{\\rtf1\\ansi\\deff0 {\\fonttbl {\\f0 Arial;}} ');
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH('\\pard\\f0\\fs24 Large RTF Content in Oracle CLOB\\par}'), '\\pard\\f0\\fs24 Large RTF Content in Oracle CLOB\\par}');
    
    COMMIT;
END;
/

Step 3: Query/Edit RTF in CLOB

-- Query full RTF content
SELECT DOC_NAME, RTF_CONTENT FROM RTF_DOCS WHERE DOC_ID = 1;

-- Truncate RTF preview (first 200 characters)
SELECT DOC_NAME, DBMS_LOB.SUBSTR(RTF_CONTENT, 200, 1) AS RTF_PREVIEW FROM RTF_DOCS;

-- Update CLOB content
UPDATE RTF_DOCS 
SET RTF_CONTENT = '{\\rtf1\\ansi\\deff0 \\pard\\f0\\fs26 Updated Oracle RTF\\par}'
WHERE DOC_ID = 1;
COMMIT;

Step 4: Delete RTF Record

DELETE FROM RTF_DOCS WHERE DOC_ID = 2;
COMMIT;

3. Simplify Oracle CLOB RTF Management with DBBlobEditor

DBBlobEditor eliminates the need for complex PL/SQL scripts for Oracle CLOB RTF management:

4. Notes

  • Oracle requires PL/SQL + DBMS_LOB package for large CLOB inserts; direct INSERT of long text triggers character length limits
  • Use DBMS_LOB.SUBSTR() instead of regular SUBSTR() for CLOB queries to avoid truncation
  • Import external RTF files to CLOB with DBMS_LOB.LOADFROMFILE() (requires directory permissions)

Related Guides