You use a LOB column when the data is too large, unstructured, or not practical to store in normal VARCHAR2 / RAW columns.
When do we use LOB?
Use LOBs when you need to store:
1) Large text data
Use CLOB (Character Large Object)
Examples
- XML / JSON documents
- long application logs
- email body / message content
- contracts / terms & conditions
- large comments / notes
- HTML content
Example use case
A document-management app stores full agreement text in a CLOB.
2) Binary data
Use BLOB (Binary Large Object)
Examples
- PDF files
- images
- audio
- video
- ZIP files
- scanned signatures
- certificates
Example use case
An HR system stores employee documents like:
- Aadhaar copy
- passport scan
- offer letter PDF
- profile photo
These are typically stored in a BLOB.
3) Unicode large text
Use NCLOB
This is similar to CLOB, but for national character set data.
Example use case
If you store large multilingual text requiring national character support, use NCLOB.
4) External file reference
Use BFILE
A BFILE does not store the file inside the database.
It stores only a pointer/reference to a file kept on the OS filesystem.
Example use case
A media application keeps video files on storage/NAS, and Oracle stores only the file reference.
Simple rule to choose datatype
Use normal columns when data is small
Use LOB when data is large
- Large text →
CLOB - Binary file →
BLOB - Unicode text →
NCLOB - External file reference →
BFILE
Common real-world use cases of LOB
A) Document storage systems
Store:
- PDFs
- Word files
- scanned documents
- invoices
- reports
Recommended type: BLOB
B) Application audit / logging
Store:
- detailed JSON payloads
- API request/response bodies
- long exception stack traces
- large XML
Recommended type: CLOB
C) Healthcare systems
Store:
- medical reports
- radiology reports
- scanned lab files
- prescription images
Recommended type:
- report text →
CLOB - image/PDF →
BLOB
D) Banking / financial systems
Store:
- KYC documents
- statement PDFs
- signed forms
- transaction payload archives
Recommended type: BLOB / CLOB
E) Product / e-commerce systems
Store:
- product manuals
- product images
- rich descriptions
- long HTML content
Recommended type:
- images/manual PDFs →
BLOB - long descriptions →
CLOB
F) Middleware / integration / API platforms
Store:
- SOAP XML payload
- JSON request body
- large message body
- queue payload archive
Recommended type: CLOB
G) Knowledge repositories / content platforms
Store:
- article content
- wiki pages
- large markdown/html content
Recommended type: CLOB
When should you NOT use LOB?
Do not use LOB when:
1) Data is small
If your value is small enough for VARCHAR2, use VARCHAR2.
Example:
- name
- city
- email
- status
- small JSON text
Using LOB unnecessarily can make design and maintenance more complex.
2) You need frequent filtering/joining on the entire value
LOBs are not ideal for regular equality joins and standard indexing patterns like normal scalar columns.
Example:
- joining tables on file content
- grouping by huge text body
That is usually a bad design.
3) The file should live outside DB for architectural reasons
For very large media repositories (huge videos, large archives), sometimes it is better to store files in:
- object storage
- filesystem
- content server
and keep only metadata / path / URL in Oracle.
Benefits of using LOB
Advantages
- can store very large data
- supports unstructured and semi-structured content
- keeps data inside database security/backup framework
- transactional consistency
- can use SecureFile features like:
- compression
- deduplication
- encryption
Drawbacks / considerations
Things to think about
- more storage consumption
- backup/restore can become heavy
- network transfer can be slower for very large LOB fetches
- application code must handle streaming/chunk reading properly
- poor design can impact performance
Example table design
Storing PDF in Oracle
CREATE TABLE employee_docs (
emp_id NUMBER,
doc_type VARCHAR2(50),
file_name VARCHAR2(200),
mime_type VARCHAR2(100),
upload_date DATE,
file_content BLOB
);
Use case
- PAN card
- passport
- degree certificate
- salary slip PDF
Storing large text / JSON / XML
CREATE TABLE api_audit_log (
log_id NUMBER,
api_name VARCHAR2(100),
request_time DATE,
request_payload CLOB,
response_payload CLOB
);
Use case
- API payload archive
- debugging integrations
- compliance audit
Performance guidance
Prefer SECUREFILE for modern Oracle databases
For most modern Oracle systems, SecureFile LOBs are preferred over BasicFile.
Performance depends on access pattern
LOB performance depends on:
- how often you read/write the LOB
- size of the LOB
- whether it is cached
- whether compression is enabled
- whether deduplication is enabled
- whether app fetches full LOB or only part of it
Quick decision guide
Use CLOB when:
- data is text
- length can be very large
- examples: logs, XML, JSON, HTML, comments
Use BLOB when:
- data is binary
- examples: PDF, image, video, ZIP, certificate
Use NCLOB when:
- large multilingual text with national character requirements
Use BFILE when:
- file should remain outside DB
- Oracle should only store the reference
Very practical examples
Example 1: Good use of LOB
A ticketing app stores full chat transcript of 2 MB per case.
✅ Use CLOB
Example 2: Good use of LOB
A claims application stores scanned image/PDF documents.
✅ Use BLOB
Example 3: Bad use of LOB
Storing customer name in CLOB
❌ Wrong — use VARCHAR2
Example 4: Bad use of LOB
Storing small status JSON of 300 bytes in CLOB for every row when VARCHAR2 is enough
❌ Usually unnecessary
1) Difference between BLOB, CLOB, NCLOB, and BFILE
BLOB — Binary Large Object
Use BLOB when the content is binary and should be stored inside the database. Typical examples are PDFs, images, audio, video, and other binary documents. Because it is an internal LOB, it is stored in the database and can participate in transactions.
Use BLOB for:
- PDF documents
- scanned images / signatures / photos
- certificates, ZIP files, binary payloads
CLOB — Character Large Object
Use CLOB when the content is large text stored in the database character set. It is best for things like XML, JSON, HTML, logs, long descriptions, and message payloads. As an internal LOB, it is stored in the database and participates in transactions.
Use CLOB for:
- XML / SOAP payloads
- JSON documents / large API requests & responses
- long logs / stack traces / comments / HTML content
NCLOB — National Character Large Object
Use NCLOB when you need large text but specifically in the national character set, typically for multilingual or Unicode-heavy text scenarios. Oracle describes it as large string/document storage in the National Character Set.
Use NCLOB for:
- multilingual documents
- large text where national character set support is explicitly required
BFILE — External Binary File
Use BFILE when the file should remain outside the database, on the server operating system file system, and the database should store only a reference/locator to it. Oracle states that BFILE is read-only from the database/application perspective and is suitable for static data and byte-stream access.
Use BFILE for:
- static images/media managed outside DB
- files stored on OS/NAS that should not be updated via Oracle
- loading external file content into internal LOBs later if needed
2) Quick rule: when to use what
Choose CLOB if the data is text
Examples:
- JSON payload archive
- XML documents
- long audit trail text or app logs
Choose BLOB if the data is binary
Examples:
- PDF invoice
- image/photo/signature
- audio/video/document binaries
Choose NCLOB if it is large multilingual text
Examples:
- large Unicode document content
- content where national character set handling is required
Choose BFILE if the file must stay outside Oracle
Examples:
- externally managed static media library
- OS-stored binary files that Oracle only references
3) Internal LOB vs external file — how to decide
Store the file inside Oracle (BLOB / CLOB / NCLOB) when:
You need the data to be part of normal database transactions, backup/recovery, and commit/rollback behavior. Oracle states that persistent internal LOBs participate in transactions and can be recovered on transaction or media failure.
This is the better choice when:
- the document is business-critical
- backup/restore must include the content automatically
- you want consistent security/governance under DB controls
Typical examples:
- KYC / HR / finance PDFs stored as
BLOB - API payloads and logs stored as
CLOB - content repository text stored as
CLOB or NCLOB
Store the file outside Oracle (BFILE, or external content store) when:
You do not want the file bytes inside the database, and a reference is enough. Oracle defines BFILE as a locator to an OS file and notes that it is read-only and suitable for static data.
This is usually better when:
- files are very large and managed by another system
- content is mostly read-only / static
- you want to avoid putting all binary storage growth into the database itself (architectural choice) while keeping metadata in Oracle
Typical examples:
- large video/media library managed by a content server, with Oracle storing metadata + pointer
- static graphics shared by applications from OS/NAS storage
4) Performance recommendation
For internal LOBs, SecureFiles is generally the preferred modern storage architecture. Oracle documents SecureFiles as the newer LOB architecture and notes performance equal to or better than file-system-style performance for large objects; SecureFiles also support features like compression, deduplication, and encryption.
So for performance/design:
BLOB/CLOB/NCLOB inside DB + SecureFile → best general choice for modern Oracle workloads BFILE → only when you intentionally want external, read-only file handling
5) Real-world use cases
Use case A: API / middleware payload archive
Store request/response payloads as CLOB, because the content is text and often large (JSON/XML). Keeping it internal makes it transactional and easy to manage under DB backup/recovery.
Use case B: HR or finance documents
Store PDFs, scanned docs, and signatures as BLOB, because the content is binary and should usually remain under DB backup/security control.
Use case C: multilingual knowledge base
Store the content as NCLOB if national character set / multilingual handling is a hard requirement.
Use case D: static external media repository
Use BFILE when files already live on server storage and Oracle only needs to reference them. This works best when the files are static and read-only from the DB side.
6) What I’d recommend in practice
Given your background as a Database Architect, here is the most practical decision framework:
Use CLOB when:
- value is text
- the text can become large
- examples: audit payloads, logs, XML/JSON, configs, long comments
Use BLOB when:
- value is a binary document/file
- examples: PDFs, images, certificate files, office documents
Use NCLOB when:
- text is large and you explicitly need national character set semantics
Use BFILE when:
- file storage is outside DB by design
- file is mostly static/read-only
- Oracle should store only the reference
Final one-line answer
Use CLOB for large text, BLOB for binary files, NCLOB for large multilingual text, and BFILE when the file should stay outside the database and be referenced read-only
Query to check Quick “red flag” query for performance review
SELECT l.owner,
l.table_name,
l.column_name,
CASE WHEN l.securefile = 'YES' THEN 'SECUREFILE' ELSE 'BASICFILE' END AS lob_type,
l.cache,
l.in_row,
l.chunk,
l.retention,
l.pctversion,
l.compression,
l.deduplication,
l.encrypt,
ROUND(s.bytes/1024/1024, 2) AS lob_mb
FROM dba_lobs l
LEFT JOIN dba_segments s
ON s.owner = l.owner
AND s.segment_name = l.segment_name
WHERE l.owner = UPPER('&OWNER')
AND (
l.securefile = 'NO'
OR NVL(s.bytes,0) > 1024*1024*1024 -- > 1 GB
OR l.compression <> 'NO'
OR l.deduplication <> 'NO'
OR l.encrypt = 'YES'
)
ORDER BY lob_mb DESC NULLS LAST;
