Creating a trigger in Oracle Express

Question

I was trying to do something like auto-increment in Oracle 11g Express and SQL Developer. I know very little about Oracle and I am also new to triggers.

I tried running this, but I don't know how to do it properly.

CREATE TABLE theschema.thetable
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

CREATE SEQUENCE theschema.test1_sequence
START WITH 1
INCREMENT BY 1;

create or replace trigger insert_nums
before insert on theschema.thetable
for each row
begin
select test1_sequence.nextval into :new.id from dual;
end;
/

When I try to create the trigger, I get a screen which asks me for some "binds". The dialog box has only one check box "null". What does this mean and how do I make a script that works properly?

Any precautions to take while doing this kind of "auto-increment" ?

Enter binds

I added the SQL-Developer tag, as this code looks fine; It's asking you to fill in a bind variable so I assume there's some setting you have to turn off... I have no idea what though. — Ben, Aug 28 '12 at 20:01
BTW, from Oracle 11 onwards you can reference a sequence directly. This means you can write `:new.id := test1_sequence.nextval` without using the SELECT. — Ben, Aug 28 '12 at 20:01
Still an issue in SQL Developer 4.2.0.x and the workaround given is still successful. — Andrew Spencer, Dec 06 '17 at 13:01

score 52 · Accepted Answer · edited Sep 08 '12 at 09:46

52

It seems that SQL Developer thinks that you are running a plain DML (data manipulation) script, not a DDL (data definition). It also thinks that :new.id is a bindable variable.

Why this happens, I don't know; I can't reproduce it in Oracle SQL Developer 2.1.

Try to open a new SQL worksheet window in the theschema schema and execute a "whole" script (not a statement) by pressing F5 (not F9).

edited Sep 08 '12 at 09:46

Ben

51,770
36
127
149

answered Aug 28 '12 at 21:10

Michael T

988
9
9

Thank you so much ! that works. I don't know why the whole script must be executed to make this thing work properly. Why can't it be done part by part. – bread butter Aug 28 '12 at 22:46
You are welcome! I don't know why this happens too, because my SQL Developer works fine. – Michael T Aug 29 '12 at 07:41
1

I had this exact problem and this was the solution. I can confirm It's related to the SQL Developer version. – 0xFED5550 Mar 23 '17 at 14:56

score 15 · Answer 2 · answered Sep 11 '13 at 14:13

15

This is how I have solved this problem, put "set define off;" before the command:

set define off;
create or replace trigger [...]
[...]
end;
/

Then highlight both commands and press F9 to run. Or you could run all the commands with F5.

It seems, that if the commands are executed separetly with F9, then the set define off does not take affect.

answered Sep 11 '13 at 14:13

doki42

319
3
3

Sounds like a good solution, but would be better if you mentionned what "set define off" actually does. From what I read, seems it turns off prompting user for this kind of variable/values binds ? Nice to know. https://stackoverflow.com/questions/34332639/when-or-why-to-use-a-set-define-off-in-oracle-database – Balmipour Jun 01 '17 at 13:42
This doesn't fix the issue in the question. It stops SQL Developer interpreting placeholder variables (marked by ampersand, & ) but does not affect treatment of bind variables (marked by a colon, : ) which was the problem OP asked about. – Andrew Spencer Dec 06 '17 at 13:02

score 0 · Answer 3 · answered Jan 02 '14 at 15:13

0

For my case, solution was entering "newrow" for 'new' and "oldrow" for 'old' as values for the binds...

answered Jan 02 '14 at 15:13

VolkanT

574
5
10

score 0 · Answer 4 · edited Sep 14 '15 at 20:16

I am a novice at this so keep that in mind as I give my answer. I think the issue is that the code

create or replace trigger insert_nums
before insert on theschema.thetable
for each row
begin
select test1_sequence.nextval into :new.id from dual;
end;

Is actually a script and not straight forward SQL statement. Hence you have to run the "Run Script". I discovered that when I had a worksheet open in SQL Developer that if I had anywhere in the worksheet the any code for trigger like above then even I just tried to run a statement that SQL Developer would look back in the worksheet and try to run the script. To stop that from happening I had to comment out the code. And If I did want to run the code for the trigger than I had to open a new worksheet, place the code there and do a RUN SCRIPT.

Creating a trigger in Oracle Express

4 Answers4

Linked