Oracle: Statements running into each other

Question

I am not fluent with Oracle, and am testing out an auto-incrementing trigger required for older versions, where IDENTITY is not yet available.

CREATE TABLE stuff (
    id int PRIMARY KEY,
    data varchar(255)
);

CREATE SEQUENCE stuff_sequence;
CREATE TRIGGER test
BEFORE INSERT ON stuff
FOR EACH ROW
BEGIN
    SELECT stuff_sequence.nextval INTO :new.id FROM dual;
END;

INSERT INTO stuff(data) VALUES('test');

Using SQL Developer, I can run the CREATE TABLE statement, and then the CREATE SEQUENCE statement, but when I run the CREATE TRIGGER statement, it highlights the INSERT statement as well, and then complains about `Encountered the symbol "INSERT". Obviously, if I try to run the whole lot as a single script I get the same error.

What is happening here, and how do I fix it?

score 4 · Accepted Answer · edited May 10 '23 at 09:17

4

Just put / at the end of the trigger code. Which represents the end of the code above it.

CREATE TRIGGER test
BEFORE INSERT ON stuff
FOR EACH ROW
BEGIN
    SELECT stuff_sequence.nextval INTO :new.id FROM dual;
END;
/ -- this

INSERT INTO stuff(data) VALUES('test');

/ during the entering of a DML or DDL or PL/SQL means "terminate the current statement, execute it and store it to the SQLPLUS buffer" and / is needed in multiple lines code to indicate that the code is ending here.

edited May 10 '23 at 09:17

halfer

19,824
17
99
186

answered Mar 18 '20 at 02:33

Popeye

35,427
4
10
31

Sorry, what is the slash and why do I use it? Is it like `GO` in Microsoft? – Manngo Mar 18 '20 at 02:43
It represents the end of the code which is written above it. Each plsql code in a single script needs to be ended with `/` – Popeye Mar 18 '20 at 02:59
If this answer solved your issue then please accept it, so that question is marked as resolved. – Popeye Mar 18 '20 at 03:04
I did ask what was happening. I wasn’t aware that I was writing any plsql code as such, just SQL. The answer doesn’t explain why the slash goes after the `CREATE TRIGGER` and not elsewhere. To me adding the slash at this point but nowhere else looks like a magical incantation. – Manngo Mar 18 '20 at 05:49
`/` during the entering of a DML or DDL or PL/SQL means "terminate the current statement, execute it and store it to the SQLPLUS buffer" and `/` is needed in multiple lines code to indicate that the code is ending here. – Popeye Mar 18 '20 at 05:56
But why there and not after the other statements? – Manngo Mar 18 '20 at 06:14
You have only one PL/SQL code here. That is why.. And it is good practice to put `/` after each statement – Popeye Mar 18 '20 at 06:16
So, what you’re saying is that `CREATE TRIGGER` is not regarded as a normal SQL DDL statement, but rather a switch to PLSQL. Is that what you’re saying? The rest is normal SQL. – Manngo Mar 18 '20 at 06:24
Yes, It is. I think you got it!! – Popeye Mar 18 '20 at 06:28
OK, I’ve found the relevant documentation: https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/CREATE-TRIGGER.html#GUID-EE0DF3AA-7ADC-4171-B8E8-138BE9224E3B. The key is that the trigger, in this case is an anonymous PL/SQL block. The slash is _not_ required for pure DDL, but it is required for PL/SQL. I thought Microsoft was the weird one with the `GO` statement. – Manngo Mar 18 '20 at 08:39
For more information: https://stackoverflow.com/questions/1079949/when-do-i-need-to-use-a-semicolon-vs-a-slash-in-oracle-sql#answer-11211137 – Manngo Mar 18 '20 at 09:29

Oracle: Statements running into each other

1 Answers1