Convert LONG | LONG RAW to LOB

Oracle supports LOB datatypes for storing large text and binary content: CLOB, NCLOB, BLOB, and BFILE. In older schemas you may still find LONG and LONG RAW, which were early “large value” types used before modern LOB storage matured.

Today, LOBs are the baseline because they integrate with modern SQL and PL/SQL patterns: SecureFiles storage features, encryption, compression, deduplication, locator semantics, streaming APIs, online redefinition, and better tooling support. This lesson focuses on a practical DBA workflow: converting existing LONG/LONG RAW columns into LOB columns safely, repeatably, and with predictable performance characteristics in Oracle 23ai environments.

Migration is not just “cleanup.” It removes architectural limits that can block features and complicate operations. Common pain points with LONG/LONG RAW include:

1. Schema constraints: historically, you are limited in how many LONG columns you can place in a table.
2. SQL limitations: LONGs are difficult (or impossible) to use in many SQL expressions, functions, and joins.
3. Bind and client handling: LONG values do not behave like true LOBs in bind-variable or streaming workflows.
4. Object-relational features: LOBs can participate in richer structures and APIs than LONG columns.
5. Operational friction: modern tooling, migration utilities, and performance features focus on LOBs—not LONG.

Practically: if you’re modernizing a schema for Oracle 23ai, LONG/LONG RAW becomes a “compatibility tax.” Converting to LOBs makes future tuning, backup/restore, encryption policies, and application development far more predictable.

• LONG → CLOB (typical) or NCLOB (if you require national character set semantics)
• LONG RAW → BLOB

In most modern deployments, CLOB is the default for large text, and NCLOB is reserved for cases where your character set strategy requires it. For binary payloads (images, PDFs, compressed blobs), BLOB is the correct target.

There is no single “right” conversion method. Pick based on downtime tolerance, table size, and dependency complexity. The three most common approaches are:

1. Batch copy using TO_LOB (classic, explicit, simple): create a new table with LOB columns and copy.
2. In-place migration using ALTER TABLE (where applicable): change the datatype from LONG to LOB via SQL.
3. Near-zero downtime using DBMS_REDEFINITION: online table redefinition while users keep working.

In Oracle 23ai migration projects, you may also encounter a fourth approach during Data Pump import: transform LONG/LONG RAW automatically into LOBs during the import operation. (This is an excellent option when you’re already moving schemas between environments.)

TO_LOB converts LONG/LONG RAW values in a query projection into LOB values. The important operational detail is that TO_LOB is used in a SELECT list inside an INSERT (or a create-and-load pattern), where you copy from the legacy table into a new table that already has LOB columns.

Step-by-step (repeatable pattern)

1. Create the new table with the correct LOB datatypes and constraints.
2. Copy rows from the legacy table using TO_LOB for the LONG/LONG RAW column(s).
3. Validate counts and checksums (or lengths/hashes for spot checks).
4. Cut over by swapping names (rename tables) or repointing application objects.
5. Rebuild dependent objects (indexes, grants, synonyms, constraints) as needed.

Example: LONG → CLOB

-- 1) Legacy table
--   docs_legacy(doc_id NUMBER PK, title VARCHAR2(200), body_long LONG)

-- 2) New table with modern type
CREATE TABLE docs_new (
  doc_id     NUMBER PRIMARY KEY,
  title      VARCHAR2(200),
  body_clob  CLOB
);

-- 3) Copy: TO_LOB converts LONG values when selecting from the legacy table
INSERT INTO docs_new (doc_id, title, body_clob)
SELECT doc_id, title, TO_LOB(body_long)
FROM   docs_legacy;

COMMIT;
  

Example: LONG RAW → BLOB

-- 1) Legacy table
--   images_legacy(img_id NUMBER PK, filename VARCHAR2(255), payload_longraw LONG RAW)

-- 2) New table
CREATE TABLE images_new (
  img_id     NUMBER PRIMARY KEY,
  filename   VARCHAR2(255),
  payload    BLOB
);

-- 3) Copy
INSERT INTO images_new (img_id, filename, payload)
SELECT img_id, filename, TO_LOB(payload_longraw)
FROM   images_legacy;

COMMIT;
  

Restrictions and practical notes

• TO_LOB is only for LONG/LONG RAW. It does not convert arbitrary datatypes.
• Many real-world migrations use TO_LOB as part of a “create new table + copy + swap” process because it is deterministic and reversible.
• For very large tables, consider copying in batches (by key range) and committing per batch to control undo/redo pressure.

Batch copy template (key-range)

-- Example batch copy pattern (repeat for key ranges)
INSERT /*+ APPEND */ INTO docs_new (doc_id, title, body_clob)
SELECT doc_id, title, TO_LOB(body_long)
FROM   docs_legacy
WHERE  doc_id BETWEEN :min_id AND :max_id;

COMMIT;
  

When your environment and table design allow it, Oracle provides an ALTER TABLE approach to migrate a LONG column definition to a LOB datatype. This method is attractive because it minimizes the number of objects you create—but you must test it carefully in a staging environment, because in-place conversions can have dependency and logging implications.

In a conversion plan, treat this method as a controlled maintenance operation: schedule it, measure it, and make sure you can roll back (via backup/flashback strategy or by keeping a copy table).

-- Illustrative example: LONG -> CLOB
ALTER TABLE docs_legacy MODIFY (body_long CLOB);

-- Illustrative example: LONG RAW -> BLOB
ALTER TABLE images_legacy MODIFY (payload_longraw BLOB);
  

If this approach is not supported for your table due to dependencies or constraints, fall back to Method 1 (TO_LOB copy) or Method 3 (online redefinition).

For production tables with uptime requirements, DBMS_REDEFINITION is the standard approach: it lets you create an “interim” table with the desired LOB columns and then redefine the original table online. This is especially valuable when the table is large, heavily referenced, or cannot tolerate a long lock.

At a high level, the flow looks like this:

1. Create an interim table with the new LOB datatype(s).
2. Start redefinition (Oracle tracks changes).
3. Copy dependent objects (indexes, triggers, grants) to the interim table.
4. Finish redefinition (swap happens under controlled lock).

DBMS_REDEFINITION has a richer API than can be covered in a single lesson page, but conceptually it is the “production-safe” version of the TO_LOB copy method.

If you are migrating schemas between databases (for example dev → test → prod, or on-prem → OCI), Oracle Data Pump can perform a conversion during import. This is a powerful “free win” because you avoid writing custom SQL and you standardize the transformation as part of your deployment pipeline.

-- Example concept (exact syntax depends on your impdp command line)
-- TRANSFORM=LONG_TO_LOB:Y
  

The important idea: when you are already doing a logical move, it can be the best time to eliminate LONG/LONG RAW from the schema.

1) Choose a LOB storage model deliberately

After migration, you will still have a key design choice: how the LOB is stored. In earlier lessons you saw the “in-row vs out-of-row” and external storage concepts. In real systems, you typically combine these choices with SecureFiles features and tablespace strategy.

• Small text fragments: may remain in-row if enabled and if the value stays under the in-row threshold.
• Large documents: generally belong out-of-row in a dedicated tablespace for manageability and IO isolation.
• External binaries you do not want inside Oracle: use BFILE (read-only from Oracle’s perspective).

2) SecureFiles and modern LOB features

Modern Oracle LOB deployments typically standardize on SecureFiles LOB storage because it supports compression, deduplication, and encryption options that are not available to older LOB storage models. This is where “LOB migration” becomes a performance topic: the datatype change is step one, but the storage configuration drives IO, CPU, and footprint over the long term.

3) Transaction, undo, redo, and batch size

LOB migrations can produce substantial redo/undo depending on method and logging mode. If you are copying millions of rows, treat the conversion as an ETL job:

1. Copy in predictable batches.
2. Monitor undo and temp pressure.
3. Commit per batch.
4. Validate row counts and sample checksums.

4) Application cutover and compatibility

Applications that previously treated LONG columns in special ways (client fetch size quirks, legacy drivers) often become simpler once they can use standard LOB APIs and streaming. After cutover, verify:

• CRUD operations still work (insert/update/select/delete).
• Any reporting jobs that selected LONG columns now behave consistently with locator-based LOB reads.
• Backup/restore and export/import routines continue to work with your new LOB strategy.

1. Row counts match: legacy table vs new table.
2. Spot checks: compare length/hash of a sample of LOB values.
3. Constraints and keys: primary key uniqueness and not-null rules preserved.
4. Dependencies: triggers, views, grants, synonyms recreated and tested.
5. Performance: validate common query paths and update patterns.

-- Row count comparison (example)
SELECT (SELECT COUNT(*) FROM docs_legacy) AS legacy_cnt,
       (SELECT COUNT(*) FROM docs_new)    AS new_cnt
FROM dual;

-- Sample length checks (example)
SELECT doc_id, DBMS_LOB.getlength(body_clob) AS clob_len
FROM   docs_new
WHERE  doc_id IN (101, 202, 303);
  

Once your checks pass, you can finalize the cutover (rename/swap) and retire the LONG/LONG RAW definitions.

In the next lesson, you will learn how to manage the size of data stored in LOBs and how storage settings influence both performance and space usage over time.