Oracle ORA-02014 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02014: Cannot SELECT FOR UPDATE from View with DISTINCT, GROUP BY, etc.

ORA-02014 occurs in Oracle Database when you attempt to use a SELECT FOR UPDATE statement against a view or subquery that contains DISTINCT, GROUP BY, aggregate functions (like SUM, COUNT), or set operators (UNION, INTERSECT, MINUS). The FOR UPDATE clause requires Oracle to identify and lock specific rows in the base table, but these operations aggregate or eliminate rows, making it impossible to trace results back to individual source rows. As a result, Oracle cannot determine which rows to lock and raises ORA-02014.

Top 3 Causes

1. Using FOR UPDATE on a View with DISTINCT or GROUP BY

When a view uses DISTINCT or GROUP BY, multiple source rows are collapsed into a single result row. Oracle has no way to identify the original rows for locking.

-- View definition (with DISTINCT)
CREATE OR REPLACE VIEW vw_unique_depts AS
SELECT DISTINCT dept_id, location
FROM employees;

-- Attempting FOR UPDATE on this view raises ORA-02014
SELECT *
FROM vw_unique_depts
WHERE dept_id = 10
FOR UPDATE; -- ERROR: ORA-02014

-- Fix: Query the base table directly
SELECT DISTINCT dept_id, location
FROM employees
WHERE dept_id = 10
FOR UPDATE; -- Still fails! Use the base table without DISTINCT instead:

SELECT emp_id, dept_id, location
FROM employees
WHERE dept_id = 10
FOR UPDATE; -- Works correctly

2. Using FOR UPDATE on a View with Set Operators (UNION, MINUS, INTERSECT)

Set operators combine results from multiple queries, making it ambiguous which base table row a result row maps to.

-- View with UNION
CREATE OR REPLACE VIEW vw_all_staff AS
SELECT emp_id, emp_name FROM full_time_employees
UNION
SELECT emp_id, emp_name FROM part_time_employees;

-- Raises ORA-02014
SELECT * FROM vw_all_staff FOR UPDATE;

-- Fix: Lock each base table separately based on your business logic
SELECT emp_id, emp_name
FROM full_time_employees
WHERE status = 'ACTIVE'
FOR UPDATE;

3. Using FOR UPDATE with Aggregate Functions (SUM, COUNT, AVG)

Aggregate functions compute a single value from multiple rows. The aggregated result cannot be mapped back to a specific lockable row.

-- View with GROUP BY and SUM
CREATE OR REPLACE VIEW vw_dept_totals AS
SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id;

-- Raises ORA-02014
SELECT * FROM vw_dept_totals WHERE dept_id = 20 FOR UPDATE;

-- Fix: Identify target rows via subquery, then lock the base table
SELECT emp_id, dept_id, salary
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM employees
    GROUP BY dept_id
    HAVING SUM(salary) > 300000
)
FOR UPDATE;

Quick Fix Solutions

Option 1: Always apply FOR UPDATE directly to the base table.

-- Instead of locking through a complex view, go directly to the source
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = 10
FOR UPDATE SKIP LOCKED; -- Skip already-locked rows gracefully

Option 2: Use PL/SQL cursor-based row-by-row processing.

DECLARE
    CURSOR cur_emp IS
        SELECT emp_id FROM employees WHERE dept_id = 10
        FOR UPDATE;
BEGIN
    FOR rec IN cur_emp LOOP
        UPDATE employees
        SET salary = salary * 1.1
        WHERE CURRENT OF cur_emp;
    END LOOP;
    COMMIT;
END;
/

Option 3: Use ROWID to lock and update specific rows.

-- Collect ROWIDs first, then lock the base table rows
SELECT emp_id, emp_name
FROM employees
WHERE ROWID IN (
    SELECT ROWID FROM employees WHERE dept_id = 30
)
FOR UPDATE NOWAIT;

Prevention Tips

1. Design views for read-only use; write operations should always target base tables.
Establish a team coding standard where views containing DISTINCT, GROUP BY, or set operators are treated as strictly read-only. Any DML or locking operation must go directly against base tables or through stored procedures that handle locking internally.

2. Document FOR UPDATE compatibility in view comments.
When creating views that are incompatible with FOR UPDATE, add an explicit comment in the DDL so developers know upfront.

COMMENT ON TABLE vw_dept_totals IS
    'READ-ONLY VIEW: Contains GROUP BY/SUM aggregates.
     FOR UPDATE is not supported. Reference the EMPLOYEES base table directly for DML operations.';

Following these practices will prevent ORA-02014 from surfacing in production and keep your row-locking strategy clean and maintainable.

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