DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01754 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01754: A Table May Contain Only One Column of Type LONG

ORA-01754 is thrown by Oracle when you attempt to define more than one LONG or LONG RAW column within a single table. Oracle's internal storage architecture strictly limits each table to a single LONG-type column, and violating this rule immediately halts the DDL statement. This error is most commonly encountered during table creation, schema migrations from other databases, or when adding columns to legacy tables.

Top 3 Causes

1. Defining Multiple LONG Columns in CREATE TABLE

The most straightforward cause: declaring two or more LONG columns in the same CREATE TABLE statement.

-- This will throw ORA-01754
CREATE TABLE bad_table (
    id       NUMBER PRIMARY KEY,
    notes    LONG,   -- first LONG column
    details  LONG    -- second LONG column → ORA-01754!
);

-- Correct approach: use CLOB instead
CREATE TABLE good_table (
    id       NUMBER PRIMARY KEY,
    notes    CLOB,   -- multiple CLOBs are perfectly fine
    details  CLOB
);
Enter fullscreen mode Exit fullscreen mode

2. Adding a LONG Column via ALTER TABLE When One Already Exists

This often happens when a developer isn't aware that the table already has a LONG column, especially in large legacy systems.

-- Check for existing LONG columns BEFORE altering a table
SELECT column_name, data_type
FROM   user_tab_columns
WHERE  table_name  = 'EXISTING_TABLE'
  AND  data_type  IN ('LONG', 'LONG RAW');

-- Wrong: will fail if a LONG column already exists
ALTER TABLE existing_table ADD extra_notes LONG;  -- ORA-01754!

-- Correct: use CLOB for any new large-text column
ALTER TABLE existing_table ADD extra_notes CLOB;
Enter fullscreen mode Exit fullscreen mode

3. Schema Migration Mapping Multiple Large-Text Types to LONG

When migrating from MySQL (TEXT, MEDIUMTEXT, LONGTEXT) or SQL Server (TEXT, NTEXT) to Oracle, automated migration tools sometimes map all large-text columns to LONG, producing multiple LONG columns in a single table.

-- Migration type-mapping fix example
-- Instead of: col1 LONG, col2 LONG  (wrong)

CREATE TABLE migrated_table (
    id          NUMBER         PRIMARY KEY,
    description CLOB,          -- was TEXT in MySQL
    body        CLOB,          -- was MEDIUMTEXT in MySQL
    raw_data    BLOB           -- was BLOB/LONGBLOB in MySQL
);
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

If you already have a LONG column and need to add another, migrate the existing one to CLOB first using Oracle's TO_LOB function:

-- Step 1: Add a temporary CLOB column
ALTER TABLE my_table ADD old_notes_clob CLOB;

-- Step 2: Copy data using TO_LOB (the only way to convert LONG to LOB)
UPDATE my_table
SET    old_notes_clob = TO_LOB(old_notes);
COMMIT;

-- Step 3: Drop the old LONG column
ALTER TABLE my_table DROP COLUMN old_notes;

-- Step 4: Rename the new CLOB column
ALTER TABLE my_table RENAME COLUMN old_notes_clob TO old_notes;

-- Step 5: Now safely add another large-text column
ALTER TABLE my_table ADD extra_notes CLOB;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Ban LONG and LONG RAW in your DDL standards. Oracle has officially deprecated LONG since Oracle 8i in favor of CLOB and BLOB. Enforce this in code reviews and add a DDL linting step to your CI/CD pipeline that rejects any script containing LONG data type declarations.

  2. Always run a pre-flight check before any DDL change. Make it a standard practice to query USER_TAB_COLUMNS or ALL_TAB_COLUMNS before modifying a table, especially on legacy schemas you didn't originally build.

-- Pre-flight check: find all LONG-type columns in your schema
SELECT table_name,
       column_name,
       data_type
FROM   user_tab_columns
WHERE  data_type IN ('LONG', 'LONG RAW')
ORDER BY table_name;
Enter fullscreen mode Exit fullscreen mode

Key takeaway: CLOB supports up to 4 GB of character data, is fully supported by all modern Oracle features (indexes, SUBSTR, INSTR, full-text search), and completely replaces LONG. There is no good reason to use LONG in any Oracle database running version 8i or later.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)