ORA-01733: VIRTUAL COLUMN NOT ALLOWED HERE

             ORA-01733: VIRTUAL COLUMN NOT ALLOWED HERE

Introduction

•  ORA-01733: virtual column not allowed here, this error appears when try to update the view using PL/SQL update statement.

Solution:

• Avoid this error, we should use instead of trigger.

  Example:

    View:

     Create or replace view customer_view as

     select cust_id,cust_name,cust_city,Zip_code

     from customer

     order by cust_name;

 Inserting records,

    Insert into customer_view values(1000,’tannie’,’us’,65001);

    Insert into customer_view values(1001,’thara’,’us’,65001);

    Select * from customer_view

CUST_ID	CUST_NAME	CUST_CITY	ZIP_CODE
1000	tannie	us	65001
1001	thara	us	65001

Trying to update,

Update customer_view set cust_name =’Lara’ where cust_id =1000;

Here we had the error,

    ORA-01733: VIRTUAL COLUMN NOT ALLOWED HERE 

To overcome this error:

 Use instead of trigger,

1. CREATE OR REPLACE TRIGGER Customer_view
   INSTEAD OF update on customer_view
   for each row
   BEGIN
    UPDATE customer_view SET cust_name =:new.cust_name
    where empid = :old.cust_id;
   END;

After the successful trigger creation run the update statement,

  Update customer_view set cust_name =’Lara’ where cust_id =1000;

  Select * from customer_view

CUST_ID	CUST_NAME	CUST_CITY	ZIP_CODE
1000	Lara	us	65001
1001	thara	us	65001

Note:

In what kind of cases view cannot be update:

       1.    When using Distinct operator in the view

       2.    When using any kind of functions or order by clause

       3.    Using a sub query

       4.    Using join condition

Share: