My developer has a trigger that I can run, without issue, in 12.1.0.2 sqlplus on my DB server. He, however, cannot get it to run from the application server - it is using the 11.2.0.4 instant client.
The code in question is:
CREATE OR REPLACE EDITIONABLE TRIGGER "ORDER_BROKER"."REQUEST_COUNTER_TRG"
BEFORE INSERT ON REQUEST_COUNTER
FOR EACH ROW
BEGIN
<<COLUMN_SEQUENCES>>
BEGIN
IF INSERTING AND :NEW.REQUEST_COUNTER_ID IS NULL THEN
SELECT REQUEST_COUNTER_SEQ.NEXTVAL INTO :NEW.REQUEST_COUNTER_ID FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/
In 12.1.0.2 sqlplus on the DB server, it compiles without issue.
With the 11.2.0.4 instant client, it fails with:
Warning: Trigger created with compilation errors.
SP2-0042: unknown command "END IF" - rest of line ignored.
SP2-0734: unknown command beginning "END COLUMN..." - rest of line ignored.
SP2-0042: unknown command "END" - rest of line ignored.
It appears to treat the ; at the end of the SELECT REQUEST line as the end of the SQL Block, rather than proceeding to the /
I upgraded to the 12.1.0.2 instant client and it works as expected. Am I missing something obvious and dumb that is invalid code in 11.2.0.4?
If it works in the 12.1.0.2 instant client, I'd suspect it is (was) a bug in the 11.2.0.4 client.
As per you, my 12 instant client has no dramas
C:\oracle\instantclient>sqlinst
SQL*Plus: Release 12.1.0.2.0 Production on Thu May 25 10:00:53 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn scott/tiger@//localhost:1521/db122
Connected.
SQL> create table REQUEST_COUNTER ( REQUEST_COUNTER_ID int );
Table created.
SQL> create sequence REQUEST_COUNTER_SEQ;
Sequence created.
SQL> CREATE OR REPLACE EDITIONABLE TRIGGER REQUEST_COUNTER_TRG
2 BEFORE INSERT ON REQUEST_COUNTER
3 FOR EACH ROW
4 BEGIN
5 <<COLUMN_SEQUENCES>>
6 BEGIN
7 IF INSERTING AND :NEW.REQUEST_COUNTER_ID IS NULL THEN
8 SELECT REQUEST_COUNTER_SEQ.NEXTVAL INTO :NEW.REQUEST_COUNTER_ID FROM SYS.DUAL;
9 END IF;
10 END COLUMN_SEQUENCES;
11 END;
12 /
Trigger created.