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)
);

Answer 1

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;
/