3 Replies Latest reply on May 27, 2011 11:03 AM by Frank Kulash

    ora-01733:virtual column not allowed here error

    864933
      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 ??
      Thank u.

      Edited by: 861930 on May 27, 2011 3:51 AM
        • 1. Re: ora-01733:virtual column not allowed here error
          Mahir M. Quluzade
          please post here you update script;
          • 2. Re: ora-01733:virtual column not allowed here error
            Mahir M. Quluzade
            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;
            
              
            • 3. Re: ora-01733:virtual column not allowed here error
              Frank Kulash
              hI,
              861930 wrote:
              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 ??
              You can UPDATE a ciew like that; you just can't UPDATE the columns that involve functions or other expressions (such as DECODE).
              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