| Syntax error with simple trigger script [message #9262] |
Wed, 29 October 2003 14:05  |
David Karr
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
I'm using Oracle 8.1.7.
I'm trying to write a simple trigger that is used to fill in a primary key column from a sequence. I have some sort of syntax problem, but I don't understand what it could be. I'd like help with the problem, but I'd also appreciate directions to where in the Oracle documentation I could go to help figure this out for myself (if I run into something similar later).
The trigger is just this:
-----------------
CREATE OR REPLACE TRIGGER VOLUNTEER_ACTIVITY_OID_TRIGGER
BEFORE INSERT ON VOLUNTEER_ACTIVITY
FOR EACH ROW
BEGIN
:new.OID := OID_SEQUENCE.NEXTVAL;
END;
-----------------
I've tried numerous variations of the last two lines. Everything I've tried gets a syntax error on the "create", or shows a validation error when I try an "insert" after this.
|
|
|
|
| Re: Syntax error with simple trigger script [message #9264 is a reply to message #9262] |
Wed, 29 October 2003 14:19  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
There is a thread on this same issue just down the page, but here you go:
create or replace trigger volunteer_activity_oid_trigger
before insert on volunteer_activity
for each row
begin
select oid_sequence.nextval
into :new.oid
from dual;
end;
/ |
|
|
|
| Re: Syntax error with simple trigger script [message #9266 is a reply to message #9264] |
Wed, 29 October 2003 14:29 |
David Karr
Messages: 5
Registered: October 2003
|
Junior Member |
|
|
Ok, this gets me closer. If I enter this directly into sqlplus, it works, and my insert works.
Now, I could still use help getting this to work with the tools that I'm trying to use. I'm storing this string into a file, and passing that into the "sql" task of Ant, which obviously uses JDBC to connect to the database and send the information.
When I process it this way, I get the following from Ant:
------------------
Executing file: createdb.sql
Failed to execute: end
BUILD FAILED
... java.sql.SQLException: ORA-00900: invalid SQL statement
------------------
Inside sqlplus, if I then do "show errors trigger volunteer_activity_oid_trigger;", I get the following:
Errors for TRIGGER VOLUNTEER_ACTIVITY_OID_TRIGGER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/57 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order partition start subpartition union where connect
SAMPLE_
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
