This content has been marked as final. Show 3 replies
please post here you update script;
I write a simple code for you
create table emp as select * from scott.emp; CREATE OR REPLACE VIEW my_view AS (SELECT empno, ename, job, mgr, hiredate, DECODE(ename, USER,sal, 'KING', sal,0) AS sal, DECODE(ename, USER,comm, 'KING', sal,0) AS comm, deptno FROM emp ); INSERT INTO my_view (empno, ename, job, mgr, hiredate) select empno, ename, job, mgr, hiredate from scott.emp; update my_view set ename='Mahir'; commit;
861930 wrote:You can UPDATE a ciew like that; you just can't UPDATE the columns that involve functions or other expressions (such as DECODE).
i created a view on emp table by below syntax:
create or replace view my_view as (select empno, ename, job, mgr, hiredate,
decode(ename, user,sal, 'KING', sal,0) as sal,
decode(ename, user,comm, 'KING', sal,0) as comm,
deptno from emp);
my view is created but when i update this view its give me ORA-01733:virtual column not allowed here error
can u tell my y this is giving error, but the main thing is that my data in view is updating, and still its giving an error,
can u tell me why this is happening ??
When you say "UPDATE my_view SET sal = 10000", the system doesn't know which column in the table to change. It doesn't parse the DECODE expression, and see that all the return values are either the sal column or a literal, and decide that you must want to UPDATE the sal column.
You can write an INSTEAD OF trigger; then, when you issue an UPDATE statement, it will won't directly UPDATE the table; it will run the trigger instead.
Edited by: Frank Kulash on May 27, 2011 6:55 AM
Edited by: Frank Kulash on May 27, 2011 7:02 AM