Querying and Modifying LOBs

Choosing a LOB type

Understanding values and locators

Internal LOBs and external files

BLOB, CLOB, and NCLOB values stored in table rows are persistent internal LOBs. Their changes participate in database transactions. BFILE content lives outside database tablespaces, and its bytes are not managed by the database transaction or included in database backups. This distinction matters when deciding who is responsible for keeping a document available.

A BFILE locator identifies a file through an Oracle directory object and a filename. Access requires suitable database privileges and operating-system permissions. A reference can become unusable if the file is moved or the database server cannot read it. Changing the stored reference and changing the external file are separate actions.

For example, a document application might reference externally maintained source material while storing approved report text internally. The design should say which system owns each copy and how each copy is recovered. Treating an external path as though it guarantees a preserved database document would leave that responsibility unclear.

Modern storage and legacy terminology

A small relational example

LOBs do not require object tables. The following illustrative example uses an ordinary relational table so that the relationship between metadata and content remains visible. Later object-table examples can apply the same type distinctions to attributes. These statements are learning examples and have not been executed as part of this lesson preparation.

CREATE TABLE lesson_documents (
    document_id NUMBER PRIMARY KEY,
    title       VARCHAR2(200) NOT NULL,
    body        CLOB
);

INSERT INTO lesson_documents (document_id, title, body)
VALUES (1, 'LOB introduction', TO_CLOB('First draft.'));

COMMIT;

The numeric identifier provides a compact way to find the row. The title is short descriptive text, and the body is the document content. The initial value is deliberately small so that the example is readable. A short demonstration value does not imply that a CLOB is needed for every short sentence; the column represents a document whose contents may grow.

The example assumes a practice schema with permission and space to create a table of this name. Keep such demonstrations separate from application tables. In a real design, choose names, constraints, privileges, and storage settings according to the surrounding application rather than copying an isolated example unchanged.

Querying metadata and content

A useful first query asks which document is present and how much content it contains. DBMS_LOB.GETLENGTH reports the length of a CLOB in characters. A small DBMS_LOB.SUBSTR request can produce a preview without requesting the entire document.

SELECT document_id,
       title,
       DBMS_LOB.GETLENGTH(body) AS body_length,
       DBMS_LOB.SUBSTR(body, 80, 1) AS body_preview
FROM lesson_documents
WHERE document_id = 1;

Here, the preview requests up to 80 characters starting at position 1. It is a sample of the contents, not a replacement for the document. A user interface should make that distinction clear: a shortened preview must not be mistaken for evidence that the stored value was truncated.

LOBs do not support every operation available for smaller scalar values. For example, directly ordering or grouping by a LOB column is restricted, as is selecting distinct LOB values. Sort a document list by appropriate metadata such as title or identifier. Consult the supported-operation rules before using a familiar SQL expression on the content itself.

Replacing content with SQL

Updating a complete document value can be expressed with ordinary SQL. The WHERE clause identifies the intended row, while the SET clause supplies its replacement content. This is conceptually different from changing a selected portion of the existing document.

UPDATE lesson_documents
SET body = TO_CLOB('Revised introduction to Oracle LOBs.')
WHERE document_id = 1;

COMMIT;

In this example, the earlier text is replaced. It is not preserved automatically as a previous revision in an application history table. If users need version history, design that feature explicitly. The presence of a large-object column does not by itself define document approval, revision naming, or retention rules.

Transaction boundaries remain important. An application should commit when its complete logical change is ready, which may include both document content and related metadata. The standalone COMMIT makes this teaching example explicit; it is not a requirement to commit after every statement inside a larger business operation.

Introducing piecewise PL/SQL operations

DECLARE
    l_body CLOB;
    l_note VARCHAR2(100) := ' Reviewed.';
BEGIN
    SELECT body
      INTO l_body
      FROM lesson_documents
     WHERE document_id = 1
       FOR UPDATE;

    DBMS_LOB.WRITEAPPEND(l_body, LENGTH(l_note), l_note);
END;
/
COMMIT;

Missing, empty, and populated values

An internal LOB can be NULL, initialized but empty, or populated. These states should have distinct meanings in your application. A NULL body might mean that no document has been supplied. An empty body might mean that a document has been initialized but contains no content yet.

For a CLOB column, EMPTY_CLOB() can initialize an empty LOB in an INSERT or UPDATE. An empty stored LOB has length zero and is not NULL. Do not assume that testing IS NOT NULL proves a document contains text, or that an empty value and a missing value are interchangeable.

When using locator-based writes, obtain a suitable locator for the stored LOB, for example through a query or a DML RETURNING clause. Do not simply pass the direct result of EMPTY_CLOB() to DBMS_LOB and expect it to identify a writable stored document. Initialization and retrieval of an appropriate locator are separate parts of the workflow.

Deleting rows and planning document lifecycle

A DELETE statement removes the selected database row. For the practice example, DELETE FROM lesson_documents WHERE document_id = 1 selects the row by its identifier. Run that only when you intend to finish using the example, because subsequent queries and updates will no longer find the row after the deletion is committed.

Application requirements may call for retaining an inactive document instead of deleting its row. Decide that from the business rule, then implement the corresponding status or history design. The datatype does not make that decision. Similarly, deleting a database row containing a BFILE reference does not delete the external operating-system file.

Loading and application interfaces

SQL*Loader provides a route for loading data into Oracle tables, including tables with LOB content. A loading plan must connect each document with its identifying metadata. For the report application, that means making sure the title, identifier, and content all describe the same document. The later loading lesson develops the mechanics.

OCI is Oracle's C-language database interface, while JDBC provides database access for Java applications. PL/SQL programs use database-side facilities such as DBMS_LOB. These interfaces address different application environments; choosing one does not change whether the underlying content is character data, binary data, or an external file.

When planning a load or an application transfer, ask how errors will be detected and which operation can be retried. A file arriving successfully is not enough if it becomes associated with the wrong document number. Reliable LOB handling includes the ordinary relational discipline of identifying the right row and preserving its relationships.

Reviewing a LOB design before implementation

Work through a document's lifecycle before writing a larger program. Start with creation: does a user upload a binary file, enter editable text, or provide a reference to a file managed elsewhere? Next consider retrieval: does the application show a title list, a preview, or the complete contents? Finally consider change: is the document replaced as a whole, extended with additional text, or retained as a fixed record?

These questions help separate application behavior from storage choices. A title list does not need to download every attachment. An approval operation may need to update both the document and its status together. A system that retains an original upload may need another value for edited text rather than repeatedly replacing the original bytes.

Use the same questions when reviewing the examples in this module. Identify the target row, the intended type, the requested operation, and the transaction boundary. If an example uses an external file, identify who controls that file and whether the database server can access it. If it uses an internal LOB, distinguish replacing its contents from changing its metadata.

A useful learning check is to describe the expected result before executing a statement. After an append, the old text should remain with new text at the end. After a replacement, the new value should stand alone. After a preview query, the stored document should remain unchanged. This habit makes later exercises easier to reason about and helps reveal mistakes that a successful statement execution alone would not expose.

Module objectives

Oracle documentation

• Large objects and modern storage
• LOB types, persistent values, and BFILE access
• DBMS_LOB package reference
• PL/SQL API for LOBs
• Restrictions on SQL operations
• LOB SQL semantics, including NULL and empty values