Autoincrement in oracle with seq and trigger - invalid sql statement

Question

I am new to oracle, comming from MySQL, and I am trying to get autoincrement to work in Oracle with a sequence and a trigger, so that it increments my field by one, each time I do an insert.

CREATE SEQUENCE proposals_seq MINVALUE 1 
START WITH 1 INCREMENT BY 1 CACHE 10;

CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT
   ON proposals
   FOR EACH ROW
BEGIN
    SELECT proposals_seq.nextval INTO :new.proposal_id FROM dual;
END;

But when I run the script, I get the error:

Error code 900, SQL state 42000: ORA-00900: invalid SQL statement

If I remove the ";" after the select statement, I get no error, until I try to insert data into the table, then I get this error:

INSERT INTO proposals (target_audience, line_of_business, activity_description, cost, comments, objectives_and_results) 
    VALUES ('test', 'test', 'test', 15, 'test', 'test');

Error code 4098, SQL state 42000: ORA-04098: trigger 'PROPOSALS_BEFORE_INSERT' is invalid and failed re-validation

I am using this version of Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

All the articles I have found about it, seems to be doing it like this and answers in here aswell: How to create id with AUTO_INCREMENT on Oracle?

Can it be because of my version of Oracle? Is there another way for me to autoincrement this? Or will I have to increment manually with the sequence in my sql?

My table looks like this:

CREATE TABLE proposals (
    proposal_id INT NOT NULL,
    target_audience VARCHAR2(50) NOT NULL,
    line_of_business VARCHAR2(50),
    activity_description VARCHAR2(250) NOT NULL,
    cost DECIMAL(19, 4) NOT NULL,
    comments VARCHAR2(250),
    objectives_and_results VARCHAR2(250),
    PRIMARY KEY (proposal_id)
);

You don't need to create a trigger. Just add the nextval of your sequence right on your insert command like `insert into proposals (proposal_id, ....) values (proposals_seq.nextval, ...)` — Jorge Campos, Apr 04 '15 at 19:24
I would like to avoid that, so that it is always consistent. — , Apr 04 '15 at 19:28
Is it possible you don't have the procedural option installed? http://www.dba-oracle.com/sf_ora_00900_invalid_sql_statement.htm - Or perhaps you're trying to accomplish the above in a tool that won't recognize PL/SQL? http://stackoverflow.com/questions/14868229/how-to-install-procedural-option-in-oracle-11gr2 — David Faber, Apr 04 '15 at 19:36
By the way, in 11g you can do this: `:new.proposal_id := proposals_seq.nextval;` instead of this: `SELECT proposals_seq.nextval INTO :new.proposal_id FROM dual;` — David Faber, Apr 04 '15 at 19:37
Thought that was only from 12g - that also bugs out, when I place the ";" at the end of the sentence — , Apr 04 '15 at 19:45
Please note that Oracle sequences aren't designed to produce contiguous ids that always increment with one in the target table. Because sequences need to be very fast and allow parallel multi-threaded access, the internal caching mechanism that acts under the hood will eventually lead into "skipped" ids. So, your ids may look like `1, 2, 3, 20, 30...`, for example. — Mick Mnemonic, Apr 04 '15 at 19:56
Your script should work just fine with [Oracle SQLDeveloper](http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html), using the Run Script (F5) command. — Mick Mnemonic, Apr 04 '15 at 21:13

score 4 · Accepted Answer · answered Apr 04 '15 at 20:37

I suspect the problem is that your client tool is reading every semicolon as the end of a command, causing PL/SQL code (which requires semicolons as statement terminators) to be incorrectly transmitted to the server.

When you remove the semicolon, the statement is correctly sent to the server, but you end up with an invalid object because the PL/SQL is incorrect.

I duplicated your problem on SQL Fiddle. Then I change the statement terminator to / instead of ; and changed the code to use a slash to execute each statement, and it worked without error:

CREATE TABLE proposals (
    proposal_id INT NOT NULL,
    target_audience VARCHAR2(50) NOT NULL,
    line_of_business VARCHAR2(50),
    activity_description VARCHAR2(250) NOT NULL,
    cost NUMBER(19, 4),
    comments VARCHAR2(250),
    objectives_and_results VARCHAR2(250),
    PRIMARY KEY (proposal_id)
)
/

CREATE SEQUENCE proposals_seq MINVALUE 1 
START WITH 1 INCREMENT BY 1 CACHE 10
/

CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT ON proposals FOR EACH ROW
BEGIN
    select proposals_seq.nextval into :new.proposal_id from dual;
END;
/

Yes, this must be it. Especially if the OP is on SQL*Plus, remembering to put slashes between the DDL statements is crucial (http://stackoverflow.com/a/10207695/905488). SQLDeveloper is much more lenient in this respect. — Mick Mnemonic, Apr 04 '15 at 20:54

score -1 · Answer 2 · answered Apr 04 '15 at 20:20

This is the code I use adapted to your table. It will work on any Oracle version but does not take advantage of the new functionality in 12 to set a sequence as an auto increment id

CREATE OR REPLACE TRIGGER your_schema.proposal_Id_TRG BEFORE INSERT ON your_schema.proposal
FOR EACH ROW
BEGIN
  if inserting and :new.Proposal_Id is NULL then
  SELECT your_schema.proposal_Id_SEQ.nextval into :new.Proposal_Id FROM DUAL;
  end if;

END;
/

and usage is

INSERT INTO proposals (proposal_id,target_audience, line_of_business, activity_description, cost, comments, objectives_and_results) 
    VALUES (null,'test', 'test', 'test', 15, 'test', 'test');

Note that we are deliberately inserting null into the primary key to initiate the trigger.

score -1 · Answer 3 · answered Jan 08 '17 at 12:03

I created SEQUENCE and Trigger in Oracle for my table.

my entity:

@Entity
@Table(name = "TBL_AUTHENTICATE")
public class UserSession implements Serializable {
    @Id
    @Column(name = "SESSIONID", nullable = false, length = 12)
    private Long sessionId;

    @Column(name = "USER_ID", nullable = false, length = 10)
    private int user_id;

    @Column(name = "TIME_TO_LIVE", nullable = false)
    private Date time_to_live;

:-D
I try insert data within this table and set sessionId with my generated value and finally I understand the problem. That's my fault.

then I drom Sequence and Trigger from table.
My problem solved.

Autoincrement in oracle with seq and trigger - invalid sql statement

3 Answers3

Linked