2 Replies Latest reply: Sep 29, 2013 3:34 PM by rp0428 RSS

    ORA-00936: missing expression when adding virtual column

    Tshifhiwa

      hi am having this error when adding virtual column am in database 11g

      am having error

      ORA-00936: missing expression

       

      from select upper

       

      my sql is

      ALTER TABLE employees ADD (username AS(select upper(substr(first_name,1,1)) || initcap(last_name)username from employees));

        • 1. Re: ORA-00936: missing expression when adding virtual column
          Etbin

          http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#sthref3374

           

          Adding a Virtual Table Column: Example The following statement adds to a copy of the hr.employees table a column named income, which is a combination of salary plus commission. Both salary and commission are NUMBER columns, so the database creates the virtual column as a NUMBER column even though the data type is not specified in the statement:

          CREATE TABLE emp2 AS SELECT * FROM employees; ALTER TABLE emp2 ADD (income AS (salary + (salary*commission_pct)));

           

          ALTER TABLE employees ADD (username AS(upper(substr(first_name,1,1)) || initcap(last_name)));


          Regards


          Etbin

          • 2. Re: ORA-00936: missing expression when adding virtual column
            rp0428

            hi am having this error when adding virtual column am in database 11g

            am having error

            ORA-00936: missing expression

             

            from select upper

             

            my sql is

            ALTER TABLE employees ADD (username AS(select upper(substr(first_name,1,1)) || initcap(last_name)username from employees));

            You can't use a SELECT statement in the virtual column definition and you don't need to anyway. Get rid of the SELECT and everything except the UPPER function declaration. This works just fine for me:

             

             

            create table emp_test (first_name varchar2(30), last_name varchar2(30));

            ALTER TABLE emp_test  ADD (username AS (upper(substr(first_name,1,1)) || initcap(last_name)));