Oracle ORA-01462 Error: Causes and Solutions Complete Guide

ORA-01462: Insertions of LONG Values Requires a Single-Row Insert Statement

ORA-01462 is an Oracle error that occurs when you attempt to insert data into a table containing a LONG or LONG RAW column using a multi-row INSERT or a subquery-based INSERT INTO ... SELECT statement. Oracle's internal architecture restricts LONG type columns to single-row insert operations only, meaning each row must be inserted independently. This error is most commonly encountered during data migrations, ETL processes, or batch jobs involving legacy schemas that still use the deprecated LONG data type.

---

Top 3 Causes

1. Using INSERT INTO ... SELECT with a LONG Column

The most frequent cause is attempting a bulk insert using a subquery when the target table has a LONG column.

-- This will throw ORA-01462
INSERT INTO target_table (id, long_col, notes)
SELECT id, long_col, notes
FROM source_table
WHERE dept_id = 10;

-- Fix: Use a PL/SQL cursor loop instead
DECLARE
    CURSOR c_src IS
        SELECT id, long_col, notes
        FROM source_table
        WHERE dept_id = 10;
BEGIN
    FOR rec IN c_src LOOP
        INSERT INTO target_table (id, long_col, notes)
        VALUES (rec.id, rec.long_col, rec.notes);
    END LOOP;
    COMMIT;
END;
/

2. Using INSERT ALL or Multi-Row VALUES

Attempting to insert multiple rows in a single statement using INSERT ALL or multiple VALUES clauses will also trigger this error when a LONG column is involved.

-- This will throw ORA-01462
INSERT ALL
    INTO long_table VALUES (1, 'First record')
    INTO long_table VALUES (2, 'Second record')
SELECT * FROM DUAL;

-- Fix: Use individual INSERT statements
INSERT INTO long_table VALUES (1, 'First record');
INSERT INTO long_table VALUES (2, 'Second record');
COMMIT;

3. Inserting Through a View That Contains a LONG Column

Inserting data through a view that references a LONG column, especially when the view involves joins or subqueries, can trigger ORA-01462.

-- Fix: Use an INSTEAD OF trigger on the view
CREATE OR REPLACE TRIGGER trg_long_view_insert
INSTEAD OF INSERT ON long_data_view
FOR EACH ROW
BEGIN
    INSERT INTO base_long_table (id, long_col, created_date)
    VALUES (:NEW.id, :NEW.long_col, SYSDATE);
END;
/

-- Now this works
INSERT INTO long_data_view (id, long_col)
VALUES (101, 'Sample LONG data via view');
COMMIT;

---

Quick Fix Solutions

The fastest fix depends on your situation:

1. Short-term: Replace INSERT INTO ... SELECT with a PL/SQL cursor loop to process one row at a time.
2. Long-term: Migrate LONG columns to CLOB using Oracle's TO_LOB() function, which removes the restriction entirely.

-- Migrate LONG to CLOB permanently
CREATE TABLE new_table AS
SELECT id, TO_LOB(long_col) AS clob_col, description
FROM old_table
WHERE 1 = 0; -- structure only

INSERT INTO new_table (id, clob_col, description)
SELECT id, TO_LOB(long_col), description
FROM old_table;

COMMIT;

---

Prevention Tips

1. Ban LONG types in new development: Oracle has officially deprecated LONG and LONG RAW. Always use CLOB, BLOB, or NCLOB for new schemas. Enforce this through DDL review policies and code standards.

2. Audit existing schemas regularly: Run the following query periodically to identify any remaining LONG columns and plan their migration proactively.
   

SELECT owner, table_name, column_name, data_type
FROM dba_tab_columns
WHERE data_type IN ('LONG', 'LONG RAW')
  AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, table_name;

By migrating away from LONG types entirely, ORA-01462 becomes a non-issue in your Oracle environment.

---

📖 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.