Auto increment with Trigger in oracle 11g : ORA-00900: invalid SQL statement [Script position: 363 - 367] END

Question

I wanna create a Table with auto increment the id in oracle 11g in dbvisualizer, I know that in oracle 12c Identity is there for this but in 11g I followed the below scenario and facing issue.

   CREATE TABLE sample (
    id NUMBER NOT NULL,
    price number
    );

ALTER TABLE sample
  ADD (
    CONSTRAINT sample_pk PRIMARY KEY (id)
  );
  
 CREATE SEQUENCE sample_sequence START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
     

But when I execute this below trigger getting the following error.

CREATE OR REPLACE TRIGGER sample_on_insert
BEFORE INSERT ON sample
FOR EACH ROW
  BEGIN
    select sample_sequence.nextval 
    into :new.id from dual;
  END;

Error:

20:16:19  SUCCESS  [CREATE - 0 rows, 0.339 secs]  OK. No rows were affected  
CREATE OR REPLACE TRIGGER sample_on_insert
BEFORE INSERT ON sample
FOR EACH ROW
  BEGIN
    select sample.nextval 
    into :new.id from dual;
20:16:19  FAILED  [END - 0 rows, 0.830 secs]  [Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
  [Script position: 363 - 367]  
END;
20:16:20  END Execution 2 statement(s) executed, 0 row(s) affected, exec/fetch time: 1.169/0.000 secs   [1 successful, 1 errors]

And When I execute only below content in trigger:

select sample_sequence.nextval 
into :new.id from dual;

Error:

20:31:07  FAILED  [SELECT - 0 rows, 0.956 secs]  [Code: 1008, SQL State: 72000]  ORA-01008: not all variables bound
  [Script position: 230 - 244]  
select sample.nextval into :new.id from dual;
20:31:08  END Execution 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.956/0.000 secs   [0 successful, 1 errors]

Even I did in this way too the error is same:

CREATE OR REPLACE TRIGGER sample_on_insert
BEFORE INSERT ON sample
FOR EACH ROW
  BEGIN
    :NEW.id := sample_sequence.NextVal;
  END;

Answer 1

Try this for your trigger. Note the addition of the "referencing" clause:

create or replace trigger sample_on_insert
before insert on sample
referencing old as old and new as new
for each row
begin
   :new.id := sample_sequence.nextval;
end;
/

Also note that the error indicates that your create trigger command is being broken up into separate submissions to the db. dbvisualizer requires special delimiter settings for multi-line PL/SQL commands (multiple ";" in a command block), as documented here: http://confluence.dbvis.com/display/UG100/Executing+Complex+Statements

Answer 2

Your code works in 11g, no problem. What might be an issue is a missing slash (see line #8 in create trigger statement).

SQL> select * From v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL>

Copy/paste of your code:

SQL> CREATE TABLE sample (
  2      id NUMBER NOT NULL,
  3      price number
  4      );

Table created.

SQL> ALTER TABLE sample
  2    ADD (
  3      CONSTRAINT sample_pk PRIMARY KEY (id)
  4    );

Table altered.

SQL>  CREATE SEQUENCE sample_sequence START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

Sequence created.

SQL> CREATE OR REPLACE TRIGGER sample_on_insert
  2  BEFORE INSERT ON sample
  3  FOR EACH ROW
  4    BEGIN
  5      select sample_sequence.nextval
  6      into :new.id from dual;
  7    END;
  8  /                                             --> this

Trigger created.

Testing:

SQL> insert into sample (price) values (100);

1 row created.

SQL> select * from sample;

        ID      PRICE
---------- ----------
         1        100

SQL>