Saturday, November 10, 2012

ORA-01733

This post shows how you might get an ORA-01733. First you need a table with some data in:

SQL> create table andrews_table
  2  (first_name varchar2(10))
  3  /
 
Table created.
 
SQL> insert into andrews_table
  2  values('Brian')
  3  /
 
1 row created.

SQL>

Next you have to create a view of the table. The view needs to include a virtual column, i.e. a column which cannot be mapped to data stored in the table. In the example, I have decided that every row in the view should have an age of 21. There is no column in the underlying table to record the person's age so I simply added the number 21 to the view and called it age. I'm not suggesting you should copy this idea in your own applications; I'm just doing it to create a simple example to illustrate the Oracle error:  
 
SQL> create view andrews_view
  2  as select first_name, 21 age
  3  from andrews_table
  4  /
 
View created.
 
SQL> select * from andrews_view
  2  /
 
FIRST_NAME        AGE
---------- ----------
Brian              21

SQL>

Then you must update the view. You can update first_name because this change can be carried through to the underlying table: 

SQL> update andrews_view
  2  set first_name = 'Colin'
  3  where first_name = 'Brian'
  4  /
 
1 row updated.

SQL>

However, if you try to update the age column, you get an ORA-01733:
 
SQL> update andrews_view
  2  set age = age + 1
  3  /
set age = age + 1
    *
ERROR at line 2:
ORA-01733: virtual column not allowed here
 
SQL>

The only way to change the data in the age column is to drop the view and recreate it with the new value.

In Spanish / en espaƱol 

No comments: