oracleoracle11gdatabase-trigger

Why am I getting "A sql/plsql compilation error occured." error because of semicolons in trigger creation?


I am trying to create a trigger using 'Oracle SQL Developer' in 'Oracle 11G'. But every time a try it I get 'ORA-24344: success with compilation error' message and my trigger gets partially created with errors.

My Oracle SQL Developer version is '3.0.04' and server version is 'Oracle Database 11G Release 11.2.0.4.0 (64-bit)'. Every time I try creating a trigger I get the same message and I realized that it stops execution at the first semicolon. I tried everything written in here that looks like having the same problem, but none of them helped.

For example I am trying to create a trigger like:

create or replace
TRIGGER TEST_TRG 
BEFORE INSERT ON TEST_TABLE2 
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE testvar number;
BEGIN 
testvar := test_sequence.nextval;
INSERT INTO TEST_TABLE(id,data) VALUES(testvar,:NEW.id);
END TEST_TRG;
/

I am getting:

Error starting at line 0 in command:
CREATE OR REPLACE TRIGGER TEST_TRG 
BEFORE INSERT ON TEST_TABLE2 
REFERENCING NEW AS NEW FOR EACH ROW
DECLARE testvar number
Error report:
SQL Command: trıgger TEST_TRG
Failed: ORA-24344: success with compilation error
24344. 00000 -  "success with compilation error"
*Cause:    A sql/plsql compilation error occurred.
*Action:   Return OCI_SUCCESS_WITH_INFO along with the error code
Bind Variable "NEW" is NOT DECLARED
anonymous block completed

As you can see, it thinks that the statement ends at first occurrence of semicolon. To prove my suspicion I performed one more test removing the unnecessary DECLARE statement:

CREATE OR REPLACE TRIGGER TEST_TRG 
BEFORE INSERT ON TEST_TABLE2 
REFERENCING NEW AS NEW FOR EACH ROW
BEGIN 
INSERT INTO TEST_TABLE(id,data) VALUES(test_sequence.nextval,:NEW.id);
END TEST_TRG;
/

Which caused the same error but at different position (again at first semicolon):

Error starting at line 0 in command:
CREATE OR REPLACE TRIGGER TEST_TRG 
BEFORE INSERT ON TEST_TABLE2 
REFERENCING NEW AS NEW FOR EACH ROW
BEGIN 
INSERT INTO TEST_TABLE(id,data) VALUES(test_sequence.nextval,:NEW.id)
Error report:
SQL Command: trıgger TEST_TRG
Failed: ORA-24344: success with compilation error
24344. 00000 -  "success with compilation error"
*Cause:    A sql/plsql compilation error occurred.
*Action:   Return OCI_SUCCESS_WITH_INFO along with the error code

Error starting at line 6 in command:
END TEST_TRG
Error report:
Unknown Command

So the question is obvious. Why can't I create those triggers and what should I do to create them?


Solution

  • Thanks to answer of Littlefoot, though it wasn't the root cause of my problem, he helped me see the problem. I tried both of our queries and seen that the trigger works if I use lowercase letters but not in uppercase letters.

    Then I realized the error message having a line like that:

    SQL Command: trıgger TEST_TRG
    

    So I have seen that if I use uppercase letters my version of 'Oracle SQL Developer' converts it to lowercase letters. But as you can see instead of using ASCII character set it is using my local character set (Turkish charset) and converts 'I' to 'ı' and also converts 'İ' to 'i'. So in my OS, Oracle client doesn't recognize the 'TRIGGER' as a valid keyword. I should either use 'trigger' or 'TRİGGER'.

    Another solution was changing "Regional format" setting of Windows OS from "Turkish (Turkey)" to "English (United states)". I checked all other regional/localization settings but only changing "Regional format" setting works. I suppose "Oracle SQL Developer" checks that setting to decide client character set.

    It might also be related to version of the "Oracle SQL developer". But admin policy of my work computer doesn't allow me to update it, so I couldn't try it.