DataGrip issues/bugs all around Oracle create trigger command.

Answered

Hi Team,

For your information: DataGrip has issues/bugs for Oracle data source related to triggers.

1. Unexpected behavior -> Why create trigger command execution in console asks for params input? FYI :new and :old is standard option to work with columns' old/new values within trigger. Just in case, Oracle treats & as parameter input, not : as in PostgreSQL. At my point of view DataGrip should use database specific symbol for parameter input. Please check test source code in Appendix A. Plus screenshot.

2. Definitely bug -> If you try to create trigger as in sample code below (no matter will it be run in console or as file execution) you will get error message. Though if you run the command in SQL*Plus - it works as expected. Trigger will be created. Such behaviour of the tool is unacceptable.

[60000][604] ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 14
ORA-04079: invalid trigger specification

3. Issue with syntax parsing -> If create trigger command contains list of affected columns (... of column, column ...) DataGrip marks this statement with two issues. If we remove the section - everything is fine. Please check test source code in Appendix A. Plus screenshot.

4. Issue with syntax parsing -> By the way, INSERTING, UPDATING and DELETING commands are also unknown for syntax parsing.

 

Best regards,

Kostyantyn

 

DataGrip 2018.3
Build #DB-183.4284.162, built on November 27, 2018
Licensed to Kostyantyn Krakovych
Subscription is active until September 25, 2019
JRE: 1.8.0_152-release-1343-b15 x86_64
JVM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
macOS 10.14.1

 

Appendix A

create table test_table
(
column_a number,
column_b varchar2(10 char),
column_c date
);

create or replace trigger br_test_trigger
before insert or update or delete
of column_a
, column_b
on test_table
for each row
begin
if inserting
then
dbms_output.put_line(:new.column_a);
end if;

if updating
then
dbms_output.put_line(:old.column_a);
end if;

if deleting
then
dbms_output.put_line(:old.column_c);
end if;
end br_test_trigger;
/

 

 

 

5 comments
Comment actions Permalink

Hi Team,

To be on the same page - the mentioned issues/bugs with Oracle trigger creation in DataGrip IDE blocks development.

1. The task https://youtrack.jetbrains.com/issue/DBE-4844 is similar to point #1 and closed though it does not work now.

4. The task https://youtrack.jetbrains.com/issue/DBE-883 is similar to point #4 and closed though DataGrip does not recognize key words in within trigger body.

Why DataGrip does not allow to create trigger from valid source code?

I can ignore syntax issues, interface glitches but at the moment I am forced to use SQL*Plus just because I need to create some triggers and DataGrip fails to help me with it.

Ought I create a new bug in tracker with HIGHEST BLOCKING priority to address the issue when I can not create trigger from IDE?

 

Best regards,

Kostyantyn

0
Comment actions Permalink

Hi @Kostyantyn Krakovych,


1. I created issues based on your description:

According to https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm , there is an error in your trigger syntax. The valid is:

create or replace trigger br_test_trigger
before insert or delete or update
of column_a, column_b on test_table
for each row
begin
if inserting
then
dbms_output.put_line(:new.column_a);
end if;

if updating
then
dbms_output.put_line(:old.column_a);
end if;

if deleting
then
dbms_output.put_line(:old.column_c);
end if;
end br_test_trigger;
/

and DG parses it correctly:


Furthermore Oracle SQL Developer shows an error too:


on execution shows user parameters input:



...

0
Comment actions Permalink

2. I case I want to execute invalid SQL script from within a SQL file I'll get:



Where create_a_trigger_with_an_error.sql contains invalid trigger script. So, it's a valid behavior.

...

0
Comment actions Permalink

3. See https://youtrack.jetbrains.com/issue/DBE-7563 . I hope we'll implement more accurate parsing to point exactly to the place with error.


4. It was caused with errors in parser earlier. See 3 for details.

 

>> Why DataGrip does not allow to create trigger from valid source code?

See 1 and trigger creation documentation https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm for proper syntax.


Thanks a lot for your feedback.

0
Comment actions Permalink

Hi @Vasil Chernov,

Thank you for detailed clarification.

I agree with you - syntax is not valid. I missed the point.

Meantime to be on the same page: SQL*Plus (11.2.0.4) and SQL Developer (Version 18.3.0.277 Build 277.2354) both work like a charm with the invalid syntax though SQL Developer does point on syntax issue.

Please check:

  • Appendix A - SQL*Plus execution.
  • Screenshot - SQLDeveloper trigger creation from worksheet panel without any param input requests. Simply selected trigger body and executed the selected statement.

 

Best regards,

Kostyantyn

 

Appendix A

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 25 16:54:54 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> connect XXXXX
Enter password:
Connected.
SQL> select count(1) from user_objects t where t.status <> 'VALID';

COUNT(1)
----------
0

SQL> create or replace trigger br_test_table
before insert or update or delete
of column_b
, column_c
on test_table
for each row
declare
...
end br_test_table;
/
101 102 103 104
Trigger created.

SQL> select count(1) from user_objects t where t.status <> 'VALID';

COUNT(1)
----------
0

SQL> select object_name, object_type, status from user_objects t where t.object_name = 'BR_TEST_TABLE';

OBJECT_NAME OBJECT_TYPE STATUS
------------- ----------- ------
BR_TEST_TABLE TRIGGER VALID
0

Please sign in to leave a comment.