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

Fatih K.

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?

Fatih K.

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. So I suppose Oracle 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.

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Why am I getting an Inferred Latch Error?

分類Dev

Why am I getting an error with Find in VBA?

分類Dev

Why am I getting unexpected string error?

分類Dev

Why am I getting an "ArrayIndexOutOfBoundsException" error?

分類Dev

Why am I getting class path resource [src/main/resources/databaseconfig.properties] cannot be opened because it does not exist error?

分類Dev

Why am I getting this error: "error: { expected" in JCreator?

分類Dev

Why am I getting a CASE error when in Data Studio?

分類Dev

Why am I getting a "Task was destroyed but it is pending" error in Python asyncio?

分類Dev

Why I am getting "getuid was not declared in that scope" error?

分類Dev

Why am I getting the error "Identifier ... has already been declared"?

分類Dev

why am i getting this error when reading a .json file?

分類Dev

Why am I getting a "Cannot find module 'jsonLogic'" error?

分類Dev

Why am I getting "Error while validating constraint None"?

分類Dev

Why am I getting an error while using group by with a left join?

分類Dev

Why am i getting error for .format? JAVA FILES

分類Dev

why am i getting this error in simple angularjs program?

分類Dev

Why am I getting an undefined?

分類Dev

Why am I getting this KeyError?

分類Dev

Why am i getting this error when i am trying to insert a string into a list?

分類Dev

Why am I getting this error: Attempted import error: DatePickerField is not exported from /..whatever

分類Dev

Why am I getting "undefined reference to sqrt" error even though I include math.h header?

分類Dev

Why am I getting a low error before I did any optimization?

分類Dev

I am trying to search up to the right diagonally in my multidimensional array in java. Why am I getting index out of bounds error?

分類Dev

I'm getting a startup error on a BlobStorage Trigger

分類Dev

Why am I getting a NoClassDefFoundError in the pdfXFA example?

分類Dev

Why am I getting "svn: command not found"

分類Dev

Why am I getting this deprecated warning?! MongoDB

分類Dev

Why am I getting an IndexError from a for loop?

分類Dev

why am I getting breakpoint compilation errors

Related 関連記事

  1. 1

    Why am I getting an Inferred Latch Error?

  2. 2

    Why am I getting an error with Find in VBA?

  3. 3

    Why am I getting unexpected string error?

  4. 4

    Why am I getting an "ArrayIndexOutOfBoundsException" error?

  5. 5

    Why am I getting class path resource [src/main/resources/databaseconfig.properties] cannot be opened because it does not exist error?

  6. 6

    Why am I getting this error: "error: { expected" in JCreator?

  7. 7

    Why am I getting a CASE error when in Data Studio?

  8. 8

    Why am I getting a "Task was destroyed but it is pending" error in Python asyncio?

  9. 9

    Why I am getting "getuid was not declared in that scope" error?

  10. 10

    Why am I getting the error "Identifier ... has already been declared"?

  11. 11

    why am i getting this error when reading a .json file?

  12. 12

    Why am I getting a "Cannot find module 'jsonLogic'" error?

  13. 13

    Why am I getting "Error while validating constraint None"?

  14. 14

    Why am I getting an error while using group by with a left join?

  15. 15

    Why am i getting error for .format? JAVA FILES

  16. 16

    why am i getting this error in simple angularjs program?

  17. 17

    Why am I getting an undefined?

  18. 18

    Why am I getting this KeyError?

  19. 19

    Why am i getting this error when i am trying to insert a string into a list?

  20. 20

    Why am I getting this error: Attempted import error: DatePickerField is not exported from /..whatever

  21. 21

    Why am I getting "undefined reference to sqrt" error even though I include math.h header?

  22. 22

    Why am I getting a low error before I did any optimization?

  23. 23

    I am trying to search up to the right diagonally in my multidimensional array in java. Why am I getting index out of bounds error?

  24. 24

    I'm getting a startup error on a BlobStorage Trigger

  25. 25

    Why am I getting a NoClassDefFoundError in the pdfXFA example?

  26. 26

    Why am I getting "svn: command not found"

  27. 27

    Why am I getting this deprecated warning?! MongoDB

  28. 28

    Why am I getting an IndexError from a for loop?

  29. 29

    why am I getting breakpoint compilation errors

ホットタグ

アーカイブ