Oracle ORA-01733 Error: Causes and Solutions Complete Guide

ORA-01733: Virtual Column Not Allowed Here — Causes, Fixes & Prevention

ORA-01733 is thrown by Oracle Database when you attempt a DML operation (INSERT, UPDATE, or DELETE) on a virtual column — a column derived from an expression rather than physically stored data. Because virtual columns are inherently read-only and computed at query time, Oracle cannot accept a direct value assignment to them. This error most commonly surfaces when working with expression-based view columns or table-level virtual columns defined with the GENERATED ALWAYS AS ... VIRTUAL clause.

---

Top 3 Causes

1. DML on Expression-Based View Columns

When a view contains columns defined as expressions (arithmetic operations, functions, etc.), attempting to update or insert into those columns triggers ORA-01733.

-- View with an expression column
CREATE OR REPLACE VIEW v_emp AS
SELECT
    employee_id,
    salary,
    salary * 12 AS annual_salary  -- expression column
FROM employees;

-- This causes ORA-01733
UPDATE v_emp
SET annual_salary = 60000
WHERE employee_id = 100;

-- ORA-01733: virtual column not allowed here

-- Fix: update the base table directly
UPDATE employees
SET salary = 60000 / 12
WHERE employee_id = 100;

COMMIT;

2. Direct Assignment to Table-Level Virtual Columns

Since Oracle 11g, tables can have virtual columns declared with GENERATED ALWAYS AS. Any attempt to explicitly provide a value for these columns in an INSERT or UPDATE statement raises ORA-01733.

-- Table with a virtual column
CREATE TABLE order_items (
    order_id    NUMBER,
    quantity    NUMBER,
    unit_price  NUMBER(10,2),
    total       NUMBER(12,2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL
);

-- Causes ORA-01733
INSERT INTO order_items (order_id, quantity, unit_price, total)
VALUES (1, 5, 20.00, 100.00);  -- ERROR!

-- Fix 1: exclude the virtual column from the column list
INSERT INTO order_items (order_id, quantity, unit_price)
VALUES (1, 5, 20.00);  -- total is auto-calculated

-- Fix 2: use DEFAULT keyword
INSERT INTO order_items (order_id, quantity, unit_price, total)
VALUES (1, 5, 20.00, DEFAULT);

-- Check which columns are virtual
SELECT column_name, virtual_column, data_default
FROM user_tab_columns
WHERE table_name = 'ORDER_ITEMS';

3. DML on a Complex or Read-Only View

Views built with GROUP BY, DISTINCT, UNION, multi-table JOINs, or created with WITH READ ONLY do not support direct DML. Trying to modify expression columns in such views produces ORA-01733.

-- Complex view with JOIN and expression
CREATE OR REPLACE VIEW v_emp_dept AS
SELECT
    e.employee_id,
    e.salary,
    e.salary * 12   AS annual_salary,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Fix: create an INSTEAD OF trigger
CREATE OR REPLACE TRIGGER trg_v_emp_dept_iou
INSTEAD OF UPDATE ON v_emp_dept
FOR EACH ROW
BEGIN
    UPDATE employees
    SET salary = :NEW.annual_salary / 12
    WHERE employee_id = :OLD.employee_id;
END;
/

-- Now this works via the trigger
UPDATE v_emp_dept
SET annual_salary = 72000
WHERE employee_id = 100;

COMMIT;

---

Quick Fix Summary

Scenario	Solution
Expression column in a view	Update the base table directly
Table virtual column in INSERT	Omit the column or use DEFAULT
Complex/read-only view	Create an INSTEAD OF trigger

---

Prevention Tips

1. Always audit virtual columns before writing DML. Run the query below before building INSERT or UPDATE statements on any table or view you're unfamiliar with:

   -- Find all virtual columns in your schema
   SELECT table_name, column_name, data_default
   FROM user_tab_columns
   WHERE virtual_column = 'YES'
   ORDER BY table_name;

   -- Check which view columns are updatable
   SELECT column_name, updatable, insertable, deletable
   FROM user_updatable_columns
   WHERE table_name = 'YOUR_VIEW_NAME';

1. Enforce WITH READ ONLY on reporting views and pair DML-capable complex views with INSTEAD OF triggers from day one. Making the read-only intent explicit forces developers to target base tables correctly and eliminates accidental DML against expression columns during development.

---

Related Errors

• ORA-01732 — DML not permitted on the view at all (not just a specific column).
• ORA-54013 — INSERT specifically disallowed on a virtual column (Oracle 11g+).
• ORA-01779 — Cannot modify a column mapped to a non-key-preserved table in a join view.
• ORA-01776 — Cannot modify more than one base table through a join view.

---

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