ORA-00933: SQL command not properly ended? Enter Bind Variables?

Question

I am attempting to write out in my SQL commands for Oracle Apex 5, and I cannot see why I'm getting an error. Also, a popup window appears once I submit asking to 'Enter Bind Variables' for ':NEW'? Isn't it already declared?

drop table tmpcapacityguide cascade constraints;
drop table tmpcaravanpark cascade constraints;

create table tmpcaravanpark as select * from Caravan_park;
create table tmpcapacityguide 
(mincap   number(3),
 maxcap   number(3));

insert into tmpcapacityguide values(30, 150);

CREATE OR REPLACE TRIGGER "PARK_CAPACITY_CHECK" 
before insert or update of capacity
on Caravan_park
for each row
when (:new.capacity < 30) DECLARE
mincapacity integer;
maxcapacity integer;

begin
select mincap, maxcap
into mincapacity, maxcapacity
from tmpcapacityguide
where capacity = :new.capacity;
if (new.capacity < mincapacity)
then raise_application_error(-20601, 'Capacity must be between 30 and 150');
end if;
end;

I have ran it as a script and have now got the following error: PL/SQL: ORA-00904: "CAPACITY": invalid identifier — Trystan Lapinig-Wilcock, Jan 04 '16 at 14:24

score 0 · Answer 1 · edited May 23 '17 at 11:59

Try using slashes, and name of trigger after last end, and also You do not need ":" sign before new variable in IF statement:

drop table tmpcapacityguide cascade constraints;
drop table tmpcaravanpark cascade constraints;

create table tmpcaravanpark as select * from Caravan_park;
create table tmpcapacityguide  (mincap   number(3), maxcap   number(3));
/
insert into tmpcapacityguide values(30, 150);
/
CREATE OR REPLACE TRIGGER PARK_CAPACITY_CHECK 
    before insert or update of capacity on caravan_park
    for each row
    when (new.capacity < 30) 
declare
    mincapacity integer;
    maxcapacity integer;
begin
    select mincap, maxcap
    into mincapacity, maxcapacity
    from tmpcapacityguide
    where capacity = :new.capacity;

    if (new.capacity < mincapacity)
    then 
        raise_application_error(-20601, 'Capacity must be between 30 and 150');
    end if;
end PARK_CAPACITY_CHECK;
/

Also, You should execute it as a script, not statement in SQL Developer. This can be done by pressing F9, not F5, as sstated here.

EDIT:

As for Your ORA-00904 error, You still do not have capacity column in Your tmpcapacityguide table. And You try to use it here:

select mincap, maxcap
into mincapacity, maxcapacity
from tmpcapacityguide
where capacity = :new.capacity;

Tried your suggestions, errors still persist. I don't understand why I'm getting a popup window asking me to enter bind variables for :NEW isnt it already declared? — Trystan Lapinig-Wilcock, Jan 04 '16 at 13:42
Reaserched this a litte bit, and added comment below example. Also, added trailing slash, please try it with it and pressing F9. — T.Z., Jan 04 '16 at 14:02
I have ran it as a script and have now got the following error: PL/SQL: ORA-00904: "CAPACITY": invalid identifier — Trystan Lapinig-Wilcock, Jan 04 '16 at 14:24
Yes, because as stated before You do not have capacity column in tmpcapacityguide table. :) See my edit. — T.Z., Jan 04 '16 at 14:28

score 0 · Answer 2 · answered Jan 04 '16 at 14:07

0

:new.capacity is input and has to be declared. For more info see here: http://www.akadia.com/services/ora_bind_variables.html

answered Jan 04 '16 at 14:07

Peter

5,556
3
23
38

This is trigger, and new and old are special cases of variables for triggers [read this](http://www.techonthenet.com/oracle/triggers/before_update.php). – T.Z. Jan 04 '16 at 18:31

ORA-00933: SQL command not properly ended? Enter Bind Variables?

2 Answers2