Wednesday, June 22, 2011

One tricky issue of creating Oracle trigger

I came across one tricky issue which puzzled me for a while when I was tring to create one trigger in Oracle.

The SQL statement is as the follow:

CREATE OR REPLACE
TRIGGER
prsd_nonidmp_req_ins_trigger
BEFORE INSERT
ON PROCESSED_NON_IDEMPOTENT_REQ
REFERENCING NEW AS NEW_ROW
FOR EACH ROW
BEGIN
SELECT processed_nonidmp_req_sequence.nextval INTO :NEW_ROW.ID FROM dual;
END;

When it is executed in Oracle SQL Developer there is no error and the trigger is created successfully.

But when it is executed from one Ant script the script always complain the error:

java.sql.SQLException: ORA-00900: invalid SQL statement.

The complain comes from the semicolon ; after dual. Ant thinks this as the end the SQL statement because when Ant processes the SQL statements bt default it uses the semicolon ; as the delimiter of the each SQL statement.

But in the above the statement which creates the trigger the semicolon is required after dual otherwise the trigger will be created with the compiler error.

No comments:

Post a Comment