6 Replies Latest reply on Aug 10, 2005 1:31 PM by 447782

    error: ORA-00984: column not allowed here

    447782
      when i try to insert one row into the table, there is an error:
      ORA-00984: column not allowed here

      insert into testtable (user_firstname, user_lastname ) values(username1, username2);
      -------------------------------------
      table is following:

      creat table testtable(
      userid number not null primary key,
      user_firstname varchar2(30),
      user_lastname varchar2(30)
      );

      since i want the userid field to be auto_increment, i used the follwoing sequence and trigger
      ---------------------------------
      create sequence my_seq
      start with 1
      increment by 1
      nomaxvalue;

      create trigger my_trigger
      before insert on testtable
      for each row
      begin
      if :new.userid is null then
      select my_seq.nextval into :new.userid from dual;
      end if;
      end;
      /

      did i miss something here?

      appreciate your kind reply,

      Emily
        • 1. Re: error: ORA-00984: column not allowed here
          245482
          Where do username1 and username2 come from? Are they pl/sql variables?
          • 2. Re: error: ORA-00984: column not allowed here
            Asif M. Naqvi
            well everything is fine Emily in your case, except one small syntactical oversight.

            in the insert statement, the values username1 and username2 should either be pre-defined variables of varchar2 type or otherwise themselves be in quotes;

            i simply repeated all of your commands and received the message that you reported. then i gave 'username1' and 'username2' and it works.

            ----------------------------------------------------------------------------------------------------

            TEST> insert into testtable (user_firstname, user_lastname ) values(username1, username2);
            insert into testtable (user_firstname, user_lastname ) values(username1, username2)
            *
            ERROR at line 1:
            ORA-00984: column not allowed here

            TEST> insert into testtable
            2 (user_firstname, user_lastname )
            3 values
            4 ('username1', 'username2') ;

            1 row created.

            TEST> select *
            2 from testtable ;

            USERID USER_FIRSTNAME USER_LASTNAME
            ---------- ------------------------------ ------------------------------
            1 username1 username2

            -----------------------------------------------------------------------------------------------------------
            i hope it helps.

            regards,

            naqvi
            • 3. Re: error: ORA-00984: column not allowed here
              149764
              You are absolutely on the right track.

              Change you insert statement to :
              insert into testtable (user_firstname, user_lastname ) values('username1', 'username2');
              • 4. Re: error: ORA-00984: column not allowed here
                447782
                hello, the error is found, the values should be quoted. i'm new to pl/sql. no much idea yet.

                appreicate your reply,

                Emily
                • 5. Re: error: ORA-00984: column not allowed here
                  439412
                  hey, i guess you will be having a front end to insert the information like firstname and lastname right?, if that is the case, which of the following two options are better in terms of performance?
                  1) use a trigger (just the like one above) to populate the id,
                  2) allow the front end to generate the value by using the sequence and then inserting all the values with no triggeres,

                  assume it is a large application with 100 lakh records created on a daily basis???
                  • 6. Re: error: ORA-00984: column not allowed here
                    447782
                    hello, thanks for your reply. my application context is the second one. how to achieve the first one then?

                    appreciate your kind instruct,

                    Emily